The short answer
A snowflake schema is like a , but some dimensions are normalized into sub-dimensions. The diagram looks like a snowflake: branches off the arms.
Star: dim_product has category, subcategory on the same table
Snowflake:
fct_orders --> dim_product --> dim_subcategory --> dim_categoryStar vs snowflake
| Star | Snowflake | |
|---|---|---|
| Dimension shape | Wide, denormalized dims | Normalized dim hierarchies |
| Joins for BI | Fewer | More |
| Storage | Slightly more duplication | Less duplication in dims |
| Ease for analysts | Usually easier | More join paths |
When people snowflake
- Very large, shared hierarchies (geography: city → state → country)
- Strict 3NF habits from thinking brought into the warehouse
Modern default
Most analytics teams prefer stars (or stars with a few carefully snowflaked pieces) because BI tools and humans prefer fewer joins. Storage is cheap; confusing join graphs are expensive.
Interview tip
> "Snowflake normalizes dimensions further than a star. I default to star for BI marts unless a hierarchy is huge and reused everywhere."