Mini-project: Kiosko's historized dim_product

Description

This project closes the module by integrating the six previous pieces: the problem declared with evidence (lesson 2), SCD type 1 and its loss of history (lesson 3), SCD type 2 built by hand (lesson 4), SCD type 2 automated with MERGE INTO (lesson 5), the mixed column policy (lesson 6), and the complete variant vocabulary (lesson 7). What's left is bringing it all together into a single formal deliverable: dim_product_scd built from scratch, historized with MERGE INTO run twice over Kiosko's two real catalog snapshots, verified number by number, and documented in a structure — SCD_SUMMARY — module 5 can consult without rebuilding it from scratch.

The project has five parts. First, you build dim_product_scd in its initial state, with Kiosko's four products in effect from August 1, 2026. Second, you run MERGE #1, with products_v1 — identical to the current state, zero real changes — confirming the statement is safe to repeat. Third, you run MERGE #2, with products_v2 — P002's real change — genuinely historizing the dimension. Fourth, you verify P002 ended up with exactly two versions, one current and one closed. Fifth, you document the entire process in SCD_SUMMARY, the formal structure that closes the module.

Connection to the module. This project introduces no new concept — it's the final integration of the seven previous lessons, packaged as SCD_SUMMARY, the structure module 5 of this guide can cite without rebuilding the historization from scratch.

An analogy: the complete file, closed and archived

Every lesson in this module worked on a separate piece of the file: the problem (what changed), the technique that loses it (type 1), the technique that preserves it — first by hand, then automated (type 2), the criterion for deciding case by case (per column), and the complete variant vocabulary (type 3 and beyond). This project is the moment to close the file: all those pieces, assembled into a single flow, from the empty table to the final verification, ready for anyone — including yourself, in module 5 — to trust the result without having to repeat the work.

The material: everything this module built, in a single flow

You need, in the same folder: kiosko.py (identical to modules 1, 2, and 3, with DIM_PRODUCT). You don't need any additional file — PRODUCTS_V1, PRODUCTS_V2, and the merge_scd() function get defined directly in this project's script, just like in earlier projects.

The reference solution, verified

Part 1 — Build dim_product_scd, initial state

# kiosko_scd_project.py -- Kiosko's historized dim_product, module 4 closing mini-project
import duckdb

from kiosko import DIM_PRODUCT

print("=== Kiosko: historized dim_product, module 4 final deliverable ===\n")

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("Part 1 -- dim_product_scd, initial state")
total_products = con.sql("SELECT COUNT(DISTINCT product_id) FROM dim_product_scd").fetchone()[0]
total_rows_p1 = con.sql("SELECT COUNT(*) FROM dim_product_scd").fetchone()[0]
print(f"  dim_product_scd  {total_rows_p1:3} rows, {total_products:3} products")

This first part builds nothing new — it rebuilds, exactly as in lesson 4, the historized table in effect from August 1, 2026, before any change.

Part 2 — MERGE #1: products_v1, no real changes

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)


def merge_scd(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
    """)
    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)
    return len(changed_ids)


load_staging(PRODUCTS_V1)
rows_changed_1 = merge_scd("2026-08-15")

print("\nPart 2 -- MERGE #1 (staging_product = products_v1, no real changes)")
print(f"  rows closed by the MERGE: {rows_changed_1}")
print(f"  dim_product_scd  {con.sql('SELECT COUNT(*) FROM dim_product_scd').fetchone()[0]:3} rows (unchanged)")

Exactly lesson 5's pattern: staging_product loaded with products_v1, MERGE INTO run, RETURNING captured in Python and used to decide, precisely, which products get the follow-up INSERT — zero, in this run, because products_v1 doesn't differ at all from the current state.

Part 3 — MERGE #2: products_v2, P002's real change

load_staging(PRODUCTS_V2)
rows_changed_2 = merge_scd("2026-08-15")

print("\nPart 3 -- MERGE #2 (staging_product = products_v2, P002 changes category and unit_cost)")
print(f"  rows closed by the MERGE: {rows_changed_2}")
print(f"  dim_product_scd  {con.sql('SELECT COUNT(*) FROM dim_product_scd').fetchone()[0]:3} rows")

Exactly lesson 5's MERGE #2: products_v2 brings P002's real change — category from snacks to health-snacks, unit_cost from 0.60 to 0.68 — the WHEN MATCHED AND (...) clause detects it, and the follow-up INSERT opens the new version.

Part 4 — Verify: P002 has exactly 2 versions, 1 current

p002_check = 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
""").fetchone()

