Module 3: Star Vs Snowflake Vs One Big Table

Module introduction: star vs snowflake vs One Big Table

Next →

Why this module exists

Module 2 closed with a sentence left pending on purpose, in the very last line of its own project: "module 3 needs, as its starting point, exactly the star schema this project just closed out." That star — fact_orders joined to dim_store, dim_product, and dim_date through three verified JOINs, forty rows before, forty after — already exists. This module doesn't replace it. It puts it to the test.

Up to now, this guide built the star schema as if it were the only reasonable way to model an analytical warehouse, and in a sense it is: Kimball recommends it by default, and modules 1 and 2 explained in detail why. But a modeler with good judgment doesn't settle for "the default recommended shape" without also understanding its alternatives and their real costs. dim_product, as module 2 left it, has a text category column — "beverages", "snacks", "electronics" — repeated on every product belonging to that category. Is that a problem? It depends on the question you ask it. If Kiosko decides tomorrow to rename "beverages" to "drinks", how many rows do you have to touch? And at the opposite extreme: if Kiosko's BI team wants a dashboard that answers sales questions without writing a single JOIN, which table do you hand them?

This module answers both questions with evidence, not a fixed rule. First it normalizes — pulling category out of dim_product into its own table, dim_category, building this guide's first snowflake schema. Then it compares a JOIN's real cost between the star and the snowflake, using EXPLAIN to see the execution plan, not just imagine it. And in the exactly opposite direction, it builds a fully denormalized wide table — mart_daily_sales_obt, a real "One Big Table" (OBT), with fifteen columns and zero pending JOINs — and measures, with literal numbers, how much that convenience costs in space and maintenance. By the end of the module, you're going to be able to defend, with evidence, when each of the three shapes wins, instead of choosing by fashion or habit.

Connection to the module. This module doesn't touch fact_orders, or the star you built in module 2 — they stay exactly the same, serving as the baseline the other two shapes get compared against. What it builds are two new, parallel structures: the snowflake version (dim_category + dim_product_normalized) and the OBT version (mart_daily_sales_obt), both verified against the same revenue as always: 106.15.

An analogy: the closet, the labeled boxes, and the table already set

Go back to module 2's closet. The star schema was the organized closet: every garment within reach, one motion to get to anything. This module adds two more shapes to the comparison.

The first is the one module 2 already named without building: the snowflake schema is labeled boxes inside other boxes. Instead of having "category" as a visible tag on each garment, you store garments by type in boxes — "beverages," "snacks," "electronics" — and those boxes, in turn, have their own index in a separate list. If you ever rename the "beverages" box to "liquids," you only touch one tag — the box's — not every individual garment inside it. You gain consistency and ease of maintenance; you pay with an extra step every time you want to know which category a specific garment belongs to: first you find the garment, then you go look for which box it's in, then you read the box's tag.

The second shape is new in this module: the wide table, One Big Table (OBT), is the table already set. Instead of a closet — organized or with nested boxes, doesn't matter — where you have to pull out each ingredient separately, imagine someone already prepared the complete dish: the protein, the side, the sauce, all on one plate, ready to eat without a single trip to the kitchen. For someone who's hungry and wants to eat right now, it's the fastest option possible. The cost shows up somewhere else: preparing that dish took time before the diner arrived, and if tomorrow someone decides the sauce should have been different, the whole dish has to be remade — it's not enough to swap a jar in the pantry. That's exactly the trade-off this module is going to measure with real numbers: the OBT saves the JOIN for whoever's querying, in exchange for repeating, on every plate served, ingredients that in the organized closet — or the nested boxes — lived in a single place.

Worked example: the three shapes, before building them

Before writing this module's first line of SQL, it's worth seeing, at a glance, what each lesson builds and how the three shapes you're going to compare differ.

# three_shapes_map.py
SHAPES = [
    ("star", "The one inherited from module 2: fact_orders + 3 JOINs. category lives as text inside dim_product."),
    ("snowflake", "Normalizes category into its own table: dim_category + dim_product_normalized. One more JOIN hop to reach category."),
    ("OBT (wide table)", "mart_daily_sales_obt: zero pending JOINs. Every row already brings everything -- date, store, product, category -- together."),
]

COMPONENTS = [
    ("Normalizing a dimension", "dim_category + dim_product_normalized, without losing a single product"),
    ("Comparing the JOIN's cost", "EXPLAIN: 1 hop (star) vs 2 hops (snowflake), real execution plan"),
    ("The modern OBT argument", "Why columnar storage changed the classic normalization calculus"),
    ("Building Kiosko's OBT", "mart_daily_sales_obt: 15 columns, zero JOINs, the same revenue as always"),
    ("When the snowflake still wins", "The real cost of updating a repeated value -- measured, not assumed"),
    ("When the OBT still wins", "The same business question, solved 3 times, same result"),
    ("Star, snowflake, and OBT compared", "SHAPE_COMPARISON: the formal declaration that closes the module"),
]

