Building the star with historized dim_product

Description

With fact_orders rebuilt from bronze and silver in the previous lesson, this lesson builds Kiosko's warehouse's complete star — but with a decisive difference from module 2's star: the product dimension isn't the static version (dim_product), it's dim_product_scd, historized with two real MERGE INTO runs, exactly as module 4 left it. And having it historized isn't enough — you have to join it correctly. This lesson builds the star (dim_store, dim_date, dim_product_scd) and, in the same flow, demonstrates — with module 5's same numbers — why joining against a historized dimension demands the point-in-time pattern, not a naive JOIN by is_current.

Connection to the module. This lesson integrates, for the first time in a single script, three pieces that until now lived in separate modules: module 2's star schema, module 4's SCD-2 historization, and module 5's point-in-time join. The result — dim_product_scd with five rows and the correct join already demonstrated — is the material this module's lessons 5 and 6 take for granted without rebuilding it again.

An analogy: the passport with a page history, not a single current photo

Think about the difference between an ID card that only shows a person's current photo and data, and a passport with years of stamps: every old page is still there, with its exact date, even though the person no longer lives at that address or looks that way anymore. If an immigration officer needs to know where that person lived on a specific past date, the current ID card is useless — it only has the present; they need the complete passport, and they need to look up the correct page according to the date, not assume the last page was always the current one.

dim_product (from module 2) is the ID card: it always shows the present. dim_product_scd (from this lesson) is the passport: it has both of P002's pages — the one from before August 15th, the one from after — and this lesson's job is learning to "look up the correct page according to the sale's date," not sticking with the last page by reflex.

Worked example: the complete star, with the historized dimension joined in time

Part 1 — dim_store, dim_date, and dim_product_scd historized with MERGE INTO x2

This script continues directly on top of the same con connection and the same fact_orders lesson 3 left — it doesn't open a new connection nor rebuild bronze again.

# capstone_star.py -- Part 1: the star with historized dim_product_scd (continues on top of con, with fact_orders already rebuilt)
from datetime import date, timedelta

DAY_NAMES = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]


def generate_date_dim(start_date: str, end_date: str) -> list[dict]:
    start = date.fromisoformat(start_date)
    end = date.fromisoformat(end_date)
    rows = []
    current = start
    while current <= end:
        weekday_index = current.weekday()
        rows.append({
            "date_key": int(current.strftime("%Y%m%d")), "calendar_date": current,
            "day_of_week": DAY_NAMES[weekday_index], "month": current.month,
            "quarter": (current.month - 1) // 3 + 1, "year": current.year,
            "is_weekend": weekday_index >= 5,
        })
        current += timedelta(days=1)
    return rows


con.execute("CREATE TABLE dim_store_natural (store_id VARCHAR, store_name VARCHAR, city VARCHAR)")
con.executemany("INSERT INTO dim_store_natural VALUES (?, ?, ?)",
                 [(s["store_id"], s["store_name"], s["city"]) for s in DIM_STORE])
con.execute("CREATE TABLE dim_store AS SELECT ROW_NUMBER() OVER (ORDER BY store_id) AS store_key, store_id, store_name, city FROM dim_store_natural")

dim_date_rows = generate_date_dim("2026-08-01", "2026-08-31")
con.execute("""
    CREATE TABLE dim_date (
        date_key INTEGER, calendar_date DATE, day_of_week VARCHAR,
        month INTEGER, quarter INTEGER, year INTEGER, is_weekend BOOLEAN
    )
""")
con.executemany("INSERT INTO dim_date VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["date_key"], r["calendar_date"], r["day_of_week"], r["month"],
      r["quarter"], r["year"], r["is_weekend"]) for r in dim_date_rows])

# dim_product_scd: historized with MERGE INTO, run twice (module 4)
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],
)

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")
load_staging(PRODUCTS_V2)
rows_changed_2 = merge_scd("2026-08-15")

dim_product_scd_count = con.sql("SELECT COUNT(*) FROM dim_product_scd").fetchone()[0]
p002_versions = con.sql("""
    SELECT COUNT(*), SUM(CASE WHEN is_current THEN 1 ELSE 0 END)
    FROM dim_product_scd WHERE product_id = 'P002'
""").fetchone()

