Describe star and snowflake schemas and their trade-offs.

What's really being asked

This question assesses your practical knowledge of dimensional modeling for data warehouses. The interviewer is evaluating your ability to articulate the fundamental trade-offs between query performance, storage costs, and data model complexity. They want to see that you can choose the right tool for the job based on business requirements, not just recite definitions.

The full answer

A great answer will cover four key points. First, define the star schema: a central fact table (e.g., sales transactions) connected to a single layer of dimension tables (e.g., date, product, customer). Emphasize that dimensions are denormalized. Second, define the snowflake schema: similar to a star, but dimension tables are normalized, creating a 'snowflake' shape with more tables (e.g., the product dimension might link to separate brand and category tables). Third, detail the trade-offs. Star schemas are simpler, have fewer joins, and are thus faster for most BI queries. Their denormalized nature leads to higher storage costs and potential data redundancy. Snowflake schemas reduce redundancy and save storage space through normalization, but this requires more complex, multi-table joins, which can slow down query performance. Fourth, provide a clear decision framework: use star for performance-critical, straightforward reporting; consider snowflake when storage is a major constraint or dimensions are very complex and large.

The mistakes people make

A red flag is treating normalization as the ultimate goal. In dimensional modeling, performance often trumps normalization. Stating that snowflake is 'better' because it's more normalized is a classic wrong answer. Another mistake is being unable to quantify the trade-offs. For example, not mentioning that the increased join complexity in a snowflake schema can significantly impact query latency for BI tools that are optimized for star schemas. Finally, confusing fact and dimension tables is a fundamental error.

What usually comes next

'When might you deliberately choose a snowflake schema despite the performance hit?' (Answer: Extremely large dimension tables, like a customer dimension with millions of rows and many attributes, where storage savings and data integrity from normalization are significant). 'How do modern columnar databases change this calculation?' (Answer: Columnar storage and compression can significantly reduce the storage penalty of star schemas, making them even more attractive). 'Describe a hybrid schema.'

A concrete example

Imagine an e-commerce data warehouse. A star schema would have a sales_facts table with foreign keys directly to dim_date, dim_product, and dim_store. The dim_product table would contain all product info: SKU, name, category, brand, etc. In a snowflake schema, the dim_product table might only have a brand_id, which then joins to a separate dim_brand table. This saves space if you have 10,000 products but only 100 brands, as you're not repeating brand names 10,000 times. However, getting the brand name for a sale now requires an extra join.