Star schema vs snowflake schema
The two classic warehouse shapes, when each one wins, and the vocabulary interviewers expect. Diagrams included, plus a free PDF.
Get the free PDF
One page, print-ready, free to share. No signup needed.
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
| star | snowflake | |
|---|---|---|
| dimensions | denormalized, flat | normalized, split |
| joins per query | fewer | more |
| storage | a bit more | a bit less |
| BI tool fit | excellent | clunkier |
| maintenance | simpler | stricter |
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
Frequently asked questions
- Which is faster, star or snowflake?
- For analytics reads, star is usually faster: dimensions are flat, so queries need fewer joins. Snowflake saves some storage by normalizing dimensions but pays for it with extra joins on nearly every query.
- Is a snowflake schema ever the right choice?
- Yes: when large dimension hierarchies are shared across many facts (a geography or product tree), or when strict consistency of dimension attributes matters more than query simplicity. It is the exception, not the default.
- What is the difference between a fact table and a dimension table?
- Fact tables hold events and measures: orders, sessions, payments, with numbers you aggregate. Dimension tables hold the context: customer, product, date, store, with attributes you filter and group by.
- Does Power BI prefer a star schema?
- Yes, explicitly. Microsoft's own guidance recommends star schemas for Power BI models: relationships stay simple, DAX measures behave predictably, and performance is better than with normalized snowflake models.
Get the free PDF
One page, print-ready, free to share. No signup needed.