star_query = """
    SELECT f.order_id, f.store_id, f.product_id, s.store_key, s.store_name,
           d.date_key, d.calendar_date, f.quantity, f.unit_price, f.revenue
    FROM fact_orders f
    JOIN dim_store s ON f.store_id = s.store_id
    JOIN dim_date  d ON CAST(strftime(f.order_ts, '%Y%m%d') AS INTEGER) = d.date_key
"""
con.execute(f"CREATE TABLE fact_orders_star AS {star_query}")
star_rows = con.sql("SELECT COUNT(*) FROM fact_orders_star").fetchone()[0]

print("Part 1 -- STAR: dim_store, dim_date, historized dim_product_scd")
print(f"  dim_store         {con.sql('SELECT COUNT(*) FROM dim_store').fetchone()[0]:3} rows")
print(f"  dim_date          {con.sql('SELECT COUNT(*) FROM dim_date').fetchone()[0]:3} rows")
print(f"  dim_product_scd   {dim_product_scd_count:3} rows (MERGE #1 closed {rows_changed_1}, MERGE #2 closed {rows_changed_2})")
print(f"  P002: {p002_versions[0]} versions, {p002_versions[1]} current")
print(f"  fact_orders_star  {star_rows:3} rows (simple JOIN: dim_store + dim_date)")
assert dim_product_scd_count == 5 and p002_versions == (2, 1)
assert star_rows == 40

What to expect.

Part 1 -- STAR: dim_store, dim_date, historized dim_product_scd
  dim_store           3 rows
  dim_date           31 rows
  dim_product_scd     5 rows (MERGE #1 closed 0, MERGE #2 closed 1)
  P002: 2 versions, 1 current
  fact_orders_star   40 rows (simple JOIN: dim_store + dim_date)

Notice something deliberate about this part: fact_orders_star got built joining only against dim_store and dim_date — the two dimensions that don't change over time — leaving dim_product_scd out of this simple JOIN. That wasn't an oversight — dim_product_scd needs a different kind of JOIN, and joining it here with the same simple syntax would have accidentally hidden exactly the error Part 2 is going to expose.

Part 2 — The correct join: historical revenue, without corrupting the category

broken = con.sql("""
    SELECT d.category, COUNT(*) AS orders, ROUND(SUM(f.revenue), 2) AS revenue,
           ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
    GROUP BY d.category ORDER BY d.category
""").fetchall()
correct = con.sql("""
    SELECT d.category, COUNT(*) AS orders, ROUND(SUM(f.revenue), 2) AS revenue,
           ROUND(SUM(f.revenue - f.quantity * d.unit_cost), 2) AS margin
    FROM fact_orders f
    JOIN dim_product_scd d
        ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
    GROUP BY d.category ORDER BY d.category
""").fetchall()

total_broken = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id AND d.is_current = true
""").fetchone()[0]
total_correct = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
    JOIN dim_product_scd d ON f.product_id = d.product_id
       AND f.order_ts BETWEEN d.valid_from AND COALESCE(d.valid_to, DATE '9999-12-31')
""").fetchone()[0]

print("\nPart 2 -- Broken JOIN (is_current) vs correct JOIN (point-in-time)")
print("  BROKEN (is_current = true):")
for category, orders_count, revenue, margin in broken:
    print(f"    {category:14} {orders_count:2} orders  revenue={revenue:7}  margin={margin:6}")
print("  CORRECT (BETWEEN valid_from AND valid_to):")
for category, orders_count, revenue, margin in correct:
    print(f"    {category:14} {orders_count:2} orders  revenue={revenue:7}  margin={margin:6}")
print(f"  Total revenue: identical in both cases -> {total_broken} == {total_correct}")

broken_categories = {c for c, *_ in broken}
correct_categories = {c for c, *_ in correct}
assert "health-snacks" in broken_categories and "health-snacks" not in correct_categories
assert "snacks" in correct_categories and "snacks" not in broken_categories
assert total_broken == total_correct == 106.15
print("  Verification OK: correct historical revenue -- 'snacks', not 'health-snacks' (a future category)")

What to expect.

