Explain a star schema and its advantages for analytics

What's really being asked

This question probes your understanding of database design trade-offs. Interviewers want to see if you know why different workloads require different data models. It's not just about defining a star schema; it's about explaining why its denormalized structure is a deliberate, performance-oriented choice for analytics (OLAP) in contrast to the write-optimized, highly normalized schemas (like 3NF) used for transactional systems (OLTP).

The full answer

A good answer covers four key points. First, define the components: a central fact table containing quantitative measures (e.g., sales amount, units sold) and foreign keys. Second, describe the dimension tables that radiate from the fact table like points of a star, containing descriptive attributes (e.g., product name, customer city, date details). Third, explain the primary benefit: query performance. By denormalizing data into wide dimension tables, you drastically reduce the number of joins needed for a query. This is critical for analytical queries that scan and aggregate millions or billions of rows. Fourth, contrast this with a 3NF schema, explaining that 3NF is optimized for writes (avoiding data redundancy and anomalies) but requires many complex joins, which are slow for reporting.

The mistakes people make

A major red flag is simply describing the "star" shape without explaining the performance implications. Many candidates say "it has fewer joins" but can't articulate why that matters for analytics workloads (e.g., simpler query plans, less I/O). Another mistake is confusing it with a snowflake schema, which is more normalized (dimension tables are broken down into sub-dimensions). Also, saying it "saves space" is generally incorrect; denormalization often increases storage footprint in exchange for query speed.

What usually comes next

Expect questions like "When would you use a snowflake schema instead?" (Answer: When dimension tables are very large and you need to save space, or to better represent complex hierarchies). Another is "What is a factless fact table?" (Answer: A table that records an event, with no numeric measures, just foreign keys to dimensions). Or, "How would you handle slowly changing dimensions (SCDs)?"

A concrete example

Imagine an e-commerce business. The fact table, fact_sales, would contain columns like order_date_key, customer_key, product_key, units_sold, and sale_amount. The dimension tables would be dim_customer (with customer_name, city, state), dim_product (with product_name, category, brand), and dim_date (with full_date, month, year, is_weekend). To get total sales by brand for a specific state, you only need to join fact_sales to dim_product and dim_customer. In a 3NF schema, you might need to join 5-7 tables (orders, order_items, products, categories, brands, customers, addresses) making the query much slower.