Module 3: Star Vs Snowflake Vs One Big Table

The wide table (One Big Table) argument

Next →

Description

The previous two lessons pushed Kiosko's model in one direction: more normalization, more JOINs, more consistency, less duplication. This lesson pushes in the exactly opposite direction, and explains why it's worth doing. The wide table — One Big Table, OBT — takes denormalization to the extreme: instead of splitting the data across several tables related by key, it packs everything into a single table, with one row per relevant combination and every descriptive column already present, ready to query without a single JOIN. This lesson doesn't build that table yet — that's lesson 5's job — it builds the argument explaining why, in the world of the modern columnar warehouse, that shape stopped being a lazy shortcut and became a legitimate design decision.

Connection to the module. This lesson is purely conceptual, but it's the necessary bridge between the first two lessons (which normalized) and the next two (which are going to build and measure the OBT). Without this argument, lesson 5 would feel like an arbitrary step backward to "the wrong shape" instead of an informed decision.

An analogy: why the table already set is sometimes the right call

In lesson 1 of this module, the OBT was introduced as the table already set — the whole dish prepared in advance, ready to eat with no trip to the kitchen. It's worth extending that analogy one step further, because the wide table's modern argument isn't "the set table is always better than cooking" — that would be an absurd claim. The real argument depends on who sits down at the table and how often they repeat the same order.

Think of a fast-food restaurant at peak hour: hundreds of people order, overwhelmingly, one of the same ten menu items. Preparing each dish from scratch, cooking every ingredient separately the exact moment someone orders it, would be unsustainable under that demand — the restaurant that survives peak hour is the one with the ingredients for its most-ordered dishes already semi-prepared, ready to assemble in seconds. That's exactly the use case where the OBT wins: a known query pattern, repeated very frequently, where the cost of "prepping the dish in advance" (building the wide table once) pays for itself many times over in serving speed (every BI team query, with no JOIN). The mistake would be generalizing that logic to an à la carte menu, where every customer orders something different and semi-prepping everything in advance would waste more than it saves — that's the case where the star, flexible by design, still wins, and it's exactly what lesson 6 is going to show with numbers.

Worked example: counting JOIN hops for a real BI question

Before building the OBT in lesson 5, it's worth seeing the argument in concrete numbers: for a typical BI dashboard question, how many JOINs does an analyst need to write under each of the three shapes?

# obt_argument_preview.py
QUESTION = "Revenue by product category, by store city, split into weekend vs weekday"

SHAPES = [
    ("star", 3, "fact_orders -> dim_product (category), fact_orders -> dim_store (city), fact_orders -> dim_date (is_weekend)"),
    ("snowflake", 4, "fact_orders -> dim_product_normalized -> dim_category (category, 2 hops), fact_orders -> dim_store (city), fact_orders -> dim_date (is_weekend)"),
    ("OBT (mart_daily_sales_obt)", 0, "no JOIN -- category, city, and is_weekend are already columns on the same row"),
]

print(f"Business question: {QUESTION}\n")
for shape, joins, path in SHAPES:
    print(f"{shape:28} {joins} JOIN(s)")
    print(f"{'':28} {path}\n")

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

Business question: Revenue by product category, by store city, split into weekend vs weekday

star                         3 JOIN(s)
                             fact_orders -> dim_product (category), fact_orders -> dim_store (city), fact_orders -> dim_date (is_weekend)

snowflake                    4 JOIN(s)
                             fact_orders -> dim_product_normalized -> dim_category (category, 2 hops), fact_orders -> dim_store (city), fact_orders -> dim_date (is_weekend)

OBT (mart_daily_sales_obt)   0 JOIN(s)
                             no JOIN -- category, city, and is_weekend are already columns on the same row

This question — revenue by category, by city, split by weekend — is exactly the kind of question a BI dashboard repeats dozens of times a day, with different filters: "show me the same thing but only for August," "now just Bogota," "now just beverages." Under the star, every one of those variations still needs three JOINs; under the snowflake, four; under the OBT, zero — the question gets resolved with a direct GROUP BY over a single already-flattened table. Lesson 7 of this module is going to run this exact same question, for real, through all three paths, and confirm all three return exactly the same result.

