Mini-project: Kiosko's star schema in DuckDB

Description

This project closes the module by integrating the six previous pieces: a star schema's anatomy (lesson 2), surrogate keys for dim_store and dim_product (lesson 3), dim_date built from scratch (lesson 4), the conformed dimension vocabulary (lesson 5), the bus matrix as a planning map (lesson 6), and the assembly of the three JOINs, verified (lesson 7). What's left is bringing it all together into a formal deliverable: the star's four tables, built end to end, documented as a data structure, and verified — number by number — against the revenue you already know from foundations.

The project has four parts. First, you build the four tables of the complete star schema. Second, you assemble and verify the join of the three dimensions against fact_orders, confirming the count doesn't change. Third, you document the star as a formal structure, reusable by the rest of the guide — this module's equivalent of what GRAIN_DECLARATION was for module 1. Fourth, you verify against foundations: you confirm that total revenue, and revenue by store and by product, now calculated through the complete star, matches exactly the numbers you already know.

Connection to the module. This project introduces no new concept — it's the final integration of the seven previous lessons, packaged as STAR_SCHEMA_DECLARATION, the structure modules 3 through 8 of this guide take for granted without re-litigating it.

An analogy: the signed architectural blueprint

Every lesson in this module built a different piece of the building: the foundation (anatomy), each door's keys (surrogate keys), a complete room that was missing (dim_date), the criteria for sharing spaces between tenants (conformed dimensions), the map of the entire building (bus matrix). This project is the signed architectural blueprint: the final document certifying that the building, as built, serves its purpose — every room correctly connected, no broken hallway, verified with a real inspection before handover.

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

You need, in the same folder: kiosko.py and raw_orders.py (identical to module 1). You don't need any additional file — generate_date_dim() gets defined directly in this project's script, just like in lesson 7.

The reference solution, verified

Part 1 — Build the star's four tables

# star_schema_project.py -- Kiosko's star schema in DuckDB (module 2 closing mini-project)
from datetime import date, timedelta, datetime

import duckdb

from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS

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)
    if end < start:
        raise ValueError(f"end_date ({end_date}) is before start_date ({start_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


print("=== Kiosko: complete star schema, module 2 final deliverable ===\n")

# --- fact_orders, inherited unchanged from M1 ---
orders = [
    Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
          unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
    for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)

con = duckdb.connect()
con.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany(
    "INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
      r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders],
)

# --- the three dimensions, with a surrogate key where it applies ---
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
""")

con.execute("CREATE TABLE dim_product_natural (product_id VARCHAR, product_name VARCHAR, category VARCHAR, unit_cost DOUBLE)")
con.executemany("INSERT INTO dim_product_natural VALUES (?, ?, ?, ?)",
                 [(p["product_id"], p["product_name"], p["category"], p["unit_cost"]) for p in DIM_PRODUCT])
con.execute("""
    CREATE TABLE dim_product AS
    SELECT ROW_NUMBER() OVER (ORDER BY product_id) AS product_key, product_id, product_name, category, unit_cost
    FROM dim_product_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],
)

