SCD Type 2 explained with SQL: keep the history when a customer changes city

The finance team asks why February's revenue by city changed after they had already sent the report. Nothing was deleted. Someone updated a customer's city in the dimension table, and every old order moved with it. This is the problem slowly changing dimensions exist to solve, and "explain SCD Type 2" is one of the most common data modeling questions in a data engineering round.

This guide uses one small customer dimension and 4 orders so you can check every result by eye. The queries ran in DuckDB, and the output under each query is what it printed. The SQL is standard and works in PostgreSQL and Snowflake with small syntax changes, and the notes say where.

The tables used in every example

A fact_orders table with 4 orders, and a staging table stg_customer that holds the customer data as it arrives on 15 March. Asha moved from Pune to Bengaluru, Ravi did not change, and Meena is a new customer.

CREATE TABLE fact_orders (
  order_id    INTEGER,
  customer_id INTEGER,
  order_date  DATE,
  amount      INTEGER
);

INSERT INTO fact_orders VALUES
  (1, 101, '2026-02-10', 1200),
  (2, 102, '2026-02-15',  800),
  (3, 101, '2026-03-20', 1500),
  (4, 102, '2026-03-25',  600);

CREATE TABLE stg_customer (
  customer_id INTEGER,
  name        VARCHAR,
  city        VARCHAR,
  loaded_at   DATE
);

INSERT INTO stg_customer VALUES
  (101, 'Asha',  'Bengaluru', '2026-03-15'),
  (102, 'Ravi',  'Mumbai',    '2026-03-15'),
  (103, 'Meena', 'Chennai',   '2026-03-15');

Asha placed order 1 while she lived in Pune and order 3 after she moved. Remember that, because the whole guide is about getting those 2 orders into the right city.

1What is a slowly changing dimension, and what does overwriting lose?

Facts are the events you count and add up, such as orders. A dimension is a table that describes the things those events are about: customers, products, stores. Most of its attributes change rarely, such as a city, a plan or a manager. "Slowly changing" is the name for that. The design question is what to do with the old value when it changes.

Type 1 overwrites it. The table always shows the latest value and keeps no history. It is the simplest design, and often right for corrections such as a fixed spelling.

CREATE TABLE dim_customer_t1 (customer_id INTEGER, name VARCHAR, city VARCHAR);
INSERT INTO dim_customer_t1 VALUES (101, 'Asha', 'Pune'), (102, 'Ravi', 'Mumbai');

UPDATE dim_customer_t1 SET city = 'Bengaluru' WHERE customer_id = 101;

Now ask the question the finance team asked, revenue by city, joining each order to the customer:

SELECT c.city, SUM(o.amount) AS revenue
FROM fact_orders o
JOIN dim_customer_t1 c ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY c.city;

city      | revenue
----------+--------
Bengaluru | 2700
Mumbai    | 1400
(2 rows)

Bengaluru shows 2700, which is both of Asha's orders. But order 1 was placed on 10 February, when she lived in Pune, so Pune should have 1200. The join used the only city the table remembers. Last month's report quietly changed, and nobody can reproduce the number that was sent.

Type 2 adds a new row for each version, with the dates it was true. Type 3 adds a column such as previous_city, so it remembers one old value and no more. Types 0, 4 and 6 exist, but interviews almost always stay with 1, 2 and 3. If the business needs to answer "what was true at the time", Type 2 is the answer, and the rest of this guide builds it.

2What does a Type 2 table look like?

Each version of a customer is its own row. Four extra columns make that work:

Start with the dimension as it stood on 1 January, before the move:

CREATE TABLE dim_customer (
  customer_sk INTEGER,
  customer_id INTEGER,
  name        VARCHAR,
  city        VARCHAR,
  valid_from  DATE,
  valid_to    DATE,
  is_current  BOOLEAN
);

INSERT INTO dim_customer VALUES
  (1, 101, 'Asha', 'Pune',   '2026-01-01', NULL, TRUE),
  (2, 102, 'Ravi', 'Mumbai', '2026-01-01', NULL, TRUE);

SELECT * FROM dim_customer ORDER BY customer_sk;

customer_sk | customer_id | name | city   | valid_from | valid_to | is_current
------------+-------------+------+--------+------------+----------+-----------
1           | 101         | Asha | Pune   | 2026-01-01 | NULL     | true
2           | 102         | Ravi | Mumbai | 2026-01-01 | NULL     | true
(2 rows)

The window is half-open: a version is valid from valid_from up to, but not including, valid_to. The next version starts on exactly that date, so there is no gap and no overlap, and any date belongs to exactly one version.

3How do you load a change: close the old row, insert the new one

