Data modeling · Foundations

How to implement SCD Type 2

MediumSCD2surrogate keymergebatch

Interview question

How do you implement SCD Type 2 in a batch pipeline?

How to answer

The short answer

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

Typical columns

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

Batch merge sketch

Output
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, / 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.