Snowflake Schema
A snowflake schema normalizes dimension tables into sub-dimensions by referencing keys, trading fewer join hops in star for less redundancy and smaller dimension storage.
Definition
In a snowflake schema, dimension attributes are further normalized: a product dimension references brand and category dimensions, so descriptive columns live once. Compared to the star schema, queries traverse more joins, storage of repeated text shrinks, and wide attribute hierarchies are easier to maintain consistently.
Context and scope
Chosen in dimensional warehouses where dimension cardinality and attribute churn make fully denormalized stars expensive, and in engines whose join elimination and columnar storage make the extra joins cheap.
Common misconceptions
- Snowflake schemas are always more normalized and therefore better — extra joins cost analyst ergonomics and optimizer trust; star beats it for most reporting
- Snowflaking only affects storage — it changes join counts in every query touching the dimension
- Star and snowflake are mutually exclusive — hybrid designs snowflake only large or volatile hierarchies
Anti-patterns
- Snowflaking every attribute into lookup tables and forcing five joins for a simple daily rollup
- Ignoring join elimination support and shipping heavy joins to engines that materialize them anyway
- Mixing snowflaked hierarchies without documenting which level each BI metric joins to, producing inconsistent numbers
Related concepts
Related examples
Authoritative source
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
What it is
A star schema denormalizes each dimension into one wide table. Snowflaking decomposes those dimensions: geography becomes country, region, city rows referenced by key. The fact table stays central; the difference is how many tables a dimension spreads across.
How it works
Normalized dimensions remove repeated attribute values — a country name stored once for millions of customers. Updates to a shared attribute touch one row. The cost is joins: every query grouping by city within country must traverse the chain, and every BI tool must model those relationships correctly.
When to use it
Very high cardinality dimensions, attributes owned by different source systems, or engines like columnar warehouses where join costs are low and storage deduplication matters. Pure star remains the default recommendation for business-facing models.
Pitfalls
Analysts mis-join snowflaked hierarchies and produce silently wrong aggregates. Role-playing and bridge tables complicate the picture further. Snowflaking time or degenerate attributes adds joins with zero storage benefit.