What is a slowly changing dimension (SCD)?

A slowly changing dimension is a dimension table whose attribute values change over time — like a customer's region or tier. The SCD 'types' (0, 1, 2, 3, 4, 6) are strategies for how much of that history you keep when a value changes.

What is the difference between SCD Type 1 and Type 2?

Type 1 overwrites the old value in place, keeping only the current state and no history. Type 2 inserts a new versioned row (with valid_from / valid_to / is_current) on every change, preserving full history so you can answer point-in-time questions.

When should I use SCD Type 2?

Use Type 2 when history matters for reporting — for example when facts must join to the attribute value that was true on the transaction date (financial, regulatory, or audit reporting). It costs more rows and ETL complexity than Type 1.

What is a point-in-time (as-of) join?

A join that matches each fact to the dimension version that was valid on the fact's date, using a predicate like fact_date >= valid_from AND fact_date < valid_to. A naive equi-join on the business key instead lands on the current row and silently re-states history.

What is bitemporal modeling?

Bitemporal modeling tracks two timelines per row: valid time (when a value was true in the real world) and system time (when you recorded it). It lets you correct the past without erasing what you previously believed — essential for audits and restatements.

Does it work online and offline?

Both. The whole simulation is JavaScript running in your browser with no server calls, so it works online with nothing to wait on — and once the page has loaded it keeps working offline.

Is the SCD Playground free, and is my data uploaded?

Yes — it is completely free and runs 100% in your browser. There is no sign-up and nothing is uploaded; the entire simulation is JavaScript executing on your device.

SCD Playground

A customer relocates. A tier gets upgraded. Now every historical fact is at risk of silently re-stating under today's attributes — unless you modelled the change. Replay the timeline below and watch the dimension transform under each Slowly Changing Dimension type.

Runs entirely in your browser — nothing uploaded

The story — one customer over time

Meet Acme Corp (customer_id=C1). They sign on in Europe, then change 3 times as they grow. Every tab below replays this exact journey— and the strategies don't all remember it the same way.

★

2023-01-01

Joins

EuropeSilver

2023-06-15

Acme moves HQ to New York

regionEurope→US

2024-02-01

Upgraded after renewal

tierSilver→Gold

2024-09-10

Relocates to Bangalore

regionUS→India

Today

regionIndia

tierGold

3 changes in

How each type stores the same change.

Step 1 · One change, six futures

Acme Corp just changed region: Europe → US. The change is identical for everyone — what differs is how much of the past each strategy decides to keep. “SCD type” isn't a menu of six options — it's a single dial, from forget it ever changed to remember every version. There's no universally right setting; you're trading history for simplicity.

No historythe past is gone

One priorjust the last value

Full historyevery version kept

Type 2 — Effective Dating

full history

What one change does to the row: region Europe → US on 2023-06-15.

Before

customer_sk	customer_id	region	tier	valid_from	valid_to	is_current	version
1	C1	Europe	Silver	2023-01-01	9999-12-31	true	1

→Insert a new versioned row

After

customer_sk	customer_id	region	tier	valid_from	valid_to	is_current	version
1	C1	Europe	Silver	2023-01-01	2023-06-15	false	1
2	C1	US	Silver	2023-06-15	9999-12-31	true	2

The whole story, replayed as Type 2

All 3 changesapplied — the table you'd actually end up with.

customer_sk	customer_id	region	tier	valid_from	valid_to	is_current	version
1	C1	Europe	Silver	2023-01-01	2023-06-15	false	1
2	C1	US	Silver	2023-06-15	2024-02-01	false	2
3	C1	US	Gold	2024-02-01	2024-09-10	false	3
4	C1	India	Gold	2024-09-10	9999-12-31	true	4

•Type 2 adds a new row with a fresh surrogate key on every change — full history, one row per period.
•The prior row is expired (valid_to set, is_current = false); facts join on the surrogate key that was current at the fact's date.
•3 change(s) → 4 rows for this one customer.

Generated SQL — schema & upsert

Schema

CREATE TABLE dim_customer (
  customer_sk      BIGINT PRIMARY KEY,
  customer_id      VARCHAR NOT NULL,
  region     VARCHAR,
  tier     VARCHAR,
  valid_from   DATE NOT NULL,
  valid_to     DATE NOT NULL DEFAULT DATE '9999-12-31',
  is_current   BOOLEAN NOT NULL DEFAULT TRUE,
  version      INT NOT NULL
);

