Module 3: Star Vs Snowflake Vs One Big Table

When the snowflake still wins

Next →

Description

Lessons 4 and 5 built a solid argument in favor of the wide table: fewer JOINs, simpler queries, and — at this toy scale — not even a measurable space cost. It would be easy, after those two lessons, to conclude normalizing is never worth it. This lesson exists to correct that conclusion with the same evidence-based discipline: it takes the simplest possible example — renaming a category — and measures, with a real UPDATE run against all three shapes, exactly how many rows each one requires touching. The result isn't an opinion; it's a count.

Connection to the module. This lesson uses the three structures you already built — dim_category (lesson 2), the star's dim_product (module 2), mart_daily_sales_obt (lesson 5) — to measure, not assume, each shape's maintenance cost against the same business change.

An analogy: fixing an address in one document versus in a hundred receipts

Imagine a street in your city officially changes its name — it goes from being called "5th Street" to "Los Robles Avenue." If your ID card stores your address as a reference to a central street registry (a "street code," with the real name living in a single place, the municipal registry), fixing the change takes exactly one update: the municipality updates the name in its registry, and automatically, any document consulting that registry sees the correct name immediately. But if, instead, you have a hundred old utility receipts where the address was written by hand, letter by letter, on each receipt — with no reference to a central registry — fixing the street name change on those hundred receipts means, literally, rewriting the same text a hundred times.

That's, precisely, the difference this lesson is going to measure. dim_category is the municipality's central registry: one place, one update. mart_daily_sales_obt is the hundred receipts: the same text, written over and over, on every row that needs it.

Worked example: the real cost of renaming a category

Kiosko's marketing team decides to rename the "beverages" category to "drinks" — a vocabulary change, with no impact on the actual business, of the kind that happens regularly in any living catalog. Apply that same change, with an UPDATE, to all three shapes you built in this module, and count how many rows each one touches.

# category_rename_cost.py
from datetime import datetime

import duckdb

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

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
""")
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 mart_daily_sales_obt AS
    SELECT
        CAST(f.order_ts AS DATE) AS sale_date, f.store_id, s.store_name, s.city,
        f.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
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
""")

print("=== Marketing decides to rename the category 'beverages' to 'drinks' ===\n")

print("--- Path 1: dim_category (normalized) ---")
print(con.sql("SELECT * FROM dim_category ORDER BY category_id"))
con.execute("UPDATE dim_category SET category_name = 'drinks' WHERE category_name = 'beverages'")
print(con.sql("SELECT * FROM dim_category ORDER BY category_id"))
count1 = con.sql("SELECT COUNT(*) FROM dim_category WHERE category_name = 'drinks'").fetchone()[0]
print(f"Rows updated: {count1}\n")

print("--- Path 2: dim_product (star, category as a text column) ---")
count2 = con.sql("SELECT COUNT(*) FROM dim_product WHERE category = 'beverages'").fetchone()[0]
print(f"dim_product rows with category = 'beverages' before the UPDATE: {count2}")
con.execute("UPDATE dim_product SET category = 'drinks' WHERE category = 'beverages'")
print(con.sql("SELECT product_id, product_name, category FROM dim_product ORDER BY product_id"))
print(f"Rows updated: {count2}\n")

print("--- Path 3: mart_daily_sales_obt (OBT, category repeated on every row) ---")
count3 = con.sql("SELECT COUNT(*) FROM mart_daily_sales_obt WHERE category = 'beverages'").fetchone()[0]
print(f"mart_daily_sales_obt rows with category = 'beverages' before the UPDATE: {count3}")
con.execute("UPDATE mart_daily_sales_obt SET category = 'drinks' WHERE category = 'beverages'")
print(f"Rows updated: {count3}\n")

print("=== Summary: same business change, three different costs ===")
print(f"dim_category (normalized):        {count1} row  updated")
print(f"dim_product (star, text):         {count2} rows updated")
print(f"mart_daily_sales_obt (OBT, text): {count3} rows updated")

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

