Module 4: Slowly Changing Dimensions

The problem: dim_product isn't static

Next →

Description

dim_product, as modules 2 and 3 left it, is a snapshot: four products, each with a product_id, a product_name, a category, and a unit_cost, captured at one instant and frozen there ever since. That snapshot has been enough for everything this guide built so far — the star schema, the snowflake, the wide table — because none of those lessons needed to ask "and was this the same last week too?" This lesson breaks that comfort with a concrete case: on August 15, 2026, Kiosko receives a real update from its P002 (Energy Bar) supplier — the wholesale cost goes up from 0.60 to 0.68, and, in the same change, Kiosko decides to reclassify the product from snacks to a new, more specific category, health-snacks, as part of repositioning its energy bar line.

Connection to the module. This lesson doesn't historize anything yet — that starts in lesson 3. What it does is declare, with executed evidence, exactly what changed: two fixed snapshots of Kiosko's product catalog, products_v1 (the state through August 14) and products_v2 (the state from August 15 on), compared column by column with a SQL query that isolates the exact difference, without assuming it. Lessons 3, 4, and 5 of this module apply three different solutions to this same change, exactly as you declared it here.

An analogy: the catalog page someone edits in silence

Imagine a printed catalog, the kind a store mails out each season. Someone, at some point, decides to correct a page: they change a product's price, move it to another section. If that person simply reprints the page and swaps it into the master catalog — without saving the previous version anywhere — any future question about "how much did this product cost in the July edition?" becomes impossible to answer. The old page no longer exists anywhere; it was replaced, not archived.

That's exactly what would happen to dim_product if Kiosko simply overwrote P002's row on August 15: the cost 0.60 and the category snacks would disappear without a trace, replaced by 0.68 and health-snacks, as if those values had never existed. This lesson doesn't yet solve how to avoid that loss — that's lessons 3 and 4's job — it first needs to make completely clear, with an executed comparison, what would be lost if nothing were done about it.

Worked example: two snapshots, one real difference

First, the catalog exactly as you know it from module 1 — products_v1, the state in effect through August 14, 2026, identical to DIM_PRODUCT from kiosko.py — and products_v2, the state in effect from August 15 on, with P002's real change already applied:

# products_snapshots.py
import duckdb

from kiosko import DIM_PRODUCT

# products_v1: Kiosko's catalog exactly as you know it from module 1,
# in effect through 2026-08-14 inclusive.
PRODUCTS_V1 = [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT]

# products_v2: the same catalog, in effect from 2026-08-15. One real change:
# P002 (Energy Bar) goes up in cost (0.60 -> 0.68) and changes category (snacks -> health-snacks),
# the same day, for the same reason -- Kiosko repositions its energy bar line
# and renegotiates the wholesale cost with its supplier in the same catalog update.
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),
]

