Comparison
SCD type 1vsSCD type 2
SCD type 1
you overwrite the old value, and last year's report now shows the customer as having always been in Bristol.
Update the attribute in place and keep no history. It is the cheapest and it is right whenever the change is a correction rather than an event — a misspelled name, a fixed category. It is wrong whenever someone will re-run an old report and expect it to match what they printed, which is why it silently rewrites history and why nobody notices until an audit.
Full entry →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 →