Compare the staging table with the current rows. A change is a row where the tracked attribute, here city, differs. The load then has 2 steps, and the order matters.

Step 1 closes the current row of every customer whose city changed. Use IS DISTINCT FROM instead of <>, because it treats NULL as a value. With <>, a city changing from NULL to 'Pune' compares as unknown and the change is missed.

UPDATE dim_customer
SET valid_to = s.loaded_at, is_current = FALSE
FROM stg_customer s
WHERE dim_customer.customer_id = s.customer_id
  AND dim_customer.is_current
  AND dim_customer.city IS DISTINCT FROM s.city;

Step 2 inserts a new current row for every staged customer who no longer has one. That covers both the customers just closed in step 1 and customers who have never been seen before. Ravi is skipped because his current row is still open.

INSERT INTO dim_customer
SELECT (SELECT COALESCE(MAX(customer_sk), 0) FROM dim_customer)
         + ROW_NUMBER() OVER (ORDER BY s.customer_id) AS customer_sk,
       s.customer_id, s.name, s.city, s.loaded_at, NULL, TRUE
FROM stg_customer s
LEFT JOIN dim_customer d
       ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL;

SELECT * FROM dim_customer ORDER BY customer_sk;

customer_sk | customer_id | name  | city      | valid_from | valid_to   | is_current
------------+-------------+-------+-----------+------------+------------+-----------
1           | 101         | Asha  | Pune      | 2026-01-01 | 2026-03-15 | false
2           | 102         | Ravi  | Mumbai    | 2026-01-01 | NULL       | true
3           | 101         | Asha  | Bengaluru | 2026-03-15 | NULL       | true
4           | 103         | Meena | Chennai   | 2026-03-15 | NULL       | true
(4 rows)

Asha now has 2 rows: Pune, closed on 15 March, and Bengaluru, open. Ravi's row is untouched, and Meena arrived as a new current row. Step 2 only works because step 1 ran first: until Asha's old row is closed, the LEFT JOIN still finds a current row for her and skips her. In a real pipeline, run both steps in one transaction, so a failure between them cannot leave a customer with no current row.

The surrogate key here is MAX + ROW_NUMBER to keep the example portable. Real warehouses use an identity column or a sequence, and the numbers it hands out do not matter, as long as every row gets a different one.

Run the same 2 steps again. Nothing should change, because every staged value now matches its current row:

UPDATE dim_customer
SET valid_to = s.loaded_at, is_current = FALSE
FROM stg_customer s
WHERE dim_customer.customer_id = s.customer_id
  AND dim_customer.is_current
  AND dim_customer.city IS DISTINCT FROM s.city;

INSERT INTO dim_customer
SELECT (SELECT COALESCE(MAX(customer_sk), 0) FROM dim_customer)
         + ROW_NUMBER() OVER (ORDER BY s.customer_id),
       s.customer_id, s.name, s.city, s.loaded_at, NULL, TRUE
FROM stg_customer s
LEFT JOIN dim_customer d
       ON d.customer_id = s.customer_id AND d.is_current
WHERE d.customer_id IS NULL;

SELECT COUNT(*) AS rows_after_second_run FROM dim_customer;

rows_after_second_run
---------------------
4
(1 row)

4 rows again, not six. A load that gives the same result when you run it twice is called idempotent, and it is what lets you rerun a failed job without cleaning up first.

4How do you join facts to the right version?

Each order belongs to the version of the customer that was valid on the order date. The join needs the natural key, and a date range:

SELECT d.city, SUM(o.amount) AS revenue
FROM fact_orders o
JOIN dim_customer d
  ON d.customer_id = o.customer_id
 AND o.order_date >= d.valid_from
 AND (o.order_date < d.valid_to OR d.valid_to IS NULL)
GROUP BY d.city
ORDER BY d.city;

city      | revenue
----------+--------
Bengaluru | 1500
Mumbai    | 1400
Pune      | 1200
(3 rows)

Now the report has Pune 1200 for Asha's February order, Bengaluru 1500 for her March order, and Mumbai 1400 for Ravi. The finance team's February number is back, and it stays there however many times Asha moves.

The most common mistake is to join on customer_id alone, because that worked with a Type 1 table. Try it and compare the total with the real total of 4100:

SELECT SUM(o.amount) AS total_revenue
FROM fact_orders o
JOIN dim_customer d ON d.customer_id = o.customer_id;

total_revenue
-------------
6800
(1 row)

The total is wrong because Asha has 2 rows in the dimension, so each of her orders matches both versions and is counted twice: (1200 + 1500) × 2 + 800 + 600 = 6800. This is join fan-out, the same grain problem you meet when a join repeats rows, and nothing errors. A quick safety check after any join is to compare the total before and after.

