Comparison
SCD type 2vsSCD type 3
SCD type 2
the change closes the old dimension row with an end date and opens a new one, so the customer now has three rows and only one is current.
Insert a new row for the new version and mark the old one as no longer current, usually with valid-from and valid-to dates and a current flag. It preserves history exactly, which is what makes point-in-time reporting possible, and it costs you every query that forgot to filter to the current row and therefore counted the customer three times. It is the default choice for anything a regulator or a finance team will look at.
Full entry →SCD type 3
the row has both current_region and previous_region, and the region before that is gone.
Keep a fixed number of prior values as extra columns on the same row. It answers exactly one question — what did this look like before the last change — without the join complexity of type 2. It is rare in practice because the question it answers is rare, and because it quietly caps history at one step, which nobody remembers until someone asks about the step before.
Full entry →