A snowflake schema is a data warehouse design pattern that extends the star schema by normalising dimension tables into multiple related sub-tables, creating a snowflake-shaped diagram with the central fact table connected to dimensions, which are themselves connected to sub-dimensions.
Why Snowflake Schema Matters
The snowflake schema is the normalised cousin of the star schema. Where star schemas keep dimensions flat (one big dim_product with all attributes), snowflake schemas split dimensions into normalised sub-tables (dim_product linked to dim_category linked to dim_department).
The trade-off: snowflake schemas save storage and reduce data redundancy, but require more joins for analytical queries and are harder for BI users to navigate. In modern cloud warehouses where storage is cheap and joins are fast, the star schema has become the dominant choice. Snowflake schemas persist in legacy systems and specific use cases where normalised dimensions add value.
How Snowflake Schema Works
A snowflake schema typically has:
- Central fact table: Same as a star schema — measurable events with foreign keys to dimensions.
- Dimension tables: Normalised into multiple related tables.
dim_productmight link todim_product_category, which links todim_product_department. - Multiple levels of joins: A query for “revenue by product department” requires joining fact → dim_product → dim_category → dim_department.
- Reduced redundancy: If a category name changes, it updates in one row in
dim_product_categoryinstead of every product row in a flat dimension.
Modern dbt + cloud warehouse stacks rarely use snowflake schemas. The performance and simplicity advantages of denormalised star schemas outweigh the storage savings, since cloud warehouse storage costs ~$23/TB/month.
Real-World Example
A retail company designs their warehouse with a snowflake schema for products. fct_sales joins to dim_product. dim_product has product_id, name, sku, category_id. dim_category has category_id, name, department_id. dim_department has department_id, name. To answer “revenue by department,” analysts join across all four tables. A star schema equivalent would denormalise everything into one dim_product with category_name and department_name as flat columns — fewer joins but redundant storage.
Common Snowflake Schema Tools and Platforms in 2026
2026 tools that support snowflake schemas:
dbt
Supports both star and snowflake schemas; most modern dbt projects choose star.
Looker
Handles snowflake schemas via LookML joins, but star schemas are simpler in LookML.
Tableau / Power BI
Both support snowflake schemas via their data modelling layer.
Snowflake / BigQuery / Databricks
All cloud warehouses can store snowflake schemas, though the cost-performance trade-off rarely justifies them in 2026.
Analytify
Reads either star or snowflake schemas for SaaS embedded analytics.
Frequently Asked Questions About Snowflake Schema
What is the difference between a star schema and a snowflake schema?
In a star schema, dimension tables are flat (denormalised). In a snowflake schema, dimensions are split into normalised sub-tables. Star schemas are simpler and faster; snowflake schemas save storage but require more joins.
When should I use a snowflake schema?
When dimensions have very large hierarchies that change frequently (large product catalogs with shifting categorisation), or when storage cost is a constraint. In 2026 cloud warehouses, this is rare.
Is the snowflake schema named after Snowflake the database?
No. The snowflake schema name predates the Snowflake company by decades. Both are named after the snowflake shape — but the database happens to be unrelated to the schema design pattern.
Why is the star schema more popular than snowflake schema in 2026?
Cloud warehouses make storage cheap and joins fast. The star schema’s simplicity (fewer joins, easier to understand) outweighs the storage savings of normalisation. Star schemas are easier for BI tools to consume and easier for AI agents to query.
Can I mix star and snowflake patterns in the same warehouse?
Yes. A common pattern is “mostly star, with snowflake for one or two dimensions that genuinely need normalisation.” Modern data modelling is pragmatic.
What is a galaxy schema?
A galaxy schema (also called fact constellation) has multiple fact tables sharing some dimensions. For example, fct_orders and fct_returns both joining to the same dim_customer and dim_product. Most enterprise warehouses are galaxy schemas at scale.