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.
Example
Copy code
CREATE TABLE dim_department (
department_key INTEGER PRIMARY KEY,
department_name VARCHAR
);
CREATE TABLE dim_category (
category_key INTEGER PRIMARY KEY,
category_name VARCHAR,
department_key INTEGER REFERENCES dim_department(department_key)
);
CREATE TABLE dim_product (
product_key INTEGER PRIMARY KEY,
product_name VARCHAR,
category_key INTEGER REFERENCES dim_category(category_key)
);
SELECT d.department_name, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product p USING (product_key)
JOIN dim_category c USING (category_key)
JOIN dim_department d USING (department_key)
GROUP BY ALL;
When to choose it
Kimball's guidance, and the practical consensus in most warehouses, favors star schemas for the fact-to-dimension layer because storage is cheap and query simplicity matters more than eliminating dimension redundancy. Snowflaking still shows up for genuinely hierarchical, high-cardinality, or frequently-updated dimensions (large product catalogs, organizational hierarchies) where normalization meaningfully reduces update anomalies and storage.
DuckDB angle
DuckDB's optimizer reorders and pushes down filters across multi-table joins well, so snowflaked dimension chains aren't a performance trap the way they can be on less sophisticated engines. Still, for a warehouse you're building and tuning yourself, flattening snowflaked dimensions into a single denormalized table with a CREATE TABLE ... AS SELECT is often simpler to maintain and just as fast to query.
Related terms
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.
