SCD Type 2 keeps every historical version of a dimension row. Each row has effective_from, effective_to, and is_current — and only one row per natural key has is_current = TRUE.
The two-statement pattern
1) MERGE: close out current rows whose attributes changed. 2) INSERT: add new current rows for new keys and changed keys. Doing both in one MERGE works on some engines but is brittle.
Three bugs to watch for
- Off-by-one on effective_to: closed-out rows must end at the same timestamp the new row starts (or one second before, if your reporting expects exclusive bounds).
- Multiple is_current rows after a partial run: always assert exactly one current row per key.
- NULL attribute changes: 'old IS NULL AND new = X' counts as a change; '= comparison' alone does not.
Run the SCD2 example in the playground to see lineage from the staging snapshot into both the closed-out historical rows and the new current row.