Star schema vs snowflake schema: differences and trade-offs

WHAT THIS TESTS: Dimensional modeling literacy and the ability to weigh storage normalization against query performance in analytics systems. Interviewers want to see that you know star schemas are not just simpler but intentionally denormalized for read-heavy OLAP workloads, and that snowflake schemas represent a normalization choice with concrete costs.

A GOOD ANSWER COVERS: First, structure: a star schema has a central fact table surrounded by denormalized dimension tables, while a snowflake schema further normalizes those dimensions into sub-tables. Second, performance: star schemas require fewer joins, so aggregation queries are typically faster and query plans are simpler; snowflake schemas require traversing more joins, which adds latency. Third, redundancy: star schemas duplicate descriptive attributes across fact rows, consuming more storage; snowflake schemas reduce duplication by pushing attributes into separate linked tables. Fourth, maintenance: star schemas are easier for analysts to navigate and write queries against; snowflake schemas can reduce update anomalies when dimension attributes change, but at the cost of complexity.

COMMON WRONG ANSWERS: Confusing the two schemas or describing a snowflake as merely a star with more fact tables. Asserting that snowflake is superior because it eliminates redundancy without acknowledging the join penalty. Claiming that star schemas are always faster regardless of data volume or filter selectivity. Failing to mention that snowflake schemas can make sense when dimensions are large, highly structured, and reused across many fact tables.

LIKELY FOLLOW-UPS: When would you actually choose a snowflake over a star schema? How do modern columnar engines change the performance calculus? What is the impact on your BI tool or SQL generation layer? How do you handle slowly changing dimensions in each model?

ONE CONCRETE EXAMPLE: Imagine a retail data warehouse with a sales fact table. In a star schema, the product dimension contains category, subcategory, brand, and supplier all in one wide table. In a snowflake schema, the product dimension links to a subcategory table, which links to a category table, and supplier lives in its own table. A query for total sales by category in the star schema joins fact to one dimension table. The same query in the snowflake schema joins fact to product, then product to subcategory, then subcategory to category, tripling the join path and typically increasing execution time, though it avoids storing category names repeatedly in the product table.

Source: Wikipedia: Star schema