print("=== The three shapes this module compares ===\n")
for name, description in SHAPES:
    print(f"- {name}")
    print(f"  {description}\n")

print("=== The seven pieces that build that comparison ===\n")
for i, (name, description) in enumerate(COMPONENTS, start=1):
    print(f"{i}. {name}")
    print(f"   {description}\n")

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

=== The three shapes this module compares ===

- star
  The one inherited from module 2: fact_orders + 3 JOINs. category lives as text inside dim_product.

- snowflake
  Normalizes category into its own table: dim_category + dim_product_normalized. One more JOIN hop to reach category.

- OBT (wide table)
  mart_daily_sales_obt: zero pending JOINs. Every row already brings everything -- date, store, product, category -- together.

=== The seven pieces that build that comparison ===

1. Normalizing a dimension
   dim_category + dim_product_normalized, without losing a single product

2. Comparing the JOIN's cost
   EXPLAIN: 1 hop (star) vs 2 hops (snowflake), real execution plan

3. The modern OBT argument
   Why columnar storage changed the classic normalization calculus

4. Building Kiosko's OBT
   mart_daily_sales_obt: 15 columns, zero JOINs, the same revenue as always

5. When the snowflake still wins
   The real cost of updating a repeated value -- measured, not assumed

6. When the OBT still wins
   The same business question, solved 3 times, same result

7. Star, snowflake, and OBT compared
   SHAPE_COMPARISON: the formal declaration that closes the module

Still no real Kiosko numbers — this map is, again, the blueprint before the construction. But notice the order: first you normalize (you build the snowflake), then you measure the cost of that normalization with EXPLAIN, then you go to the opposite extreme and understand why someone would choose not to normalize anything, then you actually build that wide table, and only at the end — with all three shapes already built and verified — do you compare when each one wins. There are no shortcuts: you can't argue with judgment about a trade-off you didn't measure yourself.

Diagram: where you were, where you're going to be

flowchart LR
    subgraph M2["Module 2 (already written)"]
        A["Complete star schema\nfact_orders + 3 JOINs\ndim_product.category = text"]
    end

    subgraph M3["This module (3 of 8)"]
        B["L2: dim_category\n(normalized, EXECUTED)"]
        C["L3: EXPLAIN\n1 hop vs 2 hops, EXECUTED"]
        D["L4: The OBT argument\n(conceptual + numbers)"]
        E["L5: mart_daily_sales_obt\n(EXECUTED, 15 columns)"]
        F["L6-L7: When each shape wins\n(EXECUTED, update cost)"]
        G["L8: All three shapes\ncompared, EXECUTED"]
    end

    subgraph Resto["Modules 4-8"]
        H["SCD, point-in-time joins,\naccumulating snapshot..."]
    end

    A --> B --> C --> D --> E --> F --> G --> H

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: the three shapes, before building them
L2        Normalizing a dimension: dim_category, EXECUTED
L3        Comparing a JOIN's cost with EXPLAIN, EXECUTED
L4        The wide table (One Big Table) argument, conceptual
L5        Building mart_daily_sales_obt, EXECUTED
L6        When the snowflake still wins, EXECUTED (update cost)
L7        When the OBT still wins, EXECUTED (same question, 3 paths)
L8        Project: Kiosko's three shapes compared

Lessons 2, 3, and 5 are the ones that run most of this module's new code: building the snowflake, comparing its execution plan against the star, and building the OBT. Lesson 4 is mostly conceptual — the modern argument behind the wide table, backed by real industry benchmarks — though it also runs a small example. Lessons 6 and 7 are the ones that give judgment to everything before them: each one runs a concrete comparison showing, with numbers, when each shape wins. Lesson 8 closes with the mini-project: all three shapes built at once, verified against the same 106.15 revenue.

Going deeper: why this comparison needs the star already built

It might seem this module could have been written before module 2 — after all, "comparing modeling shapes" sounds like a design discussion, not something that depends on already having a working star schema. This module resists that temptation for a concrete reason: you can't measure an extra JOIN's cost if the reference JOIN you're comparing it against doesn't already exist, built and verified. Module 2's star — fact_orders joined to dim_product in a single hop — is exactly that reference. Without it, this module's lesson 3 would have nothing to compare the snowflake version's EXPLAIN plan against; without module 2's already-verified revenue (106.15), lesson 5 would have no way to confirm building the OBT didn't alter a single cent of the original fact.

There's a second, more subtle reason: this guide teaches how to decide a model's shape with evidence, not with a preference declared in advance. That's only possible if, for every shape being compared, a real, executed, verifiable example exists — not a hypothetical diagram. Module 2 built that real foundation. This module uses it as a fixed starting point, and builds two variations — more normalized, less normalized — over exactly the same data, so the comparison is fair: the same forty orders, the same three stores, the same four products, in all three shapes.

