Implementing SCD type 2 with MERGE INTO

Description

Lesson 4 historized P002 by hand, with two statements you wrote, sequenced, and ran yourself. This lesson does exactly the same thing, but with the tool DuckDB designed specifically for this pattern: MERGE INTO. Instead of writing an UPDATE and trusting the order is correct, MERGE INTO compares a staging table — staging_product, the catalog's new snapshot — against dim_product_scd in a single statement, decides which rows genuinely changed, and safely closes them. You're going to run the MERGE twice: the first time with products_v1 (identical to the current state, zero real changes), the second with products_v2 (P002's real change) — and you're going to confirm, with both runs' literal output, that the statement is safe to repeat when nothing changed, and that it correctly historizes when something does change.

Connection to the module. This is the module's central lesson — the one this guide's design explicitly calls the "star" piece. Everything before it — the problem (lesson 2), the solution that loses history (lesson 3), the manual solution that preserves it (lesson 4) — exists so this lesson makes sense: MERGE INTO isn't a magic statement, it's the automation, verified line by line against DuckDB's official documentation, of exactly the same "close the old one, open the new one" mechanics you already built by hand.

An analogy: the same procedure, but at a single window

In lesson 4, historizing P002's change was like doing a procedure at two different windows in the same office: first you went to the "close expired records" window, then to the "open new records" window, and you had to stand in both lines in the right order so the system was never left in an inconsistent state. MERGE INTO is the office that redesigned its process: a single window, a single form, that internally decides — comparing what already exists against what just arrived — which records to close and which to create, without the person doing the procedure having to worry about order. The final result is identical to the two-window version — you confirmed that in lesson 4 — but the risk of someone doing the procedure in the wrong order disappears, because there are no longer two separate steps a human has to sequence correctly.

Worked example: MERGE INTO, run twice

First, the dim_product_scd table, in the same initial state as lesson 4 — in effect from August 1, 2026, with no change yet:

# scd_type2_merge.py
import duckdb

from kiosko import DIM_PRODUCT

con = duckdb.connect()
con.execute("CREATE SEQUENCE product_key_seq START 1")
con.execute("""
    CREATE TABLE dim_product_scd (
        product_key  INTEGER PRIMARY KEY,
        product_id   VARCHAR NOT NULL,
        product_name VARCHAR,
        category     VARCHAR,
        unit_cost    DOUBLE,
        valid_from   DATE NOT NULL,
        valid_to     DATE,
        is_current   BOOLEAN NOT NULL DEFAULT true
    )
""")
con.executemany(
    "INSERT INTO dim_product_scd VALUES (nextval('product_key_seq'), ?, ?, ?, ?, DATE '2026-08-01', NULL, true)",
    [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT],
)

Now, lesson 2's two snapshots — products_v1 (no changes), products_v2 (P002's real change) — loaded, one at a time, as the staging table:

PRODUCTS_V1 = [
    ("P001", "Bottled Water 600ml", "beverages", 0.40),
    ("P002", "Energy Bar", "snacks", 0.60),
    ("P003", "Instant Coffee Sachet", "beverages", 0.35),
    ("P004", "Phone Charger Cable", "electronics", 2.10),
]
PRODUCTS_V2 = [
    ("P001", "Bottled Water 600ml", "beverages", 0.40),
    ("P002", "Energy Bar", "health-snacks", 0.68),
    ("P003", "Instant Coffee Sachet", "beverages", 0.35),
    ("P004", "Phone Charger Cable", "electronics", 2.10),
]


def load_staging(products):
    con.execute("DROP TABLE IF EXISTS staging_product")
    con.execute("""
        CREATE TABLE staging_product (
            product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE
        )
    """)
    con.executemany("INSERT INTO staging_product VALUES (?, ?, ?, ?)", products)

And MERGE INTO itself — this lesson's central statement, verified against DuckDB's official "Merge Statement for SCD Type 2" guide:

