A snowflake schema is like a star schema, 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_category
Star 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 OLTP 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."