Module 3: Star Vs Snowflake Vs One Big Table
Normalizing a dimension: the snowflake schema
Description
dim_product, as module 2 left it, has a text category column: "beverages" for P001 and P003, "snacks" for P002, "electronics" for P004. That column works perfectly for querying — any JOIN against dim_product gives you the category in the same step — but it has a property worth making explicit: the text "beverages" is repeated, once for every product belonging to that category. This lesson pulls that column out into its own table — dim_category, with category_id and category_name — and builds this guide's first real snowflake schema: a dimension pointing to another dimension, instead of having all its attributes as its own columns.
Connection to the module. This is the module's first executable piece: it builds the "more normalized" half of the three-shape comparison the rest of the module develops. Lesson 3 is going to measure, with EXPLAIN, the exact cost of the table you build here.
An analogy: the labeled box inside the closet
Go back to module 2's organized closet, but now notice a detail that lesson didn't explore: inside the closet, each garment had a fabric tag sewn on with its type — "summer clothes," "winter clothes." If you bought ten summer shirts, all ten tags say, literally, the same word, sewn on ten separate times. It works, but it's redundant: if you ever decide to call it "light clothes" instead of "summer clothes," you have to unstitch and re-sew ten tags, one by one.
The alternative this lesson builds is the labeled box: instead of sewing the word "summer" onto each shirt, you store all the summer shirts inside a box that says, once, "summer clothes" — or, in the vocabulary you're going to use from here on, you assign it a box number, and in a separate index you write what each number means. Each shirt now only needs to know "I'm in box 3"; the meaning of "box 3" lives in a single place, the index. That's exactly what dim_category is going to be: the box index, with a number (category_id) and its meaning (category_name), completely separate from the products belonging to each category.
Worked example: dim_category and dim_product_normalized
Start from dim_product_natural exactly as module 1 left it — with category as a text column, with no surrogate key yet — and build the two new tables: dim_category first, dim_product_normalized after, using the exact same ROW_NUMBER() OVER (ORDER BY ...) pattern you already used in lesson 3 of module 2 for store_key and product_key.
# normalize_category.py
import duckdb
from kiosko import DIM_PRODUCT
con = duckdb.connect()
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],
)
print("=== dim_product_natural: category as a text column (inherited from module 2) ===")
print(con.sql("SELECT * FROM dim_product_natural ORDER BY product_id"))
print("=== dim_category: the distinct categories, normalized into their own table ===")
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
""")
print(con.sql("SELECT * FROM dim_category ORDER BY category_id"))
print("=== dim_product_normalized: category_id replaces text category ===")
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(con.sql("SELECT * FROM dim_product_normalized ORDER BY product_id"))
print("=== Check 1: dim_product_normalized lost or duplicated no products ===")
before = con.sql("SELECT COUNT(*) FROM dim_product_natural").fetchone()[0]
after = con.sql("SELECT COUNT(*) FROM dim_product_normalized").fetchone()[0]
print(f"dim_product_natural: {before} rows -> dim_product_normalized: {after} rows")
assert before == after, "normalization lost or duplicated products"
print("\n=== Check 2: dim_category has exactly the expected distinct categories ===")
n_categories = con.sql("SELECT COUNT(*) FROM dim_category").fetchone()[0]
print(f"distinct normalized categories: {n_categories}")
print("\n=== Check 3: joining back reconstructs exactly the original text ===")
print(con.sql("""
SELECT p.product_id, p.product_name, c.category_name, p.unit_cost
FROM dim_product_normalized p
JOIN dim_category c ON p.category_id = c.category_id
ORDER BY p.product_id
"""))What to expect. Running python3 normalize_category.py, the output is exactly this:
=== dim_product_natural: category as a text column (inherited from module 2) ===
┌────────────┬───────────────────────┬─────────────┬───────────┐
│ product_id │ product_name │ category │ unit_cost │
│ varchar │ varchar │ varchar │ double │
├────────────┼───────────────────────┼─────────────┼───────────┤
│ P001 │ Bottled Water 600ml │ beverages │ 0.4 │
│ P002 │ Energy Bar │ snacks │ 0.6 │
│ P003 │ Instant Coffee Sachet │ beverages │ 0.35 │
│ P004 │ Phone Charger Cable │ electronics │ 2.1 │
└────────────┴───────────────────────┴─────────────┴───────────┘
=== dim_category: the distinct categories, normalized into their own table ===
┌─────────────┬───────────────┐
│ category_id │ category_name │
│ int64 │ varchar │
├─────────────┼───────────────┤
│ 1 │ beverages │
│ 2 │ electronics │
│ 3 │ snacks │
└─────────────┴───────────────┘
=== dim_product_normalized: category_id replaces text category ===
┌─────────────┬────────────┬───────────────────────┬─────────────┬───────────┐
│ product_key │ product_id │ product_name │ category_id │ unit_cost │
│ int64 │ varchar │ varchar │ int64 │ double │
├─────────────┼────────────┼───────────────────────┼─────────────┼───────────┤
│ 1 │ P001 │ Bottled Water 600ml │ 1 │ 0.4 │
│ 2 │ P002 │ Energy Bar │ 3 │ 0.6 │
│ 3 │ P003 │ Instant Coffee Sachet │ 1 │ 0.35 │
│ 4 │ P004 │ Phone Charger Cable │ 2 │ 2.1 │
└─────────────┴────────────┴───────────────────────┴─────────────┴───────────┘
=== Check 1: dim_product_normalized lost or duplicated no products ===
dim_product_natural: 4 rows -> dim_product_normalized: 4 rows
=== Check 2: dim_category has exactly the expected distinct categories ===
distinct normalized categories: 3
=== Check 3: joining back reconstructs exactly the original text ===
┌────────────┬───────────────────────┬───────────────┬───────────┐
│ product_id │ product_name │ category_name │ unit_cost │
│ varchar │ varchar │ varchar │ double │
├────────────┼───────────────────────┼───────────────┼───────────┤
│ P001 │ Bottled Water 600ml │ beverages │ 0.4 │
│ P002 │ Energy Bar │ snacks │ 0.6 │
│ P003 │ Instant Coffee Sachet │ beverages │ 0.35 │
│ P004 │ Phone Charger Cable │ electronics │ 2.1 │
└────────────┴───────────────────────┴───────────────┴───────────┘
Four products came in, four products came out — dim_product_normalized lost or duplicated none of them. And something just as important: only three distinct categories (beverages, electronics, snacks) showed up in dim_category, even though dim_product_natural had four rows with the word category repeated — beverages appeared twice (in P001 and P003), but dim_category stores it just once, with category_id = 1. Check 3 confirms this normalization lost no information: joining dim_product_normalized back against dim_category reconstructs, word for word, the same text you had in dim_product_natural from the start.
Notice the name chosen for the new table: dim_product_normalized, not dim_product. This is deliberate. dim_product — the original table, with category as a text column, built in module 2 — remains the canonical table the rest of this guide is going to use from here on (module 4, for example, historizes dim_product, not this normalized version). dim_product_normalized is a parallel structure, built specifically for this three-shape comparison, that doesn't replace the original.
Diagram: from a text column to two tables joined by key
flowchart TD
subgraph Antes["dim_product (star, module 2)"]
A["product_id | product_name | category | unit_cost\nP001 | Bottled Water | beverages | 0.40\nP003 | Instant Coffee | beverages | 0.35\n(\"beverages\" repeated 2 times, as text)"]
end
subgraph Despues["snowflake (this lesson)"]
B["dim_product_normalized\nproduct_id | ... | category_id | unit_cost\nP001 | ... | 1 | 0.40\nP003 | ... | 1 | 0.35"]
C["dim_category\ncategory_id | category_name\n1 | beverages"]
B -->|"JOIN ON category_id"| C
end
Antes -->|"normalize"| Despues
Going deeper: why ROW_NUMBER() OVER (ORDER BY category) and not an arbitrary index
Notice a detail worth understanding precisely: category_id gets generated with ROW_NUMBER() OVER (ORDER BY category), the exact same pattern you already used for store_key and product_key in module 2. The ORDER BY category inside the window function isn't decorative — it determines what order ROW_NUMBER() assigns the numbers in, and that order is what makes category_id = 1 always correspond to "beverages" (the first category alphabetically), category_id = 2 to "electronics", and category_id = 3 to "snacks". Without that ORDER BY, DuckDB could assign the numbers in any internal order, and while the result would still be valid — each category would still get a unique category_id — it would stop being deterministic: running the same script twice could, in theory, produce different assignments.
This is the same reproducibility discipline this guide demands in every executable block — no random, nothing depending on the system clock — now applied to generating surrogate keys. ORDER BY category turns an operation that could be non-deterministic (the internal order in which an engine walks a subquery's rows) into a completely deterministic one: the same input data always produces, byte for byte, the same category_id assignment.
There's a second observation, just as important, about the subquery (SELECT DISTINCT category FROM dim_product_natural) t. The DISTINCT is what makes dim_category have three rows and not four — without it, ROW_NUMBER() OVER (ORDER BY category) would number all four rows of dim_product_natural one by one, and "beverages" would end up with two different category_ids (one for P001, another for P003), breaking exactly the property this lesson is after: that each category exists only once in dim_category.
Common mistakes
Forgetting the DISTINCT and ending up with duplicate categories. What happens: someone writes ROW_NUMBER() OVER (ORDER BY category) AS category_id, category AS category_name FROM dim_product_natural, without the DISTINCT subquery, and dim_category ends up with four rows — one per product — instead of three. Why it happens: the syntax without DISTINCT compiles and runs with no error at all; the problem is purely logical, not syntactic, so no warning message flags it. How to spot it: if SELECT COUNT(*) FROM dim_category gives you the same number as SELECT COUNT(*) FROM dim_product_natural, instead of the number of truly distinct categories, you have this bug — this lesson's Check 2 exists exactly to catch it. How to fix it: always build dim_category from a DISTINCT subquery over the column you're normalizing, never directly over the full table.
Comparing category (text) against category_id (integer) directly, without going through dim_category. What happens: someone, after building dim_product_normalized, tries to filter products in the "beverages" category by writing WHERE category_id = 'beverages', expecting the engine to "understand" the comparison. Why it happens: before this lesson, category was a text column, and filtering by its value was as simple as writing the text directly; it's easy to forget that, after normalizing, that text no longer lives in dim_product_normalized. How to spot it: DuckDB is going to throw a type-conversion error — category_id is INTEGER, it can't be compared against a text literal without conversion — or, in the worst case (if the engine allows implicit conversion), the filter simply won't find any row. How to fix it: to filter by category name after normalizing, you always need the JOIN against dim_category first — JOIN dim_category c ON p.category_id = c.category_id WHERE c.category_name = 'beverages' — exactly the extra cost lesson 3 is going to measure with EXPLAIN.
Assuming dim_product_normalized replaces dim_product for the rest of the guide. What happens: someone, satisfied with the freshly built normalized version, starts using dim_product_normalized instead of dim_product for future queries, or expects module 4 (SCD) to historize this version. Why it happens: dim_product_normalized is, in a real sense, "more correct" from a database-normalization standpoint — it's easy to assume "more correct" means "the one used from here on." How to spot it: if, in some exercise from a later module, you write JOIN dim_product_normalized instead of JOIN dim_product expecting the same behavior, you mixed up the two versions. How to fix it: dim_product — with category as a text column — remains this guide's canonical dimension starting from module 4. dim_product_normalized and dim_category exist only within this module, as the "normalized" half of a three-shape comparison.
Exercises
Exercise 1 — Verify each normalized product points to exactly one category. Using dim_product_normalized, write a query confirming no product_id has more than one category_id — an integrity check that should be trivially true given how the table was built, but worth confirming with evidence, not intuition.
See solution
print(con.sql("""
SELECT product_id, COUNT(DISTINCT category_id) AS distinct_categories
FROM dim_product_normalized
GROUP BY product_id
HAVING COUNT(DISTINCT category_id) > 1
"""))Expected output:
┌────────────┬─────────────────────┐
│ product_id │ distinct_categories │
│ varchar │ int64 │
└────────────┴─────────────────────┘
0 rows
Zero rows — no normalized product points to more than one category, confirming this lesson's normalization preserves a well-formed dimension's most basic property: every row of dim_product_normalized has a single category_id, with no ambiguity.
Exercise 2 — Count how many products each category has, through the normalized version. Using dim_product_normalized joined to dim_category, write a query that groups by category_name and counts how many products belong to each category.
See solution
print(con.sql("""
SELECT c.category_name, COUNT(*) AS product_count
FROM dim_product_normalized p
JOIN dim_category c ON p.category_id = c.category_id
GROUP BY c.category_name
ORDER BY c.category_name
"""))Expected output:
┌───────────────┬───────────────┐
│ category_name │ product_count │
│ varchar │ int64 │
├───────────────┼───────────────┤
│ beverages │ 2 │
│ electronics │ 1 │
│ snacks │ 1 │
└───────────────┴───────────────┘
beverages has two products (P001 and P003), while electronics and snacks have one each (P004 and P002). This query needs the JOIN against dim_category because dim_product_normalized, on its own, only knows the numeric category_id — not the readable category name. This is, precisely, the first tangible cost of having normalized: any question that needs the category's name, not just its identifier, requires an extra hop.
Exercise 3 — Explain, without code, what would happen if dim_category had a duplicated category_name value. Imagine that, due to a build error, dim_category ended up with two different rows — category_id = 1 and category_id = 4 — both with category_name = 'beverages'. In 2-3 sentences, explain what problem this would cause when doing JOIN dim_product_normalized p JOIN dim_category c ON p.category_id = c.category_id, and why this lesson's DISTINCT exists precisely to prevent it.
See solution
The JOIN itself wouldn't fail — each normalized product_id still points to a single valid category_id — so technically the result would still be correct row by row. The real problem would show up in any query that grouped or counted by category_name: products under category_id = 1 and those under a hypothetical category_id = 4, both with the same name "beverages", would show up as two separate groups in a GROUP BY category_name, instead of being summed together as one category — artificially inflating the number of distinct categories any analysis reports. This lesson's DISTINCT, when building dim_category from category's unique values, guarantees this situation never occurs: every category name exists with a single category_id, with no duplicates that could fragment a later analysis.
Summary and next step
In this lesson you normalized category out of dim_product, building your first real snowflake schema: dim_category (three rows, category_id/category_name) and dim_product_normalized (four rows, with category_id replacing the original text). You verified, with executed evidence, that the normalization lost no products and that joining back reconstructs exactly the same text you had before.
Before moving on you should be able to: explain from memory why dim_category has three rows and not four; write the ROW_NUMBER() OVER (ORDER BY ...) pattern over a DISTINCT subquery without looking at the example; and name the difference between dim_product (canonical, category as text) and dim_product_normalized (parallel, only for this comparison).
Lesson 3 takes this freshly normalized table and measures, with EXPLAIN, how much the extra JOIN hop you just introduced actually costs — not in theory, but in the real execution plan DuckDB generates for each query.
Resources
- Kimball Group — "Star Schema / OLAP Cube" — the source that formally defines a snowflake schema as the normalization of one or more dimensions of a star schema. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/star-schema-olap-cube. In English.
- DuckDB — window function documentation (
ROW_NUMBER), the same pattern you already used in module 2 for surrogate keys, applied here tocategory_id. duckdb.org/docs/current/sql/functions/window_functions. In English. - DuckDB — official
SELECT DISTINCTdocumentation, the clause that guaranteesdim_categoryhas one row per category, not one row per product. duckdb.org/docs/current/sql/query_syntax/select. In English. - Microsoft Learn — "Understand star schema and the importance for Power BI" — includes a section on why Power BI recommends avoiding snowflaking except in specific cases, useful as a practical counterpoint to this lesson. learn.microsoft.com/en-us/power-bi/guidance/star-schema. In English.