con = duckdb.connect()
con.execute("CREATE TABLE products_v1 (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
con.executemany("INSERT INTO products_v1 VALUES (?, ?, ?, ?)", PRODUCTS_V1)

con.execute("CREATE TABLE products_v2 (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
con.executemany("INSERT INTO products_v2 VALUES (?, ?, ?, ?)", PRODUCTS_V2)

print("=== products_v1: Kiosko's catalog through 2026-08-14 ===")
print(con.sql("SELECT * FROM products_v1 ORDER BY product_id"))

print("=== products_v2: Kiosko's catalog from 2026-08-15 ===")
print(con.sql("SELECT * FROM products_v2 ORDER BY product_id"))

print("=== Exact difference: what changed, and on which product ===")
print(con.sql("""
    SELECT
        v1.product_id,
        v1.category   AS category_before,
        v2.category   AS category_after,
        v1.unit_cost  AS unit_cost_before,
        v2.unit_cost  AS unit_cost_after
    FROM products_v1 v1
    JOIN products_v2 v2 ON v1.product_id = v2.product_id
    WHERE v1.category <> v2.category OR v1.unit_cost <> v2.unit_cost
"""))

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

=== products_v1: Kiosko's catalog through 2026-08-14 ===
┌────────────┬───────────────────────┬─────────────┬───────────┐
│ product_id │     product_name      │  category   │ unit_cost │
│  varchar   │        varchar        │   varchar   │  double   │
├────────────┼───────────────────────┼─────────────┼───────────┤
│ P001       │ Bottled Water 600ml   │ beverages   │       0.4 │
│ P002       │ Energy Bar            │ snacks      │       0.6 │
│ P003       │ Instant Coffee Sachet │ beverages   │      0.35 │
│ P004       │ Phone Charger Cable   │ electronics │       2.1 │
└────────────┴───────────────────────┴─────────────┴───────────┘

=== products_v2: Kiosko's catalog from 2026-08-15 ===
┌────────────┬───────────────────────┬───────────────┬───────────┐
│ product_id │     product_name      │   category    │ unit_cost │
│  varchar   │        varchar        │    varchar    │  double   │
├────────────┼───────────────────────┼───────────────┼───────────┤
│ P001       │ Bottled Water 600ml   │ beverages     │       0.4 │
│ P002       │ Energy Bar            │ health-snacks │      0.68 │
│ P003       │ Instant Coffee Sachet │ beverages     │      0.35 │
│ P004       │ Phone Charger Cable   │ electronics   │       2.1 │
└────────────┴───────────────────────┴───────────────┴───────────┘

=== Exact difference: what changed, and on which product ===
┌────────────┬─────────────────┬────────────────┬──────────────────┬─────────────────┐
│ product_id │ category_before │ category_after │ unit_cost_before │ unit_cost_after │
│  varchar   │     varchar     │    varchar     │      double      │     double      │
├────────────┼─────────────────┼────────────────┼──────────────────┼─────────────────┤
│ P002       │ snacks          │ health-snacks  │              0.6 │            0.68 │
└────────────┴─────────────────┴────────────────┴──────────────────┴─────────────────┘

Stop at the last query, because it's this lesson's core. products_v1 and products_v2 each have four rows — the same number, the same four product_ids. The difference isn't in how many rows there are, it's in the content of one of them: P001, P003, and P004 don't show up in the filtered JOIN's result, because nothing about them changed; P002 does show up, with its two changed columns — category and unit_cost — in the same update. This query is exactly the kind of check you're going to use, in lesson 5, inside MERGE INTO's WHEN MATCHED AND (...) clause: the condition that decides whether a row needs historizing is, literally, "did any of these values change?" — the same question, resolved with <> in a WHERE here and in a WHEN MATCHED AND later on.

Diagram: the fixed snapshot, and the question it can't answer

┌──────────────────────────────────────────────────────────────────┐
│  dim_product TODAY (modules 2-3, one row per product)              │
│                                                                      │
│  product_key │ product_id │ category │ unit_cost                   │
│      2       │    P002    │  snacks  │    0.60                     │
│                                                                      │
│  Question it CAN answer:                                            │
│    "What is P002's category and cost TODAY?"  -> snacks, 0.60       │
│                                                                      │
│  Question it CANNOT answer (yet):                                   │
│    "What was P002's category and cost on 2026-08-10?"               │
│    -> depends on whether it already changed, and this table doesn't │
│       store when                                                    │
└──────────────────────────────────────────────────────────────────┘
                              │
                              │  on 2026-08-15, P002 changes:
                              │  category: snacks -> health-snacks
                              │  unit_cost: 0.60 -> 0.68
                              v
┌──────────────────────────────────────────────────────────────────┐
│  If OVERWRITTEN (what lesson 3 is going to build and criticize)    │
│  product_key │ product_id │  category     │ unit_cost              │
│      2       │    P002    │ health-snacks │    0.68                │
│  "snacks" and "0.60" disappear -- no trace they ever existed       │
└──────────────────────────────────────────────────────────────────┘
                              │
                              │  or if HISTORIZED (lesson 4 onward)
                              v
┌──────────────────────────────────────────────────────────────────┐
│  product_key │ product_id │  category     │ unit_cost │ is_current│
│      2       │    P002    │  snacks       │    0.60   │  false    │
│      5       │    P002    │  health-snacks│    0.68   │  true     │
│  Both versions exist. The 2026-08-10 question CAN be answered:     │
│  it falls within the first row's validity range.                   │
└──────────────────────────────────────────────────────────────────┘

Going deeper: why the change happens after Kiosko's order week, and why that matters

Notice the change's exact date: August 15, 2026. It isn't arbitrary. The complete week of Kiosko orders you know from module 1 — the forty orders in raw_orders.py, from ORD-1001 to ORD-7003 — happens between August 3rd and 9th, that is, before P002 changes price and category. This isn't a coincidence of convenience: it's a deliberate decision that separates two different questions, so this module can answer the first without mixing it with the second.

The first question — the one this module answers — is purely structural: "what does a dimension that preserves its history, instead of losing it, look like?" To answer it, you don't need any already-recorded order to depend on the change; it's enough for the dimension, on its own, to correctly store both versions. The second question — which module 5 is going to answer — is about the change's consequences: "if an order had been recorded after August 15, what would happen to a report joining that order against the dimension, if the JOIN is written incorrectly?" That question needs, as an ingredient, exactly the historized dimension this module is going to leave built — with both versions of P002, each with its exact validity range — and a fact that can fall, depending on its date, on one side or the other of the change. Building the dimension first, without mixing it with the fact yet, is what lets you understand each piece separately before seeing them interact badly (or well) in the next module.

Common mistakes

Assuming "changing category" and "changing cost" are two separate events that need to be historized separately. What happens: someone, seeing P002 change two columns at once, designs two different new rows — one for the category change, another for the cost change — instead of a single row that captures both changes together. Why it happens: it seems "cleaner" to separate each change into its own event, especially if they come from different data sources in a real system. How to spot it: if your dim_product_scd ends up with three versions of P002 instead of two, you split something that happened at a single instant. How to fix it: in this module, both of P002's changes happen in the same catalog update, on the same day — they get compared and applied as a single difference between products_v1 and products_v2, exactly as this lesson's query did. If a real system received the two changes at different moments, it would indeed generate two separate versions — but that isn't Kiosko's case in this module.

Comparing the catalogs "by eye" instead of with a query. What happens: someone looks at the two product lists — PRODUCTS_V1 and PRODUCTS_V2 — line by line, and concludes from memory which one changed, without running the JOIN with the <> condition. Why it happens: with only four products, writing a query for something "obvious at a glance" seems unnecessary. How to spot it: if your answer about what changed doesn't come from an executed query, but from a manual reading, you have no reproducible evidence — exactly the same mistake module 1 already warned about declaring the grain by intuition instead of verifying it. How to fix it: with four products the risk of human error is low, but the pattern you're building — comparing two snapshots with a JOIN and an inequality condition — is the same pattern you're going to need when the catalog has four thousand products, not four, and "at a glance" stops being an option.

Forgetting product_name didn't change, and treating it as if it also needed historizing. What happens: someone, preparing the comparison, includes product_name in the WHERE condition (v1.product_name <> v2.product_name OR ...), without first checking whether that name actually changed. Why it happens: it seems more "thorough" to compare every business column, not just the two that changed. How to spot it: if your difference query includes product_name in the condition, but the result still shows exactly one row (P002) with the same two changed columns, you didn't break anything — product_name simply never differs between v1 and v2 — but you overextended the condition unnecessarily. How to fix it: in this lesson, the query explicitly compares category and unit_cost, the two columns that do change. The question of what to do if product_name did change — and whether it should be treated the same as category/unit_cost — is exactly lesson 6's topic in this module.

Exercises

Exercise 1 — Verify P001, P003, and P004 don't show up in the difference. Without modifying this lesson's query, write a query that explicitly confirms those three products are identical between products_v1 and products_v2.

See solution 
print(con.sql("""
    SELECT v1.product_id, 'unchanged' AS status
    FROM products_v1 v1
    JOIN products_v2 v2 ON v1.product_id = v2.product_id
    WHERE v1.category = v2.category AND v1.unit_cost = v2.unit_cost
    ORDER BY v1.product_id
"""))

Expected output:

┌────────────┬───────────┐
│ product_id │  status   │
│  varchar   │  varchar  │
├────────────┼───────────┤
│ P001       │ unchanged │
│ P003       │ unchanged │
│ P004       │ unchanged │
└────────────┴───────────┘

Exactly the three products that didn't show up in the lesson's difference query — the inverse condition (= instead of <>) confirms, with the same evidence, the complementary side: three products with no changes, one with two columns changed, four total, not one more or one fewer.

Exercise 2 — Calculate P002's cost change percentage. Using products_v1 and products_v2, write a query that calculates how much P002's unit_cost went up, as a percentage.

See solution 
print(con.sql("""
    SELECT
        v1.product_id,
        v1.unit_cost AS cost_before,
        v2.unit_cost AS cost_after,
        ROUND((v2.unit_cost - v1.unit_cost) / v1.unit_cost * 100, 1) AS pct_increase
    FROM products_v1 v1
    JOIN products_v2 v2 ON v1.product_id = v2.product_id
    WHERE v1.product_id = 'P002'
"""))

Expected output:

┌────────────┬─────────────┬────────────┬──────────────┐
│ product_id │ cost_before │ cost_after │ pct_increase │
│  varchar   │   double    │   double   │    double    │
├────────────┼─────────────┼────────────┼──────────────┤
│ P002       │         0.6 │       0.68 │         13.3 │
└────────────┴─────────────┴────────────┴──────────────┘

P002's wholesale cost went up 13.3% — a number only calculable because products_v1 and products_v2 exist as two separate, directly comparable snapshots. If Kiosko had overwritten the catalog without keeping products_v1, this question — "how much did the cost go up?" — would stop having an answer, exactly the same structural problem lessons 3 and 4 explore with the complete dimension.

Exercise 3 — Explain, in your own words, why this lesson doesn't yet write any table called dim_product_scd. In 2-3 sentences, explain why this lesson stops at "declaring the difference" and leaves building the historized dimension for the next lesson.

See solution

This lesson has a single job: demonstrating, with executed evidence, that dim_product isn't static — that a real change exists, on a real date, over a real Kiosko product. Mixing that demonstration with building the solution (overwriting or historizing) would have blurred what the problem is and what the answer is. Separating them — the problem here, the two solutions (type 1 in lesson 3, type 2 in lesson 4) afterward — follows the same discipline module 1 already used when declaring the grain before building the star schema: understand the problem precisely before solving it.

Summary and next step

This lesson declared, with an executed query, exactly what changes in Kiosko's catalog: P002 (Energy Bar), category from snacks to health-snacks, cost from 0.60 to 0.68, on August 15, 2026 — a real change, isolated with JOIN and an inequality condition, not assumed from memory. products_v1 and products_v2 remain as the two fixed snapshots the rest of this module is going to use, over and over, to demonstrate three different solutions to the same change.

Before moving on you should be able to: name the exact product that changes (P002), its two affected columns (category, unit_cost), and the change's date (2026-08-15); explain why the change happens after Kiosko's order week (August 3-9), not during it; and write from memory the JOIN-with-<> pattern that isolates a difference between two snapshots.

Lesson 3 applies the first solution to this same change — the simplest one, and the one that loses history: SCD type 1.

Resources

← PreviousModule introduction: when a dimension changes over timeNext →SCD type 1: overwrite and lose history