Why is a star schema the default choice over a snowflake for analyst-facing marts?

## The argument the snowflake makes, and why it is weak here Snowflaking a dimension normalizes its hierarchy into one table per level so each label — a brand name, a category name, a department name — is stored exactly once. That is a genuine saving of bytes and a genuine single point of update for the label text. The question is whether either matters at warehouse scale. ### The storage math Do the arithmetic out loud in an interview; it is the fastest way to settle the argument. Take a product dimension of 500,000 rows carrying about 200 bytes of hierarchy labels each: roughly 100 MB, and considerably less once the storage layer compresses columns of repeated text. Now take the fact table it describes: ten billion sales rows of maybe 60 bytes each, hundreds of gigabytes to terabytes. Normalizing the dimension might reclaim tens of megabytes from a multi-terabyte warehouse. Nobody's budget moves. The picture only changes for genuinely enormous dimensions — hundreds of millions of rows with a wide block of repeated attributes — which is exactly the case where snowflaking is defensible. ## The costs a snowflake pushes onto everyone else ### Query authorship In a star, a question about any product attribute is answered by one join. In a snowflake, the analyst must first know which of four tables holds `department_name`, then write the chain of joins that reaches it. Multiply by every dimension, every dashboard and every ad-hoc query. The failure is not that the query is slower to type; it is that a hierarchy with several tables offers several plausible join paths, and picking the wrong one produces a query that runs cleanly and returns the wrong number. ```sql -- star: one join to any product attribute SELECT p.department_name, SUM(f.net_amount) FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key GROUP BY p.department_name; ``` ### Comprehension and discoverability A wide dimension is self-documenting: open `dim_product`, read the columns, and you know what you can slice by. A snowflaked dimension requires the reader to reconstruct the hierarchy from foreign keys before they know what is available. Onboarding a new analyst is measurably slower, and "I did not know that column existed" turns into duplicate, slightly-different metrics. ### Load complexity A normalized dimension must be loaded in dependency order — departments before categories before brands before products — and every level needs its own key assignment and its own tests. A single wide dimension is one load job and one grain-uniqueness test. When the dimension also tracks history, keeping several level tables versioned consistently is markedly more work than versioning one table. ### Predictability of query cost More tables in a query means more join operations for the optimizer to order. At design level the point is simply that a one-join star gives the engine less to get wrong and gives you a plan shape you can reason about without profiling. How a particular engine executes those joins is a separate subject; the design argument stands on join count alone. ## Why the star's duplication is safe The classic reason to normalize is that repeated data drifts: two writers update two copies of the same fact and the table starts contradicting itself. That risk comes from many concurrent writers. A warehouse dimension has exactly one writer — the load process — and read-only consumers. If the department name changes upstream, the loader rewrites every affected dimension row in one controlled operation. Redundancy that a single deterministic process owns is not the same hazard as redundancy that arbitrary transactions can touch, and conflating the two is the most common conceptual error in this discussion. ## The honest counterweight A star is the default, not a law. If one hierarchy level is genuinely shared and mastered upstream, if a dimension is enormous with a large repeated block, or if a level changes on a totally different cadence, a normalized sub-table can be the right answer. The common resolution is to keep normalized reference tables in the integration layer where the data is loaded and governed, and to publish a flattened star to consumers — the maintenance benefit without pushing joins onto analysts. ## What to say in an interview Lead with the asymmetry: the benefit is small and lands on storage, the costs are large and land on every query author and every load job. Then name the one condition that would change your mind. That structure demonstrates judgment rather than doctrine.