print("\nPart 4 -- verification: P002 has exactly 2 versions, 1 current")
print(f"  product_id={p002_check[0]}  total_versions={p002_check[1]}  current_versions={p002_check[2]}")
assert p002_check == ("P002", 2, 1), "P002 was not correctly historized"
print("  Verification OK")

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"))

This is the part that gives the whole project its confidence: without this assert, there would be no executed evidence the historization worked — only the assumption that it did. p002_check == ("P002", 2, 1) is the same evidence-based verification discipline module 1 already demanded when declaring the grain: never assume, always confirm with a query.

Part 5 — Document as a formal structure

SCD_SUMMARY = {
    "table": "dim_product_scd",
    "total_products": 4,
    "total_rows": con.sql("SELECT COUNT(*) FROM dim_product_scd").fetchone()[0],
    "historized_product_id": "P002",
    "historized_columns": ["category", "unit_cost"],
    "change_date": "2026-08-15",
    "versions_before_change": 1,
    "versions_after_change": 2,
    "merge_runs": 2,
    "rows_changed_by_merge_run": [rows_changed_1, rows_changed_2],
}

print("\nPart 5 -- the formal declaration: SCD_SUMMARY")
for key, value in SCD_SUMMARY.items():
    print(f"  {key}: {value}")

What to expect. Running the complete python3 kiosko_scd_project.py (all five parts together), the output is exactly this:

=== Kiosko: historized dim_product, module 4 final deliverable ===

Part 1 -- dim_product_scd, initial state
  dim_product_scd    4 rows,   4 products

Part 2 -- MERGE #1 (staging_product = products_v1, no real changes)
  rows closed by the MERGE: 0
  dim_product_scd    4 rows (unchanged)

Part 3 -- MERGE #2 (staging_product = products_v2, P002 changes category and unit_cost)
  rows closed by the MERGE: 1
  dim_product_scd    5 rows

Part 4 -- verification: P002 has exactly 2 versions, 1 current
  product_id=P002  total_versions=2  current_versions=1
  Verification OK

=== 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       │
└─────────────┴────────────┴───────────────────────┴───────────────┴───────────┴────────────┴────────────┴────────────┘

Part 5 -- the formal declaration: SCD_SUMMARY
  table: dim_product_scd
  total_products: 4
  total_rows: 5
  historized_product_id: P002
  historized_columns: ['category', 'unit_cost']
  change_date: 2026-08-15
  versions_before_change: 1
  versions_after_change: 2
  merge_runs: 2
  rows_changed_by_merge_run: [0, 1]

Stop at Part 4 and Part 5 together, because they're the ones that sum up the entire module in a single picture. p002_check == ("P002", 2, 1) is the executed confirmation that history was correctly preserved: two versions, only one current, never zero, never two current at once. And SCD_SUMMARY gathers, in a single structure, every fact the seven previous lessons measured separately: rows_changed_by_merge_run ([0, 1]) comes directly from lessons 2 (no changes) and 5 (the real change); historized_columns comes from lesson 6's policy; versions_before_change/versions_after_change is, in numbers, the complete difference between SCD type 1 (lesson 3) and SCD type 2 (lessons 4 and 5).

Diagram: the module's seven pieces, closed out with evidence

