Module 3: Star Vs Snowflake Vs One Big Table

Comparing the cost of a JOIN with EXPLAIN

Next →

Description

In the previous lesson you built two different paths to the same information: dim_product (the star version, with category as a text column, a single JOIN away from fact_orders) and dim_product_normalized + dim_category (the snowflake version, two JOINs away). Until now, that difference — "one hop" versus "two hops" — was a conceptual claim. This lesson turns it into evidence: it uses EXPLAIN, the DuckDB command that shows the real execution plan the engine is going to run, to see, with your own eyes, the physical difference between the two queries.

Connection to the module. This lesson delivers the module's second executable result, the one lesson 3 of this guide's design explicitly names: comparing plans with EXPLAIN, one JOIN hop (star) against two hops (snowflake), over data and queries already built and verified.

An analogy: the route's itinerary, not the city's map

When you ask for directions to get somewhere, there are two ways to receive them. The first is a general map of the city: useful for context, but it doesn't tell you, step by step, what you're going to do. The second is a precise itinerary: "go out the main door, walk two blocks, turn right, enter the second building" — every step, in the exact order you're going to carry it out, with no ambiguity.

EXPLAIN is that precise itinerary, applied to a SQL query. It doesn't tell you, in the abstract, "this is going to do a JOIN" — it shows you, in the exact order the engine is going to execute them, every physical operation: which table it scans first, how it combines the results, how many rows it expects to find at each step. Comparing two queries' plans isn't comparing two general maps of "roughly what the route looks like" — it's comparing two itineraries line by line, counting how many turns each one has.

Worked example: the star's plan against the snowflake's plan

Rebuild fact_orders and the two versions of the product dimension — the star (dim_product, with category as text) and the snowflake (dim_product_normalized + dim_category, from the previous lesson) — and compare the execution plan of the same question solved by both paths: "for each order line, what's its category?"

# explain_join_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],
)

# --- dim_product, the star version (category as a text column, inherited from module 2) ---
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_category + dim_product_normalized, the snowflake version from the previous lesson ---
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
""")

star_query = """
    SELECT f.order_id, f.revenue, p.category
    FROM fact_orders f
    JOIN dim_product p ON f.product_id = p.product_id
"""

snowflake_query = """
    SELECT f.order_id, f.revenue, c.category_name AS category
    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
