Mini-project: Kiosko's three shapes compared

Description

This project closes the module by integrating the six previous pieces: normalizing a dimension (lesson 2), measuring an extra JOIN's cost with EXPLAIN (lesson 3), the wide table's modern argument (lesson 4), building Kiosko's OBT (lesson 5), the snowflake's real maintenance cost (lesson 6), and the OBT's real query gain (lesson 7). What's left is bringing all three shapes together into a single formal deliverable: built at once, in the same DuckDB connection, verified number by number against the same revenue you know from foundations, and documented in a declaration — SHAPE_COMPARISON — the rest of this guide can consult without repeating the comparison.

The project has five parts. First, you rebuild the star inherited from module 2, unchanged. Second, you build the snowflake version — dim_category and dim_product_normalized. Third, you build the OBT version — mart_daily_sales_obt. Fourth, you cross-check: you confirm all three shapes, despite their different structures, give exactly the same total revenue. Fifth, you document the comparison as a formal structure, SHAPE_COMPARISON, with the concrete numbers lessons 2 through 7 already measured.

Connection to the module. This project introduces no new concept — it's the final integration of the seven previous lessons, packaged as SHAPE_COMPARISON, the structure modules 4 through 8 of this guide can cite without rebuilding all three shapes from scratch.

An analogy: the three prototypes, presented together to the committee

Every lesson in this module built and tested a different prototype separately: the snowflake, measured with EXPLAIN and with an UPDATE's cost; the OBT, measured with row count, repeated columns, and disk size. This project is the final gathering: all three prototypes, built side by side, on the same table, presented together to a committee that needs to decide with judgment — not "which is the one correct shape," but "which shape to use for which purpose, with the evidence from the seven previous lessons already in hand."

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

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

The reference solution, verified

Part 1 — Rebuild the star, unchanged

