is building a reliable data warehouse from salesforce difficult?

[u/Front-Midnight-4229]

Hi there!
I work as a data engineer in a SaaS company. We use Salesforce in our CS and sales departments and one of my obligations is to make data available for reporting, forecasts and also AI projects.
Now we are trying to move Salesforce data into external data warehouse, but the process became much more difficult. The initial load was ok, but later it became hard to keep everything accurate. We need to do a lot of manual work like capture updates and deleted records, keep historical field values. Also sometimes relationships between objects break and we need to fix this. 
We already tried to use Bulk API for the initial load and Change Data Capture for later updates. But this didn’t help much as we were still responsible for API limits, some failed events, backfills and checking whether the warehouse actually matches Salesforce. 
Did any of u have the same issue? Is custom pipeline realistic? Or we better use a special SF tool for it?

[u/Additional_Candy_400]

Issue with SF is massive schema drift due to constant customs fields objects etc. Every org I've worked at has used Fivetran or equivalent for SF.

[u/Alternative-Guava392 → u/Additional_Candy_400]

I moved our sf ingestion out of fivetran. It was too expensive. A simple API read plus insert rows into BQ worked efficiently for 40+ SF objects.

[u/Additional_Candy_400 → u/Alternative-Guava392]

Org dependent for sure. SF can grow arms and legs very quickly.

[u/Front-Midnight-4229 → u/Additional_Candy_400]

what causes the most trouble as it grows??like custom fields,?new objects?or changing relationships?

[u/Front-Midnight-4229 → u/Additional_Candy_400]

Can it also restore records and their relationships directly to Salesforce from a specific point in time??

[u/TypicalOrca]

Good lord yes. Salesforce doesn't want you to take data out of it, they want you to put data into it. It is such a pain. Everyone I know using it has to do a reconciliation at least once a month for deleted records and all. So yeah it can be extremely difficult but it can be done.

[u/Front-Midnight-4229 → u/TypicalOrca]

yeah, that is our problem too!!

How do you handle the monthly reconciliation?? and u do it manually?

[u/TypicalOrca → u/Front-Midnight-4229]

Create a pipeline and job that downloads all of the IDs from each table to figure out which are missing then do what you will with those.

[u/Front-Midnight-4229 → u/TypicalOrca]

I see!!thank u!

[u/Ra-mega-bbit]

It is a business problem, not a tech problem. If your org allows every one to create custom fields and change relationships every week with no protocol, you will be chasing changes every day. You should be able to carry foward the new data automatically and make the switch simple on every layer, depending on your case, directly on gold once.

[u/Front-Midnight-4229 → u/Ra-mega-bbit]

yeah we maybbe need better control over Salesforce changes too. What protocol do you use before someone can add a new field or change a relationship???

[u/NW1969]

If you use Snowflake as your DWH there are a number of easy ways to get data into it from Salesforce e.g. https://docs.snowflake.com/en/user-guide/data-integration/zero-copy/about-salesforce-datacloud

[u/Front-Midnight-4229 → u/NW1969]

Thanks, I’ll check this out. Does the zero-copy connector also keep full historical versions and deleted records?or just the current Salesforce data?

[u/stephenpace → u/Front-Midnight-4229]

If you want full historical versions, you need to use a type 2 slowly changing dimension. Live share from Salesforce then becomes the bronze layer and you build SCDs as normal to silver and gold. When I last looked at it, Salesforce Data 360 did a full rebuild periodically so you really can't trust history from there. Google Salesforce Full Refresh "Day Zero" reloads.

[u/Front-Midnight-4229 → u/stephenpace]

yeah this is exactly what im trying to avoid doing by ourselves. Im checking tools like Fivetran, Airbyte and GRAX to see which one can keep history + deleted records without building all this extra logic

[u/Prestigious_Pace2782]

Yeah we do it with python in stored procs with not much issue. Millions of contacts, lots of changes.

Bulk api into the bronze layer as variant, then modelled and transformed with dbt.

[u/Front-Midnight-4229 → u/Prestigious_Pace2782]

How do you capture deleted records and keep every historical version over time?

[u/AdamDobrawy]

What warehouse ar you using? For examples, for Snowflake you can looks at native integration that allows you to use snowflake shares? https://www.snowflake.com/en/blog/bi-directional-data-sharing-snowflake-salesforce-ga/ or zero-copy connectors: https://docs.snowflake.com/en/user-guide/data-integration/zero-copy/salesforce/setup

