# Slowly Changing Dimensions Type 2, Explained Honestly

> Surrogate keys, validity intervals, and the difference between observed history and true source history.

- Source: https://akshaythakare.com/notes/data-warehousing/scd-type-2-explained
- Topic: Data Warehousing
- Level: intermediate
- Reading time: 2 min
- Tags: data-warehousing, scd2, dimensional-modeling, interview
- Updated: 2026-10-11

## 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`

*(Interactive illustration “Scd Timeline”: see the web page.)*

## 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.

> **Note: Label it correctly**
>
> Call the attribute "tier observed at first publication", not "tier at order time" — unless your source provides real change history (CDC, audit tables) and you load it.

## 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.