Common mistakes

Thinking this module replaces module 2's star schema with a "better" shape. What happens: someone, seeing this module build a snowflake version and an OBT version, assumes one of the two is going to "win" and replace the star as the rest of the guide's canonical shape. Why it happens: it's tempting to expect a module titled "star vs snowflake vs OBT" to end with a single, definitive verdict. How to spot it: if, after finishing this module, you expect module 4 (SCD) to historize dim_product_normalized or mart_daily_sales_obt instead of the original dim_product, you have this confusion. How to fix it: module 2's star remains, for the rest of this guide, the canonical foundation everything else gets built on — SCD, point-in-time joins, accumulating snapshot. This module's snowflake and OBT are parallel comparisons, built to understand the trade-off, not replacements that survive beyond this module.

Assuming "normalizing is always more correct" or "denormalizing is always faster," without measuring. What happens: someone arrives at this module with an opinion already formed — maybe from another experience, another course, another job — about which of the three shapes is "the right one," and expects this module to simply confirm that opinion. Why it happens: the normalize-vs-denormalize debate is decades old, and almost everyone shows up with a side already picked. How to spot it: if you finish lesson 3 surprised by EXPLAIN's real result, or lesson 5 surprised by what the wide table actually costs — in space, in rows to update — that's a sign your prior opinion wasn't backed by evidence measured on this specific data. How to fix it: lessons 6 and 7 of this module exist exactly for this — they show, with executed numbers, a concrete case where each shape wins. Neither of the three is universally superior; context decides.

Skipping lesson 4 because "it's just theory, no new code." What happens: someone, impatient to reach lesson 5 (where the OBT actually gets built), skims lesson 4 and skips the argument explaining why the industry has seriously reconsidered wide tables in recent years. Why it happens: a lesson with no new DuckDB table feels less "important" than one that actually builds something. How to spot it: if, in lesson 7, you can't explain in your own words why columnar storage changed the classic "normalizing always saves space" calculus, you missed lesson 4. How to fix it: lesson 4 isn't filler — it's the argument that explains why this module doesn't simply end up recommending the star and dismissing the OBT as a passing fad.

Exercises

Exercise 1 — Recall the exact checklist row this module resolves. Without rereading module 2's project, write from memory the exact name of the checklist row (introduced in module 1) that corresponds to this module.

See solution

The row reads, literally: "Snowflake vs wide table." Unlike module 2 — which resolved three combined pieces in a single checklist row (surrogate keys, dim_date, conformed dimensions) — this module resolves a row that already explicitly names the two alternative shapes it's going to build and compare against the star: the normalized version (snowflake) and the denormalized version (wide table, OBT).

Exercise 2 — Order the three shapes from most normalized to least normalized. Without looking at the worked example, order star, snowflake, and OBT from the shape with the least data duplication to the shape with the most data duplication.

See solution

From least to most duplication: snowflake (category lives in a single table, dim_category, referenced by key) → star (category lives as text inside dim_product, repeated once per product — but dim_product is still a small table, separate from fact_orders) → OBT (every store, product, and date attribute gets repeated on every row of mart_daily_sales_obt, the shape with the most duplication of the three). The snowflake is the most normalized shape because it explicitly separates category into its own table; the OBT is the least normalized because it packs everything into a single wide row, with no separate dimension table at all.

Exercise 3 — Explain the table-already-set analogy in your own words. Using this lesson's analogy (organized closet = star, labeled boxes inside boxes = snowflake, table already set = OBT), explain in 2-3 sentences what someone gains and loses by choosing to eat from the table already set instead of going to the kitchen for each ingredient.

See solution

Someone eating from the table already set gains immediate speed: they don't have to go to the kitchen, look for each ingredient separately, and combine them — the dish is already complete and ready. What they lose is flexibility and preparation efficiency: if the dish was prepared with the wrong ingredient, or if tomorrow a different version of the same dish is needed, the whole dish has to be remade from the kitchen, not just swap a jar in the pantry. The OBT (mart_daily_sales_obt) is exactly that set table: fast to query, but expensive to maintain when something already "served" on every row needs to change.

Summary and next step

This module takes the star schema module 2 left verified and puts it through two real comparisons: normalizing it further (snowflake, with dim_category) and fully denormalizing it (OBT, with mart_daily_sales_obt). You're going to measure, with EXPLAIN and with literal counts, each direction's real cost — not assume it — and you're going to close with your own judgment about when each of the three shapes wins.

Before moving on you should be able to: name the three shapes this module compares and what each one builds; explain, with this lesson's analogy, the difference between normalizing and denormalizing; and say from memory the exact row of module 1's checklist this module resolves.

Lesson 2 starts by normalizing: it pulls category out of dim_product and builds, for the first time in this guide, a real, verified snowflake schema.

Resources

← PreviousMini-project: Kiosko's star schema in DuckDBNext →Normalizing a dimension: the snowflake schema