Why is star schema preferred over 3NF for analytics?

WHAT THIS TESTS: The interviewer wants to see if you understand the architectural split between OLTP and OLAP systems. They care whether you can articulate why a model optimized for fast writes and update integrity is usually the wrong shape for read-heavy analytical workloads. Specifically they are checking if you know what fact and dimension tables represent, why join minimization matters at scale, and whether you can discuss the trade-offs without treating denormalization as a moral failing.

A GOOD ANSWER COVERS: First, define the star schema structure: a central fact table containing measurable business events like orders or clicks, surrounded by dimension tables that hold descriptive attributes like date, product, or customer. Second, explain the query pattern advantage: analytics workloads typically scan millions of rows and aggregate metrics, so requiring six or eight joins across normalized tables creates CPU and memory overhead that star schemas avoid by keeping dimensions in wider denormalized tables. Third, contrast with 3NF goals: third normal form eliminates update anomalies and redundancy for transactional inserts and updates, which is irrelevant when data is batch-loaded and immutable. Fourth, mention practical benefits: simpler SQL reduces analyst errors, query optimizers generate better plans with fewer joins, and modern columnar storage often compresses denormalized dimensions better than the overhead of repeated join keys. Fifth, acknowledge the cost: dimension tables may contain redundant data and require ETL to rebuild, but this is an acceptable trade for read performance.

COMMON WRONG ANSWERS: Claiming that star schemas are always smaller on disk is incorrect because denormalized text fields repeat. Arguing that 3NF is faster for reads shows confusion about the workload type. Saying star schemas are only for small data sets misses the point that they are the dominant pattern in petabyte-scale warehouses. Failing to mention fact versus dimension tables suggests you have only read definitions and never built one.

LIKELY FOLLOW-UPS: How would you handle a slowly changing dimension like a customer address? When would you break a star schema into a snowflake schema? How do you choose the grain of a fact table? What indexing or partitioning strategies matter most for the fact table versus dimensions?

ONE CONCRETE EXAMPLE: Imagine a retail data warehouse. A 3NF transactional schema might store orders linked to order lines, which link to products, which link to categories, which link to suppliers. An analyst asking for total revenue by product category in Q3 would join five tables. In a star schema, the fact table contains order line revenue and foreign keys, while a product dimension contains category and supplier attributes denormalized into one wide table. The analyst joins two tables, the database scans fewer blocks, and the query finishes in seconds instead of minutes.

Source: Wikipedia: Star schema