# three_shapes_project.py -- Kiosko's star vs snowflake vs OBT, module 3 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)
    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: star vs snowflake vs OBT, module 3 final deliverable ===\n")

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

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 inherited from module 2, unchanged")
for table in ["fact_orders", "dim_store", "dim_product", "dim_date"]:
    count = con.sql(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    print(f"  {table:12} {count:3} rows")

This first part builds nothing new — it rebuilds, exactly as in module 2's lesson 8, the complete star schema that serves as the baseline for the two comparisons that follow.

Part 2 — Build the snowflake version

con.execute("""
    CREATE TABLE dim_category AS
    SELECT ROW_NUMBER() OVER (ORDER BY category) AS category_id, category AS category_name
    FROM (SELECT DISTINCT category FROM dim_product_natural) t
""")
con.execute("""
    CREATE TABLE dim_product_normalized AS
    SELECT ROW_NUMBER() OVER (ORDER BY n.product_id) AS product_key, n.product_id, n.product_name, c.category_id, n.unit_cost
    FROM dim_product_natural n JOIN dim_category c ON n.category = c.category_name
""")
print("\nPart 2 -- the snowflake version: dim_category + dim_product_normalized")
print(f"  dim_category            {con.sql('SELECT COUNT(*) FROM dim_category').fetchone()[0]:3} rows")
print(f"  dim_product_normalized  {con.sql('SELECT COUNT(*) FROM dim_product_normalized').fetchone()[0]:3} rows")

Exactly lesson 2's build: dim_category normalizes the distinct categories from dim_product_natural, and dim_product_normalized replaces category (text) with category_id (surrogate key).

Part 3 — Build the OBT version

con.execute("""
    CREATE TABLE mart_daily_sales_obt AS
    SELECT
        CAST(f.order_ts AS DATE) AS sale_date, d.day_of_week, d.is_weekend, d.month, d.quarter, d.year,
        s.store_id, s.store_name, s.city, p.product_id, p.product_name, p.category, p.unit_cost,
        SUM(f.quantity) AS quantity, ROUND(SUM(f.revenue), 2) AS 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
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13
""")
print("\nPart 3 -- the OBT version: mart_daily_sales_obt")
print(f"  mart_daily_sales_obt    {con.sql('SELECT COUNT(*) FROM mart_daily_sales_obt').fetchone()[0]:3} rows")

Exactly lesson 5's build: a CREATE TABLE ... AS SELECT that joins the star's four tables and groups by day, store, and product — the coarser grain that collapses forty order lines into thirty-nine rows.

Part 4 — Cross-check: all three shapes, the same revenue

star_rev = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
    JOIN dim_product p ON f.product_id = p.product_id
""").fetchone()[0]
snowflake_rev = con.sql("""
    SELECT ROUND(SUM(f.revenue), 2) FROM fact_orders f
    JOIN dim_product_normalized p ON f.product_id = p.product_id
    JOIN dim_category c ON p.category_id = c.category_id
""").fetchone()[0]
obt_rev = con.sql("SELECT ROUND(SUM(revenue), 2) FROM mart_daily_sales_obt").fetchone()[0]

print("\nPart 4 -- cross-check: all three shapes, the same revenue")
print(f"  star (1 JOIN hop):      {star_rev}")
print(f"  snowflake (2 JOIN hops): {snowflake_rev}")
print(f"  OBT (0 JOIN hops):       {obt_rev}")
assert star_rev == snowflake_rev == obt_rev == 106.15, "the three shapes do not match"
print("  Verification: all three shapes match at 106.15 -- OK")

This is the part that gives the whole project its confidence: no matter how many JOINs each shape needs to reach category, or whether the grain is "an order line" (star, snowflake) or "a product sold at a store on a day" (OBT), the total business revenue — the number Kiosko's manager actually cares about — is identical across all three.

Part 5 — Document the comparison as a formal structure

SHAPE_COMPARISON = {
    "star": {
        "tables": ["fact_orders", "dim_store", "dim_product", "dim_date"],
        "joins_to_category": 1,
        "columns_widest_table": 5,
        "rows_widest_table": 4,
        "update_cost_category_rename": 2,
    },
    "snowflake": {
        "tables": ["fact_orders", "dim_store", "dim_product_normalized", "dim_category", "dim_date"],
        "joins_to_category": 2,
        "columns_widest_table": 5,
        "rows_widest_table": 4,
        "update_cost_category_rename": 1,
    },
    "obt": {
        "tables": ["mart_daily_sales_obt"],
        "joins_to_category": 0,
        "columns_widest_table": 15,
        "rows_widest_table": 39,
        "update_cost_category_rename": 22,
    },
    "verified_revenue_all_shapes": obt_rev,
}

print("\nPart 5 -- the formal declaration: SHAPE_COMPARISON")
for shape, data in SHAPE_COMPARISON.items():
    print(f"  {shape}: {data}")

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

=== Kiosko: star vs snowflake vs OBT, module 3 final deliverable ===

Part 1 -- the star inherited from module 2, unchanged
  fact_orders   40 rows
  dim_store      3 rows
  dim_product    4 rows
  dim_date      31 rows

Part 2 -- the snowflake version: dim_category + dim_product_normalized
  dim_category              3 rows
  dim_product_normalized    4 rows

Part 3 -- the OBT version: mart_daily_sales_obt
  mart_daily_sales_obt     39 rows

Part 4 -- cross-check: all three shapes, the same revenue
  star (1 JOIN hop):      106.15
  snowflake (2 JOIN hops): 106.15
  OBT (0 JOIN hops):       106.15
  Verification: all three shapes match at 106.15 -- OK

Part 5 -- the formal declaration: SHAPE_COMPARISON
  star: {'tables': ['fact_orders', 'dim_store', 'dim_product', 'dim_date'], 'joins_to_category': 1, 'columns_widest_table': 5, 'rows_widest_table': 4, 'update_cost_category_rename': 2}
  snowflake: {'tables': ['fact_orders', 'dim_store', 'dim_product_normalized', 'dim_category', 'dim_date'], 'joins_to_category': 2, 'columns_widest_table': 5, 'rows_widest_table': 4, 'update_cost_category_rename': 1}
  obt: {'tables': ['mart_daily_sales_obt'], 'joins_to_category': 0, 'columns_widest_table': 15, 'rows_widest_table': 39, 'update_cost_category_rename': 22}
  verified_revenue_all_shapes: 106.15

Stop at Part 4 and Part 5 together, because they're the ones that sum up the entire module in a single picture. 106.15 shows up three times, once per shape — the business fact doesn't change with the model's structure. And SHAPE_COMPARISON gathers, in a single structure, every number the seven previous lessons measured separately: joins_to_category comes from lesson 3 (EXPLAIN), update_cost_category_rename comes from lesson 6 (the real UPDATE), rows_widest_table and columns_widest_table come from lesson 5 (building the OBT). Every field of this structure has, behind it, a lesson that verified it with evidence — it isn't a table of opinions, it's a summary of measurements.

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

flowchart TD
    A["L2: dim_category\nVERIFIED -- 4 products, 3 categories"] --> B
    B["L3: EXPLAIN\nVERIFIED -- 1 HASH_JOIN vs 2"] --> C
    C["L4: The OBT argument\nFivetran + dataarchitect.studio"] --> D
    D["L5: mart_daily_sales_obt\nVERIFIED -- 39 rows, 106.15"] --> E
    E["L6-L7: When each shape wins\nVERIFIED -- cost 1/2/22, identical result"] --> F
    F["SHAPE_COMPARISON\nthe formal contract this project delivers"]
    F --> G["Modules 4-8: can cite\nthis contract without repeating the comparison"]

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

Three of the checklist's seven rows are now resolved. Module 4, next on the list, specifically needs the star schema this module left intact — not the snowflake version or the OBT, which were parallel comparisons within this module: dim_product, with category as a text column, is the table module 4 is going to historize with SCD type 1 and type 2, precisely because, as you learned in lesson 6, dim_product is a dimension — a source of truth, not derived — and therefore the correct place to record how it changes over time.

Common mistakes

Delivering SHAPE_COMPARISON without Part 4's cross-check. What happens: someone, in a hurry to show the comparison structure as the final result, builds SHAPE_COMPARISON directly after Part 3, without first running Part 4's assert star_rev == snowflake_rev == obt_rev == 106.15. Why it happens: the comparison structure looks more presentable as "the deliverable," and the revenue check feels like a disposable preliminary step. How to spot it: if your final deliverable includes no executed evidence that all three shapes match in revenue, you're documenting a structural comparison without having confirmed the structures actually represent the same business — exactly the same trap module 2 already warned about with the unverified JOIN. How to fix it: Part 4 of this project isn't optional — it's the guarantee that makes everything SHAPE_COMPARISON documents in Part 5 trustworthy.

Confusing "I compared all three shapes" with "I decided which one to use forever." What happens: someone finishes this project and, looking for a single answer, decides Kiosko should use the OBT (or the snowflake) as its permanent model from here on, discarding the other two shapes entirely. Why it happens: after an entire module comparing alternatives, it's natural to look for a final, definitive verdict. How to spot it: if your takeaway from this project is a single "winning" shape, with no mention of context — who queries, how often the data changes, how repeated the query pattern is — you missed lessons 6 and 7's central argument. How to fix it: remember SHAPE_COMPARISON doesn't declare a winning shape — it documents three valid structures, each with its own cost and its own benefit, measured with evidence. The decision of which to use depends on the specific use case, not a fixed preference.

Assuming dim_product_normalized or mart_daily_sales_obt are going to keep existing, unchanged, in the following modules. What happens: someone, seeing this project document both tables in SHAPE_COMPARISON, expects module 4 to use them as a base for historization, or module 6 to build fact_sessions by joining against dim_product_normalized. Why it happens: SHAPE_COMPARISON presents all three shapes with the same level of detail, which can suggest all three have the same permanent status in the guide. How to spot it: if, in an exercise from a later module, you write JOIN dim_product_normalized or JOIN mart_daily_sales_obt expecting them to stay in sync with changes you're about to introduce, you mixed up this module's purpose with the rest of the guide. How to fix it: as already warned in lessons 2 and 4 of this module, dim_product (star, category as text) remains the canonical dimension for the rest of this guide. dim_category, dim_product_normalized, and mart_daily_sales_obt exist for this module's comparison, documented here, and don't get updated in the following modules.

Exercises

Exercise 1 — Verify the OBT also reproduces revenue by store and by product. Using mart_daily_sales_obt, write two queries that group by store_name and by product_name respectively, and compare the results against the numbers you already know from module 1 (38.3/38.8/29.05 by store; 33.55/21.6/10.5/40.5 by product).

print(con.sql("""
    SELECT store_name, ROUND(SUM(revenue), 2) AS revenue
    FROM mart_daily_sales_obt
    GROUP BY store_name
    ORDER BY store_name
"""))
print(con.sql("""
    SELECT product_name, ROUND(SUM(revenue), 2) AS revenue
    FROM mart_daily_sales_obt
    GROUP BY product_name
    ORDER BY product_name
"""))

┌───────────────┬─────────┐
│  store_name   │ revenue │
│    varchar    │ double  │
├───────────────┼─────────┤
│ Kiosko Centro │    38.3 │
│ Kiosko Norte  │    38.8 │
│ Kiosko Sur    │   29.05 │
└───────────────┴─────────┘

┌───────────────────────┬─────────┐
│     product_name      │ revenue │
│        varchar        │ double  │
├───────────────────────┼─────────┤
│ Bottled Water 600ml   │   33.55 │
│ Energy Bar            │    21.6 │
│ Instant Coffee Sachet │    10.5 │
│ Phone Charger Cable   │    40.5 │
└───────────────────────┴─────────┘

Exercise 2 — Extend SHAPE_COMPARISON with a recommendation field. Without using datetime.now(), add to SHAPE_COMPARISON a recommended_default field with the value "star", and a recommendation_reason field explaining, in one sentence, why the star remains the recommended default shape for the rest of this guide.

SHAPE_COMPARISON["recommended_default"] = "star"
SHAPE_COMPARISON["recommendation_reason"] = (
    "The star balances query flexibility and maintenance cost; "
    "snowflake and OBT get built on top when a specific use case justifies it."
)
print(f"recommended_default: {SHAPE_COMPARISON['recommended_default']}")
print(f"recommendation_reason: {SHAPE_COMPARISON['recommendation_reason']}")

recommended_default: star
recommendation_reason: The star balances query flexibility and maintenance cost; snowflake and OBT get built on top when a specific use case justifies it.

Exercise 3 — Explain, from memory, what module 4 needs from this project to get started. Without looking at the guide's design, describe in a 4-6 sentence paragraph which pieces of SHAPE_COMPARISON — and of the tables built in this project — module 4 is going to need to historize dim_product with SCD type 1 and type 2.

Summary and next step: the end of module 3

With this mini-project you close out module 3 completely. You built Kiosko's three shapes in the same DuckDB connection: the star inherited from module 2 (unchanged), the snowflake version (dim_category + dim_product_normalized), and the wide table (mart_daily_sales_obt, with its coarser day + store + product grain). You verified, with a literal assert, that all three shapes match at the same total revenue — 106.15 — and documented the entire module's comparison in SHAPE_COMPARISON: one JOIN away in the star, two in the snowflake, zero in the OBT; one row of update cost in the normalized dimension, twenty-two in the wide table.

You took the third step of an eight-module journey: fact_orders and dim_product (in its star shape) remain, column for column, the same tables you already knew — what changed is that you now know, with your own evidence, why this guide chose them as the foundation to build on, instead of jumping straight to the most normalized or the most flattened shape.

Where you go next. Module 4 — slowly-changing-dimensions — takes dim_product, as this project left it, and asks it the question no lesson in this guide has answered yet: what happens when a product's price or category genuinely changes? SCD type 1 overwrites and loses history; SCD type 2 historizes with valid_from/valid_to/is_current — and you're going to implement it, for real, with MERGE INTO over two real snapshots of products.

Resources

• Kimball Group — "Star Schema / OLAP Cube" — the complete source for the dimensional vocabulary this project integrates: star, snowflake, and the discipline of deciding between them with judgment. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
• Fivetran — "Star Schema vs. OBT for Data Warehouse Performance" — the production benchmark backing, with real data, this module's complete quantitative argument. fivetran.com/blog/star-schema-vs-obt. In English.
• dataarchitect.studio — "One Big Table vs the Star Schema: The Real Trade-off" — the layered recommendation (star as foundation, OBT as service) SHAPE_COMPARISON adopts as its closing principle. dataarchitect.studio/essays/one-big-table-vs-star-schema. In English.
• DuckDB — "EXPLAIN: Inspect Query Plans" — the tool lesson 3 of this module used to measure, not assume, an extra JOIN's cost. duckdb.org/docs/current/guides/meta/explain. 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.