flowchart TD
    A["L2: The problem\nVERIFIED -- P002 changes category and unit_cost"] --> B
    B["L3: SCD type 1\nVERIFIED -- history lost, 1 row"] --> C
    C["L4: Manual SCD type 2\nVERIFIED -- 2 rows, UPDATE + INSERT"] --> D
    D["L5: MERGE INTO\nVERIFIED -- 2 runs, idempotent"] --> E
    E["L6: Type 1 vs type 2 per column\nVERIFIED -- product_name is different"] --> F
    F["L7: Type 3 and variants\npartly VERIFIED -- complete vocabulary"] --> G
    G["SCD_SUMMARY\nthe formal contract this project delivers"]
    G --> H["Module 5: point-in-time join\nuses dim_product_scd exactly as it stands here"]

Closing out module 1's lesson 2 checklist, piece by piece

Four of the checklist's seven rows are now resolved. Module 5, next on the list, specifically needs dim_product_scd exactly as this project left it — five rows, P002 with two versions, non-overlapping validity ranges — not dim_product (module 2's star version, still with no history, which keeps existing unchanged) nor dim_product_type1/dim_product_type3 (lessons 3 and 7's tables, built only for comparison). With dim_product_scd ready, module 5 can finally ask the question no earlier module could ask: if fact_orders had a P002 order dated after August 15th, does the JOIN connect it to the dimension's correct version?

Common mistakes

Delivering SCD_SUMMARY without Part 4's assert. What happens: someone, in a hurry to show the summary structure as the final result, builds SCD_SUMMARY directly after Part 3, without first running Part 4's assert p002_check == ("P002", 2, 1). Why it happens: the summary structure looks more presentable as "the deliverable," and the verification feels like a disposable preliminary step. How to spot it: if your final deliverable includes no executed evidence that P002 ended up with exactly two versions, you're documenting a process without having confirmed that process worked — exactly the same trap module 3 already warned about with its own cross-check. How to fix it: Part 4 of this project isn't optional — it's the guarantee that makes everything SCD_SUMMARY documents in Part 5 trustworthy.

Confusing "historizing dim_product_scd" with "replacing dim_product across the rest of the guide." What happens: someone, finishing this project, assumes the entire rest of the guide should use dim_product_scd instead of dim_product from here on, including module 2's star schema and module 3's OBT. Why it happens: after an entire module dedicated to historizing, it seems natural for the historized version to become the only valid one. How to spot it: if you expect module 5 to modify mart_daily_sales_obt (from module 3) to use dim_product_scd, you lost sight of the fact that each table in this guide has a specific purpose. How to fix it: dim_product (star, no history) remains correct for any report that only needs the current state; dim_product_scd is specifically correct for reports that need to reconstruct the past — module 5 is going to use dim_product_scd because its central question (correct historical revenue) demands it, not because dim_product_scd "replaced" the earlier tables.

Assuming this project exhausted every possible SCD scenario. What happens: someone finishes this project thinking they've seen "every case" of dimension historization, without considering scenarios Kiosko didn't have in this module — a discontinued product, a completely new product, two changes on the same day across different products. Why it happens: a single complete, well-built, verified example can feel like "the general case" when it's actually a specific, deliberately simple one. How to spot it: if you can't explain how your MERGE would behave if staging_product brought a completely new P005, or if P004 disappeared from the catalog, you're missing the complete pattern lesson 5 named — WHEN NOT MATCHED BY SOURCE / WHEN NOT MATCHED BY TARGET — without implementing it. How to fix it: this project solves, with complete evidence, the case Kiosko needed — a fixed catalog of four products, one of which changes; DuckDB's official guide, cited in lesson 5, documents the complete pattern for catalogs where products also enter and leave.

Exercises

Exercise 1 — Verify P001, P003, and P004 still each have a single, unchanged version. Using dim_product_scd already built, write a query confirming the three products that never changed keep exactly their original module 1 values.

print(con.sql("""
    SELECT product_id, product_name, category, unit_cost, valid_from, valid_to, is_current
    FROM dim_product_scd
    WHERE product_id != 'P002'
    ORDER BY product_id
"""))

┌────────────┬───────────────────────┬─────────────┬───────────┬────────────┬──────────┬────────────┐
│ product_id │      product_name     │  category   │ unit_cost │ valid_from │ valid_to │ is_current │
│  varchar   │        varchar        │   varchar   │  double   │    date    │   date   │  boolean   │
├────────────┼───────────────────────┼─────────────┼───────────┼────────────┼──────────┼────────────┤
│ P001       │ Bottled Water 600ml   │ beverages   │       0.4 │ 2026-08-01 │ NULL     │ true       │
│ P003       │ Instant Coffee Sachet │ beverages   │      0.35 │ 2026-08-01 │ NULL     │ true       │
│ P004       │ Phone Charger Cable   │ electronics │       2.1 │ 2026-08-01 │ NULL     │ true       │
└────────────┴───────────────────────┴─────────────┴───────────┴────────────┴──────────┴────────────┘

Exercise 2 — Extend SCD_SUMMARY with a field showing how many rows each table type in this module has. Without re-running the whole project, add a tables_built_this_module field to SCD_SUMMARY listing, with their row counts, the tables built throughout the module: dim_product_type1 (lesson 3), dim_product_scd (lessons 4-8), and dim_product_type3 (lesson 7).

SCD_SUMMARY["tables_built_this_module"] = {
    "dim_product_type1": {"rows": 4, "purpose": "SCD type 1 -- comparison, not used in later modules"},
    "dim_product_scd":   {"rows": 5, "purpose": "SCD type 2 -- the canonical table module 5 uses"},
    "dim_product_type3": {"rows": 4, "purpose": "SCD type 3 -- comparison, not used in later modules"},
}
print(f"tables_built_this_module: {SCD_SUMMARY['tables_built_this_module']}")

tables_built_this_module: {'dim_product_type1': {'rows': 4, 'purpose': 'SCD type 1 -- comparison, not used in later modules'}, 'dim_product_scd': {'rows': 5, 'purpose': 'SCD type 2 -- the canonical table module 5 uses'}, 'dim_product_type3': {'rows': 4, 'purpose': 'SCD type 3 -- comparison, not used in later modules'}}

Exercise 3 — Explain, from memory, what module 5 needs from this project to get started. Without looking at the guide's design, describe in a 4-6 sentence paragraph which pieces of SCD_SUMMARY — and of the tables built in this project — module 5 is going to need to do the point-in-time join between fact_orders and dim_product_scd.

Summary and next step: the end of module 4

With this mini-project you close out module 4 completely. You built dim_product_scd from scratch, historized it with MERGE INTO run twice — once with no real changes, once with P002's real, verified change — confirmed with a literal assert that the dimension ended up with exactly two versions of P002 (one closed, one current), and documented the entire process in SCD_SUMMARY: the table, the historized product, the affected columns, the change's date, and how many rows each MERGE run changed.

You took the fourth step of an eight-module journey: dim_product_scd — product_key, product_id, product_name, category, unit_cost, valid_from, valid_to, is_current — is, from here on, Kiosko's canonical historized dimension, ready for any fact to query it respecting time, instead of assuming the present was always the past.

Where you go next. Module 5 — point-in-time-joins-and-deduplication — takes dim_product_scd, exactly as this project left it, and answers the question this module prepared but didn't answer: when fact_orders joins against a historized dimension, what happens if the JOIN only filters by is_current = true, instead of respecting each sale's validity range? You're going to see, in real Kiosko numbers, the difference between a correct historical report and a corrupted one — and you're going to learn, in the same lesson, to deduplicate repeated rows with ROW_NUMBER() and QUALIFY.

Resources

• Kimball Group — "Slowly Changing Dimension Type 2" — the formal definition behind this project's entire dim_product_scd build. 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 this project integrates end to end, across the two MERGE runs. duckdb.org/docs/current/guides/sql_features/merge. In English.
• DuckDB — official MERGE INTO statement documentation — the complete syntax reference used across this project's five parts. duckdb.org/docs/lts/sql/statements/merge_into. In English.
• DuckDB — official Python client documentation, the interface that ran every verification in this project. duckdb.org/docs/current/clients/python/overview. In English.