I’m seeking help on creating slowly changing dimensions (SCD-2; full history) datasets in Foundry. We have source systems that routinely modify business data and we need to track the changes over time; maintaining a full history while also flagging the latest records.
In typical data warehouse/lakes, e.g. SQL, Databricks, etc., the relevant syntax to create SCD-2 tables would be MERGE INTO: a combination of updates, insertions and flagged deletes on the target table. This approach does not require a full table refresh. In Foundry, it’s unclear how to achieve the same behavior, as while we can emulate it with incremental transforms, we’re limited to appending or replacing the outputs, which is a very expensive operation with larger tables. I assume that many other developers have similar requirements.
What is the recommended approach for SCD-2 datasets in Foundry?
Example reference - SCD-2 in Databricks:
https://www.databricks.com/blog/implementing-dimensional-data-warehouse-databricks-sql-part-2
Edit: clarified that SCD-2 includes updates, insertions and deletions.