Integration design: ETL vs. ETL + Apex

Question

We need to load large volumes of data from external vendors into our Salesforce org on a daily basis. Because the format in which each vendor provides the data is different, we use an ETL tool to transform into a consistent format before loading into Salesforce.

We're unsure whether to have the ETL tool integrate with the complex data model with which the core users interact directly or whether it's best to capture the incoming data as a custom object and use queueable jobs to integrate the data into the core objects.

The benefits we see in using the ETL tool exclusively are:

1. Transformation/load logic easier to maintain
2. Cheaper long-term

The benefits we see in loading to a custom object, then processing via Apex are:

1. Reduction of cross-system dependencies
2. Protection against destructive changes (since dependency is maintained in Salesforce)
3. Retention of original incoming data for maximum traceability

The transaction scope will be limited to inserts and updates, but the updates will likely require querying (because the source data is very different, there's no consistent external ID). And this is a large data volume org.

At the moment, we are inclined towards the 2nd approach (log into custom object and process). From an integration design perspective, what additional factors should we keep in mind when deciding between these two approaches?

Answer 1

I have dealt with this kind of situation before while migrating data from multiple systems to Salesforce and we preferred ETL tool to do all the transformation and then load into appropriate Salesforce objects.

I favor ETL solution even though you are dependent on some other ETL software to do this transformation than apex because if you do use Apex here are the issues you could run into,

• If you dont insert data directly to the core objects from ETL and rely on Apex to do so, one issue is the overhead of processing time. Because before you bring the data to Salesforce from ETL tool, you will have to do some level of clean up of the data at Transform stage of ETL to make sure data is clean (no duplicates, proper fields types etc.). Once you are done with clean up you insert data into salesforce where you queueables runs and that again could be a lengthy process depending on the amount of data and how many objects are involved.

• To build a scalable system keeping a huge data set in mind, let's say there are relationships that exists in the data (both master detail and look up), in this case, you also have to capture this relationship into some custom objects while inserting this into Salesforce then your queueable has to pick up all related custom objects, make additional queries to get appropriate parent records which adds to the processing time.

• Also, because you mentioned this is a large data volume org, now you have problems of maintaining the data in Salesforce. Once you run the queueables/batch jobs, what do you do with the data that was inserted by ETL in custom objects? Keeping the old data (that has been processed by queueables into core objects) into Salesforce adds to the storage cost as well.

All this time, users can't really access the data anyway since it's not ready to be consumed at the core objects level where users are actually expecting records.

If you move all the operations of mapping parent-child, cleaning up data to ETL, the end product is clean data that is ready to be ingested in Salesforce core objects and users can directly start using it.

In terms of updating the same data, you can maintain an external Id field on all core objects and ETL tool can maintain a copy of ONLY final processed dataset, every update from ETL just simply updates using bulk API and External Id.

I hope this helps you making the right decision.

I will add a few points if I can think of, I am trying to think about the sharing rules and indexing as well, I doubt if those will really have a different impact based on the approach you choose.