Slowly Changing Dimensions (SCDs)

Dimensions change. Customers move countries, products change categories, employees change managers. How you handle that change is one of the most-asked warehousing interview questions — and one of the most-painful production bugs when you get it wrong.

SCD Type 1 — overwrite

Simplest. When the source changes, you overwrite the dimension row. No history kept.

UPDATE dim_customer SET country = 'IN' WHERE customer_id = 7;

Use when:

• The attribute is corrective (fixing a typo, normalizing values).
• History doesn't matter (historical analyses don't need the prior value).
• Storage / complexity savings outweigh the lost history.

Example: a customer's email is updated when they change it. The old email is irrelevant for analytics. Type 1 is fine.

Risk: you can never answer "what was customer X's country when they placed their Q1 order?" Once you overwrite, the history is gone.

SCD Type 2 — versioned rows

The most common. When the source changes, add a new dimension row with the new attribute, mark the old row as "expired."

dim_customer:
   customer_key  customer_id  country  effective_from  effective_to    is_current
   --------------------------------------------------------------------
   1             cust_7       US       2024-01-01      2026-03-15      FALSE
   18934         cust_7       IN       2026-03-15      9999-12-31      TRUE

Same customer_id, two surrogate customer_keys, one per state.

Fact rows reference the surrogate key, so each fact is forever linked to the customer's state at the time of the event:

fct_orders:
   order_id  order_date    customer_key  ...
   100       2025-12-20    1             -- US (the old version)
   200       2026-04-01    18934         -- IN (the new version)

Use when:

• History matters — "what was country when this order was placed?"
• Analyses by historical state of the dimension are required.
• This is the default for most warehouse-level dimensions.

The query pattern for "current state of all customers":

SELECT * FROM dim_customer WHERE is_current = TRUE;

The query pattern for "customer state at a point in time":

SELECT *
FROM dim_customer
WHERE effective_from <= '2025-12-20'
  AND effective_to   >  '2025-12-20';

SCD Type 3 — carry both old and new

Add columns for "current value" and "previous value" on the dimension row.

dim_customer:
   customer_key  customer_id  current_country  previous_country  country_changed_at
   ----------------------------------------------------------------------------
   1             cust_7       IN               US                2026-03-15

Use when:

• You need a quick before/after comparison (e.g., "customers who changed plan in the last 30 days").
• You don't need the full history, just the immediate previous state.
• You want a single-row-per-entity model.

Rare in practice. Most real designs use Type 2 plus a derived view for "current" if needed.

SCD Type 4, 5, 6, 7

Real Kimball lists more types. The ones worth knowing:

• Type 4: separate history table. Current state in dim_customer; history in dim_customer_history. Lighter dimension, heavier query if history needed.
• Type 6: hybrid — Type 1 + Type 2 + Type 3 combined. Versioned rows AND current-attribute columns AND previous-attribute columns. Maximum flexibility, maximum complexity.

Most real warehouses use Type 1 for some attributes and Type 2 for others, on the same dimension. That's the pragmatic answer to "which SCD type are you using?"

How to decide per attribute

For each attribute on a dimension, ask:

1. Is the attribute corrective or a real change? Corrective → Type 1. Real → Type 2/3.
2. Will any historical analysis need the prior value? Yes → Type 2. No → Type 1.
3. Is the volume of changes manageable? A dimension that changes every second is awkward as Type 2 (millions of rows per real entity).

Apply per-column. A dim_customer could be Type 1 on email, Type 2 on country, Type 2 on subscription_plan. Mixed types on the same dim is normal.

Implementation notes

• Use 9999-12-31 (or similar) as the open-ended effective_to for current rows. Avoids NULL handling in joins.
• Index on (natural_id, effective_from, effective_to) or (natural_id, is_current) for fast lookups.
• The ETL: detect change between source and current dimension row; if changed, INSERT new row and UPDATE old row's effective_to and is_current = FALSE. Most warehouse tools (dbt snapshots, Fivetran SCD) handle this.

Common SCD mistakes

1. Overwriting (Type 1) when history was needed later — irreversible once the source has moved on.
2. Type 2 on a high-cardinality attribute that changes constantly — last_login_at doesn't belong on a dimension; it goes in a fact.
3. Joining facts to the current dim row — WHERE is_current = TRUE everywhere, defeating the point of Type 2. Join to the version effective at the fact's event time.
4. Forgetting to update the prior row's effective_to — overlapping ranges, double-counting in time-aware joins.

Takeaway

Default to Type 2 for dimension attributes where history matters. Type 1 for corrections and history-irrelevant fields. Per-attribute decision, not per-table. The cleanest production warehouses mix types within a single dimension.