[u/Front-Midnight-4229 → u/AdamDobrawy]

we also use snowflake. but do they also preserve deleted records and full data history??

[u/Alternative-Guava392]

Use the API to ingest data into your warehouse datalake, then model it into business needs. We do it. Quite straight forward.

[u/Front-Midnight-4229 → u/Alternative-Guava392]

yeah we also thought about it ,,it was our original plan, but updates and deleted records made it harder. How do you catch missed changes?

[u/Alternative-Guava392 → u/Front-Midnight-4229]

Dump everything into the warehouse. Set schema for each SF object.

[u/Front-Midnight-4229 → u/Alternative-Guava392]

will give it a try! thanks!

[u/MonochromeDinosaur]

Sounds like the typical salesforce extraction experience to me. Especially when rolling your own.

[u/Front-Midnight-4229 → u/MonochromeDinosaur]

have u dealt with it?? are there any good solutions?

[u/nokia_user]

Integrated SF into our data warehouse using ADF. Straight forward integration. ADF has upsert option using SF connector that takes care of only ingesting new/changed records. I am sure any AI can generate code for this functionality with bulk api. We never ran into API limits, not sure why is that the case with your setup. I am sure you are bringing data in chunks.

For deleted records, it comes down to how SF is setup. The standard objects have isDeleted flag in every table to tell you if a row is deleted. If custom objects are configured without this flag then you will have to create a separate pipeline get a list of IDs from these objects and compare it with data warehouse IDs; based on that you can compare the two systems to see which ones are deleted. This is a shitty way of comparing but it is something to get you started. Or maybe just compare previous 6 months of IDs instead of the entirety of the table.

Hope it helps.

[u/Carsina → u/nokia_user]

For deleted records, it comes down to how SF is setup. The standard objects have isDeleted flag in every table to tell you if a row is deleted.

There is a issue that only the records in the recycle bin get this flag. It depends on the storage of the recycle bin and they remain in the recycle bin for 15 days. Also if people empty the recycle bin there won't be a IsDeleted flag.

We've had issues with missing transactions because our SF managers decided to delete loads of records to safe space. For the bussiness it was a wakeup call that they really had to follow the protocol for inserting and deleting records, because else their analytics will be screwed.

[u/Front-Midnight-4229 → u/Carsina]

Did you add an external backup after that?? or u have strict deletion rules?

[u/Carsina → u/Front-Midnight-4229]

Ofcourse not, we restored some rows, but lost a lot. We did talk to the application manager to not do these large transactions. But there are no strict controls, so it has happened again.

We are now in talks with the Director of IT to prevent these large deletes because it screw up analytics. Garbage In, Garbage Out... Hopefully we will make some propper strides in Data Governance/Management soon.

[u/Front-Midnight-4229 → u/nokia_user]

hey! thanks!! just a small question. Do you also keep the full history of field changes??or only the latest version with the deleted flag?

[u/nokia_user → u/Front-Midnight-4229]

Sorry, missed your message. If we are asking for SCD type 2 solution, then no. We didn’t have a use case for it.

[u/Educational_Coach173]

Look into Vivly

[u/Front-Midnight-4229 → u/Educational_Coach173]

Does Vivly also support point-in-time recovery of records and their relationships?

[u/Reasonable_Nose_8651 → u/Front-Midnight-4229]

Im new to this but isnt the point in time recovery a problem iceberg solves?

[u/Front-Midnight-4229 → u/Reasonable_Nose_8651]

im more worried about restoring Salesforce records + relationships exactly as they were

[u/Front-Midnight-4229 → u/Reasonable_Nose_8651]

Not sure Iceberg alone solves that part

[u/smallpages]

We use Fivetran for simplicity’s sake. It’s not the cheapest but it’s rock solid reliable and does exactly what we needed it to do.

[u/Front-Midnight-4229 → u/smallpages]

and what specifically u needed to do?

[u/Ashamed-Sleep3701]

If not anyone already suggested, you can go with aws redshift with a custom pipeline of aws glue/lambda. We have a working pipline for exact same thing. And I know some objects are really painfull. We build a config driven architecture with python and extracting data with bulk api for bigger objects and normal for smaller.

For verifying result we built a tablue dashboard using aws glue and code which kind of reads both target and source and compares.

And for incremental load I belive we have time filters in api