I have an SCD Type 1 delta table (target) for which I am trying to figure out how to facilitate insert, updates, and deletes. This table is sourced by multiple delta tables, with an SCD Type 2 structure, which are joined together to create the target table. The goal is to implement an incremental load, such that 1) only records in the target table that are impacted by changes to the source tables are updated or deleted, and 2) only new records from the source that are not currently in the target table are inserted.
Each of the SCD Type 2 source tables are updated independently of one another. So it's possible that no tables, one table, or several source tables may have incremented versions since the prior time a load to the target table occurred.
I am trying to think through an process for how to facilitate incremental updates to the target table that does not require a full reload of the table. Thoughts and advice or much appreciated.
