Star schema vs snowflake schema trade-offs?

WHAT THIS TESTS This probes whether you can weigh normalization trade-offs in dimensional modeling rather than memorizing one preferred shape.

A GOOD ANSWER COVERS Both schemas center on a fact table; they differ in how dimensions are structured. In a star schema each dimension is a single denormalized table holding all its attributes flat. In a snowflake schema dimensions are normalized into multiple related sub-tables, for example splitting a product dimension into product, category, and supplier tables linked by keys, which gives the branching snowflake shape. Query performance usually favors the star because reaching a dimension takes one join, producing simpler SQL and predictable plans; the snowflake requires traversing several joined tables per dimension, which can slow large analytical queries. Storage favors the snowflake because normalization removes redundant repeated attribute values, though dimension tables are typically small so the savings are modest. Maintenance can favor the snowflake when a shared attribute changes in one place, avoiding updating many duplicated rows, but it also means more tables and relationships to manage.

COMMON WRONG ANSWERS Saying snowflake is always better because normalization is good, ignoring that warehouses optimize for read speed over write integrity. Claiming the storage savings are large; dimensions are usually small relative to the fact table, so the gain is minor. Forgetting that modern columnar engines and query optimizers narrow the performance gap. Treating the choice as fixed rather than dependent on workload.

LIKELY FOLLOW-UPS When do dimension tables grow large enough that snowflaking matters? How do columnar storage and compression affect the trade-off? Can you mix the approaches in a galaxy or constellation schema? How do slowly changing dimensions interact with each?

ONE CONCRETE EXAMPLE A product dimension with millions of rows and a frequently changing category hierarchy might be snowflaked so category updates touch one small table. A modest, stable customer dimension stays a flat star table for fast single-join lookups in dashboards that group sales by customer region.