DOCS
← Back to Glossary
Snowflake schema
A snowflake schema is a dimensional model where dimension tables are normalized into multiple related sub-tables instead of one flat table, reducing redundancy at the cost of extra joins.
Overview
A snowflake schema extends the star schema by normalizing dimension tables. Instead of one flat dim_product table that repeats category_name and department_name on every row, a snowflake schema splits it into dim_product, dim_category, and dim_department, each joined by a key. Visualized, the dimensions branch outward into further sub-dimensions, resembling a snowflake rather than a single-hop star.
The motivation is the same one behind normalization generally: eliminate repeated text values, shrink storage, and guarantee that an attribute like a category name is updated in exactly one place. The cost is query complexity — answering "revenue by department" now requires joining fact → product → category → department instead of a single join to a flat dimension.
Related terms
Star schema →
A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.
Dimension table →A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Conformed dimension →A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.
Fact constellation schema →A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.
Snowflake (data cloud) →Snowflake is a cloud data platform that separates storage, compute, and services into independent layers, letting you scale query power without moving the underlying data.
Dimensional modeling →Dimensional modeling is a data warehouse design methodology, developed by Ralph Kimball, that organizes data into fact tables (measures) and dimension tables (descriptive context) optimized for business-user querying.
FAQS
Not generally. Snowflaking reduces redundancy in dimension tables but adds joins that make queries more complex and often slower. Most warehouses default to star schemas and only normalize a dimension when it's large, hierarchical, or updated often enough that the redundancy becomes a real problem.
