Star vs. Snowflake Schemas: Trade-offs

What's really being asked

This question assesses your practical knowledge of dimensional modeling for data warehousing. The interviewer wants to see if you understand the fundamental trade-off between query performance (fewer joins) and data normalization (storage efficiency, data integrity). It's a test of your ability to choose the right tool for the job based on specific business and technical requirements.

The full answer

A solid answer will cover four key points in order. First, define the star schema: a central fact table (e.g., sales transactions) connected to a single layer of denormalized dimension tables (e.g., product, time, customer). Second, define the snowflake schema: a star schema where dimension tables are normalized into multiple, related tables (e.g., the product dimension branches into product_category and product_brand tables). Third, articulate the core trade-off: star schemas prioritize query performance with fewer, simpler joins, at the cost of increased storage and data redundancy. Snowflake schemas prioritize storage efficiency and data integrity by normalizing data, but this requires more complex queries with more joins, which can be slower. Fourth, state the common industry practice: start with a star schema for its simplicity and performance, and only snowflake a dimension if there's a compelling reason, like a massive, high-cardinality dimension or strict data governance requirements.

The mistakes people make

A red flag is describing the schemas without explaining the 'why'—the trade-offs that drive the design choice. Another common mistake is stating that one is universally 'better' than the other; the correct answer is always 'it depends on the use case.' Candidates also get flagged for being unable to explain how normalization in a snowflake schema impacts query complexity (more joins) and performance (slower reads, faster writes/updates). Simply saying 'snowflake saves space' without quantifying it or explaining the mechanism (normalization) is a weak answer.

What usually comes next

Expect follow-up questions like: 'When have you personally chosen one over the other, and what was the outcome?', 'How would you handle a slowly changing dimension (SCD) in a star schema?', or 'Describe a business scenario where a snowflake schema would be the clearly superior choice.'

A concrete example

Imagine an e-commerce data warehouse. A star schema would have a sales_facts table. It would join directly to a dim_product table that has columns like product_id, product_name, brand_name, and category_name all in one table. This is denormalized. A snowflake schema would break dim_product into three tables: dim_product (product_id, name), dim_brand (brand_id, name), and dim_category (category_id, name), linked by foreign keys. Querying for sales by brand in the star schema is one join. In the snowflake, it's two joins (sales_facts -> dim_product -> dim_brand), which is more complex and likely slower.