def merge_scd(change_date):
    print(f"--- MERGE INTO dim_product_scd (change_date = {change_date}) ---")
    result = con.sql(f"""
        MERGE INTO dim_product_scd AS target
        USING staging_product AS source
        ON target.product_id = source.product_id AND target.is_current = true
        WHEN MATCHED AND (
            target.unit_cost <> source.unit_cost OR
            target.category  <> source.category
        ) THEN UPDATE SET
            valid_to   = DATE '{change_date}' - INTERVAL 1 DAY,
            is_current = false
        RETURNING merge_action, product_id, category, unit_cost, valid_to, is_current
    """)
    print(result)

    # Second statement: opens the new row ONLY for the product_ids the MERGE
    # just closed in this run -- the RETURNING above is, literally, that
    # exact list, captured with fetchall() before building the INSERT.
    changed_ids = [row[1] for row in result.fetchall()]
    if changed_ids:
        placeholders = ", ".join("?" for _ in changed_ids)
        con.execute(f"""
            INSERT INTO dim_product_scd (product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current)
            SELECT nextval('product_key_seq'), source.product_id, source.product_name, source.category, source.unit_cost,
                   DATE '{change_date}', NULL, true
            FROM staging_product AS source
            WHERE source.product_id IN ({placeholders})
        """, changed_ids)

