DWH Interview Question 34 - How do you load SCD Type 2 with a MERGE statement?

Data Warehouse Interview Q&A · Question 34

In this post we are going to cover how to load an SCD Type 2 dimension with a single MERGE statement.

MERGE compares a source with a target table and, for each source row, runs an UPDATE or an INSERT, all in one atomic statement. That sounds perfect for dimension loads. The catch is that a changed Type 2 row needs two actions: expire the old version and insert the new one, and MERGE gives each source row only one action.

We will see why a plain MERGE is not enough, build a source query with one small trick, run the full MERGE, and then run it a second time to prove it is safe to re-run.

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

On this page
  1. The interview answer
  2. Why a plain MERGE is not enough
  3. Let's create some sample data
  4. Step 1: Build the source with a NULL merge key
  5. Step 2: Run the full MERGE
  6. Step 3: Run it again
  7. Step 4: Prove nothing was duplicated
  8. Platform notes before you use MERGE
  9. Follow-up questions interviewers ask
  10. The full script

The interview answer

Say this first

MERGE compares a source with a target and runs UPDATE or INSERT per row in one atomic statement. But a changed SCD2 row needs two actions: expire the old version and insert the new one. The trick is a source that holds every staging row, plus a copy of each changed row with a NULL merge key: the first copy matches and expires, the NULL copy never matches and inserts.

Why a plain MERGE is not enough

MERGE matches source rows to target rows on the natural key. Think about the three customers in today's extract:

  • C102 is the same - it matches and nothing changed, so there is nothing to do.
  • C103 is new - it does not match, so WHEN NOT MATCHED inserts it.
  • C101 changed - it matches, so we can update it and expire the Toronto row. But we also need a new Vancouver row, and a matched row cannot also be inserted.

So we need C101 to appear in the source twice: once to match and expire, and once to not match and insert.

Let's create some sample data

We need the Type 2 customer dimension and today's extract in staging. This time the surrogate key comes from an identity column, so MERGE does not have to calculate it. The load date is 2026-06-01.

Sample data — run this first
DROP TABLE IF EXISTS stg_customer;
DROP TABLE IF EXISTS dim_customer;

-- identity column generates the surrogate key
-- (SQL Server: customer_key INT IDENTITY(1,1) PRIMARY KEY)
CREATE TABLE dim_customer (
  customer_key INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  VARCHAR(10),
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE,
  is_current   CHAR(1)
);
INSERT INTO dim_customer (customer_id, city, valid_from, valid_to, is_current) VALUES
  ('C101', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
  ('C102', 'Dallas',  '2025-01-01', '9999-12-31', 'Y');

-- today's extract (load date 2026-06-01)
CREATE TABLE stg_customer (customer_id VARCHAR(10), city VARCHAR(50));
INSERT INTO stg_customer VALUES ('C101', 'Vancouver'), ('C102', 'Dallas'), ('C103', 'Chicago');

Here is the dimension before the load.

Query
SELECT * FROM dim_customer ORDER BY 1;
Result2 rows · 6 columns
customer_keycustomer_idcityvalid_fromvalid_tois_current
1C101Toronto2025-01-019999-12-31Y
2C102Dallas2025-01-019999-12-31Y

Two customers with one current row each. Now the staging extract.

Query
SELECT * FROM stg_customer ORDER BY 1;
Result3 rows · 2 columns
customer_idcity
C101Vancouver
C102Dallas
C103Chicago

C101 has moved from Toronto to Vancouver, C102 is unchanged in Dallas, and C103 is a new customer in Chicago.

Step 1: Build the source with a NULL merge key

Here is the trick. The source has two parts. The first part is every staging row, with customer_id as the merge_key. The second part finds the changed customers, by joining staging to the current dimension rows where the city differs, and returns them again with a NULL merge_key.

Query
SELECT s.customer_id AS merge_key, s.customer_id, s.city
FROM stg_customer s
UNION ALL
SELECT NULL, s.customer_id, s.city
FROM stg_customer s
JOIN dim_customer d
  ON d.customer_id = s.customer_id AND d.is_current = 'Y'
WHERE s.city <> d.city
ORDER BY customer_id, merge_key;
Result4 rows · 3 columns
merge_keycustomer_idcity
C101C101Vancouver
NULLC101Vancouver
C102C102Dallas
C103C103Chicago

Four rows. C102 and C103 appear once each. C101 appears twice: once with merge key C101, which will match its current row and expire Toronto, and once with a NULL merge key. NULL never equals anything, so that copy can never match and will always fall into WHEN NOT MATCHED, which inserts the Vancouver version.

Step 2: Run the full MERGE

Now let's put that source inside the MERGE. The ON clause matches the merge key to a current row. When matched and the city changed, we expire the row. When not matched, we insert a new current version.

Statement
MERGE INTO dim_customer d
USING (
  SELECT s.customer_id AS merge_key, s.customer_id, s.city
  FROM stg_customer s
  UNION ALL
  SELECT NULL, s.customer_id, s.city
  FROM stg_customer s
  JOIN dim_customer d
    ON d.customer_id = s.customer_id AND d.is_current = 'Y'
  WHERE s.city <> d.city
) src
ON d.customer_id = src.merge_key AND d.is_current = 'Y'
WHEN MATCHED AND d.city <> src.city THEN
  UPDATE SET valid_to = DATE '2026-05-31', is_current = 'N'
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, valid_from, valid_to, is_current)
  VALUES (src.customer_id, src.city,
          DATE '2026-06-01', DATE '9999-12-31', 'Y');

