Comparison
SCD type 2vsSurrogate key
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 →Surrogate key
the dimension row has a meaningless integer id of your own invention, which is why the source renumbering its customers did not break anything.
A key generated by the warehouse, carrying no business meaning, used to join facts to dimensions. It insulates you from the source changing its identifiers, it lets one logical entity have several rows over time, and it is short, which matters when it is repeated across a billion fact rows. It is the enabling trick for type-2 history: without it, there is no way for a fact to point at the version of the customer that existed when the fact occurred.
Full entry →