Diagram: the same data, three different access costs

flowchart LR
    subgraph Consulta["The same BI question"]
        Q["Revenue by category,\nby city, by weekend"]
    end

    Q --> S["star: 3 JOINs\nfact_orders + 3 dimensions"]
    Q --> SF["snowflake: 4 JOINs\nfact_orders + 4 tables\n(dim_product_normalized -> dim_category)"]
    Q --> O["OBT: 0 JOINs\ndirect GROUP BY\nover mart_daily_sales_obt"]

Going deeper: why columnar storage changed the classic calculus

The classic argument against denormalization — the one that's dominated relational-database teaching for decades — says, in essence: "denormalizing duplicates data, and duplicating data costs disk space and consistency risk." That argument is still valid in its form — lesson 6 of this module is going to confirm it with numbers — but it was born in an era of row-oriented storage, where each row of a table gets stored physically together, byte after byte, on disk. In that world, repeating "Kiosko Centro" as text on every row of a wide table genuinely takes up extra space, proportional to the number of repetitions.

Columnar storage — Parquet, and the engines that use it as their base format, including DuckDB — changes that calculus significantly. In a columnar format, each column gets compressed separately, and a column with few distinct values repeated many times — like store_name, with only three possible values repeated across dozens of rows — compresses extremely well with techniques like dictionary encoding (storing each unique value once, and replacing every occurrence with a short reference to that value) or run-length encoding (storing "this value repeats N times in a row" instead of writing it N times). You're going to see this with literal evidence, not just as a claim, in lesson 5: at Kiosko's toy scale, the complete wide table's Parquet file can end up weighing less than the sum of the star's four normalized tables — a result that contradicts classic intuition, and that lesson 5 is going to explain precisely why it happens at this scale, and why the pattern reverses at production scale.

This is, precisely, what the Fivetran benchmark cited in this guide's design reports: over real data on Redshift, Snowflake, and BigQuery — production columnar engines, not a toy dataset — a denormalized wide table came out 25% to 50% faster on typical BI queries, at the cost of 2 to 3 times more storage space. The storage cost still exists at production scale — columnar compression reduces it, it doesn't eliminate it — but the speed gain, for the right query pattern, is real and measured, not a marketing promise. The dataarchitect.studio article, also cited in this guide's design, sums up the argument with a concrete recommendation worth adopting as this lesson's principle: keep a star schema as your central model — the one that sustains flexibility, governance, and the ability to answer questions you don't know yet — and build the wide table on top of that star, as a service layer for consumers who do know their questions in advance and repeat them frequently. It's not "star or OBT" — it's "star, and optionally OBT on top, for the use case that justifies it." Lesson 5 builds exactly that layer, on top of the star that already exists, without replacing it.

Common mistakes

Concluding "columnar compression means denormalization no longer costs anything." What happens: someone, after reading that Parquet compresses repeated values well, concludes the classic argument against denormalization no longer applies at all, and that normalizing is simply a waste of time in a columnar world. Why it happens: the real finding — "columnar compression reduces duplication's cost" — is easy to overstate into "eliminates the cost entirely." How to spot it: if your takeaway from this lesson is "you never need to normalize anything," you're missing lesson 6 — the Fivetran benchmark, cited above, still reports 2-3x more storage with OBT, even on production columnar engines. Compression reduces the cost; it doesn't make it disappear. How to fix it: hold both ideas at once — columnar compression changed the calculus compared to the row-storage era, but the space cost (and the maintenance cost lesson 6 is going to measure) is still real.

Thinking this lesson recommends replacing the star with the OBT. What happens: someone reads this lesson's argument and understands the guide is about to abandon module 2's star schema in favor of the wide table, treating this module as a course correction. Why it happens: presenting an argument in favor of the OBT, after two entire lessons on normalization, can feel like the guide itself changing its mind. How to spot it: if you expect module 4 (SCD) to historize mart_daily_sales_obt instead of dim_product, you have this confusion — it was already warned about in lesson 1 of this module, and it's worth repeating here. How to fix it: remember the dataarchitect.studio argument cited above — the star remains the central model; the OBT is a service layer built on top, for a specific consumer (Kiosko's BI team), not a replacement.