"""

print(f"DuckDB version: {duckdb.__version__}\n")

print("=== EXPLAIN: star -- 1 JOIN hop to reach category ===")
for key, value in con.execute("EXPLAIN " + star_query).fetchall():
    print(value)

print("=== EXPLAIN: snowflake -- 2 JOIN hops to reach category ===")
for key, value in con.execute("EXPLAIN " + snowflake_query).fetchall():
    print(value)

print("=== Verification: both paths return the same result ===")
star_rows = con.sql(f"SELECT COUNT(*) FROM ({star_query}) t").fetchone()[0]
sf_rows = con.sql(f"SELECT COUNT(*) FROM ({snowflake_query}) t").fetchone()[0]
star_rev = con.sql(f"SELECT ROUND(SUM(revenue), 2) FROM ({star_query}) t").fetchone()[0]
sf_rev = con.sql(f"SELECT ROUND(SUM(revenue), 2) FROM ({snowflake_query}) t").fetchone()[0]
print(f"star:      {star_rows} rows, revenue {star_rev}")
print(f"snowflake: {sf_rows} rows, revenue {sf_rev}")
assert star_rows == sf_rows and star_rev == sf_rev, "the two paths do not match"
print("Verification: both paths match -- OK")

What to expect. Running python3 explain_join_cost.py (with DuckDB 1.5.5, the version used for this lesson — a EXPLAIN plan's exact format can vary slightly between engine versions, though the number of HASH_JOIN operators you're about to count doesn't depend on the version), the output is exactly this:

DuckDB version: 1.5.5

=== EXPLAIN: star -- 1 JOIN hop to reach category ===
┌───────────────────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│  product_id = product_id  │              │
│                           │              │
│          ~40 rows         │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││          SEQ_SCAN         │
│    ────────────────────   ││    ────────────────────   │
│           Table:          ││           Table:          │
│  memory.main.fact_orders  ││  memory.main.dim_product  │
│                           ││                           │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│                           ││                           │
│        Projections:       ││        Projections:       │
│         product_id        ││         product_id        │
│          order_id         ││          category         │
│          revenue          ││                           │
│                           ││                           │
│          ~40 rows         ││          ~4 rows          │
└───────────────────────────┘└───────────────────────────┘

=== EXPLAIN: snowflake -- 2 JOIN hops to reach category ===
┌───────────────────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│  product_id = product_id  │              │
│                           │              │
│          ~40 rows         │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││         HASH_JOIN         │
│    ────────────────────   ││    ────────────────────   │
│           Table:          ││      Join Type: INNER     │
│  memory.main.fact_orders  ││                           │
│                           ││        Conditions:        │
│   Type: Sequential Scan   ││ category_id = category_id │
│                           ││                           ├──────────────┐
│        Projections:       ││                           │              │
│         product_id        ││                           │              │
│          order_id         ││                           │              │
│          revenue          ││                           │              │
│                           ││                           │              │
│          ~40 rows         ││          ~4 rows          │              │
└───────────────────────────┘└─────────────┬─────────────┘              │
                             ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
                             │          SEQ_SCAN         ││          SEQ_SCAN         │
                             │    ────────────────────   ││    ────────────────────   │
                             │           Table:          ││           Table:          │
                             │        memory.main        ││  memory.main.dim_category │
                             │  .dim_product_normalized  ││                           │
                             │                           ││   Type: Sequential Scan   │
                             │   Type: Sequential Scan   ││                           │
                             │                           ││        Projections:       │
                             │        Projections:       ││        category_id        │
                             │         product_id        ││       category_name       │
                             │        category_id        ││                           │
                             │                           ││                           │
                             │          ~4 rows          ││          ~3 rows          │
                             └───────────────────────────┘└───────────────────────────┘

=== Verification: both paths return the same result ===
star:      40 rows, revenue 106.15
snowflake: 40 rows, revenue 106.15
Verification: both paths match -- OK

Count the HASH_JOIN operators in each plan: the star's tree has exactly one — fact_orders gets combined directly with dim_product, each read with a SEQ_SCAN (sequential scan), and that's it. The snowflake's tree has exactly two — the first HASH_JOIN combines fact_orders with the result of a second HASH_JOIN, which in turn combines dim_product_normalized with dim_category. This isn't an opinion or an estimate: it's, literally, the physical plan DuckDB generates to execute each query, with the number of operators you're going to count yourself. And the final verification confirms something just as important: the two plans — with different execution costs — produce exactly the same result, 106.15 in revenue in both cases. An extra JOIN doesn't change the correct answer; it changes how much work it takes the engine to reach it.

Diagram: the operator tree, side by side

flowchart TD
    subgraph Star["star -- 1 HASH_JOIN"]
        A1["HASH_JOIN\nproduct_id = product_id"]
        A2["SEQ_SCAN\nfact_orders"] --> A1
        A3["SEQ_SCAN\ndim_product\n(category is already a column)"] --> A1
    end

    subgraph Snowflake["snowflake -- 2 HASH_JOINs"]
        B1["HASH_JOIN\nproduct_id = product_id"]
        B2["SEQ_SCAN\nfact_orders"] --> B1
        B3["HASH_JOIN\ncategory_id = category_id"] --> B1
        B4["SEQ_SCAN\ndim_product_normalized"] --> B3
        B5["SEQ_SCAN\ndim_category"] --> B3
    end

Going deeper: what each piece of the plan means, and why the order matters

A DuckDB EXPLAIN plan reads bottom to top: the operators at the base of the tree execute first, and the result climbs, level by level, up to the root operator — the topmost HASH_JOIN in both of this lesson's plans. Each box in the plan tells you three things: what type of operator it is (SEQ_SCAN for a sequential scan of a complete table, HASH_JOIN for combining two sets of rows by an equality condition), which table or condition it uses, and how many rows it expects to produce (the ~N rows estimate, calculated by the optimizer before executing the query, not measured afterward).

Notice a revealing detail in the snowflake's plan: the second HASH_JOIN — the one combining dim_product_normalized with dim_category — appears before, in execution order, the main HASH_JOIN that combines it with fact_orders. This makes sense if you think about what the query needs: to be able to join fact_orders against "each product's category," you first have to reconstruct that information — join dim_product_normalized with dim_category — and only then use that reconstructed result as the right side of the main JOIN. The snowflake doesn't just have one more operator: it has an extra dependency the engine must resolve before it can complete the work that, in the star version, a single direct SEQ_SCAN over dim_product resolves.

It's worth being precise about what this lesson proves and what it doesn't. With three stores, four products, and forty orders, the difference between one HASH_JOIN and two is, in terms of real execution time, insignificant — both queries run in microseconds on this toy dataset. What this lesson demonstrates isn't "the snowflake is slow in practice today" — at this volume, it isn't — but something more fundamental: the execution plan's structure scales with the number of normalization hops, regardless of data volume. A production warehouse with millions of rows in its fact table and a dimension hierarchy normalized across four or five levels — product → subcategory → category → department, say — pays that same pattern, multiplied, on every query. This lesson teaches you to read that structure with EXPLAIN; it doesn't teach you to tune a production engine with millions of rows — that's the territory of advanced-sql-querying-guide, the sibling guide that goes deep into execution plans, indexes, and real tuning.

Common mistakes

Confusing EXPLAIN with EXPLAIN ANALYZE. What happens: someone expects EXPLAIN to show them real execution times — how many milliseconds each operator took — and is surprised when the plan only shows estimates (~N rows), with no timing numbers at all. Why it happens: in everyday language, "explaining" a query sounds like "telling me how it behaved," which is exactly what the EXPLAIN ANALYZE variant does — it does execute the query and measure real times — not plain EXPLAIN, which only generates the plan, without running anything. How to spot it: if your output has no time column (ms, μs) or actually-processed-rows count, and only has estimates marked with ~, you're looking at a plain EXPLAIN, not an EXPLAIN ANALYZE. How to fix it: for this lesson, plain EXPLAIN is exactly what you need — comparing the plan's shape (how many operators, what type), not measuring times on a forty-row dataset, where any real-time measurement would be noise, not signal. EXPLAIN ANALYZE — and real tuning that depends on measured times — is the territory of advanced-sql-querying-guide.

Assuming "more JOINs is always slower at any volume." What happens: someone sees the snowflake's plan with two HASH_JOINs and concludes, with no further evidence, that the snowflake is always going to be slower than the star, in any situation and at any data volume. Why it happens: "more operators in the plan" intuitively feels like "slower," and that intuition isn't entirely wrong — but it's incomplete. How to spot it: if your takeaway from this lesson is "never normalize anything, it's always worse," you're missing lesson 6, which is going to show you, with evidence just as concrete, a scenario where the JOIN's extra cost is far smaller than the benefit of keeping a single source of truth. How to fix it: the number of JOINs is one factor in a query's cost, not the only one. The size of the tables involved in each JOIN (here, dim_category has only three rows — almost free to scan), how often the data changes, and how many times the same query runs against how many times the data gets updated matter just as much as the operator count.

Running EXPLAIN on a different query than the one you're actually going to run, and comparing apples to oranges. What happens: someone compares the plan of a query selecting few columns against the plan of another query selecting many more, or with an extra WHERE, and attributes the plan difference exclusively to the number of JOINs. Why it happens: it's easy, when putting together a quick comparison, to write two slightly different queries without realizing it. How to spot it: if the two queries you're comparing don't select exactly the same logical columns (here, order_id, revenue, and category, nothing more), your comparison doesn't isolate the variable you want to measure. How to fix it: as in this lesson, keep the two queries identical in everything except the path that reaches category — that way any difference in the plan is explained exclusively by the dimension's shape, not by some other hidden variable.

Exercises

Exercise 1 — Count the SEQ_SCAN operators in each plan. Without re-running the script, review this lesson's "What to expect" and count how many SEQ_SCAN operators (sequential table scan) appear in the star's plan and how many in the snowflake's plan. Explain in one sentence why the number matches the number of tables involved in each query.

See solution

The star's plan has two SEQ_SCAN operators (one for fact_orders, one for dim_product), because the star query only involves two tables. The snowflake's plan has three SEQ_SCAN operators (one for fact_orders, one for dim_product_normalized, one for dim_category), because the snowflake query involves three tables. The number of SEQ_SCANs matches, in both cases, the number of distinct tables the query reads — each table needs, at minimum, a scan to make its rows available to the JOIN operators that combine them afterward.

Exercise 2 — Write the snowflake query for revenue by category, and compare it against module 2's result. Using dim_product_normalized and dim_category, write a query that groups fact_orders by category and calculates total revenue — the same question you solved in module 2's lesson 7, exercise 2, now via the snowflake path.

See solution 
print(con.sql("""
    SELECT c.category_name AS category, ROUND(SUM(f.revenue), 2) AS revenue, SUM(f.quantity) AS total_units
    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
    GROUP BY c.category_name
    ORDER BY c.category_name
