System Design · medium
How do you model a Type 2 slowly changing dimension, and how do you test it?
Asked in Analytics Engineer interviews, in the System Design round.
Short answer
One row per version of the entity with valid_from / valid_to and a current flag, and a surrogate key per version so facts join to the state as of the event. Test that exactly one version is current per natural key, that the validity windows never overlap, and that valid_from < valid_to, dbt unique/not_null plus a custom overlap test.
How to answer it
Definition
A Type 2 dimension keeps history by writing a new row each time a tracked attribute changes, rather than overwriting. Each row is valid for a window of time, so you can answer what was true when something happened.
The shape
customer_key surrogate, unique per VERSION
customer_id natural key, repeats across versions
segment the tracked attribute
valid_from inclusive
valid_to exclusive, far-future sentinel for the open row
is_current convenience flag, derivable from valid_tocustomer_key customer_id segment valid_from valid_to is_current
90001 4471 Standard 2025-01-01 2025-03-01 false
90002 4471 Premium 2025-03-01 9999-12-31 trueHow it updates
Compare incoming rows against the current version. Where a tracked attribute differs, close the old row by setting valid_to to now and insert a new row with valid_from now and the sentinel valid_to. In dbt this is a snapshot, with a strategy of either check on named columns or timestamp on an updated_at column.
Decide deliberately which columns are tracked. Making every column Type 2 means a new version whenever a phone number is corrected, and the table grows without adding analytical value.
How to test it
1. No overlapping intervals per natural key
two versions of one customer must not both cover the same instant
2. No gaps
the intervals for one customer are contiguous from first to last
3. Exactly one current row per natural key
the count where is_current is true must equal 1
4. is_current agrees with valid_to
true if and only if valid_to is the sentinel
5. valid_from < valid_to on every row
6. Fact row count unchanged after joining
the range join must not duplicate or drop factsTests 1 and 3 are the ones that catch real bugs. The overlap test in particular is what stops a fact joining to two versions and double counting.
Key points
- One row per version, with half-open intervals so boundaries belong to exactly one version.
- A far-future sentinel rather than NULL for the open row, so range joins work without special cases.
- Choose the tracked columns deliberately; not every change deserves a version.
- Facts join on a range condition against the event timestamp, not on is_current.
- Test for overlaps, gaps and exactly one current row. Those three catch most breakage.
FAQ
- Type 1, 2 or 3? Type 1 overwrites and keeps no history. Type 3 keeps only the previous value in a second column. Type 2 is the full-history option.
- Does dbt do this for me? Snapshots implement it, and you still own the tests and the choice of tracked columns.
- What about very high churn? Consider Type 1 for volatile attributes and Type 2 only for the ones analysis depends on.
The 30-second answer
Each change to a tracked attribute writes a new row rather than overwriting, with valid_from and valid_to marking the window it applied to, an is_current flag, and a surrogate key unique per version rather than per customer. I would use half-open intervals, valid_from inclusive and valid_to exclusive, and a far-future sentinel rather than NULL on the open row.
In dbt that is a snapshot, and I would choose the tracked columns deliberately, because making everything Type 2 creates a new version every time a phone number is corrected.
For testing, the three that catch real bugs are no overlapping intervals for a natural key, no gaps between them, and exactly one current row per key. I would also assert that joining a fact table does not change the fact row count, since an overlap shows up as double counted revenue.
In the interview
- What they are checking: the version key, the half-open window, and that you name the overlap and one-current tests.
- Common mistake: joining facts on the natural key and filtering is_current, which reports today's attributes for all of history.