Describe star and snowflake schemas and their trade-offs

WHAT THIS TESTS: 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.

A GOOD ANSWER COVERS: 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.

COMMON WRONG ANSWERS: 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.

LIKELY FOLLOW-UPS: 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.

ONE 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.

Source: Wikipedia: Star schema