Star Schema vs Snowflake Schema: Trade-offs on Modern Cloud Warehouses

A star schema has a central fact table joined directly to a set of denormalised dimension tables. A snowflake schema has the same fact table, but some dimensions are normalised into chains of related tables: product joins to subcategory, which joins to category, which joins to department. Drawn on a diagram, the first looks like a star and the second branches out like a snowflake.

Both are dimensional models. Both separate facts from dimensions and use surrogate keys. The difference is entirely in how the dimensions are stored, and that difference affects query complexity, storage, maintenance and how well business users and BI tools understand the model.

(The snowflake schema has nothing to do with the Snowflake data platform, which runs star schemas perfectly well. The name is a coincidence that causes regular confusion.)

The same model, both ways

Take a retail sales model with a product hierarchy: product, subcategory, category, department.

Star

create table dim_product (    product_key        bigint primary key,    sku                varchar(40)  not null,    product_name       varchar(200) not null,    brand              varchar(100),    subcategory_name   varchar(100) not null,    category_name      varchar(100) not null,    department_name    varchar(100) not null);​create table fct_sales (    date_key      integer not null,    store_key     bigint  not null,    product_key   bigint  not null references dim_product (product_key),    quantity      integer not null,    net_amount    numeric(12,2) not null);

The hierarchy is flattened into the dimension. "Electronics" appears on every electronics product row.

Snowflake

create table dim_department (    department_key   bigint primary key,    department_name  varchar(100) not null);​create table dim_category (    category_key     bigint primary key,    category_name    varchar(100) not null,    department_key   bigint not null references dim_department (department_key));​create table dim_subcategory (    subcategory_key  bigint primary key,    subcategory_name varchar(100) not null,    category_key     bigint not null references dim_category (category_key));​create table dim_product (    product_key      bigint primary key,    sku              varchar(40)  not null,    product_name     varchar(200) not null,    brand            varchar(100),    subcategory_key  bigint not null references dim_subcategory (subcategory_key));

Each level is stored once. "Electronics" exists in one row of dim_department.

The query difference

Sales by department, star:

select p.department_name, sum(f.net_amount) as revenuefrom fct_sales fjoin dim_product p on p.product_key = f.product_keygroup by p.department_name;

Snowflake:

select d.department_name, sum(f.net_amount) as revenuefrom fct_sales fjoin dim_product     p  on p.product_key = f.product_keyjoin dim_subcategory sc on sc.subcategory_key = p.subcategory_keyjoin dim_category    c  on c.category_key = sc.category_keyjoin dim_department  d  on d.department_key = c.department_keygroup by d.department_name;

One join versus four, for the same answer.

The trade-offs

Storage: mostly irrelevant now

The original argument for snowflaking was storage. In the 1990s, repeating a department name across a million product rows cost real money. On columnar cloud warehouses it barely registers. Snowflake, BigQuery, Redshift, Databricks (Delta/Parquet) and Fabric all store data by column and compress it, and a low-cardinality text column like department_name compresses down to almost nothing through dictionary and run-length encoding. Dimensions are also small relative to facts. The storage saved by snowflaking a dimension is usually a rounding error next to the fact table.

Query performance: usually favours star

Every extra join is work for the optimiser and the engine. Modern engines are good at joining small dimension tables, and the difference for a single query may be small. But the cost compounds: more joins mean more opportunities for a bad plan, harder-to-predict performance, and less effective pruning. Several platforms also optimise star joins specifically, for example by pushing dimension filters down to the fact scan. Flat dimensions make that easier.

Maintenance: favours snowflake, a little

If the department "Home & Garden" is renamed "Home", a snowflake updates one row. A star updates every product in that department. In practice this is a non-issue: dimensions are rebuilt or merged by a pipeline, and the pipeline does not care whether it touches one row or ten thousand. If your dimension is Type 2 on hierarchy attributes, a star design means a reorganisation creates new versions for every affected product, which is correct behaviour but worth being aware of.