Part 2 -- Broken JOIN (is_current) vs correct JOIN (point-in-time)
  BROKEN (is_current = true):
    beverages      23 orders  revenue=  44.05  margin= 14.75
    electronics     7 orders  revenue=   40.5  margin=  21.6
    health-snacks  10 orders  revenue=   21.6  margin=  9.36
  CORRECT (BETWEEN valid_from AND valid_to):
    beverages      23 orders  revenue=  44.05  margin= 14.75
    electronics     7 orders  revenue=   40.5  margin=  21.6
    snacks         10 orders  revenue=   21.6  margin=  10.8
  Total revenue: identical in both cases -> 106.15 == 106.15
  Verification OK: correct historical revenue -- 'snacks', not 'health-snacks' (a future category)

Stop on this output, because it's this whole module's heart. Kiosko's forty orders happened between August 3rd and 9th, 2026 — before P002's change, which happened on the 15th. The broken JOIN, joining only by is_current = true, assigns those sales the category current today (health-snacks), as if the name-and-price change had already happened when those sales went through — a category that, on those sales' real date, didn't even exist yet. The correct JOIN, with BETWEEN valid_from AND valid_to, assigns P002's real category the day it sold: snacks. Revenue — 21.6 — never changes, because it's a measure that lives in fact_orders, not in the dimension; what changes is the category and the margin (9.36 broken vs 10.8 correct), because unit_cost does live in the historized dimension.

Diagram: why dim_product_scd needs a different JOIN

flowchart TD
    subgraph Simple["dim_store, dim_date -- don't change over time"]
        A["JOIN f.store_id = s.store_id\nJOIN date_key = d.date_key"]
    end
    subgraph Historized["dim_product_scd -- DOES change over time"]
        B["Broken JOIN: ON product_id = product_id\nAND is_current = true\n-> uses TODAY's version"]
        C["Correct JOIN: ON product_id = product_id\nAND order_ts BETWEEN valid_from AND valid_to\n-> uses the version CURRENT ON THE SALE DATE"]
    end
    A --> D["fact_orders_star\n40 rows, no fan-out"]
    B --> E["health-snacks, margin 9.36\nBROKEN -- a future category"]
    C --> F["snacks, margin 10.8\nCORRECT -- the real category on that date"]

Going deeper: why this error is silent, not noisy

It's worth insisting on something module 5 already warned about, because this module confirms it with the complete integrated warehouse: the broken JOIN never produces an error. It doesn't throw any exception, doesn't print any warning, doesn't even change total revenue — 106.15 in both cases, identical. If someone on the BI team only checked total revenue before trusting a report, this error would go completely unnoticed, because the only signal something's wrong is in a column nobody usually audits with the same attention as the total: the category.

This is, precisely, why module 5 dedicated a whole lesson to demonstrating this with real numbers, and why this module repeats it here, integrated into the complete star: a modeling error that doesn't break any visible number is, in practice, more dangerous than one that does — because nobody's going to notice it until someone, much later, wonders why "snacks"'s historical revenue looks lower than it should be.

Common mistakes

Joining dim_product_scd with the same simple syntax as dim_store and dim_date. What happens: someone, used to Part 1's pattern (JOIN dim_store s ON f.store_id = s.store_id), applies exactly the same form to dim_product_scd — JOIN dim_product_scd d ON f.product_id = d.product_id — with no additional filter. Why it happens: it's the shortest way to write a JOIN, and it works with no error for dim_store and dim_date because those two dimensions never have more than one row per natural key. How to spot it: if your query produces more than 40 rows when joining fact_orders with dim_product_scd with no filter, you have a fan-out — every P002 sale is multiplying by its two versions — exactly the error module 5 (lesson 2) already demonstrated with the number 50. How to fix it: any historized dimension needs, at minimum, AND d.is_current = true to avoid the fan-out — and, as this lesson demonstrated, that filter alone still isn't enough for a correct historical report.

Building the OBT or any derived table before the two MERGE runs have finished. What happens: someone, writing their own integration script, builds fact_orders_star or any report using dim_product_scd between MERGE #1 and MERGE #2, before the history is complete. Why it happens: in this lesson's code, the two MERGEs and the report queries are close together in the script, and it's easy to move a line without noticing it breaks the order. How to spot it: if your report shows P002 with a single version (snacks, with no health-snacks anywhere, not even with the broken JOIN), check whether you built the report before MERGE #2. How to fix it: this lesson's Part 1's two MERGEs have to complete before any Part 2 report query — the same dependency order this module's lesson 1 warned about.

