Module 4: Slowly Changing Dimensions

Module introduction: when a dimension changes over time

Next →

Why this module exists

Module 3 closed with a sentence left pending on purpose, in the very last line of its own project: "module 4 needs, as its starting point, the dim_product table in its star shape — with category as a text column, product_key as its surrogate key — exactly as module 2 left it and unchanged in this module." That table — four products, four rows, no historical version yet — already exists. This module doesn't replace it. It asks it the question no earlier module in this guide has answered yet: what happens the day a product's price or category genuinely changes?

Up to now, this guide treated dim_product as if it were fixed data: four rows that don't change, useful for joining against fact_orders, comparing against a normalized version (module 3) or a denormalized one (also module 3), but always the same four rows. That assumption was correct while you were using it — no Kiosko product changed price or category across the three previous modules — but it's also the least realistic assumption this guide has held up until now. In a real business, products change: a supplier renegotiates a cost, a marketing team reclassifies a product into a different category, a catalog gets corrected. And when that happens, the question a dimensional modeler has to answer isn't "what's the correct value for this product?" — that question has an obvious answer: the most recent one — but a much harder one: "what was the correct value for this product on the day it sold?"

This module answers that question with two complementary techniques, both with a formal name in Ralph Kimball's vocabulary: SCD type 1 (Slowly Changing Dimension type 1), which overwrites the old value with the new one and deliberately loses history, because sometimes losing it is exactly the right call; and SCD type 2, which historizes the change by adding a new row to the dimension, marked with a validity date range (valid_from, valid_to) and a flag for which version is current (is_current), preserving both versions — the old and the new — as separate rows, forever. You're going to implement SCD-2 for real, with DuckDB's MERGE INTO statement, over two real snapshots of Kiosko's product catalog — one before the change, one after — and you're going to end the module knowing, with judgment and not by habit, when each type of historization is the right decision for a specific column.

