I’m working on creating dbt models with the materialization set to incremental. During the initial load, the data is processed correctly. However, on subsequent runs, if there are changes in the source data for the same primary key, the target table should mark the existing record with DW_CURRENT_FLAG = ‘N’, update the DW_UPDATED_DATE, and insert a new record with the updated data for the same primary key and the same surrogate key.
The issue I’m encountering is that the dbt incremental process marks the old record with DW_CURRENT_FLAG = ‘N’, but it also retains the same old record with DW_CURRENT_FLAG = ‘Y’. I do not want the same record to persist with DW_CURRENT_FLAG = ‘Y’.
Has anyone implemented this process for handling SCD Type 2 using the incremental process in dbt?
Note: I am aware that SCD Type 2 can be handled using snapshots, but I need to maintain some data warehouse audit columns, which is why I am customizing my model and opting for the incremental model.