Assuming the broken margin (9.36) is "a small error" because the difference from the correct one (10.8) looks small. What happens: someone, seeing the difference between 9.36 and 10.8 is barely 1.44, concludes the broken JOIN's error has no real practical consequences. Why it happens: in absolute terms, over forty orders from one week, 1.44 seems like a small number to worry about. How to spot it: if your conclusion is "it doesn't matter, it's little money," you lost sight of scale — this dataset is toy-sized on purpose (this guide's design warned about it since module 1); the same error, over a real retailer's complete catalog with millions of orders and dozens of products changing category every quarter, wouldn't be 1.44 — it would be a fraction of margin systematically misattributed across the entire history. How to fix it: the magnitude of the number in Kiosko's dataset isn't the point — the point is that the error's pattern (joining by is_current instead of point-in-time) is the same, regardless of scale, and at larger scale the cost of not fixing it grows proportionally.

Exercises

Exercise 1 — Confirm P001, P003, and P004 give the same result in both JOINs. Using Part 2's two queries, confirm that the beverages and electronics categories — which never changed — give exactly the same revenue and margin in the broken JOIN and the correct one.

Exercise 2 — Calculate how many of P002's ten orders happened before and after the change. Using fact_orders, count how many of P002's ten orders have order_ts before 2026-08-15 and how many after, and explain why the result confirms this lesson's correct JOIN was predictable in advance.

print(con.sql("""
    SELECT
        CASE WHEN order_ts < '2026-08-15' THEN 'before change' ELSE 'after change' END AS period,
        COUNT(*) AS orders
    FROM fact_orders WHERE product_id = 'P002' GROUP BY period
"""))

┌───────────────┬────────┐
│    period     │ orders │
│    varchar    │ int64  │
├───────────────┼────────┤
│ before change │     10 │
└───────────────┴────────┘

Exercise 3 — Explain, from memory, why fact_orders_star (Part 1) doesn't include dim_product_scd, even though this lesson's title says "the star with historized dim_product." In 2-3 sentences, resolve this apparent contradiction.

Summary and next step

In this lesson you built Kiosko's warehouse's complete star: dim_store and dim_date, joined with the usual simple pattern, and dim_product_scd, historized with two real MERGE INTO runs — P002 with two versions, snacks/0.60 before August 15th, health-snacks/0.68 after. You demonstrated, with module 5's same numbers now integrated into this warehouse, that the correct JOIN against a historized dimension demands BETWEEN valid_from AND valid_to, not is_current = true: revenue never changes (106.15 in both cases), but the category does (snacks correct, health-snacks broken), and so does the margin (10.8 correct, 9.36 broken).

Before moving on you should be able to: explain why dim_product_scd needs a different JOIN than dim_store/dim_date; recite from memory the two margin numbers (broken vs correct) for P002; and explain why total revenue is never enough evidence that a historical JOIN is correctly written.

Lesson 5 adds two more layers to the warehouse: fact_sessions, the session funnel's accumulating snapshot, and fact_store_activity, the daily per-store activity's cumulative table design — the two pieces from module 6 no transactional fact, not even the star just completed in this lesson, can solve.

Resources

• Kimball Group — "Slowly Changing Dimension Type 2" — the formal definition backing dim_product_scd, integrated in this lesson within the complete star. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2. In English.
• DuckDB — official guide "Merge Statement for SCD Type 2" — the exact MERGE INTO pattern this lesson runs twice over dim_product_scd. duckdb.org/docs/current/guides/sql_features/merge. In English.
• DuckDB — official documentation on the MERGE INTO statement. duckdb.org/docs/lts/sql/statements/merge_into. In English.
• Kimball Group — "Late Arriving Dimension" — the technique backing, in spirit, why the point-in-time join is the correct way to connect facts with changing dimensions. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/late-arriving-dimension. In English.
• DuckDB — official Python client documentation, the interface that runs every query in this lesson. duckdb.org/docs/current/clients/python/overview. In English.