Star schema vs snowflake schema: differences and trade-offs
Tests dimensional modeling: star schemas flatten dimensions for fast joins and simple queries, while snowflakes normalize them to cut redundancy at the cost of extra joins. Red flag: praising snowflake storage savings without admitting query overhead.
What's really being asked
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.
The full answer
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.
The mistakes people make
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.
What usually comes next
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?
A 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.
Interview question
In a retail data warehouse, why is a query for total sales by category usually slower when using a snowflake schema instead of a star schema?
- a.The star schema normalizes category into its own table, adding extra join steps
- b.The snowflake schema must traverse multiple dimension sub-tables to reach category, increasing join overheadCorrect
- c.The snowflake schema stores category names repeatedly in the product dimension, wasting I/O
- d.The snowflake schema replaces the central fact table with several smaller fact tables
Why? this is the answer
In a snowflake schema, category is stored in a separate table linked through subcategory and product, so the query must traverse multiple joins, increasing execution time. The distractor claiming the snowflake stores repeated category names confuses it with the denormalized star schema, which keeps all attributes in one wide dimension table.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #data-warehousing
- #dimensional-modeling
- #star-schema
- #snowflake-schema
- #database-design
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles