Star schema vs snowflake: which one should you use?

Both shapes put one fact table in the middle. The difference is what happens to the dimensions around it: a star keeps them flat, a snowflake keeps splitting them into sub-dimensions. That one choice drives joins, speed, and how pleasant the model is to work with.

The two shapes

Star: a fact table (orders) surrounded by flat, denormalized dimensions: customer, product, date, store. One join per dimension.

Snowflake: same center, but dimensions normalize further: product splits into category and supplier, geography splits into city, region, country. Joins multiply.

Side by side

When to pick which

Star when: you feed BI dashboards (fewer joins, faster reads), the team is small (simpler to reason about), and you run a cloud warehouse where storage is cheap anyway.

Snowflake when: big hierarchies are genuinely shared across facts, or dimension consistency must be updated in exactly one place. Storage-bound arguments are mostly a relic.

The trap: snowflaking to save storage

You save cents per GB on normalized dimensions and pay in extra joins on every dashboard query, forever. Normalization is for OLTP systems that write constantly; warehouses deliberately trade redundancy for read speed.

Default to star. Choose snowflake only with a reason you can say out loud.

The vocabulary

• fact table: events and measures: orders, sessions, payments
• dimension: context: customer, product, date, store
• grain: what one fact row means. Decide it first, everything follows.
• surrogate key: warehouse-generated id joining facts to dimensions