Star Schema vs Snowflake Schema: What's the Difference?
Both are dimensional modeling layouts for data warehouses. Star schema has a fact table surrounded by flat, denormalized dimension tables — one join hop. Snowflake schema normalizes dimensions into sub-tables — multiple join hops. For modern cloud warehouses, star schema wins: storage is cheap and simple joins are faster and easier to query.
Side-by-Side Comparison
Star Schema
- • Fact table + flat denormalized dimensions
- • One join from fact → dimension
- • Fast queries — minimal joins
- • Easy for analysts to understand
- • Slightly more storage (repeated values)
- • Default choice in modern analytics stacks
Snowflake Schema
- • Dimensions normalized into sub-tables
- • Multi-hop joins: fact → dim → sub-dim
- • Slower queries due to more joins
- • Harder for analysts to self-serve
- • Less storage (no repeated values)
- • Useful only when storage is a constraint
Mental Model
Think of a star schema as a reference card — all the context you need about a customer (name, country, segment, tier) is in one place, directly next to the transaction. Think of a snowflake schema as a filing cabinet — customer points to city, city points to country, country points to region. Technically more organized, but you have to open multiple drawers to answer a question. In modern warehouses where compute is cheap and analysts need self-service, the reference card wins.
Schema Diagrams
Star Schema
dim_date
│
dim_product──fct_orders──dim_customer
│
dim_channel
All dims: flat, denormalized
One join hop from factSnowflake Schema
fct_orders──dim_customer──dim_city
└──dim_country
──dim_product──dim_category
└──dim_brand
Dims normalized into sub-dims
Multiple join hops requiredWhen to Use Each
Choose Star Schema when:
- • Building on a cloud warehouse (BigQuery, Snowflake, Redshift)
- • Analysts will query dimensions directly
- • You use BI tools (Looker, Metabase, Tableau)
- • Self-serve analytics is a goal
- • Storage cost is not a concern (almost always)
Consider Snowflake Schema when:
- • Storage is genuinely expensive (rare)
- • Dimensions have deep, changing hierarchies
- • All queries go through a BI layer that handles joins
- • Dimension sub-tables need independent update cycles
- • Working in a legacy OLTP-style EDW
How They Look in dbt
-- Star schema: dim_customers is flat (denormalized)
SELECT
o.order_id,
o.revenue,
-- everything in one join:
c.country, -- already in dim_customers
c.customer_segment
FROM {{ ref('fct_orders') }} o
JOIN {{ ref('dim_customers') }} c USING (customer_id)
-- Snowflake schema: need 3 joins for the same result
SELECT
o.order_id,
o.revenue,
co.country_name, -- 3 hops away from fact
cs.segment_name
FROM fct_orders o
JOIN dim_customers c USING (customer_id)
JOIN dim_cities ci USING (city_id)
JOIN dim_countries co USING (country_id)
JOIN dim_segments cs USING (segment_id)
Feature Comparison
| Feature | Star Schema | Snowflake Schema |
|---|---|---|
| Join complexity | 1 hop | 2–4+ hops |
| Query speed | Faster | Slower |
| Storage | More (denormalized) | Less (normalized) |
| Analyst self-service | ✓ easy | ✗ complex |
| BI tool compatibility | ✓ excellent | ✓ with configuration |
| Hierarchy updates | Update dim table | Update sub-dim table |
| Recommended for cloud DW | ✓ yes | ✗ rarely |
Common Mistakes
Thinking "snowflake schema" means using Snowflake (the DB)
They share a name but are unrelated. Snowflake the database supports any schema design. Most dbt projects on Snowflake use star schema.
Normalizing dimensions to save storage in cloud warehouses
Columnar warehouses like BigQuery and Snowflake store repeated string values efficiently (dictionary encoding). The storage difference between star and snowflake schema is negligible. The query complexity cost is real.
Mixing normalized and denormalized dims in the same model
Inconsistency creates confusion. Pick one approach per mart layer and document it. Analysts should not have to guess whether to join one table or three to get a country name.
FAQ
- What is the difference between a star schema and a snowflake schema?
- Star schema: fact table + flat denormalized dims, one join hop. Snowflake schema: dims normalized into sub-dims, multi-hop joins. Star schema is faster and simpler; snowflake schema uses less storage but is harder to query.
- When should I use a snowflake schema?
- Rarely in cloud warehouses — only when storage cost is a genuine constraint or dimension hierarchies are very deep and update independently. Default to star schema.
- Does Snowflake (the database) require a snowflake schema?
- No. The name is a coincidence. Snowflake the cloud data warehouse works with any schema design and most teams use star schema.