What is the difference between a star schema and a snowflake schema? When would you choose one over the other?

Question

Accepted Answer

Star schema and snowflake schema are the two primary approaches to organizing tables in a dimensional data warehouse. They differ in how dimension tables are structured.

A star schema places a central fact table surrounded by denormalized dimension tables. Each dimension is a single flat table containing all its attributes. For example, a product dimension table contains the product name, category, subcategory, department, and brand all in one table. The schema looks like a star when diagrammed, with the fact table at the center and dimensions radiating outward.

A snowflake schema normalizes dimension tables into multiple related sub-tables. Instead of a single product dimension, you might have a product table linking to a separate category table, which links to a department table. The dimension branches outward like a snowflake pattern. The fact table remains the same; only the dimensions differ.

Star schema advantages include simpler queries with fewer joins, which makes it easier for analysts and BI tools to work with. Query performance is typically better because the engine joins the fact table directly to each dimension without traversing hierarchies. The schema is intuitive and maps naturally to how business users think about their data.

Snowflake schema advantages include reduced data redundancy in dimensions, which saves storage and ensures consistency. Updates to hierarchical data happen in one place rather than across a denormalized table. It works well when dimensions have deep hierarchies or when storage efficiency matters.

Choose star schema in most cases, especially when query performance and simplicity are priorities. It is the standard recommendation for business intelligence and reporting. Modern cloud warehouses have abundant storage, making the redundancy argument for snowflake less relevant.

Choose snowflake schema when dimension tables are very large and normalization significantly reduces storage. It also makes sense when dimension hierarchies change frequently and maintaining consistency in a denormalized table becomes burdensome. Some compliance requirements favor normalized structures for auditability.

In practice, many data warehouses use a hybrid approach, normalizing only the dimensions that benefit from it while keeping others denormalized.