=== Marketing decides to rename the category 'beverages' to 'drinks' ===

--- Path 1: dim_category (normalized) ---
┌─────────────┬───────────────┐
│ category_id │ category_name │
│    int64    │    varchar    │
├─────────────┼───────────────┤
│           1 │ beverages     │
│           2 │ electronics   │
│           3 │ snacks        │
└─────────────┴───────────────┘

┌─────────────┬───────────────┐
│ category_id │ category_name │
│    int64    │    varchar    │
├─────────────┼───────────────┤
│           1 │ drinks        │
│           2 │ electronics   │
│           3 │ snacks        │
└─────────────┴───────────────┘

Rows updated: 1

--- Path 2: dim_product (star, category as a text column) ---
dim_product rows with category = 'beverages' before the UPDATE: 2
┌────────────┬───────────────────────┬─────────────┐
│ product_id │     product_name      │  category   │
│  varchar   │        varchar        │   varchar   │
├────────────┼───────────────────────┼─────────────┤
│ P001       │ Bottled Water 600ml   │ drinks      │
│ P002       │ Energy Bar            │ snacks      │
│ P003       │ Instant Coffee Sachet │ drinks      │
│ P004       │ Phone Charger Cable   │ electronics │
└────────────┴───────────────────────┴─────────────┘

Rows updated: 2

--- Path 3: mart_daily_sales_obt (OBT, category repeated on every row) ---
mart_daily_sales_obt rows with category = 'beverages' before the UPDATE: 22
Rows updated: 22

=== Summary: same business change, three different costs ===
dim_category (normalized):        1 row  updated
dim_product (star, text):         2 rows updated
mart_daily_sales_obt (OBT, text): 22 rows updated

A single business change — "beverages" is now called "drinks" — measured with the same UPDATE ... WHERE category = 'beverages' (or category_name, depending on the table), touches 1 row in dim_category, 2 rows in dim_product, and 22 rows in mart_daily_sales_obt. The ratio isn't a coincidence: dim_category touches one row because each category exists only once, regardless of how many products belong to it. dim_product touches two because two products (P001 and P003) belong to "beverages". And mart_daily_sales_obt touches twenty-two because, unlike the two dimensions, every row of the OBT represents a day + store + product combination — and "beverages" shows up repeated in every one of the twenty-two combinations where a product from that category sold, across the entire week and all three stores.

Diagram: the same change, propagated at different costs

flowchart TD
    Change["'beverages' -> 'drinks'\n(one business change)"]

    Change --> A["dim_category\n1 row updated"]
    Change --> B["dim_product (star)\n2 rows updated"]
    Change --> C["mart_daily_sales_obt\n22 rows updated"]

    A -.->|"any query that JOINs\nagainst dim_category\nsees 'drinks' immediately"| D["No extra work"]
    B -.->|"any query that JOINs\nagainst dim_product\nsees 'drinks' immediately"| D
    C -.->|"the OBT needs to be\nfully re-materialized, or the manual\nUPDATE drifts out of sync with the source"| E["Inconsistency risk"]

Going deeper: why the real cost isn't just "number of rows touched"

Counting updated rows is the easy part to measure, and it's already enough evidence for this lesson's central argument. But it's worth naming a second, more subtle dimension of the cost that the row count doesn't fully capture: where does the data being updated come from?

dim_category and dim_product are dimension tables, built directly from Kiosko's source catalog (DIM_PRODUCT in kiosko.py). When you rename a category in dim_category, you're correcting the source of truth itself — any derived table depending on it (including, if you rebuilt it, mart_daily_sales_obt itself) would inherit the change automatically the next time it gets regenerated. mart_daily_sales_obt, on the other hand, is a derived table: it isn't the source of truth for which category each product has, it's a materialization of that truth, frozen at the moment it was built. The twenty-two-row UPDATE you ran in this lesson fixes the symptom — the text shown in the OBT — but doesn't fix the cause: if tomorrow someone rebuilds mart_daily_sales_obt from scratch with lesson 5's same CREATE TABLE ... AS SELECT, and dim_product still says "beverages" instead of "drinks" because nobody updated it there too, the freshly rebuilt OBT ends up with the old name again — silently undoing the manual UPDATE you just applied.

