How do I best handle invalid records in a fact table after a MERGE statement?
This is my fact table: schema_name.fact_table
This is my dimensional table (SCD2): schema_name.dim_table
In my dim_table I have a proper SCD2 with unique surrogate keys and the table as a whole is in 1NF. Now, when a change occurs in my SCD2 table, business key stays the same, old row is invalidated and marked as IS_CURRENT = 0 and a new row is added with new values, different surrogate key and same business key.
Moving on to fact table:
1. TRUNCATE TABLE stage.stage_fact
2. INSERT INTO stage.stage_fact
3. MERGE INTO schema_name.fact_table USING stage.stage_fact
Now, you can probably see what is happening - if a change in SCD2 occurs, I will be inserting a new row and no change will happen on now invalid row. In the end, I will end up with a duplicated row in fact table which is a big no-no.
For now I am handling this as I am simply running a DELETE statement (could replace with UPDATE), but this seems more like a hack and not a proper way to do things.
Is there any better way to do things?