Usability: strongly favours star

This is the argument that matters most. A business user, a BI semantic model or an AI agent looking at a star sees one dim_product table with every product attribute on it. In a snowflake they see four tables and must know which chain to follow. More tables mean more chances to join wrongly, and more documentation to keep current.

When snowflaking is the right call

Snowflaking is not always wrong. Normalise part of a dimension when:

The outrigger is shared and large

A dimension outrigger is a secondary table referenced from a dimension. Suppose dim_customer and dim_supplier both need 40 geographic attributes (postal code, region, demographic bands, sales territory). Storing those once in dim_geography and referencing it from both keeps them consistent. That is a deliberate, limited snowflake.

The hierarchy has its own attributes and its own facts

If categories have budgets, managers and targets, and there is a fct_category_budget at category grain, then dim_category must exist as its own table anyway, conformed between the budget fact and the sales fact. You can still denormalise category attributes onto dim_product for convenience; the two are not mutually exclusive.

The hierarchy is ragged or variable depth

Organisational charts and bills of materials do not have a fixed number of levels. Flattening them into fixed columns is awkward. A bridge table or a parent-child structure (a recursive relationship) is often the better answer. That is not strictly a snowflake, but it is a case where a single flat dimension does not fit.

Very high-cardinality, frequently changing sub-attributes

If a subset of customer attributes changes daily (credit score band, engagement tier), splitting it into a mini-dimension stops dim_customer from growing a new Type 2 row every day. Again, this is a targeted split, not full normalisation.

A healthcare example

A hospital analytics model has fct_encounter with a dim_provider. Providers belong to departments, departments belong to facilities, facilities belong to health systems.

A good default is a star: dim_provider carries department_name, facility_name, facility_state and health_system_name. Analysts can slice encounters by any level with one join.

Now add a fct_facility_capacity at facility-per-day grain (beds available, staffed beds). That fact needs a dim_facility. You build dim_facility as a conformed dimension shared by both facts. Whether dim_provider also references dim_facility (a snowflake) or simply repeats the facility attributes is a design choice. Repeating them keeps encounter queries simple; referencing keeps one source of truth. Many teams do both: repeat the commonly used attributes, and include facility_key for the less common ones.

Common mistakes

Normalising because it feels more correct. Teams coming from OLTP design sometimes snowflake by instinct, because duplication looks like a defect. In a warehouse, controlled duplication in dimensions is the design. Normalisation belongs in the source system and, if you use one, the integration layer. See database normalization 1NF to 3NF for where it does belong.

Snowflaking the date dimension. Splitting dim_date into day, month, quarter and year tables adds joins to almost every query in the warehouse for no benefit. Keep it flat. The date dimension guide shows a complete one.

Inconsistent hierarchy values in a star. If product attributes are flattened, the load must guarantee that every product in subcategory "Laptops" has the same category and department. Add a test that each subcategory maps to exactly one category.

select subcategory_name, count(distinct category_name)from dim_productgroup by subcategory_namehaving count(distinct category_name) > 1;

Joining facts directly to snowflaked levels. If fct_sales has both product_key and category_key, and they disagree, the model has two answers for one question. Derive higher levels through the dimension, or conform them carefully.

The practical default

On a modern cloud warehouse, start with a star. Flatten hierarchies into the dimension. Introduce outriggers, mini-dimensions and separate hierarchy-level dimensions only where a concrete need appears: shared attributes, a fact at a higher grain, a ragged hierarchy or a fast-changing attribute set. Document each exception.

Learn it hands-on

The Dimensional Modeling (Kimball) track in offdataai Learn includes exercises where you design dimensions for realistic hierarchies, decide when an outrigger or mini-dimension is warranted, and build it in the Model Builder with an instant automated check. It is included in the Explorer plan at $1/month.

To see finished stars across industries, browse the templates or read the dimensional modeling overview.