SCD type 2: historize with valid_from/valid_to

Description

This lesson applies this exact same P002 change — category from snacks to health-snacks, cost from 0.60 to 0.68, on August 15, 2026 — with the technique that does preserve history: SCD type 2. Kimball's formal definition is precise: "SCD type 2 changes add a new row in the dimension with the updated attribute values," and that new row needs, at minimum, three additional columns: "1) row effective date or timestamp; 2) row expiration date or timestamp; and 3) current row indicator." At Kiosko, those three columns are called valid_from, valid_to, and is_current. You're going to build dim_product_scd with those three columns, and you're going to historize P002's change with two explicit SQL statements — an UPDATE that closes the old row, an INSERT that opens the new row — to understand exactly what mechanics need to run, before lesson 5 automates it with MERGE INTO.

Connection to the module. This lesson builds dim_product_scd by hand, with two separate statements you write and sequence yourself. It's deliberately the "long" way to solve the problem — lesson 5 is going to show that a single MERGE INTO does exactly the same thing, in one statement, more safely and more scalable to a catalog with thousands of products. But understanding the manual mechanics first is what makes MERGE INTO, in lesson 5, feel like a tool that automates something you already understand, not magic.

An analogy: the address history, again, now with the two exact entries

Go back to the analogy that opened this module: the ID document recording your address history. When you move, the system does, in essence, two separate operations, in a specific order. First, it closes the record for the previous address: it adds a "valid until" date, and marks it as no longer in effect. Second, it opens a new record for the current address: with its own "valid from" date, marked as the one in effect. No well-designed system does these two operations in the reverse order — opening the new record before closing the old one would leave, for an instant, two addresses marked "in effect" at once, something that shouldn't be possible.

This lesson does, literally, those two operations on dim_product_scd: an UPDATE that closes P002's old version (valid_to = '2026-08-14', is_current = false), followed by an INSERT that opens the new version (valid_from = '2026-08-15', valid_to = NULL, is_current = true). The order matters exactly as much as in the analogy: close first, open after.

Worked example: dim_product_scd, historized by hand

First, the table with the three validity columns SCD type 2 needs, loaded with Kiosko's complete catalog as its first version — in effect from August 1, 2026, the date Kiosko started keeping this historized dimension:

# scd_type2_manual.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],
)

print("=== dim_product_scd, initial state (in effect from 2026-08-01) ===")
print(con.sql("SELECT * FROM dim_product_scd ORDER BY product_key"))

What to expect (part 1). Running this first part, the output is exactly this:

=== dim_product_scd, initial state (in effect from 2026-08-01) ===
┌─────────────┬────────────┬───────────────────────┬─────────────┬───────────┬────────────┬──────────┬────────────┐
│ 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 │ 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       │
└─────────────┴────────────┴───────────────────────┴─────────────┴───────────┴────────────┴──────────┴────────────┘

Notice valid_to: NULL on all four rows, not an arbitrary future date. That's a deliberate decision, and the best practice DuckDB's own guide documents for this pattern: "keep end_date NULL for current rows, to improve query performance" — a NULL value in valid_to reads, unambiguously, as "still in effect, with no known expiration date," and avoids having to invent a sentinel date like 9999-12-31 for every row that never changed.

Now, the two statements that historize P002's change, in the exact order that matters:

