How to implement SCD Type 2

Goal: when a tracked attribute changes, close the old dimension row and insert a new version with a new surrogate key.

Typical columns

• customer_sk (surrogate, unique per version)
• customer_id (natural key, repeats across versions)
• tracked attributes (city, segment, …)
• valid_from, valid_to (or effective_end)
• is_current boolean (optional but handy)

Batch merge sketch

1. Load today's customer source snapshot (natural key + attributes)
2. Compare to current dim rows (is_current = true)
3. Unchanged -> do nothing
4. Changed ->
     a. UPDATE old row: valid_to = today, is_current = false
     b. INSERT new row: new_sk, valid_from = today, valid_to = null, is_current = true
5. New natural keys -> INSERT first version

Fact load rule

When loading facts for event time T, look up the dimension version where natural_key matches and valid_from <= T < valid_to (or is_current carefully for "as of now"). Store that *_sk on the fact.

Tools

SQL MERGE, Spark merge, dbt snapshots / incremental models are common implementations.

Interview tip

Walk through close-old + insert-new, then say facts must store the surrogate key that was valid at event time.