This is the real reason, beyond the twenty-two-row count, why a production warehouse normalizes its reference dimensions and treats any wide table as a materialized view that gets rebuilt from those dimensions, not as a table edited directly. This lesson's UPDATE is a valid pedagogical exercise — it lets you see the cost with your own eyes — but in a real pipeline, the correct way to propagate the change from "beverages" to "drinks" would be: update dim_product (the source of truth, two rows), and rebuild mart_daily_sales_obt from scratch with lesson 5's query — not edit it row by row. The OBT's real maintenance cost isn't just "twenty-two UPDATEs"; it's "the discipline of never editing it directly, and always regenerating it from a source that stays consistent."

Common mistakes

Editing the OBT directly in production, instead of regenerating it from the source. What happens: someone, in a hurry to fix incorrect data a BI user reported, writes a direct UPDATE against the production wide table, without touching the source dimension. Why it happens: the direct UPDATE feels faster and simpler than redoing the OBT's entire build pipeline. How to spot it: if the next time the pipeline runs, the "fixed" data shows up again with the old value, that's evidence the UPDATE fixed the symptom without fixing the cause — exactly what this lesson's "going deeper" section warned about. How to fix it: any data correction must be applied at the source dimension (dim_product, in this case) and propagated by regenerating the derived table, never the other way around.

Concluding "22 rows updated" means the OBT is 22 times more expensive to maintain than the dimension. What happens: someone takes the literal count — 1 versus 22 — and interprets it as a fixed, universal cost factor, applicable to any future change. Why it happens: the number is concrete and easy to remember, and it's tempting to treat it as a constant instead of a specific measurement of this change, on this dataset. How to spot it: if your argument in another context cites "22x more expensive" as a general rule, with no mention that it depends on how many OBT rows contain that specific category, you overgeneralized. How to fix it: the real factor depends on the change's cardinality — how many rows of the wide table touch the value that changed; in Kiosko, with only four products and three categories, the factor is small; in a production catalog with thousands of products and millions of rows of historical sales, the same kind of change could touch millions of rows, not twenty-two.

Thinking this lesson invalidates lesson 4's argument. What happens: someone, after seeing the UPDATE's cost, concludes the OBT was a mistake and that lesson 4 was wrong to defend it. Why it happens: it's easy to treat each lesson as competing with the previous one, instead of adding nuance to the same decision. How to spot it: if your takeaway from this module is "the OBT is never worth it," you missed lesson 4's central point — the argument depends on the query pattern and how often the underlying data changes. How to fix it: a catalog's categories change infrequently — "beverages" to "drinks" is, in practice, a rare event — the OBT's query-speed gain (conceptually measured in lesson 4, and which lesson 7 is going to confirm with evidence) happens on every query, many times a day. A rare maintenance cost, compared against a frequent query gain, still favors the OBT for the right use case — this lesson only makes that cost visible, it doesn't declare it disqualifying.

Exercises

Exercise 1 — Calculate the cost of renaming "snacks" instead of "beverages". Repeat this lesson's experiment, but with the "snacks" category instead of "beverages", and compare the three resulting counts against this lesson's.

See solution 
count1_snacks = con.sql("SELECT COUNT(*) FROM dim_category WHERE category_name = 'snacks'").fetchone()[0]
count2_snacks = con.sql("SELECT COUNT(*) FROM dim_product WHERE category = 'snacks'").fetchone()[0]
count3_snacks = con.sql("SELECT COUNT(*) FROM mart_daily_sales_obt WHERE category = 'snacks'").fetchone()[0]
print(f"dim_category: {count1_snacks}, dim_product: {count2_snacks}, mart_daily_sales_obt: {count3_snacks}")

Expected output:

dim_category: 1, dim_product: 1, mart_daily_sales_obt: 10

