Slowly Changing Dimensions Type 2, Explained Honestly

The problem

Customer C001 was Silver last month and is Gold today. Should last month's sales report under Silver or Gold?

• Type 1 overwrites: all history now says Gold
• Type 2 keeps both versions with validity periods, so facts can point to the version that applied

The shape of a Type 2 dimension

customer_sk	customer_id	loyalty_tier	valid_from	valid_to	is_current
1	C001	Silver	2026-09-01	2026-10-01	false
7	C001	Gold	2026-10-01	9999-12-31	true

• customer_id identifies the customer; customer_sk identifies a version
• Use half-open intervals: valid_from <= t < valid_to, so versions never overlap
• Facts store customer_sk, not customer_id

scd type 2 · customer C001 Replay

batch 1 · 1 Sepbatch 2 · 1 Octnow

sk 1 · Silver

valid_to = 1 Oct · is_current = false

sk 7 · Gold

is_current = true

Batch 2 observes a tier change → close the current row, insert a new one

fact_sales(O100) → customer_sk = 1 (stays Silver — “tier observed at first publication”)

The algorithm

1. Compute a hash of the tracked attributes for incoming records
2. For current rows whose hash differs: set valid_to = batch_upper, is_current = false
3. Insert a new current row for changed and brand-new customers with valid_from = batch_upper
4. Run it in the same transaction as the fact load

Tests worth having: exactly one current row per customer, no overlapping intervals, re-running a batch changes nothing.

Observed history ≠ true history

A batch pipeline only sees what each extraction observes. If C001 went Silver → Platinum → Gold between two batches, you will only ever record Silver → Gold, and the change is dated at your batch, not when it happened in the source.

Late-arriving facts

A fact that arrives after a dimension change gets the version known at ingestion unless you deliberately look up the version valid at the fact's event time. Decide which one you want and document it.