Snowflake Schemas
When dims get normalized back
A snowflake schema normalizes dimensions into sub-tables. It's a star's cousin that buys space at the price of joins — usually the star is the better trade for BI.
What you will learn
- Recognize and draw a snowflake schema
- Articulate the join-vs-space trade
- Decide star vs snowflake per scenario
What is this?
A snowflake schema is a star whose dimensions are normalized into multiple related tables — dim_product splits into dim_product (linking to dim_brand) or dim_subcategory. It saves a bit of storage and mirrors source normalcy, but every level adds a join. Most BI teams pick stars; snowflakes appear when multiple teams share authored dims.
Why do you need it?
- Storage savings are real but marginal versus their join cost.
- Deep snowflakes slow down filtering and confuse business users.
- Some platforms/requirements (shared dim governance) justify snowflakes.
- You must be able to argue the trade explicitly in reviews.
Mental model
Filing folders inside labeled drawers
A snowflake dimension
Folders (category level) inside drawers (department) inside cabinets (region) — you still find the file, but each drawer-folder level is one more step. A star just writes the folder name on the file. Snowflake saves shelf space; star saves reaching time.
How it works
- Central fact connects to dim tables just like a star.
- Each dim may fan out to sub-dims (product → category → department).
- Filters must traverse each sub-join; each adds a lookup cost.
- Storage drops as duplicated attributes are removed.
- BI tools can hide the graph, but the query engine still joins it.
Star vs snowflake on the same business.
Examples
Reading a snowflake's join chain
star : fact_orders ⋈ dim_product(category, brand, price) -- 1 joinsnowflake: fact_orders ⋈ dim_product ⋈ dim_brand (brand_id) -- filters descend: brand → product → fact Query cost: star = 1 probe row-set; snowflake = N. Practical rule: Dims that change rarely + shared across teams = snowflake OK. Dims with a few extra attributes = fold them into the star.The trade is join-depth vs storage. BI readability almost always favors the star.
In production
- A shared 'customer' dim owned by Finance snowflakes to a firmographic sub-dim teams reuse.
- A product hierarchy with deep taxonomy snowflakes where heavy governance exists.
- All other cases: stars, because self-service users shape by the single flat dim.
Best practices
- Snowflake only when the dim genuinely needs shared sub-model ownership.
- Keep snowflakes shallow (1 level max) — depth is where joins multiply.
- Document which dims are normalized and why.
- Default to star; escalate to snowflake with a written rationale.
Common mistakes
- Deep multi-level snowflakes 'to be clean' — query drag with no user payoff.
- Snowflaking on rarely-filtered attributes.
- Believing snowflakes are 'more correct' — they're just different trade.
- Mixing star and snowflake on one fact without a plan (surprise join costs).
Debugging tips
- 'Why is filtering slow?' → trace the join chain depth on dims.
- Shapes users complain about → they're navigating sub-dims; flatten the hot ones.
- Grain confusion → snowflaked keys occasionally mis-join; verify uniqueness per level.
Interview checkpoint
Hands-on lab
- Build the same data as star and snowflake
- Compare join-depth query plans
- Quantify the extra-join cost
Measure Star vs Snowflake Query Cost
40 min · intermediate
Quick quiz
A snowflake schema differs from a star because its dimensions are:
Snowflakes always beat stars for query performance.