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:
- Transformation/load logic easier to maintain
- Cheaper long-term
The benefits we see in loading to a custom object, then processing via Apex are:
- Reduction of cross-system dependencies
- Protection against destructive changes (since dependency is maintained in Salesforce)
- 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?