Data Warehousing
Intermediate 12 min read Data Warehousing

Star Schema vs Snowflake Schema

One layer of joins vs many — and the same answer

Star schemas denormalize dimensions for simple, fast queries; snowflake schemas normalize them into hierarchies. Modern warehouses and BI made stars the default, but snowflakes still win for highly-shared hierarchies.

star schemasnowflake schemadenormalizednormalizeddimension hierarchy
Prerequisites: Dimensional Modeling
Before we start

What you will learn

  • Draw each schema shape from the same business model
  • Explain the query-performance trade-off of each
  • Decide which fits the team's BI and warehouse
Introduction

What is this?

A star schema stores each dimension as one denormalized table around the fact; a snowflake schema normalizes dimensions into linked lookup tables (product → category → department). Modern warehouses and BI layers make the star the pragmatic default; snowflakes reduce redundancy when hierarchies are heavily shared.

Motivation

Why do you need it?

  • The choice changes join complexity and portability of your analyst SQL.
  • Renaming it 'snowflake' (vs the platform) is a common source of confusion — worth naming clearly.
  • BI tools and columnar warehouses have tilted the cost equation toward stars.
Think about it

Mental model

Imagine

A phone list with all details on one card (star) vs a card that points to other cards (snowflake)

Think of

Denormalized vs normalized dimensions

One-card lookup is faster per query but repeats some data; chaining cards saves space but adds hops and pages to flip.

Mechanics

How it works

  • Star: dimension holds category/region attributes inline; one join hop from fact to dimension.
  • Snowflake: dimension holds foreign keys to hierarchy tables (category separate from product).
  • Star benefits: simpler SQL, fewer joins, better for columnar compression of repeated values, BI-friendly.
  • Snowflake benefits: no repeated category text, single source of truth for a hierarchy, smaller footprint in row stores.
  • Modern guidance: prefer star; only snowflake where a hierarchy is genuinely shared and referenced.

Star vs snowflake shapes.

fact_sales
dim_product
dim_category
dim_region
BI query
Hands-on

Examples

The same report, two shapes

The same report, two shapes
-- STAR: one hop
SELECT sum(f.gross_amount) FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
WHERE p.category = 'Widgets';
 
-- SNOWFLAKE: two hops
SELECT sum(f.gross_amount) FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_category c ON p.category_key = c.category_key
WHERE c.name = 'Widgets';

Snowflake adds a hop but stores the category once; star repeats category on every product row.

Real world

In production

  • A columnar warehouse with dictionary compression happily repeats category across product rows — star wins.
  • An org with a shared region/currency hierarchy used by many facts snowflakes just that hierarchy.
  • BI tools with star-auto-join accelerate analysts without them hand-writing joins.
Guidelines

Best practices

  • Default to star; add normalized hierarchies only for genuinely shared lookups.
  • If you snowflake, keep the fact→dimension keyed path short so SQL stays readable.
  • Document which hierarchy you denormalized and why, so a later team doesn't 'normalize it back'.
Watch out

Common mistakes

  • Snowflaking everything because 'normalized is more correct' and paying join taxes on every report.
  • Star-denormalizing a volatile hierarchy that itself needs versioning, then blasting history.
  • Naming a star model 'snowflake' in docs and confusing everyone about the platform.
When things break

Debugging tips

  • A report that is slower with more hops often needs the star denormalized dimension, not more indexes.
  • Watch for category values duplicated inconsistently inside star rows — a classic denormalization drift.
  • Compare plan join count between an analyst's SQL and the intended star shape.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

In a star schema, how many joins separate a fact from its dimension attribute?

2

Snowflake schemas eliminate all join hops in analyst queries.

Go deeper

Official documentation

Kimball on star vs snowflake
docs
dbt best-practices on modeling
docs