What to expect (MERGE #1, with products_v1 — no real changes). Running load_staging(PRODUCTS_V1) followed by merge_scd("2026-08-15"), the output is exactly this:

--- MERGE INTO dim_product_scd (change_date = 2026-08-15) ---
┌──────────────┬────────────┬──────────┬───────────┬──────────┬────────────┐
│ merge_action │ product_id │ category │ unit_cost │ valid_to │ is_current │
│   varchar    │  varchar   │ varchar  │  double   │   date   │  boolean   │
└──────────────┴────────────┴──────────┴───────────┴──────────┴────────────┘
                                   0 rows

Zero rows. No product from products_v1 differs from the current version in dim_product_scd — that makes sense, because products_v1 is identical to the catalog the table was initialized with — so the WHEN MATCHED AND (...) clause doesn't fire for any row. Stop on this, because it's this lesson's first important confirmation: running MERGE INTO with unchanged data is completely safe. It historizes nothing extra, creates no phantom rows, breaks nothing — it's idempotent against unchanged data, exactly the property you need if you're going to run this same process every day, whether or not there was a real change that day.

What to expect (MERGE #2, with products_v2 — P002's real change). Running load_staging(PRODUCTS_V2) followed by merge_scd("2026-08-15"), the output is exactly this:

--- MERGE INTO dim_product_scd (change_date = 2026-08-15) ---
┌──────────────┬────────────┬──────────┬───────────┬────────────┬────────────┐
│ merge_action │ product_id │ category │ unit_cost │  valid_to  │ is_current │
│   varchar    │  varchar   │ varchar  │  double   │    date    │  boolean   │
├──────────────┼────────────┼──────────┼───────────┼────────────┼────────────┤
│ UPDATE       │ P002       │ snacks   │       0.6 │ 2026-08-14 │ false      │
└──────────────┴────────────┴──────────┴───────────┴────────────┴────────────┘

Now yes: one row, merge_action = 'UPDATE', exactly P002 — the only row where category or unit_cost in staging_product differ from the current version in dim_product_scd. Notice the returned columns: category and unit_cost show the old values (snacks, 0.6) — a MERGE's RETURNING reflects the row's state after applying the UPDATE, and since this UPDATE only modifies valid_to and is_current, category and unit_cost remain the values from the version that just got closed. After the MERGE, the follow-up INSERT creates the new row. The final state:

print("\n=== dim_product_scd, final state after the two MERGE runs ===")
print(con.sql("SELECT * FROM dim_product_scd ORDER BY product_id, product_key"))

print("\n=== Verification: P002 has exactly 2 rows, 1 current ===")
print(con.sql("""
    SELECT product_id, COUNT(*) AS total_versions,
           SUM(CASE WHEN is_current THEN 1 ELSE 0 END) AS current_versions
    FROM dim_product_scd WHERE product_id = 'P002' GROUP BY product_id
"""))

=== dim_product_scd, final state after the two MERGE runs ===
┌─────────────┬────────────┬───────────────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │     product_name      │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │        varchar        │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼───────────────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           1 │ P001       │ Bottled Water 600ml   │ beverages     │       0.4 │ 2026-08-01 │ NULL       │ true       │
│           2 │ P002       │ Energy Bar            │ snacks        │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
│           5 │ P002       │ Energy Bar            │ health-snacks │      0.68 │ 2026-08-15 │ NULL       │ true       │
│           3 │ P003       │ Instant Coffee Sachet │ beverages     │      0.35 │ 2026-08-01 │ NULL       │ true       │
│           4 │ P004       │ Phone Charger Cable   │ electronics   │       2.1 │ 2026-08-01 │ NULL       │ true       │
└─────────────┴────────────┴───────────────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

=== Verification: P002 has exactly 2 rows, 1 current ===
┌────────────┬────────────────┬──────────────────┐
│ product_id │ total_versions │ current_versions │
│  varchar   │     int64      │      int128      │
├────────────┼────────────────┼──────────────────┤
│ P002       │              2 │                1 │
└────────────┴────────────────┴──────────────────┘

Exactly the same final result as lesson 4 — five rows total, P002 with two versions, product_key = 2 and product_key = 5 — but produced by two runs of a generic statement, capable of handling four products or four thousand without changing a single line of code.

Diagram: what each part of the MERGE does

flowchart TD
    A["staging_product\n(the new snapshot)"] --> C{"ON target.product_id = source.product_id\nAND target.is_current = true"}
    B["dim_product_scd\n(each product's current version)"] --> C
    C -->|"matches, AND\ncategory/unit_cost CHANGED"| D["WHEN MATCHED AND (...)\nTHEN UPDATE SET valid_to, is_current=false"]
    C -->|"matches, no real change"| E["no action\n(the product is already up to date)"]
    D --> F["follow-up INSERT\nopens the new row,\nis_current=true"]

What ON target.product_id = source.product_id AND target.is_current = true compares
──────────────────────────────────────────────────────────────────────────────────
staging_product (source)         dim_product_scd, ONLY the current row (target)
P001 beverages    0.40      <->  P001 beverages    0.40   (current)  -- no change
P002 health-snacks 0.68     <->  P002 snacks       0.60   (current)  -- REAL change
P003 beverages    0.35      <->  P003 beverages    0.35   (current)  -- no change
P004 electronics  2.10      <->  P004 electronics  2.10   (current)  -- no change

Going deeper: why the MERGE needs a follow-up INSERT, and what deliberately gets left out

It's reasonable to ask why, if MERGE INTO is so capable, it can't close the old row and open the new row in the same statement. The reason is the same one you already saw in lesson 4, now applied to MERGE: the condition ON target.product_id = source.product_id AND target.is_current = true makes the MERGE find, for P002, one match — the current version's row — and that match fires exactly one branch (WHEN MATCHED). There's no way for that same source row to fire, at the same time, an UPDATE on the old row and an INSERT of a new row — each source row produces, at most, one action per branch. DuckDB's official guide solves this exactly like this lesson does: the MERGE closes the versions that changed, and a follow-up INSERT — a separate statement, run immediately after — opens the new version for each just-closed product.

It's worth pausing on how that follow-up INSERT precisely decides which products to open a new row for. DuckDB's official guide filters by target.end_date = CURRENT_DATE - INTERVAL '1 day' — in a real production pipeline, running once a day with today's date, that filter unambiguously identifies "what just got closed," because nothing else could have been closed with yesterday's date in the same run. This lesson adapts that idea with a fixed date ('2026-08-15'), but a fixed date carries a risk CURRENT_DATE doesn't have: if the MERGE runs more than once with the same change_date — exactly what this lesson's exercise 1 is going to have you do — a filter based only on the date would find the same already-closed row again in later runs, and insert a duplicate version every time. That's why merge_scd() doesn't filter by date to decide what to insert: it captures, with result.fetchall(), the exact list of product_ids this specific run just closed — empty if nothing changed, with P002 only the first time it genuinely changes — and the follow-up INSERT restricts itself to that list. It's the same idempotency guarantee you already saw in MERGE #1, now extended to the INSERT that accompanies it too.

It's worth naming, without implementing it for Kiosko, the complete pattern DuckDB's official guide documents, because a production catalog is rarely as stable as Kiosko's in this exercise. The complete pattern includes two additional clauses this lesson doesn't need: WHEN NOT MATCHED BY SOURCE AND target.is_current = true THEN UPDATE SET ... — closes the current version of any product that disappeared from the source (for example, if Kiosko discontinued P004 entirely); and WHEN NOT MATCHED BY TARGET THEN INSERT (...) — directly inserts any product that's completely new, with no prior version in dim_product_scd (for example, if Kiosko added a P005 to its catalog). This lesson doesn't implement them because Kiosko's catalog, in this module, always has exactly the same four products — none gets added, none gets discontinued — so those two branches would never fire with this exercise's data. In a real production catalog, where products do enter and leave, those two clauses are just as necessary as the one you did implement — DuckDB's official guide, cited in this lesson's Resources, shows all three branches together.

Common mistakes

The classic mistake: forgetting to close the old row, and ending up with two is_current = true. What happens: someone, in a hurry, runs only P002's new row INSERT — without the MERGE that should precede it, or with a MERGE whose WHEN MATCHED AND (...) condition never triggers due to a typo — and ends up with two P002 rows, both with is_current = true. See it yourself, in a separate script (scd_error_demo.py), which rebuilds dim_product_scd exactly as at the start of this lesson, but this time with only the INSERT, without the MERGE that should precede it:

# scd_error_demo.py -- the classic mistake, isolated, so you can see it with evidence
import duckdb

from kiosko import DIM_PRODUCT

con = duckdb.connect()
con.execute("CREATE SEQUENCE product_key_seq START 1")
con.execute("""
    CREATE TABLE dim_product_scd (
        product_key INTEGER PRIMARY KEY, product_id VARCHAR NOT NULL, product_name VARCHAR,
        category VARCHAR, unit_cost DOUBLE, valid_from DATE NOT NULL, valid_to DATE,
        is_current BOOLEAN NOT NULL DEFAULT true
    )
""")
con.executemany(
    "INSERT INTO dim_product_scd VALUES (nextval('product_key_seq'), ?, ?, ?, ?, DATE '2026-08-01', NULL, true)",
    [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT],
)

# The mistake: INSERT of the new row WITHOUT the MERGE that closes the old one first.
con.execute("""
    INSERT INTO dim_product_scd (product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current)
    VALUES (nextval('product_key_seq'), 'P002', 'Energy Bar', 'health-snacks', 0.68, DATE '2026-08-15', NULL, true)
""")
print(con.sql("SELECT product_key, product_id, category, unit_cost, valid_from, valid_to, is_current FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))
print(con.sql("""
    SELECT product_id, COUNT(*) AS total_versions, SUM(CASE WHEN is_current THEN 1 ELSE 0 END) AS current_versions
    FROM dim_product_scd WHERE product_id = 'P002' GROUP BY product_id
"""))

┌─────────────┬────────────┬───────────────┬───────────┬────────────┬──────────┬────────────┐
│ product_key │ product_id │   category    │ unit_cost │ valid_from │ valid_to │ is_current │
│    int32    │  varchar   │    varchar    │  double   │    date    │   date   │  boolean   │
├─────────────┼────────────┼───────────────┼───────────┼────────────┼──────────┼────────────┤
│           2 │ P002       │ snacks        │       0.6 │ 2026-08-01 │ NULL     │ true       │
│           5 │ P002       │ health-snacks │      0.68 │ 2026-08-15 │ NULL     │ true       │
└─────────────┴────────────┴───────────────┴───────────┴────────────┴──────────┴────────────┘

┌────────────┬────────────────┬──────────────────┐
│ product_id │ total_versions │ current_versions │
│  varchar   │     int64      │      int128      │
├────────────┼────────────────┼──────────────────┤
│ P002       │              2 │                2 │
└────────────┴────────────────┴──────────────────┘

current_versions = 2 — the classic mistake, in numbers. Notice the row with product_key = 2: it still has valid_to = NULL, exactly as if it had never changed — nobody closed it. Why it happens: in a multi-step pipeline, it's easy for a change in execution order, a partial retry after a failure, or simply a copy-pasted INSERT without its corresponding MERGE, to leave the old row unclosed. How to spot it: exactly with the query above — SUM(CASE WHEN is_current THEN 1 ELSE 0 END) grouped by product_id should never exceed 1 — turn it into a data-quality check you run after every MERGE, not something you discover by accident. How to fix it: any product_id with more than one is_current = true row means that, at some point, an INSERT ran without its corresponding closing UPDATE — the fix is always the same discipline from lesson 4: close before opening, and verify after every run that no product_id ended up with more than one current version.

Confusing WHEN MATCHED with "the product exists in staging," instead of "the product exists AND its current version matches." What happens: someone writes the ON condition without AND target.is_current = true, that is, just ON target.product_id = source.product_id. With a single version per product (before the first change), this shows no problem — but once P002 already has two versions, the MERGE would find two target rows matching the same source row (product_id = 'P002'), an ambiguity that can cause the UPDATE to apply to the wrong row — the already-closed one, instead of the current one. Why it happens: with a small catalog, with no history yet, the is_current = true filter seems unnecessary. How to spot it: if, after a second P002 change, the MERGE reports a "multiple rows match" error or, worse, silently modifies the historical row instead of the current one, you're missing this filter. How to fix it: a MERGE's ON condition over an SCD-2 dimension always needs to restrict the target side to the current row — AND target.is_current = true, no exceptions, exactly as in DuckDB's official pattern.

Running the MERGE without a freshly loaded staging_product, reusing data from a previous run. What happens: someone runs merge_scd("2026-08-15") a second time, intending to simulate "a third change," but forgets to first call load_staging(...) with a new snapshot — staging_product still has the same data from the previous run. Why it happens: it's easy to assume the MERGE "remembers" what the last applied change was, when in reality it compares, every time, against whatever is in staging_product at that moment. How to spot it: if the MERGE reports 0 rows when you expected a new change, check first what staging_product contains at that moment — it's the most common cause of a MERGE that "does nothing" with no visible error. How to fix it: staging_product should always be reloaded with the correct snapshot before every MERGE run — exactly this lesson's load_staging(PRODUCTS_V1) / load_staging(PRODUCTS_V2) pattern, never implicitly assumed.

Exercises

Exercise 1 — Run the MERGE a third time, with products_v2 again, and explain the result. Without changing anything about the catalog, call load_staging(PRODUCTS_V2) again followed by merge_scd("2026-08-15"). Predict, before running it, whether the MERGE is going to report any change.

load_staging(PRODUCTS_V2)
merge_scd("2026-08-15")
print(con.sql("SELECT COUNT(*) AS total_rows FROM dim_product_scd"))

┌──────────────┬────────────┬──────────┬───────────┬──────────┬────────────┐
│ merge_action │ product_id │ category │ unit_cost │ valid_to │ is_current │
│   varchar    │  varchar   │ varchar  │  double   │   date   │  boolean   │
└──────────────┴────────────┴──────────┴───────────┴──────────┴────────────┘
                                   0 rows

┌────────────┐
│ total_rows │
│   int64    │
├────────────┤
│          5 │
└────────────┘

Exercise 2 — Simulate a second real change: P002's cost goes up again, on 2026-08-25, to 0.72, with no category change. Write PRODUCTS_V3, load it as staging, and run the MERGE with change_date = "2026-08-25". Check how many versions P002 has at the end.

PRODUCTS_V3 = [
    ("P001", "Bottled Water 600ml", "beverages", 0.40),
    ("P002", "Energy Bar", "health-snacks", 0.72),
    ("P003", "Instant Coffee Sachet", "beverages", 0.35),
    ("P004", "Phone Charger Cable", "electronics", 2.10),
]
load_staging(PRODUCTS_V3)
merge_scd("2026-08-25")
print(con.sql("SELECT product_key, category, unit_cost, valid_from, valid_to, is_current FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))

┌─────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           2 │ snacks        │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
│           5 │ health-snacks │      0.68 │ 2026-08-15 │ 2026-08-24 │ false      │
│           6 │ health-snacks │      0.72 │ 2026-08-25 │ NULL       │ true       │
└─────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

Exercise 3 — Explain why MERGE #2's RETURNING shows category = 'snacks' (the old value) and not category = 'health-snacks' (the new value). In 2-3 sentences, using what you learned about exactly what the WHEN MATCHED clause does, explain why this lesson's RETURNING shows the values of the row that got closed, not the one that got opened.

Summary and next step

This lesson implemented production SCD type 2: MERGE INTO dim_product_scd USING staging_product, with an ON condition restricting the comparison to each product's current version, a WHEN MATCHED AND (...) clause detecting real changes in category or unit_cost, and a follow-up INSERT that opens the new version. You ran the MERGE twice — once with no real changes, once with P002's real change — and confirmed, with RETURNING and a verification query, that the result is identical to lesson 4's, but produced by a generic, safe-to-repeat statement.

Before moving on you should be able to: write from memory the structure of MERGE INTO ... USING ... ON ... WHEN MATCHED AND (...) THEN UPDATE SET ...; explain why it needs a follow-up INSERT instead of doing everything in a single statement; and reproduce, without looking, this lesson's classic mistake — forgetting to close the old row — and how to detect it with a single query.

Lesson 6 gives judgment to everything you built: not every column in dim_product_scd deserves the same treatment — you're going to decide, column by column, which ones need SCD type 2 and which get corrected with SCD type 1, even within the same historized table.

Resources

• DuckDB — official MERGE INTO statement documentation — the complete syntax reference (WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, WHEN NOT MATCHED BY TARGET, RETURNING) this lesson implements and verifies. duckdb.org/docs/lts/sql/statements/merge_into. In English.
• DuckDB — official "Merge Statement for SCD Type 2" guide — the complete reference pattern, with all three branches (WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, WHEN NOT MATCHED BY TARGET) that Kiosko doesn't need in this exercise but a production catalog does. duckdb.org/docs/current/guides/sql_features/merge. In English.
• Kimball Group — "Slowly Changing Dimension Type 2" — the formal definition this lesson implements with MERGE INTO. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.