Now let's check the result.

Query
SELECT * FROM dim_customer ORDER BY customer_key;
Result4 rows · 6 columns
customer_keycustomer_idcityvalid_fromvalid_tois_current
1C101Toronto2025-01-012026-05-31N
2C102Dallas2025-01-019999-12-31Y
3C103Chicago2026-06-019999-12-31Y
4C101Vancouver2026-06-019999-12-31Y

Toronto (key 1) is expired with valid_to 2026-05-31 and is_current = 'N'. Chicago and Vancouver are inserted as current rows from 2026-06-01, with keys 3 and 4 from the identity column. Dallas (key 2) is untouched because it matched but the city did not change.

That is a complete Type 2 load in one atomic statement: either all of it happens, or none of it does.

Step 3: Run it again

A good load must be safe to re-run, because jobs do get restarted. Let's run the exact same MERGE a second time.

Statement
MERGE INTO dim_customer d
USING (
  SELECT s.customer_id AS merge_key, s.customer_id, s.city
  FROM stg_customer s
  UNION ALL
  SELECT NULL, s.customer_id, s.city
  FROM stg_customer s
  JOIN dim_customer d
    ON d.customer_id = s.customer_id AND d.is_current = 'Y'
  WHERE s.city <> d.city
) src
ON d.customer_id = src.merge_key AND d.is_current = 'Y'
WHEN MATCHED AND d.city <> src.city THEN
  UPDATE SET valid_to = DATE '2026-05-31', is_current = 'N'
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, valid_from, valid_to, is_current)
  VALUES (src.customer_id, src.city,
          DATE '2026-06-01', DATE '9999-12-31', 'Y');

Now let's check the result.

Query
SELECT * FROM dim_customer ORDER BY customer_key;
Result4 rows · 6 columns
customer_keycustomer_idcityvalid_fromvalid_tois_current
1C101Toronto2025-01-012026-05-31N
2C102Dallas2025-01-019999-12-31Y
3C103Chicago2026-06-019999-12-31Y
4C101Vancouver2026-06-019999-12-31Y

The table is exactly the same as after Step 2. C101's current row is already Vancouver, so the second part of the source returns no NULL copy. Every staging row now matches a current row, so WHEN NOT MATCHED inserts nothing, and none of those rows has a different city, so the update condition is never true either.

Step 4: Prove nothing was duplicated

Let's count the rows by is_current to be sure.

Query
SELECT is_current, COUNT(*) AS row_count
FROM dim_customer
GROUP BY is_current
ORDER BY is_current;
Result2 rows · 2 columns
is_currentrow_count
N1
Y3

Still four rows: one expired and three current. Nothing was duplicated. A load that gives the same result no matter how many times it runs is called idempotent, and it is a good word to use in the interview.

Platform notes before you use MERGE

  • Support - MERGE exists in SQL Server, Oracle, PostgreSQL 15 and later, Snowflake, BigQuery and Databricks, with small syntax differences. MySQL does not have it. Oracle, for example, puts the update condition in a WHERE clause and does not allow updating a column used in the ON clause.
  • Duplicates - if the source has the same key twice in a way that updates one target row twice, most databases raise an error. Deduplicate staging first.
  • NULLs - compare columns null-safely, or a change to or from NULL is missed.
