Star Schema vs Snowflake Schema: Which to Use and When

The difference between a star schema and a snowflake schema is smaller than the

debate around it suggests. Both are dimensional models — a central fact table

surrounded by dimensions — and the entire distinction is one decision: do you

keep each dimension in a single flat table (star), or normalize it into related

sub-tables (snowflake)? For analytics on a modern cloud warehouse, the star is

almost always the better default. Here's why, with a worked example and a diagram.

Star vs snowflake, at a glance

The one real difference

In a star schema, each dimension is a single, wide, denormalized table — the

product dimension holds the product, its category, its brand, and its supplier all in

one place, even though "Electronics" repeats across many rows. In a snowflake

schema, you normalize that dimension into a branching hierarchy: product points to a

separate category table, which points to a department table, and so on. The single

dimension "snowflakes" out into smaller related tables, which is where the name comes

from.

        STAR SCHEMA                          SNOWFLAKE SCHEMA



         dim_date                                dim_date

            |                                        |

 dim_customer — fact_sales — dim_product   dim_customer — fact_sales — dim_product

            |                                        |                    |

         dim_store                                dim_store          (category)

                                                                         |

                                                                      (brand)



 Dimensions sit directly on        A dimension (product) is normalized

 the fact table.                    into further sub-tables.

If you understand why dimensional models split measurements from

context, you already understand both —

snowflaking is just normalization applied

to the dimension tables.

A worked example

Say you want sales by product category. In a star, category lives right on the

product dimension, so it's one join:

-- STAR: one join, category is on the dimension

SELECT p.category, SUM(f.net_amount) AS revenue

FROM fact_sales f

JOIN dim_product p ON f.product_key = p.product_key

GROUP BY p.category;

In a snowflake, category has been normalized into its own table, so the same

question now traverses the hierarchy:

-- SNOWFLAKE: an extra hop to reach category

SELECT c.category, SUM(f.net_amount) AS revenue

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

GROUP BY c.category;

Every level of normalization is another join the analyst must write and the engine

must execute. Multiply that across a real schema and the snowflake's "tidiness"

becomes a steady tax on every query.

When to use a star schema

For analytics on a columnar cloud warehouse — which is most analytics today —

default to the star. Denormalize your dimensions. The storage cost is negligible

because columnar engines compress repeated values away to almost nothing, queries are

dramatically simpler, and performance is typically better than the snowflake, not

worse. Optimizing for storage by normalizing is solving a 1998 problem with a 2026

bill.

When to use a snowflake schema

Reach for snowflaking only in specific cases, and even then only for the dimension

that needs it:

• A dimension is genuinely enormous (tens of millions of rows) and a shared
  attribute is large and highly repetitive, so the storage saving is material.


• A rapidly changing shared attribute is meaningfully cheaper and safer to update
  in one normalized place.


• A compliance or governance rule forces a single authoritative table for an
  entity.

Mixing is fine — a mostly-star model with one snowflaked dimension is a perfectly

reasonable, pragmatic design. You don't owe the schema purity.

The thing underneath the choice

"Star vs snowflake" is really a proxy for an older question: normalize for

write-efficiency, or denormalize for read-efficiency? A warehouse is overwhelmingly

read-heavy — written by a few pipelines, queried by everyone — so it should optimize

for reads, which means denormalizing, which means the star. (If you want the deeper

version of that trade-off, see normalization vs

denormalization; if you want the even more

aggressive end of denormalization, see one big table vs the star

schema.)

Pick the star by default. Snowflake a dimension only when you can name the specific

problem it solves. And don't lose an afternoon to the debate — it was only ever one

decision wearing two names.

FAQ

What is the difference between a star schema and a snowflake schema?

A star schema keeps each dimension in a single flat, denormalized table. A snowflake schema normalizes those dimensions into multiple related sub-tables. That one choice — denormalized versus normalized dimensions — is the entire distinction; the fact table is the same in both.

Which is faster, star schema or snowflake schema?

On modern columnar warehouses, usually the star. Denormalized dimensions mean fewer joins at query time, and columnar compression shrinks the repeated values that normalization was meant to eliminate, so the snowflake's storage saving rarely outweighs its extra join cost.

When should you use a snowflake schema?

When a dimension is genuinely enormous and a shared attribute is large and highly repetitive, when a rapidly changing shared attribute is cheaper to update in one normalized place, or when a compliance rule forces a single authoritative table. Even then, snowflake only the dimension that needs it.

Is the snowflake schema related to the Snowflake data warehouse?

No. The schema pattern is decades older than the vendor and unrelated to it — you can build star or snowflake schemas on any warehouse, including Snowflake, BigQuery, or Redshift.

This post was originally published on dataarchitect.studio, where I write about data architecture, dimensional modeling, and the lakehouse.