print("Part 1 -- the star's four tables, built")
for table in ["fact_orders", "dim_store", "dim_product", "dim_date"]:
    count = con.sql(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    print(f"  {table:14} {count:3} rows")

This first part isn't an isolated exercise — it rebuilds, in one place, everything lessons 3 and 4 built separately. It's the input material for the three parts that follow.

Part 2 — Assemble the star and verify the grain

star_query = """
    SELECT
        f.order_id, f.store_id, f.product_id,
        s.store_key, s.store_name,
        p.product_key, p.product_name, p.category,
        d.date_key, d.calendar_date, d.day_of_week, d.is_weekend,
        f.quantity, f.unit_price, f.revenue
    FROM fact_orders f
    JOIN dim_store   s ON f.store_id = s.store_id
    JOIN dim_product p ON f.product_id = p.product_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}")

print("\nPart 2 -- assembling the star with the 3 JOINs and verifying the grain")
before = con.sql("SELECT COUNT(*) FROM fact_orders").fetchone()[0]
after = con.sql("SELECT COUNT(*) FROM fact_orders_star").fetchone()[0]
print(f"  fact_orders (unjoined):             {before} rows")
print(f"  fact_orders_star (with 3 JOINs):     {after} rows")
assert before == after, "the join lost or duplicated rows"
print(f"  Verification: {before} == {after} -> OK, no JOIN lost or duplicated a single row")

Notice this part materializes the JOIN's result as a new table, fact_orders_star — unlike lesson 7, which left the JOIN as a reused subquery. Both approaches are valid; materializing the table here makes Part 4's checks easier without repeating the full query each time.

Part 3 — Document the star as a formal structure

STAR_SCHEMA_DECLARATION = {
    "fact_table": "fact_orders",
    "grain": "an order line (inherited from module 1, unchanged)",
    "dimensions": {
        "dim_store": {"surrogate_key": "store_key", "natural_key": "store_id", "rows": 3},
        "dim_product": {"surrogate_key": "product_key", "natural_key": "product_id", "rows": 4},
        "dim_date": {"surrogate_key": "date_key", "natural_key": "calendar_date", "rows": 31},
    },
    "conformed_dimensions": ["dim_store", "dim_date"],
    "verified_star_row_count": after,
}

print("\nPart 3 -- Kiosko's formal star schema declaration")
for key, value in STAR_SCHEMA_DECLARATION.items():
    print(f"  {key}: {value}")

Each field of STAR_SCHEMA_DECLARATION corresponds to a specific lesson in this module: dimensions comes from lessons 3 and 4, conformed_dimensions comes from lesson 5, and verified_star_row_count is the numeric evidence from Part 2 itself. This structure, just like GRAIN_DECLARATION in module 1, is the contract modules 3 through 8 of this guide are going to take for granted without re-litigating it.

Part 4 — Verify against foundations, number by number

print("\nPart 4 -- cross-check: the same numbers, now through the complete star")
print(con.sql("SELECT ROUND(SUM(revenue), 2) AS total_revenue FROM fact_orders_star"))
print(con.sql("""
    SELECT store_id, store_name, COUNT(*) AS order_count, SUM(quantity) AS total_units, ROUND(SUM(revenue), 2) AS revenue
    FROM fact_orders_star
    GROUP BY store_id, store_name
    ORDER BY store_id
"""))
print(con.sql("""
    SELECT product_id, product_name, COUNT(*) AS order_count, SUM(quantity) AS total_units, ROUND(SUM(revenue), 2) AS revenue
    FROM fact_orders_star
    GROUP BY product_id, product_name
    ORDER BY product_id
"""))

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

=== Kiosko: complete star schema, module 2 final deliverable ===

Part 1 -- the star's four tables, built
  fact_orders     40 rows
  dim_store        3 rows
  dim_product      4 rows
  dim_date        31 rows

Part 2 -- assembling the star with the 3 JOINs and verifying the grain
  fact_orders (unjoined):             40 rows
  fact_orders_star (with 3 JOINs):     40 rows
  Verification: 40 == 40 -> OK, no JOIN lost or duplicated a single row

Part 3 -- Kiosko's formal star schema declaration
  fact_table: fact_orders
  grain: an order line (inherited from module 1, unchanged)
  dimensions: {'dim_store': {'surrogate_key': 'store_key', 'natural_key': 'store_id', 'rows': 3}, 'dim_product': {'surrogate_key': 'product_key', 'natural_key': 'product_id', 'rows': 4}, 'dim_date': {'surrogate_key': 'date_key', 'natural_key': 'calendar_date', 'rows': 31}}
  conformed_dimensions: ['dim_store', 'dim_date']
  verified_star_row_count: 40

Part 4 -- cross-check: the same numbers, now through the complete star
┌───────────────┐
│ total_revenue │
│    double     │
├───────────────┤
│        106.15 │
└───────────────┘

┌──────────┬───────────────┬─────────────┬─────────────┬─────────┐
│ store_id │  store_name   │ order_count │ total_units │ revenue │
│ varchar  │    varchar    │    int64    │   int128    │ double  │
├──────────┼───────────────┼─────────────┼─────────────┼─────────┤
│ S01      │ Kiosko Centro │          16 │          34 │    38.3 │
│ S02      │ Kiosko Norte  │          13 │          37 │    38.8 │
│ S03      │ Kiosko Sur    │          11 │          31 │   29.05 │
└──────────┴───────────────┴─────────────┴─────────────┴─────────┘

┌────────────┬───────────────────────┬─────────────┬─────────────┬─────────┐
│ product_id │     product_name      │ order_count │ total_units │ revenue │
│  varchar   │        varchar        │    int64    │   int128    │ double  │
├────────────┼───────────────────────┼─────────────┼─────────────┼─────────┤
│ P001       │ Bottled Water 600ml   │          16 │          61 │   33.55 │
│ P002       │ Energy Bar            │          10 │          18 │    21.6 │
│ P003       │ Instant Coffee Sachet │           7 │          14 │    10.5 │
│ P004       │ Phone Charger Cable   │           7 │           9 │    40.5 │
└────────────┴───────────────────────┴─────────────┴─────────────┴─────────┘

Stop at Part 4, because it's the one that gives the whole project its confidence: 106.15 total revenue, 38.3/38.8/29.05 by store, 33.55/21.6/10.5/40.5 by product — exactly the same numbers you already saw in module 1's project, and before that, in foundations' capstone. This confirms something no earlier lesson in this module proved this completely: assembling the complete star schema — with surrogate keys, with dim_date, with all three JOINs — changed not a single cent of revenue. The star adds structure and context; it doesn't alter the fact you already knew.

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

flowchart TD
    A["L2: Star anatomy\n(tall/narrow fact, short/wide dims)"] --> B
    B["L3: store_key, product_key\nVERIFIED -- deterministic keys"] --> C
    C["L4: dim_date\nVERIFIED -- 31 rows, August 2026"] --> D
    D["L5-L6: Conformed dimensions\nand bus matrix -- the complete map"] --> E
    E["L7: The 3 JOINs assembled\nVERIFIED -- 40 == 40"] --> F
    F["STAR_SCHEMA_DECLARATION\nthe formal contract this project delivers"]
    F --> G["Modules 3-8: take this contract\nfor granted without re-litigating it"]

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

Two of the original checklist's twelve rows are now resolved — and they're, in order, exactly the first two: without a declared grain (module 1) and without a complete star schema with surrogate keys and conformed dimensions (this module), none of the remaining five items would have a reliable foundation to build on. Module 3, next on the list, specifically needs the star schema this project just closed out: comparing a normalized (snowflake) JOIN's cost against this same star JOIN's cost only makes sense once the star already exists, built and verified.

Common mistakes

Delivering STAR_SCHEMA_DECLARATION without Part 2's verification. What happens: someone, in a hurry to show the formal structure as the final result, builds STAR_SCHEMA_DECLARATION directly, without first running Part 2's assert before == after. Why it happens: the data structure looks more presentable as "the deliverable," and the JOIN's verification feels like a disposable preliminary step. How to spot it: if your final deliverable includes no executed evidence that the JOIN lost or duplicated no rows, you're documenting a claim, not a verified declaration — exactly the same trap module 1 already warned about with the grain. How to fix it: Part 2 of this project isn't optional — it's the guarantee that makes everything that follows in Part 3 and Part 4 trustworthy.

Confusing "the star is built" with "the model is already finished." What happens: someone finishes this project, sees the four tables built and verified, and concludes Kiosko's dimensional warehouse is already complete. Why it happens: a complete star schema, with surrogate keys and dim_date, feels like a substantial achievement — and it is — and it's easy to forget it's still only the second of eight modules. How to spot it: if you can't name, from memory, at least three of the five items still pending in this lesson's checklist table, you need to reread that table. How to fix it: dim_product is still completely static — with no historical version — fact_orders is still the only business process, and there's still no deduplication or accumulating snapshot at all. This project closes the second step of eight, not the entire guide.

Reusing fact_orders_star as if it were the guide's new fact_orders. What happens: someone, satisfied with the JOIN's result, starts referring to fact_orders_star as "the new fact_orders," mentally replacing the original table with the version already joined to its dimensions. Why it happens: fact_orders_star has more useful columns (store_name, product_name, day_of_week) and feels, in practice, more complete to query. How to spot it: if, in some future exercise, you assume fact_orders already has columns like store_name without joining anything, you mixed up the two tables. How to fix it: fact_orders — the original table, with its seven columns and natural keys — remains this guide's canonical fact, unchanged, exactly as module 1 left it. fact_orders_star is a derived product, useful for one-off queries, but it doesn't replace the original fact — this guide's following modules keep building on fact_orders, not on its already-joined version.

Exercises

Exercise 1 — Verify revenue by day of the week, through the star. Using fact_orders_star, write a query that groups by day_of_week and calculates total revenue, ordered by actual date of occurrence (not alphabetically).

print(con.sql("""
    SELECT day_of_week, is_weekend, ROUND(SUM(revenue), 2) AS revenue
    FROM fact_orders_star
    GROUP BY day_of_week, is_weekend
    ORDER BY MIN(calendar_date)
"""))

┌─────────────┬────────────┬─────────┐
│ day_of_week │ is_weekend │ revenue │
│   varchar   │  boolean   │ double  │
├─────────────┼────────────┼─────────┤
│ Monday      │ false      │   15.85 │
│ Tuesday     │ false      │   15.85 │
│ Wednesday   │ false      │    9.55 │
│ Thursday    │ false      │   11.05 │
│ Friday      │ false      │   18.05 │
│ Saturday    │ true       │   31.85 │
│ Sunday      │ true       │    3.95 │
└─────────────┴────────────┴─────────┘

Exercise 2 — Extend STAR_SCHEMA_DECLARATION with the verification date. Without using datetime.now(), add a verified_on field with the date of the last day of the data week this project used, "2026-08-09" — the same pattern you already used in module 1's project.

STAR_SCHEMA_DECLARATION["verified_on"] = "2026-08-09"
print(f"verified_on: {STAR_SCHEMA_DECLARATION['verified_on']}")

verified_on: 2026-08-09

Exercise 3 — Explain, from memory, what module 3 needs from this project to get started. Without looking at the guide's design, describe in a 4-6 sentence paragraph which pieces of STAR_SCHEMA_DECLARATION — and of the four tables built in this project — module 3 is going to need to compare star, snowflake, and wide table (OBT).

Summary and next step: the end of module 2

With this mini-project you close out module 2 completely. You built Kiosko's star schema's four tables: fact_orders (inherited unchanged), dim_store and dim_product with a surrogate key, and dim_date built from scratch with generate_date_dim(). You assembled the three JOINs and verified, with evidence, that they lost or duplicated not a single one of the original forty rows. You documented everything in STAR_SCHEMA_DECLARATION — the formal contract the rest of this guide takes for granted — and confirmed, number by number, that total revenue (106.15) and its breakdowns by store and by product are exactly the same ones you know from foundations.

You took the second step of an eight-module journey: fact_orders is still, column for column, the same fact — what changed is that it now lives surrounded by a complete star schema, with its own keys, a reusable calendar dimension, and the vocabulary for recognizing which dimensions it's going to be able to share with the business processes that don't exist yet.

Where you go next. Module 3 — star-vs-snowflake-vs-one-big-table — takes the star you just built and puts it to the test: normalizes dim_product into a snowflake version, compares each JOIN's real cost with EXPLAIN, and builds a fully denormalized wide table (One Big Table) — so you can decide, with evidence rather than fashion, when each shape wins.

Resources

• Kimball Group — "Star Schema / OLAP Cube" — the complete source for the dimensional vocabulary this project integrates: surrogate keys, conformed dimensions, and the star shape verified end to end. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
• Microsoft Learn — "Understand star schema and the importance for Power BI" — a practical, vendor-neutral confirmation of every piece of the star schema built in this module. learn.microsoft.com/en-us/power-bi/guidance/star-schema. In English.
• "The Data Warehouse Toolkit", 3rd edition (Kimball & Ross, Wiley) — the canonical reference behind the complete vocabulary used in this module, from the star's anatomy to the bus matrix. wiley.com/en-jp/The+Data+Warehouse+Toolkit. In English.
• DuckDB — official Python client documentation, the tool that ran every verification in this module. duckdb.org/docs/current/clients/python/overview. In English.