"""))

Expected output:

┌─────────────┬─────────┬─────────────┐
│  category   │ revenue │ total_units │
│   varchar   │ double  │   int128    │
├─────────────┼─────────┼─────────────┤
│ beverages   │   44.05 │          75 │
│ electronics │    40.5 │           9 │
│ snacks      │    21.6 │          18 │
└─────────────┴─────────┴─────────────┘

Exactly the same numbers you already saw in module 2 — 44.05, 40.5, 21.6 — now calculated through two JOINs instead of one. The model's shape changed; the business fact it reports didn't.

Exercise 3 — Explain, without code, what would happen to the plan if dim_category had a million rows instead of three. In 2-3 sentences, explain whether the number of HASH_JOIN operators in the snowflake's plan would change, and what would actually change in the plan if dim_category were a much larger table.

See solution

The number of HASH_JOIN operators wouldn't change — it would still be two, because the query's structure (how many tables need joining to get from fact_orders to category_name) doesn't depend on any table's row volume, only on how many normalization hops exist between them. What would change is the row estimate (~N rows) shown next to dim_category's SEQ_SCAN — it would go from ~3 rows to ~1000000 rows — and that estimate is exactly the kind of information a production engine's optimizer uses to decide, for example, whether to build the hash table from dim_category or from the other side of the JOIN. That level of tuning — how the optimizer decides execution order and strategy based on real volume — is the territory of advanced-sql-querying-guide, not this lesson.

Summary and next step

In this lesson you turned a conceptual claim — "the snowflake needs one more JOIN hop than the star" — into literal evidence: the star's EXPLAIN plan has one HASH_JOIN; the snowflake's has two, with an extra dependency the engine must resolve before completing the main JOIN. You also verified both paths produce exactly the same result — 106.15 in revenue — confirming the extra cost doesn't buy any additional correctness, only a more consistent piece of data to maintain (which lesson 6 is going to measure with numbers of its own).

Before moving on you should be able to: count from memory how many HASH_JOIN operators each of this lesson's plans has; explain the difference between EXPLAIN and EXPLAIN ANALYZE, and why this lesson uses the former; and describe, in one sentence, what comparing plans over a forty-row dataset proves and what it doesn't.

Lesson 4 steps back from the structural and gets into the modern argument: why the columnar-warehouse industry has seriously reconsidered the wide table (One Big Table) as a legitimate shape, not a lazy shortcut.

Resources

← PreviousNormalizing a dimension: the snowflake schemaNext →The wide table (One Big Table) argument