Connection to the module. This module doesn't touch fact_orders — that's lesson 5 of module 5, once a historized dimension exists to correctly join the fact against. What it builds is a new table, dim_product_scd, that coexists with dim_product (the star version, unchanged, that the rest of the guide keeps using when history isn't needed) and demonstrates, with one real Kiosko product changing price and category the same day, the complete difference between overwriting and historizing.

An analogy: an ID document's address history

Think of an identity document that records your address — an ID card, a passport, a driver's license. When you move, what does the institution issuing that document do? It doesn't erase your old address and silently replace it, as if you'd never lived there. What the best administrative systems do is exactly the opposite: they mark the old address as expired, with an exact date of how long it was valid, and register the new address with its own start-of-validity date. If some authority ever needs to know where you lived on March 15th of last year — for a legal notice, for an audit, to reconstruct a history — the system can answer precisely, because it never overwrote the data: it historized it.

That's exactly what SCD type 2 does with a row of dim_product. When a product's cost or category changes, the old row doesn't disappear — it gets marked as expired, with an expiration date (valid_to) and a flag that it's no longer the current version (is_current = false) — and a new row gets added, with its own start date (valid_from) and marked as the current version (is_current = true). Any future question about "what was this product's cost on such-and-such date?" has an exact, verifiable answer, exactly like an ID document's address history. SCD type 1, on the other hand, is the administrative system that does erase the old address with no trace: fast, simple, but incapable of answering any question about the past.

Worked example: this module's map, before building it

Before touching real Kiosko data, it's worth seeing, at a glance, what each lesson builds and how they relate to each other — the same kind of map that opened module 3 before comparing star, snowflake, and OBT.

# scd_module_map.py
CONCEPTS = [
    ("SCD type 1", "Overwrites the value in place. Fast, but loses history completely."),
    ("SCD type 2", "Adds a new row with valid_from/valid_to/is_current. Keeps every version, forever."),
    ("MERGE INTO", "The DuckDB statement that closes the old row and opens the new one in a repeatable flow."),
]

LESSONS = [
    ("The problem: dim_product isn't static", "Two snapshots of products, P002's real change declared"),
    ("SCD type 1: overwrite and lose history", "In-place UPDATE, EXECUTED"),
    ("SCD type 2: historize with valid_from/valid_to", "Two manual statements (UPDATE + INSERT), EXECUTED"),
    ("Implementing SCD type 2 with MERGE INTO", "Real MERGE INTO, run twice, EXECUTED"),
    ("Choosing type 1 vs type 2 per column", "product_name (type 1) vs category/unit_cost (type 2), EXECUTED"),
    ("SCD type 3 and other variants, briefly", "previous_category EXECUTED; type 4 and type 6 named"),
    ("Project: Kiosko's historized dim_product", "The complete pipeline from the 6 previous lessons, EXECUTED"),
]

print("=== This module's three central concepts ===\n")
for name, description in CONCEPTS:
    print(f"- {name}")
    print(f"  {description}\n")

print("=== The seven lessons that build on them ===\n")
for i, (name, description) in enumerate(LESSONS, start=2):
    print(f"L{i}. {name}")
    print(f"    {description}\n")

What to expect. Running python3 scd_module_map.py, the output is exactly this:

=== This module's three central concepts ===

- SCD type 1
  Overwrites the value in place. Fast, but loses history completely.

- SCD type 2
  Adds a new row with valid_from/valid_to/is_current. Keeps every version, forever.

- MERGE INTO
  The DuckDB statement that closes the old row and opens the new one in a repeatable flow.

=== The seven lessons that build on them ===

L2. The problem: dim_product isn't static
    Two snapshots of products, P002's real change declared

L3. SCD type 1: overwrite and lose history
    In-place UPDATE, EXECUTED

L4. SCD type 2: historize with valid_from/valid_to
    Two manual statements (UPDATE + INSERT), EXECUTED

L5. Implementing SCD type 2 with MERGE INTO
    Real MERGE INTO, run twice, EXECUTED

L6. Choosing type 1 vs type 2 per column
    product_name (type 1) vs category/unit_cost (type 2), EXECUTED

L7. SCD type 3 and other variants, briefly
    previous_category EXECUTED; type 4 and type 6 named

L8. Project: Kiosko's historized dim_product
    The complete pipeline from the 6 previous lessons, EXECUTED

No real Kiosko product has changed price yet in this map — that starts in lesson 2. But notice the order: first the problem gets laid out with evidence (lesson 2), then the naive solution that fails gets shown (lesson 3), then the correct solution gets built by hand, statement by statement, to understand exactly what it does (lesson 4), then that same solution gets automated with the right tool for the job (lesson 5), then judgment gets added — not everything needs the same treatment (lesson 6) — then the variants beyond type 1 and type 2 get briefly named (lesson 7), and only at the end does everything get integrated into the closing project (lesson 8). No lesson jumps straight to MERGE INTO without first understanding, by hand, what problem it solves.

Diagram: where you were, where you're going to be

flowchart LR
    subgraph M3["Module 3 (already written)"]
        A["dim_product star\nproduct_key, product_id,\nproduct_name, category, unit_cost\n4 rows, no history"]
    end

    subgraph M4["This module (4 of 8)"]
        B["L2: The problem\nP002 changes category and unit_cost"]
        C["L3: SCD type 1\noverwrite, EXECUTED"]
        D["L4: Manual SCD type 2\nvalid_from/valid_to/is_current"]
        E["L5: MERGE INTO\nEXECUTED 2 times"]
        F["L6: Type 1 vs type 2\nper column, EXECUTED"]
        G["L7: Type 3 and variants\nbriefly"]
        H["L8: complete dim_product_scd\nEXECUTED"]
    end

    subgraph M5["Module 5 (next)"]
        I["Point-in-time join\nagainst dim_product_scd"]
    end

    A --> B --> C --> D --> E --> F --> G --> H --> I

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: the three concepts, before building them
L2        The problem: dim_product isn't static, EXECUTED
L3        SCD type 1: overwrite and lose history, EXECUTED
L4        SCD type 2: historize by hand, with UPDATE + INSERT, EXECUTED
L5        SCD type 2 with MERGE INTO, run 2 times, EXECUTED
L6        Type 1 vs type 2 per column, EXECUTED
L7        Type 3 and other variants, briefly, partly EXECUTED
L8        Project: Kiosko's complete dim_product_scd, EXECUTED

Lessons 3, 4, and 5 are the module's executable backbone: the same question — what happens to dim_product when P002 changes price and category on August 15, 2026? — answered three times, with three different tools, so you understand not just that MERGE INTO is the right way to do it, but why the simpler alternatives fall short. Lesson 6 gives lesson 5 judgment: not every column needs the same treatment. Lesson 7 briefly opens the door to what exists beyond type 1 and type 2, without building it in depth. Lesson 8 closes with the project: the complete pipeline, start to finish, verified.

Going deeper: why this module needs the star already built

It might seem that historizing a dimension is an independent topic, one that could be taught with any example table, without depending on this guide's three previous modules. This module resists that temptation for two concrete reasons.

The first is the surrogate key. Module 2, lesson 3, already previewed — without being able to demonstrate it yet — why product_key gets generated with ROW_NUMBER() OVER (ORDER BY product_id) instead of a random identifier: "once dim_product starts versioning its rows... product_id stops identifying a single row of the dimension... product_key is going to identify a specific version of a product." That moment has arrived. Without first having understood what a surrogate key is and why it's separated from the natural key, the first question that would come up seeing dim_product_scd with two rows for P002 would be genuinely confusing: why are there two rows with the same product_id? Module 2 already answered that question, two modules in advance.

The second reason is more practical: this module needs a real product catalog, already built and verified, to apply the change to. DIM_PRODUCT — four products, defined since module 1, reused unchanged in modules 2 and 3 — is exactly that foundation. There's no need to invent a new catalog for this lesson: the same P002 (Energy Bar, category snacks, cost 0.60) you already know from this guide's very first module is the product that's going to change category and cost in this module. Nothing new to learn about the catalog — all the novelty is in what happens to it when it changes.

Common mistakes

Thinking this module modifies dim_product directly. What happens: someone, reading "historizing dim_product," assumes this module is going to alter module 2's dim_product table — adding valid_from/valid_to/is_current columns to it, turning it into the historized version. Why it happens: the module's name (slowly-changing-dimensions) and the fact that the product that changes is one you already know from module 1 reasonably, but incorrectly, suggest an in-place modification. How to spot it: if, by the end of this module, you expect dim_product (module 2's table, with no suffix) to have validity columns, you have this confusion. How to fix it: this module builds a new table, dim_product_scd, separate from dim_product. dim_product keeps existing, unchanged, as the star version the rest of the guide uses when history isn't needed — exactly the same discipline module 3 already established with dim_product_normalized and mart_daily_sales_obt: new, parallel structures, not replacements for the original.

Assuming fact_orders also needs historizing. What happens: someone, seeing a dimension can change over time, wonders whether fact_orders — the fact — also needs valid_from/valid_to. Why it happens: the vocabulary of "historizing" sounds, at first hearing, like something that could apply to any table. How to spot it: if you find yourself designing validity columns for fact_orders, you lost the central fact-versus-dimension distinction module 1, lesson 6, already established precisely. How to fix it: a fact records an event that already happened, at a fixed instant (order_ts) — it doesn't change after being recorded, and therefore doesn't need historizing. What changes over time is the descriptive context around the event — a product's cost or category, not the sale itself — and that context lives in the dimensions. SCD type 1 and type 2 are dimension techniques, exclusively.

Jumping straight to MERGE INTO without understanding the problem it solves. What happens: someone, impatient to reach the "real" production statement, wants to copy lesson 5's MERGE INTO syntax without going through lessons 2, 3, and 4, which build the problem and the manual solution first. Why it happens: MERGE INTO looks like "the answer," and the earlier lessons feel like a dispensable preamble. How to spot it: if, in lesson 5, you can't explain, without looking, why lesson 3's solution (SCD type 1) loses information that lesson 4 (manual SCD type 2) preserves, you're missing the foundation that makes MERGE INTO make sense. How to fix it: lessons 3 and 4 aren't filler — they're the evidence, built by hand, of exactly what problem MERGE INTO automates in lesson 5. Without having done it by hand once, MERGE INTO's syntax is memorization without understanding.

Exercises

Exercise 1 — Recall the exact checklist row this module resolves. Without rereading module 3's project, write from memory the exact wording of the checklist row (introduced in module 1, lesson 2) that corresponds to this module.

See solution

The row reads, literally: "Historization of a dimension that changes (SCD)." Unlike module 3 — which compared three modeling shapes without changing any data — this module resolves a row that depends on a real value changing: without a price or category change to historize, there's nothing to demonstrate. That's why this module explicitly picks a specific product and a concrete change — P002, category and cost, on August 15, 2026 — instead of reasoning in the abstract.

Exercise 2 — Explain, in your own words, the difference between dim_product and dim_product_scd. Without looking at the following lessons, write 2-3 sentences explaining which table exists today in this guide, which table this module is going to build, and why they're going to coexist instead of one replacing the other.

See solution

dim_product is the table module 2 built and module 3 used unchanged: four products, one row each, with product_key as a surrogate key but no notion of validity over time at all — it's the canonical "star" version the rest of this guide keeps using when history isn't needed. dim_product_scd is a new table this module is going to build, with the same business columns (product_id, product_name, category, unit_cost) plus three historization columns (valid_from, valid_to, is_current), capable of storing more than one row per product_id when a product changes. The two tables coexist because they solve different needs: dim_product for when the report doesn't need to know "what was the value on such-and-such date," dim_product_scd for when it does.

Exercise 3 — Predict what would happen if Kiosko never changed any product. In 2-3 sentences, explain whether dim_product_scd, built over a catalog that never changes, would have any visible difference against dim_product.

See solution

If no Kiosko product ever changed, dim_product_scd would have exactly the same four rows as dim_product — one per product — with valid_from fixed at the initial load date, valid_to always NULL, and is_current always true. The difference would be purely structural (three extra columns, not yet used), not one of data. This confirms historization isn't a cost you always pay: a dimension that genuinely never changes "costs" three extra columns with no visible benefit at all — the real payoff shows up precisely the day something changes, as is about to happen to P002 in lesson 2.

Summary and next step

This module takes dim_product as module 3 left it — four products, no history — and asks it the question no earlier lesson in this guide has answered: what happens when a product's price or category genuinely changes? You're going to answer it twice — overwriting with SCD type 1, historizing with SCD type 2 — and you're going to end up implementing production SCD-2 with MERGE INTO, over a real, verified Kiosko change: P002 (Energy Bar), category and cost, on August 15, 2026.

Before moving on you should be able to: name this module's three central concepts (type 1, type 2, MERGE INTO) and what each one does; explain why this module builds dim_product_scd as a new table instead of modifying dim_product; and say from memory the exact row of module 1's checklist this module resolves.

Lesson 2 opens the problem with evidence: two real snapshots of Kiosko's catalog, products_v1 and products_v2, compared column by column to find exactly what changed, and on which product.

Resources

← PreviousMini-project: Kiosko's three shapes comparedNext →The problem: dim_product isn't static