5How do you read the current rows, and check the table is healthy?

For "who lives where now", filter on the flag:

SELECT customer_id, name, city
FROM dim_customer
WHERE is_current
ORDER BY customer_id;

customer_id | name  | city
------------+-------+----------
101         | Asha  | Bengaluru
102         | Ravi  | Mumbai
103         | Meena | Chennai
(3 rows)

2 rules should hold for every Type 2 table, and both are one query. First, each customer has exactly one current row. This should return no rows:

SELECT customer_id, COUNT(*) AS current_rows
FROM dim_customer
WHERE is_current
GROUP BY customer_id
HAVING COUNT(*) <> 1;

customer_id | current_rows
------------+-------------
(0 rows)

Second, no 2 versions of the same customer overlap in time. Join the table to itself and look for a pair where one version starts before the other ends. This should also return no rows:

SELECT a.customer_id, a.customer_sk AS version_a, b.customer_sk AS version_b
FROM dim_customer a
JOIN dim_customer b
  ON a.customer_id = b.customer_id
 AND a.customer_sk < b.customer_sk
 AND a.valid_from < COALESCE(b.valid_to, DATE '9999-12-31')
 AND b.valid_from < COALESCE(a.valid_to, DATE '9999-12-31');

customer_id | version_a | version_b
------------+-----------+----------
(0 rows)

Run these as a data quality check after every load. A duplicated current row is the usual symptom of a load that ran only step 2, or ran twice in two processes at once.

6Can MERGE do it in one statement?

Yes, with a well-known trick. A Type 2 change needs two actions for one source row: update the old row and insert the new one. MERGE gives each source row only one action, so you feed the changed rows in twice. Once with the real key, which matches the old row and closes it. Once with a NULL key, which matches nothing and so inserts the new version.

Start a fresh copy of the dimension in its 1 January state, and use a sequence for the surrogate key:

CREATE TABLE dim_customer_m (
  customer_sk INTEGER, customer_id INTEGER, name VARCHAR, city VARCHAR,
  valid_from DATE, valid_to DATE, is_current BOOLEAN
);
INSERT INTO dim_customer_m VALUES
  (1, 101, 'Asha', 'Pune',   '2026-01-01', NULL, TRUE),
  (2, 102, 'Ravi', 'Mumbai', '2026-01-01', NULL, TRUE);

CREATE SEQUENCE customer_sk_seq START 3;

MERGE INTO dim_customer_m AS d
USING (
  SELECT s.customer_id AS merge_key, s.customer_id, s.name, s.city, s.loaded_at
  FROM stg_customer s
  UNION ALL
  SELECT NULL, s.customer_id, s.name, s.city, s.loaded_at
  FROM stg_customer s
  JOIN dim_customer_m c
    ON c.customer_id = s.customer_id AND c.is_current AND c.city IS DISTINCT FROM s.city
) AS src
  ON d.customer_id = src.merge_key AND d.is_current
WHEN MATCHED AND d.city IS DISTINCT FROM src.city THEN
  UPDATE SET valid_to = src.loaded_at, is_current = FALSE
WHEN NOT MATCHED THEN
  INSERT VALUES (nextval('customer_sk_seq'), src.customer_id, src.name, src.city,
                 src.loaded_at, NULL, TRUE);

SELECT customer_id, name, city, valid_from, valid_to, is_current
FROM dim_customer_m
ORDER BY customer_id, valid_from;

customer_id | name  | city      | valid_from | valid_to   | is_current
------------+-------+-----------+------------+------------+-----------
101         | Asha  | Pune      | 2026-01-01 | 2026-03-15 | false
101         | Asha  | Bengaluru | 2026-03-15 | NULL       | true
102         | Ravi  | Mumbai    | 2026-01-01 | NULL       | true
103         | Meena | Chennai   | 2026-03-15 | NULL       | true
(4 rows)

The same 4 versions as the 2-step load. The WHEN MATCHED line also checks that the city differs, so an unchanged customer such as Ravi matches his own row and is left alone.

MERGE is supported in Snowflake, BigQuery, Databricks (Delta), SQL Server and, as the examples above show, DuckDB. Syntax details differ by engine, so treat this as the pattern and check your warehouse's documentation for the exact form.

7What interviewers ask next

Most SCD follow-ups are small variations on what you have already built. The answer is usually one sentence once you know the table.

Practise the pattern with Build SCD2 ranges from daily snapshots, then see the PySpark version in SCD Type 2 merge. The Data modeling questions cover the theory side one question at a time.