Describe star and snowflake schemas and their trade-offs
Dimensional modeling denormalization trade-offs.
Star schemas flatten dimensions for fast joins; snowflake schemas normalize hierarchies to reduce redundancy but add joins.
What's really being asked
Whether you can articulate the classic dimensional modeling trade-off between normalization and query performance. Interviewers want to see that you know star schemas are not just simpler but intentionally denormalized for analytics, while snowflake schemas push toward third normal form within dimensions to save space at the cost of join complexity. They also check if you understand when each pattern fits real warehouse workloads.
The full answer
Four things in order. First, structure: a star schema has one central fact table with foreign keys to surrounding dimension tables that are flat and denormalized, while a snowflake schema breaks those dimension tables into related sub-tables creating a hierarchical shape. Second, performance: star schemas minimize joins which speeds up aggregation and simplifies the query planner path; snowflake schemas require more joins to reconstruct dimension attributes which can slow down large scans. Third, maintenance: star schemas may duplicate dimensional data causing larger storage and more expensive updates when dimension attributes change; snowflake schemas reduce redundancy so updates propagate through smaller normalized tables. Fourth, decision criteria: choose star for most analytical data marts where query simplicity and speed dominate; choose snowflake when dimension tables are massive and highly redundant or when you need to enforce strict relational integrity across a dimension hierarchy.
The mistakes people make
Three red flags appear often. One, claiming snowflake is always more performant because it is more normalized; in analytics databases the extra joins usually hurt scan performance. Two, describing star schemas as fully normalized; they are explicitly denormalized relative to 3NF. Three, framing the choice purely as a storage versus speed binary without mentioning operational complexity like ETL maintenance or slowly changing dimension handling.
What usually comes next
The interviewer may ask how you would handle a slowly changing dimension in each schema, or when you would pivot from snowflake back to star. They might ask about the impact on columnar storage engines where wide denormalized dimension tables are actually cheap to scan. Another common pivot is asking how bridge tables or outrigger tables fit into the spectrum between pure star and pure snowflake.
A concrete example
Imagine a retail data warehouse with a product dimension. In a star schema the product dimension contains category name, subcategory name, brand name, and supplier name all in one wide table repeated for every SKU. In a snowflake schema you split category into a category table, subcategory into a subcategory table referencing category, brand into a brand table, and supplier into a supplier table, with the product dimension holding only foreign keys. A query for total sales by category in the star schema joins the fact table to one dimension table. The same query in the snowflake schema joins the fact table to product, then subcategory, then category, adding two extra joins for the same result.
Interview question
Which statement accurately captures a key architectural trade-off between star and snowflake schemas in a data warehouse?
- a.Star schemas denormalize dimensions into flat tables, accepting redundancy to reduce joins and speed up readsCorrect
- b.Star schemas reduce storage redundancy by normalizing dimension hierarchies into sub-tables
- c.Snowflake schemas minimize joins to improve aggregation query speed over star schemas
- d.Snowflake schemas are preferred primarily when query simplicity and fast scans matter most
Why? this is the answer
Star schemas deliberately flatten and denormalize dimensions, which increases storage redundancy but reduces the number of joins needed for aggregation queries. Option C is a common misconception: snowflake schemas actually add joins by normalizing dimensions, which usually slows down large analytical scans despite reducing redundancy.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #dimensional modeling
- #data warehousing
- #star schema
- #snowflake schema
- #analytics
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