Snowflake Schema
Dimensions normalised into their own hierarchies. Fewer repeated values, one place to correct a taxonomy, more joins in every query — and an honest comparison of when that trade pays.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
When is it worth normalising a dimension into a hierarchy instead of flattening it into the dimension table?
Analysts writing joins by hand, BI tools inferring join paths, and the team that owns a taxonomy and has to correct it in one place rather than in eight denormalised copies.
The fact's grain is unchanged — normalising dimensions is a change to context, not to the unit of observation. Each dimension level becomes its own table with its own grain: one product, one subcategory, one category, one department.
Normalise everything, because normalisation is what a well-trained engineer does to a schema. Each attribute appears once, nothing can disagree with itself, and the model looks correct by the standards of operational design (Normalization: 1NF to BCNF).
A simple question now needs four joins to reach department from the fact, and analysts write them inconsistently or give up and use the wrong level (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
- A simple question now needs four joins to reach
departmentfrom the fact, and analysts write them inconsistently or give up and use the wrong level (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF). - The BI tool cannot infer the path from fact to department across three intermediate tables, so it either refuses the field or picks a route nobody checked (Star Schema).
- Each intermediate table is another place a duplicate key can appear, and a duplicate three levels up fans out every query that reaches through it (Grain: What Does One Row Represent?).
- Query plans get harder for the optimiser: more joins means more join-order decisions, and a bad one on a large fact is expensive (Cost-Based Optimization).
- The storage saved is negligible. Dimension tables are small and columnar compression already collapses a repeated category string to almost nothing, so the classic argument for normalising has largely evaporated (Dictionary, Run-Length, Delta and Bit Packing).
What is actually happening
- A snowflake schema takes a dimension's hierarchy and splits it into one table per level:
dim_productpoints atdim_subcategory, which points atdim_category, which points atdim_department. Drawn out, the dimension branches, hence the name. - The gain is single-place correction. A category renamed once is renamed in one row, and every product beneath it inherits the change. In a flattened dimension the same rename is an update across every product row that carried the string (Denormalization on Purpose).
- The second gain is genuine when hierarchies are shared across dimensions. If both products and suppliers reference the same geography hierarchy, normalising it means one place to maintain rather than two copies that will drift (Dimension Tables).
- The cost is joins, and the cost of joins here is more about humans and tools than about engines. Every extra hop is a place a query can take the wrong route, a place a BI tool has to be configured, and a place a duplicate key can multiply rows.
- The historic argument — that repeated strings waste storage — is much weaker on columnar storage than it was on row storage, because dictionary encoding stores each distinct category once per column chunk and references it by a small integer. Normalising to save those bytes is optimising something the format already did (Why Analytical Data Compresses).
- The practical middle ground most platforms land on is a flattened dimension built from normalised sources: normalise upstream in staging where the taxonomy is maintained, flatten in the serving model where it is queried (Model Layering).
The same dimension, branched
The snowflake is the star with its dimensions unpacked. dim_product no longer carries category and department as strings; it carries a key to dim_subcategory, which carries a key to dim_category, which carries a key to dim_department.
Look at what the diagram now requires of a query that groups revenue by department: four joins from the fact, in a specific order, through three tables that contain nothing anyone wants to see. Each one is a place to take a wrong turn and a place a duplicate key can multiply rows.
And look at what it buys: renaming a department is one UPDATE to one row, and every product beneath it is renamed. In the flattened version the same rename touches every product row carrying that string, and if one row is missed the taxonomy has silently forked.
The trade, stated honestly
Most writing on this topic picks a side. The honest position is that the trade genuinely depends on three things: who maintains the hierarchy, how often it changes, and which tool generates the queries.
The storage column is the one that has moved. When the pattern was named, dimensions were stored row-wise and a repeated department string genuinely cost bytes on every row. Columnar formats with dictionary encoding store each distinct value once per chunk, so the same repetition now costs approximately a small integer per row (Dictionary, Run-Length, Delta and Bit Packing).
What has not moved is the human column. A four-join path to a department is four chances to write the query differently, and the cost of that lands on every analyst, every day, forever.
| Aspect | Flat dimension (star) | Normalised hierarchy (snowflake) |
|---|---|---|
| Joins to reach the top level | One, always | One per level — four is common for a product taxonomy |
| Storage in the dimension | Values repeated per row; dictionary encoding makes this nearly free on columnar storage | Each value stored once — a real saving on row storage, a marginal one on columnar |
| Correcting a hierarchy value | Update every row that carries it, or rebuild the dimension | Update one row; every descendant inherits it immediately |
| Retroactivity of a correction | Whatever you built — can be versioned per path | Type 1 by default: the change applies to all history unless every level is versioned |
| Places a duplicate key can fan out | One: the dimension key | One per level, and the higher the level the larger the blast radius |
| Ragged hierarchies | A null column, visible and easy to filter | A missing parent row, which an inner join deletes silently |
| BI tool compatibility | Universally understood; one hop from fact to dimension | Depends entirely on the tool; some cannot express it at all |
| Adding a new level | A new column — invisible to existing queries | A new table and a new join in every consumer query |
| Who it suits | Analysts and generated SQL — the readers | The team that owns and maintains the taxonomy — the writers |
Choosing, per hierarchy rather than per platform
The decision is not "star or snowflake" for the whole warehouse. It is per hierarchy, and most real models are mixed: a flat date dimension, a flat customer dimension, and a product taxonomy that is normalised upstream and flattened for serving.
The question that settles it fastest is who changes the hierarchy and how often. A taxonomy owned by a merchandising team that reorganises quarterly is a maintenance surface, and maintenance surfaces want normalising. A geography hierarchy that has not changed in five years is not a maintenance surface, and normalising it buys nothing.
The second question is which tool writes the queries. If the BI layer cannot traverse three levels, the decision is made for you regardless of what the modelling literature says.
Who maintains this hierarchy, how often does it change, and what generates the queries against it?
when The hierarchy is stable, owned by nobody in particular, and queried constantly. Most date, geography and customer attributes.
cost A correction becomes a dimension rebuild rather than a one-row update, and the same value is stored per row — which columnar encoding makes nearly free. Buys one-hop queries and universal tool support (Star Schema).
when A team actively maintains the taxonomy, it changes often, and analysts still need one-hop access.
cost One more model in the DAG and a schedule dependency between the hierarchy and the view. Buys single-place maintenance and a simple consumer interface — the usual answer (Model Layering).
when The hierarchy is shared by several dimensions, the BI tool traverses relationships well, and analysts are comfortable with multi-hop joins.
cost Every consumer query carries the hops, every level is a fan-out risk, and adding a level is a breaking change for consumers. Buys the least duplication.
when Re-parenting happens and historical reports must not change when it does — a product moving between categories mid-year.
cost SCD2 at every level you version, which multiplies rows and complicates every join with a validity predicate. Buys reports that do not change retroactively (SCD Type 2 in Practice).
when The relationship is genuinely many-to-many — a product in several categories, an account with several owners — which neither shape can express.
cost Deliberate, documented fan-out with allocation weights, and every query through it needs care. Buys the ability to model reality instead of a simplification of it (Grain: What Does One Row Represent?).
How to build it
Most important first.
- Default to flat dimensions in the serving layer. Flattening is the shape that BI tools understand and analysts write correctly, and the storage argument against it is largely obsolete (Star Schema).
- Normalise where the hierarchy is genuinely shared between several dimensions or maintained by a team that needs one authoritative place to change it — geography and product taxonomy are the recurring real cases.
- Where you normalise, still expose a flattened view for consumers. The normalised tables are the maintenance surface; the flat view is the interface (Model Layering).
- Test uniqueness at every level of the hierarchy, not just the leaf. A duplicate at the category level fans out every product beneath it (Grain: What Does One Row Represent?).
- If a hierarchy is ragged — some products have no subcategory, some categories have no department — decide explicitly what fills the gap, because outer joins through a hierarchy produce nulls that consumers read as zeros (Nullability & Defaults).
- Never snowflake a dimension purely because normalisation is a virtue elsewhere. The serving layer is optimised for reading, and normalisation optimises for writing (Operational vs Analytical Models).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Normalising guarantees a single stored copy of each hierarchy value, which guarantees it cannot disagree with itself. It does not guarantee the value is right.
- It guarantees grain preservation only if every level has a unique key. More levels means more places that guarantee can fail (Database Constraints).
- It guarantees nothing about query correctness through the hierarchy: an inner join through a ragged level silently drops rows that have no parent, and that looks like missing data rather than an error (Missing Rows).
- Flattening guarantees the opposite trade: one hop, no path ambiguity, and no protection against the same value being spelled two ways in two rows.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Test uniqueness at every hierarchy level. The blast radius of a duplicate grows with height: a duplicate department multiplies every fact row under every product in that department (Data Tests).
- Test hierarchy completeness: every child has exactly one parent, and no orphans exist. This is the check that catches ragged hierarchies before an inner join silently drops their rows.
- Where a flattened dimension is derived from normalised sources, test that the flattening is lossless — distinct hierarchy paths in the source equal distinct paths in the flattened table (Reconciliation).
- What these miss: a taxonomy that is internally consistent and wrong. Every key unique, every parent present, and
Peripheralsstill contains items the business considersAccessories. No structural test reaches meaning (Semantic Changes).
- A hierarchy correction propagates instantly in a normalised model — one row updated, every descendant sees it. In a flattened model it propagates on the next dimension rebuild, which is a real freshness difference for taxonomy changes (Data Marts).
- Flattened dimensions add a build step, so the serving model is always at least one run behind the normalised source it derives from.
- Neither shape affects fact freshness, which is governed by the fact load and not by dimension structure.
- Adding a level to a hierarchy is a schema change in a snowflake — a new table, new joins in every consumer query. In a flattened dimension it is a new column, which existing queries ignore (Schema Evolution).
- Re-parenting an item — moving a product to a different category — is instantly retroactive in a normalised model and applies to all history whether you wanted it to or not. Deciding whether a taxonomy change should apply retroactively is an SCD question that normalising quietly answers for you, with Type 1 (Slowly Changing Dimensions).
- A flattened dimension can version the whole path, so a report can say what a product's category was at the time. A normalised hierarchy versioned at every level is possible and considerably more work (SCD Type 2 in Practice).
- Both shapes rebuild from the same sources. The snowflake rebuild has an ordering constraint — parents before children — that the flat one does not (Task Dependencies).
- A wrong flattening is repaired by rebuilding the dimension and the facts that took keys from it, which is why deterministic keys matter here too (Surrogate Keys).
- A retroactive taxonomy change that should not have been retroactive cannot be undone from the model alone — you need the previous hierarchy state, which normalising did not keep (Keeping Raw History: The Recovery Position and the Liability).
What can go wrong
- A duplicate key at a high hierarchy level, multiplying every fact beneath it (Grain: What Does One Row Represent?).
- An inner join through a ragged hierarchy silently dropping items with no parent (Missing Rows).
- A BI tool guessing a join path across three levels and getting it wrong, with no SQL anyone reviews (Dashboards Built Around Questions).
- A taxonomy correction applied retroactively to all history, changing signed-off reports because normalising made Type 1 the default (Slowly Changing Dimensions).
- The mitigation failing: a flattened view over normalised tables that is rebuilt on a different schedule from the hierarchy, so the two disagree for part of every day.
- "Snowflaking saves significant storage." On columnar storage with dictionary encoding it saves very little, and dimensions are the smallest tables in the warehouse regardless (Why Analytical Data Compresses).
- "Normalisation is always better practice." Normalisation is better practice for a write path with many concurrent writers. The serving layer has one writer and many readers, which inverts the argument (Operational vs Analytical Models).
- "Snowflake schema is about Snowflake the warehouse." Unrelated. The name predates the product by decades and describes the branching shape of the diagram.
- "More joins means slower, so flatten everything." Join count is close to free while dimensions broadcast. The reasons to flatten are about humans and tools, and they are good reasons; the performance argument is mostly folklore (Star Schema).
Operating it
- Row counts and distinct keys per hierarchy level per load. Duplicates at any level are the failure that matters (Pipeline Metrics).
- Count of items with no parent at each level, tracked over time — the raggedness signal (The Data Quality Dashboard).
- Query logs showing which hierarchy levels are actually grouped by. Levels nobody uses are maintenance with no consumer (Data Discovery).
- At 10x facts, dimension shape is irrelevant to cost — the fact scan dominates either way (Scan Cost).
- At 100x, or with versioning, individual hierarchy levels can outgrow broadcast independently, so a snowflake has several places where the join strategy can flip rather than one.
- More consumers argues for flattening: every additional analyst and every additional BI model multiplies the cost of an ambiguous join path.
- Storage saved by normalising a dimension is negligible on columnar storage, because dictionary encoding already stores each distinct value once per column chunk (Dictionary, Run-Length, Delta and Bit Packing).
- Query cost added by extra joins is small while every level is broadcastable, and becomes real when a level is not (Broadcast Joins).
- The dominant cost of snowflaking is human: longer queries, more configuration in BI tools, more onboarding time, and more places for a wrong join path (Data Platform Anti-Patterns).
- Normalising buys one authoritative place to maintain a hierarchy and costs a join per level in every query, plus a place per level where a duplicate can fan out. That trade is good when a team maintains the taxonomy and bad when analysts query it constantly.
- Flattening buys query simplicity and tool compatibility and costs a rebuild to propagate a hierarchy change, plus repeated values that could in principle diverge.
- The hybrid — normalised in staging, flat in serving — buys both and costs one more model in the DAG. It is the usual answer and it is not free.
Modeling lab — one grain, ten questions
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Business question | At this grain | Why |
|---|---|---|
What was revenue by country last month? Wants: One order, or one order line — either works, provided the measure is additive at that grain and is not summed twice. | answered | The measure is additive at this grain and each order is counted once. |
What is the average order value? Wants: One order. An average over lines answers a different question entirely. | answered | The denominator is orders, which is exactly what one row is. |
What was revenue by customer country at the time of each order? Wants: One order, joined to the version of the customer that was current when the order was placed. | WRONG no history | With a Type 1 dimension every historical order is attributed to the customer's current country. A customer moving from Poland to Germany silently rewrites last year's regional reports, and last month's report no longer reproduces. |
What is net revenue after refunds? Wants: One order, with refunds either netted into the measure or held as a separate signed fact at the same grain. | answered | Refunds net into the measure, or sit beside it as a signed fact at the same grain. |
What was the total account balance on each day last year? Wants: One account-day. A balance is a state, not an event, and cannot be reconstructed by summing transactions unless every transaction since account opening is retained. | WRONG | Summing transactions per day gives the daily change in balance, not the balance. The chart has the right shape and the wrong y-axis. |
What is month-three retention by signup cohort? Wants: One user-month of activity, joined to the user's signup month. | WRONG | Users who were active but did not buy are invisible, so retention is understated by exactly the non-buyers. |
What share of sessions ended in a purchase? Wants: One session — which requires a session window over events, because no source system emits a session. | unanswerable | No source system emits a session. Without a session window over events there is no denominator to divide by. |
Which products are most often bought together? Wants: One order line, with the order key retained so lines can be grouped back into baskets. | unanswerable | The most instructive failure in this lab: the model is not wrong, it is at the wrong resolution, and no query can recover what was aggregated away. |
What was yesterday's revenue, asked at 06:00 this morning? Wants: One order, in a period that is not yet closed. | with care | The grain is right and the period is not closed. Orders that happened yesterday and arrive later today are still missing at 06:00. |
What was global revenue, across markets that bill in different currencies? Wants: One order, with both the transaction amount and the converted amount stored, plus the rate and the date the rate applied. | WRONG no history | Converting at query time with today's rate makes every historical report change daily. Converting once with no record of the rate makes the number unreproducible. Both pass every type check there is. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe trade — fewer stored copies against more joins and more paths to get wrong — is structural and holds anywhere. What has changed over time is the weight of the storage side, which columnar encoding has made much smaller than it was when the pattern was named.
- FORMAT-SPECIFICDictionary encoding in columnar formats stores each distinct value once per column chunk and references it by a small integer, so a repeated category string costs far less than the same repetition in a row-oriented or text format — which is precisely the storage argument snowflaking was invented to address.
- TOOL-SPECIFICSome BI tools require an explicit semantic model in which each dimension sits one hop from the fact and simply cannot express a three-level hierarchy; others traverse declared relationships happily. The same schema is therefore usable in one tool and unusable in another, which is a stronger constraint than any performance consideration here.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns how a taxonomy change is reviewed and released, which is the difference between a re-parenting that is announced and one that silently restates last quarter.