Handle one change

-- Type 2: expire the old version, insert the new one.
UPDATE dim_customer
   SET valid_to = :change_date, is_current = FALSE
 WHERE customer_id = :bk AND is_current = TRUE;

INSERT INTO dim_customer (customer_sk, customer_id, region, tier, valid_from, valid_to, is_current, version)
SELECT nextval('customer_sk_seq'), :bk, :region, :tier,
       :change_date, DATE '9999-12-31', TRUE,
       COALESCE(MAX(version), 0) + 1
  FROM dim_customer WHERE customer_id = :bk;

Type 2 — Effective Dating

Add a new versioned row per change with valid_from / valid_to / is_current. The workhorse of dimensional modeling.

Tradeoff: Full history and correct point-in-time joins, at the cost of more rows and ETL complexity.

Questions you can answer

What was the value when this fact happened? (point-in-time)

-- The version whose validity window covers the fact date.
SELECT region, tier
FROM dim_customer
WHERE customer_id = 'C1'
  AND DATE '2024-03-01' >= valid_from
  AND DATE '2024-03-01' <  valid_to;

What is the current value? (is_current = true)

SELECT region, tier
FROM dim_customer
WHERE customer_id = 'C1' AND is_current;

What was the full history, and how long did each value last?

SELECT version, region, tier,
       valid_from, valid_to,
       valid_to - valid_from AS days_held
FROM dim_customer
WHERE customer_id = 'C1'
ORDER BY version;

Learn the internals →

So which do you pick?

Same story, all six strategies side by side. Click a row to load it above.

Type	History	Rows now	Point-in-time	Use when
Type 0 Retain Original	no history	1	— no	Values that must never change — date of birth, original score.
Type 1 Overwrite	no history	1	— no	Only current state matters; history is noise — corrected typos, current email.
Type 2 Effective Dating	full history	4	✓ yes	History drives reporting — the workhorse for dims in financial/regulatory joins.
Type 3 Limited History	1 prior value	1	— no	A one-time realignment where ‘before vs after’ is enough — an org restructure.
Type 4 History Table	full history	5	✓ yes	Hot current-state queries plus occasional history, kept physically apart.
Type 6 Hybrid (1+2+3)	full history	4	✓ yes	You need fast current attributes and full version history in one model.

Slowly Changing Dimensions — FAQ

What is a slowly changing dimension (SCD)?: A slowly changing dimension is a dimension table whose attribute values change over time — like a customer's region or tier. The SCD 'types' (0, 1, 2, 3, 4, 6) are strategies for how much of that history you keep when a value changes.
What is the difference between SCD Type 1 and Type 2?: Type 1 overwrites the old value in place, keeping only the current state and no history. Type 2 inserts a new versioned row (with valid_from / valid_to / is_current) on every change, preserving full history so you can answer point-in-time questions.
When should I use SCD Type 2?: Use Type 2 when history matters for reporting — for example when facts must join to the attribute value that was true on the transaction date (financial, regulatory, or audit reporting). It costs more rows and ETL complexity than Type 1.
What is a point-in-time (as-of) join?: A join that matches each fact to the dimension version that was valid on the fact's date, using a predicate like fact_date >= valid_from AND fact_date < valid_to. A naive equi-join on the business key instead lands on the current row and silently re-states history.
What is bitemporal modeling?: Bitemporal modeling tracks two timelines per row: valid time (when a value was true in the real world) and system time (when you recorded it). It lets you correct the past without erasing what you previously believed — essential for audits and restatements.
Does it work online and offline?: Both. The whole simulation is JavaScript running in your browser with no server calls, so it works online with nothing to wait on — and once the page has loaded it keeps working offline.
Is the SCD Playground free, and is my data uploaded?: Yes — it is completely free and runs 100% in your browser. There is no sign-up and nothing is uploaded; the entire simulation is JavaScript executing on your device.

Why these tradeoffs matter in production

The Slowly Changing Dimensions course covers Types 1/2/3/6, effective dating, bitemporal modeling, late-arriving dimensions, and the anti-patterns that bite teams in their first year.

Open the SCD deep dive →More free tools