How to handle SCD type 2 changes - when source records are archived?

[ian.coetzer]

Hi Everyone

I have a problem - I have a dimension made up of 4 source tables.
so - i merge all 4 source tables (starting with a core table and left joining the rest in succession).

Now one of the 4 source tables have been archived!

when i left join all is fine - BUT the attributes coming from this 4th source table is defined as SCD type 2!

So because they are no longer found (they are now NULL = "Unknown" where it was previously something like "House")
The original dimension record is expired! and a new one is inserted - which is fine! as per design - BUT in the new one the specific attribute is 'Unknown' and in the expired dimension record it is 'House' ! I would rather have 'House' appear in the newest record?

[BoxesAndLines]

I keep the old row and mark as logically deleted, meaning it no longer showed up in the source. It still is the current record for joining purposes.

[ngalemmo]

Another option is to propagate those attributes from the current row to the new row.

[hang]

Is it about handling deletes in dimension table? If the dimension record is not found in the source, you should not insert a new row for the deletion. Instead, you mark the current record as "deleted" by a flag, possibly without even expiring the record, so that you can always leave the last record open but marked for filtering purpose.