Slowly changing dimensions

Lesson 5 of 5 in Data Modeling and Schema Evolution.

A customer moves from the West region to the East. What were last year's sales?

That question has no technically correct answer — it has a business answer, and the slowly changing dimension types are the four ways of encoding whichever one your organisation means.

Type 0 never changes the stored value. Correct for attributes that genuinely cannot change: a date of birth, an original signup channel.

Type 1 overwrites. History is restated as if the customer had always been in the East, so last year's revenue moves regions. One row per entity, cheapest load, and every report run before the change no longer reproduces.

Type 2 closes the current row and inserts a new one with its own surrogate key and validity dates. Each fact stays joined to the attributes that were true when it happened, so last year's sales remain under the West. Full history, and it costs something on every read afterwards.

Type 3 adds a previous_region column beside the current one. It answers exactly one before-and-after comparison, and the second change destroys the first one's evidence.

The question that picks between them is never technical. It is whether the business reports history as it was or as it is now — and both are legitimate. Regulatory reporting and commission calculations usually want as-it-was; a current-state operational dashboard usually wants as-it-is. A warehouse serving both keeps a Type 2 dimension and exposes a current-row view over it, which is the answer worth remembering because it is the one that satisfies both without a second copy.

The four types, and what each one costs after it is loaded
Type	What a change does	History	Cost on every read afterwards
Type 0	nothing — the value is never updated	not applicable: the attribute cannot change	none
Type 1	overwrites the column in place	none — every report is restated	none, which is exactly why it is chosen and why it misleads
Type 2	closes the current row, inserts a new one with a new surrogate key	exact — each fact joins to the version in force at its time	every lookup must qualify the version — `is_current`, or a date-range join
Type 3	moves the old value into a `previous_*` column	one step only, and the second change destroys the first	none, and the column needs explaining to everyone who reads the table
Type 2 + a current view	as Type 2, with a view selecting `is_current = true`	exact, and the current state is one object away	none for current-state consumers; the qualification lives in the view

A Type 2 row carries the natural key, a new surrogate key, effective_from, effective_to — conventionally 9999-12-31 rather than null on the open row, so range predicates work without special-casing — and usually an is_current flag. The load closes the old row and inserts the new one, which is naturally one MERGE statement: when matched and the attributes differ, set effective_to and clear is_current; when not matched, insert.

Three costs deserve stating plainly, because they are what a question about "the downside of Type 2" is reaching for.

Every lookup must qualify the version. A query joining customer_id to a Type 2 dimension gets several rows back and silently multiplies its totals. Either is_current = true for current-state reporting, or a date-range join (fact.order_date BETWEEN dim.effective_from AND dim.effective_to) for as-it-was reporting. Forgetting it does not error; it inflates.

The dimension grows with change rather than with population. A hundred thousand customers whose attributes change monthly become 1.2 million rows a year — which is also why a volatile attribute probably does not belong in a Type 2 dimension at all, and belongs in the fact as a degenerate attribute or in its own mini-dimension.

The load has to detect change. Which means reading the current rows on every run and comparing attributes — often with a hash of the tracked columns, so one comparison replaces forty. That read is a real cost on a large dimension, and it is why change data capture from the source is worth having.

On a data lake, all of this is what the table formats made possible: MERGE INTO on an Iceberg table performs the close-and-insert in one atomic commit, so there is no window in which a reader sees two current rows or none. Before them, a Type 2 load on a lake was a full partition rewrite with a visible seam.

The Type 2 bug that produces larger numbers, not an error

-- wrong: returns one row per version of each customer
SELECT d.region, SUM(f.amount)
FROM fact_revenue f
JOIN dim_customer d ON f.customer_id = d.customer_id
GROUP BY d.region

A customer with three versions contributes its revenue three times, once per version, and the total is simply bigger. No error, no warning, and the figure is plausible enough to be published.

Two correct forms, and which one you want is a business decision rather than a style choice:

-- as it is now: join on the current version
JOIN dim_customer d
  ON f.customer_id = d.customer_id AND d.is_current = true

-- as it was: join on the version in force at the fact's date
JOIN dim_customer d
  ON f.customer_id = d.customer_id
 AND f.order_date BETWEEN d.effective_from AND d.effective_to

And the structural defence: store the surrogate key on the fact and join on that, which pins each fact to one version by construction and makes the unqualified join impossible to write. The natural-key join is a convenience that the model was designed to make unnecessary.

Key terms: Slowly changing dimension (SCD), Type 2 dimension, MERGE INTO, Surrogate key, Dimension table

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.