dim_category still touches 1 row (every category, regardless of which one, exists only once). dim_product touches 1 row — not 2, like with "beverages" — because only P002 (Energy Bar) belongs to "snacks". mart_daily_sales_obt touches 10 rows — not 22 — the exact count of day+store+product combinations where P002 sold during the week. The ratio changes with each category, confirming exactly this lesson's second common mistake's point: the cost factor isn't a universal constant, it depends on how many products and how many sales the specific category that changes has.

Exercise 2 — Verify the JOIN between dim_product and mart_daily_sales_obt stayed in sync after the UPDATE. After running this lesson's worked example (where you already updated all three tables), confirm dim_product's category and mart_daily_sales_obt's category remain consistent with each other for P001 — and explain why this consistency was a deliberate decision of this exercise, not an automatic system guarantee.

See solution 
print(con.sql("""
    SELECT dp.product_id, dp.category AS category_in_dim_product, obt.category AS category_in_obt
    FROM dim_product dp
    JOIN (SELECT DISTINCT product_id, category FROM mart_daily_sales_obt) obt
      ON dp.product_id = obt.product_id
    WHERE dp.product_id = 'P001'
"""))

Expected output:

┌────────────┬─────────────────────────┬─────────────────┐
│ product_id │ category_in_dim_product │ category_in_obt │
│  varchar   │         varchar         │     varchar     │
├────────────┼─────────────────────────┼─────────────────┤
│ P001       │ drinks                  │ drinks          │
└────────────┴─────────────────────────┴─────────────────┘

Both tables show "drinks" for P001, consistent with each other — but this is only true because this lesson's worked example explicitly ran all three UPDATEs, one per table. If you had only updated dim_product and forgotten mart_daily_sales_obt (or vice versa), the two tables would have gone out of sync, with no automatic system mechanism warning you — nothing in DuckDB propagates an UPDATE from one independent table to another. This is exactly the reason, named in this lesson's "going deeper" section, why a real pipeline regenerates the OBT from the source instead of keeping it in sync by hand with parallel UPDATEs.

Exercise 3 — Explain, without code, a Kiosko scenario where normalized dim_category's cost would be even lower than 1 row. In 2-3 sentences, describe a hypothetical situation — not necessarily about renaming a category — where having dim_category separate would allow a change with zero rows touched in the fact tables or the OBT.

See solution

Adding a completely new attribute to categories — for example, a requires_refrigeration BOOLEAN column, useful for Kiosko store logistics — is a case where normalized dim_category allows the change without touching a single row of fact_orders, dim_product, or mart_daily_sales_obt: it's enough to run ALTER TABLE dim_category ADD COLUMN requires_refrigeration BOOLEAN and populate that column for the three existing categories. Under the denormalized version (star or OBT), adding that same attribute would require, at minimum, a new column in every table that already stores category as text, and deciding how to populate it for every existing row — a schema change, not just a data change, with a much larger migration cost than the simple ALTER TABLE over the normalized dimension.

Summary and next step

In this lesson you measured, with a real UPDATE run against all three shapes, the concrete cost of maintaining a repeated dimension value: 1 row in normalized dim_category, 2 in dim_product (star), 22 in mart_daily_sales_obt (OBT). You also understood why a wide table's real cost in production isn't just the UPDATE count — it's the discipline of treating it as a derived view that gets regenerated from the source, never as a table edited directly.

Before moving on you should be able to: recite this lesson's three numbers (1, 2, 22) from memory and which table each one corresponds to; explain the difference between "fixing the symptom" and "fixing the cause" when updating a dimension value; and name a scenario where normalized dim_category allows a change with zero rows touched in the fact tables.

Lesson 7 completes the argument in the opposite direction: the same business question, resolved through all three paths, confirming with evidence that the OBT does deliver the query-speed gain lesson 4 promised — the other side of the scale this lesson just weighed.

Resources

← PreviousBuilding Kiosko's sales OBTNext →When the OBT still wins