DWH Interview Question 33 - How do you implement SCD Type 2?

Data Warehouse Interview Q&A · Question 33

In this post we are going to cover how to implement SCD Type 2, step by step, with plain SQL.

SCD Type 2 is the slowly changing dimension type that keeps full history. When an attribute changes, we do not overwrite the row. We close the old version and add a new row with a new surrogate key, so every fact keeps pointing to the version that was true when it happened.

We will load one day's extract from a CRM into a Type 2 customer dimension: detect what changed, expire the old rows, insert the new versions, query the result for today and for a past date, and finish with a health check.

Question 33 of the Real DWH Question and Answers series on TechBrothersIT.

On this page

1. The interview answer
2. Anatomy of a Type 2 row
3. Let's create some sample data
4. Step 1: Detect changes
5. Step 2: Expire the changed rows
6. Step 3: Insert the new versions
7. Step 4: Query the current view
8. Step 5: Query as of a past date
9. Step 6: Check for duplicate current rows
10. Traps interviewers like to ask about
11. Follow-up questions interviewers ask
12. The full script

The interview answer

Say this first

An SCD Type 2 dimension keeps one row per version, with a surrogate key, the natural key, valid_from and valid_to dates and an is_current flag. Each load compares staging with the current rows, expires the changed ones, and inserts new versions and new members. Queries use is_current for today, or the date range for any point in time.

Anatomy of a Type 2 row

Before any SQL, let's be clear about what each column in a Type 2 dimension is for.

• Surrogate key (customer_key) - identifies one version. Facts point to this key.
• Natural key (customer_id) - identifies the customer, and repeats when there are several versions.
• Type 2 attribute (city) - the column whose history we track.
• valid_from and valid_to - when this version was true. A valid_to of 9999-12-31 means it is still valid.
• is_current - a shortcut flag for the latest version, so everyday queries do not need dates.

Let's create some sample data

We need the customer dimension and a staging table with today's full extract from the CRM. The load date is 2026-06-01.

Sample data — run this first

DROP TABLE IF EXISTS stg_customer;
DROP TABLE IF EXISTS dim_customer;

CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,   -- surrogate key: one per VERSION
  customer_id  VARCHAR(10),       -- natural key: repeats across versions
  city         VARCHAR(50),       -- the Type 2 attribute
  valid_from   DATE,
  valid_to     DATE,              -- 9999-12-31 = still valid
  is_current   CHAR(1)
);
INSERT INTO dim_customer VALUES
  (1, 'C101', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
  (2, 'C102', 'Dallas',  '2025-01-01', '9999-12-31', 'Y');

-- today's full extract from the CRM (load date 2026-06-01)
CREATE TABLE stg_customer (customer_id VARCHAR(10), city VARCHAR(50));
INSERT INTO stg_customer VALUES
  ('C101', 'Vancouver'),   -- changed
  ('C102', 'Dallas'),      -- same
  ('C103', 'Chicago');     -- new customer

Let's look at the dimension before the load.

Query

SELECT * FROM dim_customer ORDER BY 1;

Result2 rows · 6 columns

customer_key	customer_id	city	valid_from	valid_to	is_current
1	C101	Toronto	2025-01-01	9999-12-31	Y
2	C102	Dallas	2025-01-01	9999-12-31	Y

Two customers, each with one open, current row. Now today's extract.

Query

SELECT * FROM stg_customer ORDER BY 1;

Result3 rows · 2 columns

customer_id	city
C101	Vancouver
C102	Dallas
C103	Chicago

C101 now says Vancouver instead of Toronto, C102 is still in Dallas, and C103 is a customer we have never seen.

Step 1: Detect changes

Now let's compare staging with the current dimension rows. We left join on the natural key, only to rows with is_current = 'Y', and classify each customer as new, changed or the same.

Query

SELECT s.customer_id, d.city AS old_city, s.city AS new_city,
       CASE WHEN d.customer_id IS NULL THEN 'NEW'
            WHEN s.city <> d.city     THEN 'CHANGED'
            ELSE 'SAME' END AS action
FROM stg_customer s
LEFT JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND d.is_current = 'Y'
ORDER BY s.customer_id;

Result3 rows · 4 columns

customer_id	old_city	new_city	action
C101	Toronto	Vancouver	CHANGED
C102	Dallas	Dallas	SAME
C103	NULL	Chicago	NEW

C101 moved from Toronto to Vancouver, so it is CHANGED. C102 is still in Dallas, so it is SAME and needs nothing. C103 has no match at all, so its old city is NULL and it is NEW.

In a real dimension we compare many columns at once. A common approach is to compare a hash of all the Type 2 columns, so one comparison covers them all.

Step 2: Expire the changed rows

The load itself is two statements, and the order matters. First we expire the current row of every customer whose city changed: valid_to becomes 2026-05-31, the day before the load, and is_current becomes N.

Statement

UPDATE dim_customer
SET valid_to = DATE '2026-05-31', is_current = 'N'
WHERE is_current = 'Y'
  AND EXISTS (SELECT 1 FROM stg_customer s
              WHERE s.customer_id = dim_customer.customer_id
                AND s.city <> dim_customer.city);

Now let's check the result.

Query

SELECT * FROM dim_customer ORDER BY customer_key;

Result2 rows · 6 columns

customer_key	customer_id	city	valid_from	valid_to	is_current
1	C101	Toronto	2025-01-01	2026-05-31	N
2	C102	Dallas	2025-01-01	9999-12-31	Y

Only C101's Toronto row was touched. It is now closed at 2026-05-31 and is no longer current. C102 still has its open row.

Step 3: Insert the new versions

Now let's insert a new current row for every staging customer that does not have one. After Step 2 that is C101, whose current row we just expired, and the brand-new C103. One insert handles both cases.

Statement

INSERT INTO dim_customer
SELECT m.max_key + ROW_NUMBER() OVER (ORDER BY s.customer_id),
       s.customer_id, s.city,
       DATE '2026-06-01', DATE '9999-12-31', 'Y'
FROM stg_customer s
CROSS JOIN (SELECT MAX(customer_key) AS max_key
            FROM dim_customer) m
WHERE NOT EXISTS (SELECT 1 FROM dim_customer d
                  WHERE d.customer_id = s.customer_id
                    AND d.is_current = 'Y');

Now let's check the result.

Query

SELECT * FROM dim_customer ORDER BY customer_key;

Result4 rows · 6 columns

customer_key	customer_id	city	valid_from	valid_to	is_current
1	C101	Toronto	2025-01-01	2026-05-31	N
2	C102	Dallas	2025-01-01	9999-12-31	Y
3	C101	Vancouver	2026-06-01	9999-12-31	Y
4	C103	Chicago	2026-06-01	9999-12-31	Y

Sara (C101) now has two versions: key 1 for Toronto, valid until 2026-05-31, and key 3 for Vancouver, valid from 2026-06-01. C103 gets key 4 for Chicago. C102 was not changed, so it still has only key 2.

This is why we expire first and insert second: once the changed customers have no current row, they fall into the same group as new customers, and one simple NOT EXISTS finds them all.

Step 4: Query the current view

For today's view of every customer, we filter on is_current = 'Y'.

Query

SELECT customer_key, customer_id, city
FROM dim_customer
WHERE is_current = 'Y'
ORDER BY customer_id;

Result3 rows · 3 columns

customer_key	customer_id	city
3	C101	Vancouver
2	C102	Dallas
4	C103	Chicago

Each customer appears once with the latest city: C101 in Vancouver with key 3, C102 in Dallas with key 2, and C103 in Chicago with key 4.

Step 5: Query as of a past date

For any point in time, we use the date range instead. Let's ask where every customer was on 2026-03-15.

Query

SELECT customer_key, customer_id, city
FROM dim_customer
WHERE DATE '2026-03-15' BETWEEN valid_from AND valid_to
ORDER BY customer_id;

Result2 rows · 3 columns

customer_key	customer_id	city
1	C101	Toronto
2	C102	Dallas

On that date C101 was still in Toronto (key 1), and C102 was in Dallas. C103 does not appear because it did not exist yet.

Facts do not need either filter. Each fact row already points to the version that was valid when it happened, through its surrogate key.

Step 6: Check for duplicate current rows

Finally, a check every Type 2 load should run: no customer may have more than one current row.

Query

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

Result0 rows · 2 columns

customer_id	current_rows
(no rows)

The query returns no rows, so every customer has exactly one current row and the dimension is healthy. If this ever returns a row, the load has a bug.

Traps interviewers like to ask about

• NULLs - if city can be NULL, a <> comparison misses the change, because comparing NULL to anything is unknown. Handle NULLs explicitly, or compare hashes.
• Date conventions - here valid_to is inclusive, the day before the next version. Some teams use an exclusive end date instead. Pick one and use it everywhere, because mixing them causes gaps or overlaps.
• One current row - always run the check from Step 6.

Follow-up questions interviewers ask

Why expire first, then insert?

The insert adds a row for every customer without a current row; expiring first makes changed customers fall into that group.

How do you detect changes across many columns?

Compare a hash of all Type 2 columns, and handle NULLs explicitly.

NULL or 9999-12-31 for the open end date?

Either works; a far-future date keeps BETWEEN queries simple. Pick one convention and use it everywhere.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom

-- ========================================================================
-- Data Warehouse Interview Q&A #33 - SCD Type 2 deep dive
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q33;  SET search_path TO dwh_q33;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS stg_customer;
DROP TABLE IF EXISTS dim_customer;

CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,   -- surrogate key: one per VERSION
  customer_id  VARCHAR(10),       -- natural key: repeats across versions
  city         VARCHAR(50),       -- the Type 2 attribute
  valid_from   DATE,
  valid_to     DATE,              -- 9999-12-31 = still valid
  is_current   CHAR(1)
);
INSERT INTO dim_customer VALUES
  (1, 'C101', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
  (2, 'C102', 'Dallas',  '2025-01-01', '9999-12-31', 'Y');

-- today's full extract from the CRM (load date 2026-06-01)
CREATE TABLE stg_customer (customer_id VARCHAR(10), city VARCHAR(50));
INSERT INTO stg_customer VALUES
  ('C101', 'Vancouver'),   -- changed
  ('C102', 'Dallas'),      -- same
  ('C103', 'Chicago');     -- new customer


-- Step 1: Compare staging with the CURRENT dimension rows
SELECT s.customer_id, d.city AS old_city, s.city AS new_city,
       CASE WHEN d.customer_id IS NULL THEN 'NEW'
            WHEN s.city <> d.city     THEN 'CHANGED'
            ELSE 'SAME' END AS action
FROM stg_customer s
LEFT JOIN dim_customer d
  ON d.customer_id = s.customer_id
 AND d.is_current = 'Y'
ORDER BY s.customer_id;

-- Step 2: Expire the current row of every CHANGED customer
UPDATE dim_customer
SET valid_to = DATE '2026-05-31', is_current = 'N'
WHERE is_current = 'Y'
  AND EXISTS (SELECT 1 FROM stg_customer s
              WHERE s.customer_id = dim_customer.customer_id
                AND s.city <> dim_customer.city);

-- check the result
SELECT * FROM dim_customer ORDER BY customer_key;

-- Step 3: Insert a new current row for every customer without one
INSERT INTO dim_customer
SELECT m.max_key + ROW_NUMBER() OVER (ORDER BY s.customer_id),
       s.customer_id, s.city,
       DATE '2026-06-01', DATE '9999-12-31', 'Y'
FROM stg_customer s
CROSS JOIN (SELECT MAX(customer_key) AS max_key
            FROM dim_customer) m
WHERE NOT EXISTS (SELECT 1 FROM dim_customer d
                  WHERE d.customer_id = s.customer_id
                    AND d.is_current = 'Y');

-- check the result
SELECT * FROM dim_customer ORDER BY customer_key;

-- Step 4: Query: the current version of every customer
SELECT customer_key, customer_id, city
FROM dim_customer
WHERE is_current = 'Y'
ORDER BY customer_id;

-- Step 5: Query: where was every customer on 2026-03-15? (as-of)
SELECT customer_key, customer_id, city
FROM dim_customer
WHERE DATE '2026-03-15' BETWEEN valid_from AND valid_to
ORDER BY customer_id;

-- Step 6: Check: no customer may have more than one current row
SELECT customer_id, COUNT(*) AS current_rows
FROM dim_customer
WHERE is_current = 'Y'
GROUP BY customer_id
HAVING COUNT(*) > 1;

Key points

• One row per version: SK, NK, valid_from, valid_to, is_current
• Detect: staging vs current rows
• Expire changed rows, then insert new versions
• Query by is_current or by date range
• Watch NULLs, date conventions, one current row

Next in this series

Question 34 — SCD Type 2 in SQL with MERGE How do you load SCD Type 2 with a MERGE statement?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.