TL;DR
A snowflake schema is a dimensional-modeling pattern where dimension tables are normalised into multiple sub-tables — producing a more complex shape than a <a href="/glossary/star-schema" class="text-brand-600 underline decoration-brand-200 underline-offset-2 hover:text-brand-700">star schema</a> but with stricter normalisation. Snowflake schemas save storage at the cost of query complexity and performance. They were more common when storage was expensive; modern columnar warehouses have made the storage savings nearly irrelevant, leaving star schemas as the dominant default.
What is a snowflake schema?
A snowflake schema is a dimensional-modeling pattern where dimension tables are normalised into multiple related sub-tables — producing a structure that resembles a snowflake when diagrammed (a central fact table surrounded by dimensions, each of which has its own normalised hierarchy of sub-tables).
It is the normalised alternative to the star schema (where dimensions are kept denormalised). The trade-off is storage savings vs query complexity and performance.
Anatomy of a snowflake schema
Star schema (denormalised):
Product Dim contains:
product_id, name, category, sub_category, brand, brand_country
Snowflake schema (normalised):
Product Dim -> product_id, name, sub_category_id, brand_id
Sub-Category -> sub_category_id, name, category_id
Category -> category_id, name
Brand -> brand_id, name, country_id
Country -> country_id, name
Same data; the snowflake version uses 5 tables where the star uses 1. Snowflake vs star — when each makes sense
| Use case | Star or snowflake? |
|---|---|
| Standard BI / analytics | Star |
| Storage-constrained legacy environments | Snowflake |
| Very large hierarchical dimensions (5+ levels) | Snowflake (selectively) |
| Strict 3NF normalisation requirements | Snowflake |
| Modern columnar warehouse (Snowflake, BigQuery) | Star |
| Frequent dimension-attribute updates | Snowflake (less duplication) |
Why star schemas usually win in 2025
Snowflake schemas remain reasonable for very large hierarchical dimensions (geographic hierarchies with many levels, deeply-nested organisational structures) and for environments where strict normalisation is required by policy. For most modern analytical workloads, star is the default.
- Storage is cheap: modern columnar warehouses compress repeated values nearly to zero
- Join performance is consistent: warehouse query optimisers handle large star-schema joins predictably; multi-join snowflake queries less so
- BI tooling assumes star: Looker, Tableau, Power BI all optimise for star-schema patterns
- Analyst productivity: simpler joins mean faster query authoring and fewer errors
Common pitfalls
- 1. Snowflaking by reflex. Engineers trained on transactional 3NF databases sometimes default to snowflake design for analytical schemas. The norms differ — dimensional analytics intentionally violates 3NF for query simplicity.
- 2. Mixing star and snowflake inconsistently. Some dimensions snowflaked, others not — produces unpredictable query patterns. Pick one default per warehouse and apply consistently.
- 3. Performance issues from over-snowflaking. 5-table snowflake-dimension queries can run 5–20× slower than equivalent star queries, especially on high-concurrency BI workloads.
Related concepts
Star schema is the denormalised alternative and the modern default. Dimensional modeling is the broader discipline. Fact tables are the same in both patterns; dimension tables differ.
At a glance
- Category
- Business Intelligence
- Related
- 5 terms
Frequently asked questions
Should I use a snowflake schema?
Default to star. Snowflake schemas only justify the complexity in specific cases: very large hierarchical dimensions (geographic with 5+ levels), strict normalisation policy requirements, or storage-constrained legacy environments. For modern columnar warehouses, star wins on every dimension that matters operationally.
What's the difference between a snowflake schema and the Snowflake data warehouse?
Coincidental naming. Snowflake schema is the normalised dimensional-modeling pattern (named after the visual shape). Snowflake (the company / data warehouse product) is named after the same visual concept but is unrelated to whether you use star or snowflake schemas inside it. You can build either pattern in any warehouse.
Can you mix star and snowflake patterns?
Yes — sometimes called 'galaxy schema' or 'fact constellation' when fact tables share some star and some snowflake dimensions. Generally inadvisable as a default; pick one pattern and apply consistently. Mixed patterns produce unpredictable query authoring.
Sources
- Kimball Group dimensional-modeling resources
- The Data Warehouse Toolkit (Kimball, 3rd ed)
- Modern Data Stack benchmark reports (2024–25)
Fairview is an operating intelligence platform that reads both star-schema and snowflake-schema models — surfacing operating views without dictating which dimensional pattern teams use underneath. Start your free trial →
Siddharth Gangal is the founder of Fairview. He built the schema-agnostic ingestion path after watching teams hold off from using new analytics tools because their warehouse was modelled as a snowflake and the tool assumed star — preserving years of careful dimensional work shouldn't require schema rebuilds for every new consumer.
See it in Fairview
Track Snowflake Schema automatically.
14-day free trial. No credit card. First data source connected in 5 minutes.