Tip

On SQL Server with heavy concurrent loads, use a lock hint or separate statements. The expire-then-insert pattern from the previous question is always a valid alternative to MERGE.

Follow-up questions interviewers ask

Why the NULL merge key?

NULL never equals anything, so that copy can never match and always falls into WHEN NOT MATCHED, which inserts the new version.

What happens if the source has duplicate keys?

MERGE may try to update the same target row twice; most databases raise an error. Deduplicate staging first.

Is the MERGE idempotent?

Yes: on a re-run the current rows already match staging, so no row is expired or inserted.

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 #34 - SCD Type 2 with MERGE
-- 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_q34;  SET search_path TO dwh_q34;
-- 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;

-- identity column generates the surrogate key
-- (SQL Server: customer_key INT IDENTITY(1,1) PRIMARY KEY)
CREATE TABLE dim_customer (
  customer_key INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  VARCHAR(10),
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE,
  is_current   CHAR(1)
);
INSERT INTO dim_customer (customer_id, city, valid_from, valid_to, is_current) VALUES
  ('C101', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
  ('C102', 'Dallas',  '2025-01-01', '9999-12-31', 'Y');

-- today's extract (load date 2026-06-01)
CREATE TABLE stg_customer (customer_id VARCHAR(10), city VARCHAR(50));
INSERT INTO stg_customer VALUES ('C101', 'Vancouver'), ('C102', 'Dallas'), ('C103', 'Chicago');


-- Step 1: The MERGE source: every staging row, plus a second copy of each CHANGED row with merge_key = NULL
SELECT s.customer_id AS merge_key, s.customer_id, s.city
FROM stg_customer s
UNION ALL
SELECT NULL, s.customer_id, s.city
FROM stg_customer s
JOIN dim_customer d
  ON d.customer_id = s.customer_id AND d.is_current = 'Y'
WHERE s.city <> d.city
ORDER BY customer_id, merge_key;

-- Step 2: One MERGE statement does the whole SCD Type 2 load
MERGE INTO dim_customer d
USING (
  SELECT s.customer_id AS merge_key, s.customer_id, s.city
  FROM stg_customer s
  UNION ALL
  SELECT NULL, s.customer_id, s.city
  FROM stg_customer s
  JOIN dim_customer d
    ON d.customer_id = s.customer_id AND d.is_current = 'Y'
  WHERE s.city <> d.city
) src
ON d.customer_id = src.merge_key AND d.is_current = 'Y'
WHEN MATCHED AND d.city <> src.city THEN
  UPDATE SET valid_to = DATE '2026-05-31', is_current = 'N'
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, valid_from, valid_to, is_current)
  VALUES (src.customer_id, src.city,
          DATE '2026-06-01', DATE '9999-12-31', 'Y');

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

-- Step 3: Run the same MERGE again: nothing changes (safe to re-run)
MERGE INTO dim_customer d
USING (
  SELECT s.customer_id AS merge_key, s.customer_id, s.city
  FROM stg_customer s
  UNION ALL
  SELECT NULL, s.customer_id, s.city
  FROM stg_customer s
  JOIN dim_customer d
    ON d.customer_id = s.customer_id AND d.is_current = 'Y'
  WHERE s.city <> d.city
) src
ON d.customer_id = src.merge_key AND d.is_current = 'Y'
WHEN MATCHED AND d.city <> src.city THEN
  UPDATE SET valid_to = DATE '2026-05-31', is_current = 'N'
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, valid_from, valid_to, is_current)
  VALUES (src.customer_id, src.city,
          DATE '2026-06-01', DATE '9999-12-31', 'Y');

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

-- Step 4: Proof: still 4 rows, 3 of them current
SELECT is_current, COUNT(*) AS row_count
FROM dim_customer
GROUP BY is_current
ORDER BY is_current;

Key points

  • MERGE: UPDATE or INSERT per source row, atomically
  • A changed SCD2 row needs two actions
  • Trick: a second copy with a NULL merge key
  • Matched copy expires, NULL copy inserts
  • Re-run safe: idempotent
Next in this series

Question 35 — SCD Types 4 and 6 What are SCD Type 4 and SCD Type 6?

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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)