---
title: "Snowflake schema"
description: "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."
canonical: "https://motherduck.com/glossary/snowflake-schema/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Information schema | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/information-schema/"
  - title: "Ingest Snowflake Tables into MotherDuck From a Flight | MotherDuck Docs"
    url: "https://motherduck.com/docs/cookbook/flight-snowflake-ingest/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
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.