Ignoring that the argument depends on the query pattern, not a universal property of "wideness." What happens: someone generalizes this lesson's argument beyond its real scope, assuming any wide table, for any purpose, is automatically a good idea. Why it happens: the argument comes with concrete, convincing examples, and it's easy to lose sight of the condition holding it up. How to spot it: if you can't name, for a hypothetical wide table, who queries it and how often they repeat the same pattern, you don't have the information needed to justify it. How to fix it: this lesson's restaurant analogy makes it explicit — the OBT wins when the query pattern is known and repeated frequently (the ten most-ordered menu items); it loses when the pattern is unpredictable (the à la carte menu). Lesson 7 is going to show the first case with evidence; lesson 6, the second.

Exercises

Exercise 1 — Count the JOINs for a different question. Without looking at the worked example, count how many JOINs, under the star and under the OBT, the question "total revenue by product, with no other breakdown" would need (the same question from module 2's lesson 7, exercise 2).

See solution

Under the star: 1 JOIN — fact_orders joined to dim_product is enough, because product_name (or category, if the question asked for it) lives directly in that table. Under the OBT: 0 JOINs — mart_daily_sales_obt already brings product_name as its own column, so a direct GROUP BY product_name resolves the question with no JOIN at all. This question, being simpler than the worked example's (a single breakdown, not three), shows a smaller difference between star and OBT (1 hop versus 0) than the worked example's three-breakdown question (3 hops versus 0) — the OBT's argument gets stronger the more simultaneous breakdowns a typical BI team question needs.

Exercise 2 — Explain the restaurant analogy in your own words. Using this lesson's analogy (fast food with a known, repeated menu vs. an à la carte restaurant), explain in 2-3 sentences why the OBT wins in the first case and loses in the second.

See solution

At the fast-food restaurant, most customers order one of the same ten dishes, so prepping those ingredients in advance saves time on every order — the cost of "cooking in advance" pays for itself many times over, because the same pattern repeats constantly. At the à la carte restaurant, every customer orders something different and unpredictable, so semi-prepping everything in advance would waste ingredients nobody ends up ordering in that exact combination — there, cooking each dish from scratch, with flexibility, when the order comes in is the right call. The OBT is the fast-food kitchen: it wins when the query pattern is known and repeats frequently (a BI dashboard that always asks the same thing); the star is the à la carte restaurant: it wins when the questions are unpredictable and change with every new analysis.

Exercise 3 — Explain, from memory, the dataarchitect.studio recommendation cited in this lesson. Without rereading the "going deeper" section, write in 2-3 sentences the concrete recommendation the dataarchitect.studio article offers about how a star schema and an OBT coexist in a real warehouse, not as mutually exclusive alternatives.

See solution

The recommendation is to keep the star schema as the warehouse's central model — the source of flexibility, governance, and the ability to answer new questions not yet known — and to build the wide table (OBT) on top of that star, as an additional service layer for consumers who do have a known, frequently repeated query pattern, like a high-traffic BI dashboard. It isn't a choice of "star or OBT" but a layered architecture: the star as the foundation, the OBT as a materialized, denormalized view built from that foundation, for the specific use case that justifies it.

Summary and next step

This lesson built the argument that carries the next one: why the wide table — One Big Table — stopped being, in a world of compressed columnar storage, a careless shortcut, and became a legitimate design decision for the right query pattern. You counted, in concrete numbers, how many JOINs the OBT saves on a typical BI question (three under the star, four under the snowflake, zero under the OBT), and you understood why columnar compression changes — without eliminating — denormalization's classic cost.

Before moving on you should be able to: explain, in your own words, why columnar storage changed the classic "normalizing always saves space" calculus; recite dataarchitect.studio's recommendation for how the star and the OBT coexist; and name the condition (a known, repeated query pattern) under which the OBT wins.

Lesson 5 stops arguing and starts building: mart_daily_sales_obt, Kiosko's real wide table, with fifteen columns, zero pending JOINs, and the same revenue as always.

Resources

← PreviousComparing the cost of a JOIN with EXPLAINNext →Building Kiosko's sales OBT