❄️ Warehouse & Modeling · Chapter 4

Slowly Changing Dimensions — Type 1, 2, 3

A driver moves from Standard to Premium. Should last year's rides now show up as Premium revenue? Your answer is an SCD type — and picking it silently is how finance and ops end up with different numbers.

Study this lesson free in the app →

The basics

A Slowly Changing Dimension (SCD) is a strategy for what happens to a dimension row when the real-world thing it describes changes — a driver's tier, a rider's home city, a zone's boundary.

Why it exists: facts are history (a ride happened on 3 March), but dimensions describe things that keep changing. If you simply overwrite the driver's tier, every past ride is re-labelled with today's tier, and last year's "Premium revenue" number changes every time a driver is promoted. Sometimes that is what you want. Often it is not.

The mental model: for each attribute, ask "when it changes, should old facts see the old value or the new one?"

• Type 1 — overwrite. History is rewritten; old facts see the new value.
• Type 2 — add a new row (new surrogate key) with validity dates. Old facts keep pointing at the old row.
• Type 3 — keep a previous_value column. Exactly one step of history.

Explained

On 1 July, driver Banteilang (DRV-0042) is promoted from Standard to Premium.

On 2 July, the "Premium revenue, H1 2026" number in the board deck goes up by ₹4.1 lakh — for a half-year that already ended.

Nobody changed the rides. Somebody overwrote the driver row. That was a Type 1 decision nobody knew they had made.

The school register

A school keeps one card per student.

Type 1 school: when Daphi moves from Section B to Section A, they rub out B and write A. Last year's sports-day results now say Section A won a race it never ran.

Type 2 school: they file a new card — Daphi, Section A, from 1 July — and stamp the old card valid until 30 June. Old results stay with the old card.

Type 3 school: one card, two boxes: current section A, previous section B. Good enough for "how did the students who switched do?" — useless if she switches again.

No approach is wrong. Each answers a different question.

The three types, precisely

Type 1 — overwrite. UPDATE dim_driver SET tier = 'Premium' WHERE driver_id = 'DRV-0042'. One row per driver. Every fact, past and future, sees Premium. Use for corrections and attributes you never analyse over time (phone number, spelling fixes).

Type 2 — add a row. Expire the current row (valid_to = change_ts, is_current = FALSE) and insert a new one with a new surrogate key, valid_from = change_ts, valid_to = '9999-12-31'. Facts loaded before the change keep the old key; facts after get the new key. History is exact.

Type 3 — add a column. previous_tier, tier_changed_on. One row per driver; you can compare current vs previous only. Use for a planned one-off reclassification (zones remapped into new city boundaries) where people want both views side by side.

Related: Type 0 — never update (signup date). Type 4 — current row in the dim, history in a separate table. Type 6 — Type 2 rows that also carry a Type 1 current_tier column, so you can report by historical or current value from one table.

One promotion, three outcomes

Banteilang promoted Standard → Premium on 2026-07-01

TYPE 1        driver_key  driver_id  tier
              17          DRV-0042   Premium     ← old rides now 'Premium'

TYPE 2        driver_key  driver_id  tier      valid_from  valid_to    is_current
              17          DRV-0042   Standard  2025-02-10  2026-07-01  FALSE
              5121        DRV-0042   Premium   2026-07-01  9999-12-31  TRUE
              ↑ new surrogate key; March rides still point at 17

TYPE 3        driver_key  driver_id  tier     previous_tier  tier_changed_on
              17          DRV-0042   Premium  Standard       2026-07-01

Type 2 with one MERGE

MERGE INTO dim_driver d
USING (
  -- every changed driver appears twice: once to expire, once to insert
  SELECT s.driver_id AS merge_key, s.* FROM stg_driver s
  UNION ALL
  SELECT NULL AS merge_key, s.* FROM stg_driver s
  JOIN dim_driver c ON c.driver_id = s.driver_id AND c.is_current
  WHERE c.row_hash <> s.row_hash            -- tier/city actually changed
) src
ON d.driver_id = src.merge_key AND d.is_current
WHEN MATCHED AND d.row_hash <> src.row_hash THEN UPDATE SET
  valid_to = src.updated_at, is_current = FALSE
WHEN NOT MATCHED THEN INSERT
  (driver_key, driver_id, tier, home_city, row_hash,
   valid_from, valid_to, is_current)
VALUES (dim_driver_seq.nextval, src.driver_id, src.tier, src.home_city,
        src.row_hash, src.updated_at, '9999-12-31', TRUE);

The NULL merge_key copy can never match, so it always hits NOT MATCHED → INSERT. The real-key copy matches the current row and expires it. Brand-new drivers match nothing and insert once. (dbt's snapshot does this for you.)

At 80,000 drivers and 2 billion rides

• Detect change with a hash. row_hash = md5(tier || '|' || home_city || ...) over only the Type 2 columns. Comparing one hash beats comparing 15 columns, and Type 1 columns stay out of it so a phone-number edit doesn't spawn a version
• Watch the version explosion. If a weekly-changing field (vehicle insurance status) is tracked as Type 2, 80k drivers become millions of rows. Split fast-moving attributes into their own dimension or mini-dimension
• Facts never get rewritten. That's the whole economy of Type 2: history is preserved by adding dimension rows, not touching 2B fact rows
• Reporting by current value on a Type 2 dim means joining back through driver_id to the is_current row — or adding a Type 1 current_tier column (Type 6) so it's one join
• Backfills and late data need the point-in-time lookup; looking up is_current = TRUE while reloading 2025 rides assigns today's tier to last year

Gotchas

• Joining facts to a Type 2 dim on the natural key — every ride matches every version; revenue multiplies
• valid_to NULL for current rows — then ride_ts BETWEEN valid_from AND valid_to silently drops every recent ride. Use 9999-12-31
• Closed intervals — <= on both ends lets a ride at exactly the change instant match two versions. Use >= from AND < to
• Tracking every column as Type 2 — a typo fix in the driver's name creates a new version and breaks 'drivers who changed tier' counts
• Using is_current when loading old facts — correct only for facts arriving in real time
• Two changes in one batch — if staging has two updates for DRV-0042, de-duplicate to the latest (or process in order), or the MERGE fails with a non-deterministic match

Type 1 rewrites history. Type 2 records it. Type 3 remembers one step.

Type 2 = new surrogate key + valid_from/valid_to + is_current.

Facts carry the key that was true when the ride happened — half-open interval, 9999-12-31, unknown member -1.

Words to know

In two minutes

• SCD types decide, per attribute, whether old facts see the old value or the new one after a change.
• Type 1 overwrites in place: no history, simplest, right for corrections (typo in a name) and attributes nobody analyses historically.
• Type 2 inserts a new row with a new surrogate key and closes the old one with valid_to; facts keep the key that was current at event time.
• Type 3 adds a previous_value column: one level of history, useful for 'before vs after a reorganisation' comparisons.
• Type 2 depends on surrogate keys and on loading facts with a point-in-time lookup (event_ts between valid_from and valid_to).
• Implement Type 2 with a MERGE that both expires the current row and inserts the new version — the classic trick is staging each changed row twice.
• Mix types per column: a driver dim can be Type 2 on tier and city, Type 1 on phone number.

Test yourself

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.