Data engineering·Modelling for analysis
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.
SCD type 2
Also calledrow versioning, type 2, effective dating
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.