print("\n=== Step 1: UPDATE closes P002's old version ===")
con.execute("""
    UPDATE dim_product_scd
    SET valid_to = DATE '2026-08-14', is_current = false
    WHERE product_id = 'P002' AND is_current = true
""")
print(con.sql("SELECT * FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))

print("\n=== Step 2: INSERT opens P002's new version ===")
con.execute("""
    INSERT INTO dim_product_scd VALUES
        (nextval('product_key_seq'), 'P002', 'Energy Bar', 'health-snacks', 0.68, DATE '2026-08-15', NULL, true)
""")
print(con.sql("SELECT * FROM dim_product_scd WHERE product_id = 'P002' ORDER BY product_key"))

print("\n=== dim_product_scd, final state (4 products, 5 rows) ===")
print(con.sql("SELECT * FROM dim_product_scd ORDER BY product_id, product_key"))

What to expect (steps 1 and 2). Running the rest of the script, the output is exactly this:

=== Step 1: UPDATE closes P002's old version ===
┌─────────────┬────────────┬──────────────┬──────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │ product_name │ category │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │   varchar    │ varchar  │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼──────────────┼──────────┼───────────┼────────────┼────────────┼────────────┤
│           2 │ P002       │ Energy Bar   │ snacks   │       0.6 │ 2026-08-01 │ 2026-08-14 │ false      │
└─────────────┴────────────┴──────────────┴──────────┴───────────┴────────────┴────────────┴────────────┘

=== Step 2: INSERT opens P002's new version ===
┌─────────────┬────────────┬──────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ product_key │ product_id │ product_name │   category    │ unit_cost │ valid_from │  valid_to  │ is_current │
│    int32    │  varchar   │   varchar    │    varchar    │  double   │    date    │    date    │  boolean   │
├─────────────┼────────────┼──────────────┼───────────────┼───────────┼────────────┼────────────┼────────────┤
│           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       │
└─────────────┴────────────┴──────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

=== dim_product_scd, final state (4 products, 5 rows) ===
┌─────────────┬────────────┬───────────────────────┬───────────────┬───────────┬────────────┬────────────┬────────────┐
│ 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       │
└─────────────┴────────────┴───────────────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

This is the evidence lesson 3 couldn't give: P002 now has two rows, with different product_keys (2 and 5), each with its own validity range. If someone asks "what was P002's category on August 10, 2026?", the answer is right there, with no ambiguity: the row with product_key = 2, valid_from = '2026-08-01', valid_to = '2026-08-14' — August 10th falls within that range, and the category was snacks. Also notice something easy to overlook: product_key jumps from 4 to 5, reusing no number at all — the SEQUENCE keeps growing, an already-assigned product_key is never recycled, exactly the guarantee a surrogate key needs.

Diagram: P002's complete timeline

flowchart LR
    subgraph V1["product_key = 2"]
        A["valid_from: 2026-08-01\nvalid_to: 2026-08-14\nis_current: false\ncategory: snacks\nunit_cost: 0.60"]
    end

    subgraph V2["product_key = 5"]
        B["valid_from: 2026-08-15\nvalid_to: NULL\nis_current: true\ncategory: health-snacks\nunit_cost: 0.68"]
    end

    A -->|"UPDATE closes\n(step 1)"| A
    A -.->|"INSERT opens\n(step 2)"| B

P002's timeline in dim_product_scd
────────────────────────────────────────────────────────────────────
2026-08-01              2026-08-14 │ 2026-08-15              (today)
│─────────── product_key = 2 ───────│─────── product_key = 5 ───────►
│  category: snacks, unit_cost: 0.60│  category: health-snacks,     │
│  is_current: false                │  unit_cost: 0.68              │
│  (in effect in this range)        │  is_current: true              │
────────────────────────────────────────────────────────────────────

Going deeper: why two statements, and why in this exact order

It's worth pausing on why this lesson's solution needs two separate statements — an UPDATE and an INSERT — instead of just one. The reason is structural: UPDATE and INSERT are, by definition, two different operations. UPDATE modifies columns of a row that already exists — P002's row with product_key = 2, which remains, at all times, the same physical row, only with valid_to and is_current modified. INSERT creates a new row, with a new product_key, that never existed before. There's no standard SQL statement capable of doing both things to different rows in a single, simple atomic operation — you need both, in sequence.

The order matters for a concrete reason, not just aesthetics: if the INSERT ran before the UPDATE, there would be an instant — however brief — where dim_product_scd would have two rows for P002 with is_current = true simultaneously: the old one (not yet closed) and the new one (just opened). Any query run against is_current = true during that instant would get an ambiguous result — two current categories for the same product, something that shouldn't be possible by design. Closing first, opening after, guarantees that inconsistent intermediate state never exists. This lesson's "Common mistakes" section builds exactly that broken scenario, so you see it with your own eyes before lesson 5 shows you how MERGE INTO solves this same ordering problem more safely.

A note on why product_key now uses a SEQUENCE (CREATE SEQUENCE product_key_seq) instead of ROW_NUMBER() OVER (ORDER BY product_id), the pattern you used in module 2 for dim_store and dim_product. ROW_NUMBER() recalculates the surrogate key from scratch, every time it runs, over all the table's rows at that moment — it works perfectly for a dimension that gets fully rebuilt on every load, but it would break dim_product_scd: if you renumbered every row with ROW_NUMBER() after adding P002's new version, the old version's product_key = 2 could change value, and any external table that had already referenced that product_key (for example, in an already-loaded fact) would suddenly point to the wrong row. A SEQUENCE solves this by assigning each product_key only once, growing and never reused — old rows keep their key forever, and only new rows get a number never used before.

Common mistakes

Reversing the order: INSERT first, UPDATE after. What happens: someone writes the new version's INSERT before the UPDATE that closes the old version. Why it happens: in the code, "adding the new one" feels like the main step, and "closing the old one" like a cleanup detail that could go afterward. How to spot it: run a SELECT COUNT(*) FROM dim_product_scd WHERE product_id = 'P002' AND is_current = true query right after running the INSERT but before the UPDATE — if it returns 2 instead of 1, you have two current versions at the same time, an inconsistent state no point-in-time query (module 5) can reliably interpret. How to fix it: always close the old row (UPDATE ... SET is_current = false) before opening the new one (INSERT ... is_current = true) — this lesson's exact order. Lesson 5 shows how MERGE INTO reduces this risk, though it doesn't fully eliminate it when the INSERT remains a separate statement.

Forgetting the is_current = true filter in the UPDATE. What happens: someone writes UPDATE dim_product_scd SET valid_to = ..., is_current = false WHERE product_id = 'P002', without the AND is_current = true. The first time you run this, nothing visible happens — only one P002 row exists — but if P002 ever already had more than one historical version, this UPDATE would close all of P002's versions, including ones already correctly closed, overwriting their original valid_to with the current change's date. Why it happens: with only one existing version, the is_current = true filter seems redundant. How to spot it: if, after a second P002 change, all its historical rows have the same valid_to, you lost the earlier versions' real closing date. How to fix it: the UPDATE that closes a version should always explicitly filter by is_current = true — only the current row can be closed; already-closed rows should never be touched again.

Using CURRENT_DATE instead of the change's fixed date. What happens: someone writes valid_to = CURRENT_DATE - INTERVAL 1 DAY instead of valid_to = DATE '2026-08-14', thinking about how this would look in a real production system, where the change gets applied the same day it happens. Why it happens: in a real production pipeline, CURRENT_DATE is exactly correct — the MERGE runs on the day of the change, and "today" and "the change's date" are the same thing. How to spot it: if you run this script on two different days and get different valid_to values, your script stopped being reproducible — this guide's hard rule. How to fix it: in this guide, every date is fixed and explicit — DATE '2026-08-14', DATE '2026-08-15' — precisely so the output is identical, byte for byte, no matter when the script runs. In production, you'd replace the fixed date with the process's real date — typically CURRENT_DATE — but that substitution falls outside this guide's scope for the same reason the rest of the code avoids any source of non-determinism.

Exercises

Exercise 1 — Verify P001, P003, and P004 keep exactly one row each. After historizing P002, write a query confirming how many rows each product_id has in dim_product_scd.

print(con.sql("""
    SELECT product_id, COUNT(*) AS total_versions
    FROM dim_product_scd
    GROUP BY product_id
    ORDER BY product_id
"""))

┌────────────┬────────────────┐
│ product_id │ total_versions │
│  varchar   │     int64      │
├────────────┼────────────────┤
│ P001       │              1 │
│ P002       │              2 │
│ P003       │              1 │
│ P004       │              1 │
└────────────┴────────────────┘

Exercise 2 — Reconstruct which category P002 had on three different dates. Without using is_current, write a query that, for the dates 2026-08-05, 2026-08-14, and 2026-08-20, returns P002's category in effect on each one, using valid_from/valid_to.

print(con.sql("""
    SELECT
        check_date,
        (SELECT category FROM dim_product_scd
         WHERE product_id = 'P002'
           AND check_date BETWEEN valid_from AND COALESCE(valid_to, DATE '9999-12-31')) AS category_on_that_date
    FROM (VALUES (DATE '2026-08-05'), (DATE '2026-08-14'), (DATE '2026-08-20')) AS t(check_date)
"""))

┌────────────┬───────────────────────┐
│ check_date │ category_on_that_date │
│    date    │        varchar        │
├────────────┼───────────────────────┤
│ 2026-08-05 │ snacks                │
│ 2026-08-14 │ snacks                │
│ 2026-08-20 │ health-snacks         │
└────────────┴───────────────────────┘

Exercise 3 — Explain why P002's old version's product_key (2) is lower than P003's and P004's (3 and 4), even though P002's new version (5) was created afterward. In 2-3 sentences, explain this seemingly "out of order" numbering using what you learned about SEQUENCE in this lesson's "going deeper" section.

Summary and next step

This lesson built dim_product_scd with the three columns SCD type 2 requires — valid_from, valid_to, is_current — and historized P002's change by hand, with two statements in the correct order: UPDATE to close the old version, INSERT to open the new one. The result — two rows for P002, with non-overlapping validity ranges — is the evidence lesson 3 couldn't give: any question about "what was the value on such-and-such date?" now has an exact, verifiable answer.

Before moving on you should be able to: name the three minimum columns SCD type 2 needs and what each represents; write, from memory, the UPDATE that closes a version and the INSERT that opens the next one, in the correct order; and explain why product_key in dim_product_scd uses a SEQUENCE instead of ROW_NUMBER().

Lesson 5 automates this exact same mechanics — close the old one, open the new one — with a single DuckDB statement designed specifically for this pattern: MERGE INTO.

Resources

• Kimball Group — "Slowly Changing Dimension Type 2" — the official definition of the three minimum columns (valid_from, valid_to, is_current) this lesson implements. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.
• DuckDB — official "Merge Statement for SCD Type 2" guide — the reference pattern, including the recommendation to keep end_date NULL for current rows, that this lesson follows. duckdb.org/docs/current/guides/sql_features/merge. In English.
• DuckDB — CREATE SEQUENCE documentation — the reference for the growing surrogate-key generator that replaces ROW_NUMBER() in a historized dimension. duckdb.org/docs/current/sql/statements/create_sequence. In English.