Dimensional Modelling in 2026: Star vs Snowflake Schema for Modern Warehouses

Dimensional modelling is one of the most enduring ideas in data engineering. The concepts Ralph Kimball popularised in the 1990s — fact tables, conformed dimensions, surrogate keys, slowly changing dimensions — still underpin most analytical workloads running today. But the trade-offs that originally drove the choice between a star schema and a snowflake schema were rooted in a very different world: nightly batch loads, expensive disk, row-based query engines, and join performance that punished anything beyond a handful of tables.

We don’t live in that world anymore. Storage costs are negligible compared to compute. Columnar engines like Snowflake handle wide, denormalised tables with ease. Joins between large dimensions and fact tables are no longer the performance cliff they used to be. So in 2026, when does each pattern still earn its place — and when is the answer simply “denormalise and move on”?

In this article, we will revisit both schemas through a modern lens, with practical guidance on when to choose which.

Features change from time to time with new features being added regularly, it is recommended that you review the documentation ↗ for the latest on what specific features are included with any of the Editions.

A Quick Refresher on Dimensional Modelling#

Dimensional modelling organises analytical data into two types of table:

• Fact tables — the measurements of the business. Sales transactions, web events, support tickets, energy readings. Facts are typically numeric, additive (or semi-additive), and high cardinality.
• Dimension tables — the context around those measurements. Customers, products, dates, stores, devices. Dimensions are descriptive, lower cardinality, and provide the who, what, where, and when of every fact.

The two patterns we are comparing differ only in how the dimension tables themselves are structured.

The Star Schema#

In a star schema, every dimension is a single, denormalised table. A dim_product table contains the product, its category, its sub-category, its brand, its supplier, and any other descriptive attribute — all in one place, all in one row per product.

The fact table sits in the centre, surrounded by dimensions, with one join hop to reach any descriptive attribute. Hence “star”.

The Snowflake Schema#

In a snowflake schema, dimensions are normalised into multiple tables. The product hierarchy might be split across dim_product, dim_category, dim_subcategory, and dim_brand, with each level connecting to the next through foreign keys.

The shape is more elaborate, with dimensions branching outward — like the points of a snowflake — and queries needing multiple join hops to reach the leaf attributes.

The naming, incidentally, is unrelated to the Snowflake platform. The schema pattern was named decades before the company existed, which has caused no end of confusion in conversations and search results ever since.

What the Trade-offs Used to Be#

In the on-premise data warehouse era, the choice between the two schemas was driven by hard constraints:

• Storage was expensive. Repeating a brand name millions of times across every product row felt wasteful. Normalising the brand into its own table saved real money on disk.
• Joins were costly. Row-based engines paid a meaningful penalty for every join, but the alternative — wide, denormalised dimensions — meant scanning more bytes per query on slow disks.
• Query optimisers were less mature. A poorly written query against a snowflake schema could explode into a Cartesian product or pick the wrong join order entirely.
• Updates mattered more. Re-categorising a thousand products in a normalised hierarchy meant updating one row in dim_category. In a fully denormalised star, it meant updating thousands of rows in dim_product.

Different teams weighted these constraints differently, and the resulting religious war between Kimball (star) and Inmon (more normalised) advocates produced a generation of book chapters and conference debates.

What Changed in the Cloud Era#

Almost every one of those constraints has either disappeared or inverted on a modern columnar platform.

Storage is effectively free. A Snowflake table storing 100 million product rows with twenty redundant attributes costs perhaps a few pounds a month. Optimising disk usage is no longer a meaningful design driver for dimensional modelling.

Columnar compression neutralises redundancy. Repeating “Acme Industries” a million times across rows compresses to almost nothing on a columnar engine. The “wasted space” argument against star schemas largely evaporates.

Joins are cheap on small dimensions. Snowflake’s query optimiser handles joins between a billion-row fact table and a few hundred-thousand-row dimensions trivially. The performance penalty that snowflake-schema advocates used to invoke against wide stars is mostly noise.

Multi-hop joins, however, still cost. Joining through three dimension tables to reach a leaf attribute — fact_sales → dim_product → dim_subcategory → dim_category — is genuinely slower and harder to reason about than a single join into a denormalised dimension. This penalty is small in absolute terms but consistent.

Query writers and BI tools prefer flat structures. Self-service analysts using Power BI, Tableau, or Snowsight build models more reliably against star schemas than snowflake schemas. The cognitive load of remembering which table holds which attribute is real, even when the engine doesn’t care.

The net effect is that the historical arguments for the snowflake schema have weakened, and the historical arguments against it (complexity, slower queries, harder for BI tools) have remained. In 2026, star schema is the sensible default for almost every analytical workload.

When to Use a Star Schema (Default)#

Use a star schema when:

• The dimension is small enough to denormalise without operational pain (almost always the case under a few million rows)
• BI users will query the dimension directly
• Attributes within the dimension are stable, or change at the same cadence
• You want simple, predictable join paths in semantic layers

Let’s look at a practical example. A retail sales fact, joined to a denormalised product dimension:

-- Star schema query: one join hop, all product attributes available
SELECT
    p.brand,
    p.category,
    p.subcategory,
    SUM(s.sales_amount) AS total_sales
FROM analytics.fact_sales s
JOIN analytics.dim_product p
    ON s.product_key = p.product_key
WHERE s.sale_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY p.brand, p.category, p.subcategory
ORDER BY total_sales DESC;

Explanation: A single join, all product hierarchy attributes available on the same row. Simple to write, simple to optimise, simple for a BI tool to model.

When a Snowflake Schema Still Earns Its Place#

The pattern is not dead. It still wins in specific situations:

1. Genuinely large dimensions with deep hierarchies. A customer dimension with hundreds of millions of rows, a product catalogue with twenty levels of nested categorisation, or a clinical taxonomy with industry-standard codes — these benefit from normalisation because the savings on duplicated text become material and the hierarchies have independent update lifecycles.

2. Hierarchies that change at different rates. If your product attributes change weekly but your category structure is updated quarterly under tight governance, separating them is sensible. You can manage slowly-changing-dimension logic on each table independently.

3. Conformed reference data shared across many marts. When a single category or geography hierarchy serves multiple data products, separating it into its own table avoids drift and lets it be shared as a referenceable object.

4. Data vault hybrids. Modern lakehouse architectures often blend dimensional modelling with data vault patterns. The hub-link-satellite structure of data vault is, in effect, a normalised approach. If your raw layer is data-vault-shaped, a snowflake-style presentation layer can be a natural intermediate before final denormalisation into stars.

The same query against a snowflake schema looks like this:

-- Snowflake schema query: multiple join hops to reach hierarchy attributes
SELECT
    b.brand_name,
    c.category_name,
    sc.subcategory_name,
    SUM(s.sales_amount) AS total_sales
FROM analytics.fact_sales s
JOIN analytics.dim_product       p  ON s.product_key      = p.product_key
JOIN analytics.dim_brand         b  ON p.brand_key        = b.brand_key
JOIN analytics.dim_subcategory   sc ON p.subcategory_key  = sc.subcategory_key
JOIN analytics.dim_category      c  ON sc.category_key    = c.category_key
WHERE s.sale_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY b.brand_name, c.category_name, sc.subcategory_name
ORDER BY total_sales DESC;

Explanation: The same business question, four extra joins. Snowflake the platform will run this just as quickly as the star equivalent on most workloads — but the SQL is harder to write, harder for a self-service user to recreate, and more brittle to schema changes.

Best Practices#

From experience, I would recommend considering the below:

1. Default to star schema. Unless you have a specific reason to normalise, denormalise dimensions into wide tables. Storage savings are not a sufficient reason on a modern platform.

2. Use surrogate keys consistently. Integer surrogate keys generated through IDENTITY columns or a sequence outperform composite natural keys, isolate downstream models from upstream key changes, and simplify slowly changing dimension logic.

3. Model slowly changing dimensions explicitly. Type 2 SCDs with effective_from, effective_to, and is_current columns work well in either schema. Document your SCD type for every dimension — silent assumptions cause weeks of debugging later.

4. Use views to present a star face over a snowflake-shaped storage layer. If you have legitimate reasons to keep dimensions normalised in storage (governance, deep hierarchies), build flattened views that BI tools can consume. This gives you the engineering discipline of normalisation with the user experience of a star.

5. Push hierarchy logic into the dimension, not the fact. The category that a sale belongs to is a property of the product, not the sale. Resist the temptation to denormalise category onto the fact table itself — it duplicates massively and breaks if the hierarchy changes.

6. Lean on Snowflake-native features. Dynamic tables, streams and tasks, and the MERGE statement make maintaining either schema dramatically easier than building bespoke ETL. If you find yourself writing custom Python to manage SCD logic, you have probably missed a built-in pattern.

Common Pitfalls#

Pitfall: Normalising “Just in Case”#

Problem: Teams default to a snowflake schema because it “looks more correct” or matches a textbook diagram, then spend years fighting with multi-join queries and confused BI users.

Solution: Start with a star schema. Move to a snowflake schema only when you hit a concrete pain point that normalisation actually solves.

Pitfall: Denormalising Too Aggressively#

Problem: Pushing every dimension attribute onto the fact table itself produces a single, monstrous wide table. It looks performant in isolation but breaks on dimension changes, drives up storage cost meaningfully (because the fact table is huge), and makes shared dimensional governance impossible.

Solution: Keep dimensions as separate tables, even when denormalised internally. The fact table joins to them; it does not absorb them.

Pitfall: Treating “Snowflake schema” and “Snowflake the platform” as Related#

Problem: Conversations get confused when “we use a snowflake schema on Snowflake” comes up. New starters infer a connection that does not exist.

Solution: Use clearer language — “normalised dimensions” and “denormalised dimensions” — when context allows. Save “star” and “snowflake” terminology for written documentation where the surrounding text removes the ambiguity.

Pitfall: Ignoring Hierarchy Drift#

Problem: Categories get re-mapped, products get reclassified, organisational structures change. In a fully denormalised star, this means rewriting millions of fact-table rows or rebuilding the dimension entirely.

Solution: Decide early whether your hierarchy needs Type 2 SCD treatment (preserving history) or Type 1 (overwriting). Document the decision. Use Snowflake streams to capture changes cleanly when the time comes.

Conclusion#

The choice between star and snowflake schemas mattered enormously when it was first formalised, because the underlying constraints made it expensive to get wrong. On a modern cloud platform, the constraints have shifted so far that star schema is now the right default for almost every workload, and snowflake schema is a specialised tool for genuinely deep hierarchies, vast dimensions, or shared reference data.

The deeper lesson is that dimensional modelling principles — facts, dimensions, surrogate keys, conformed reference data, deliberate handling of change — are still as relevant as ever. What has changed is the implementation discipline. Spend less time worrying about normalisation and more time getting your fact grain, your dimension hierarchies, and your slowly-changing-dimension behaviour right. Those are the choices that will still hurt you in five years if you get them wrong.

Key Takeaways:

• Default to star schema on modern cloud warehouses; storage and join cost arguments for normalisation no longer hold
• Snowflake schema still earns its place for genuinely large dimensions, deep independent hierarchies, and shared conformed reference data
• The naming clash between snowflake-the-schema and Snowflake-the-platform is a coincidence — they are unrelated concepts
• Get fact grain, surrogate keys, and SCD strategy right; these matter far more than the star-versus-snowflake choice
• Use views to present a flattened face over normalised storage when you need both engineering discipline and user friendliness

Features change from time to time with new features being added regularly, it is recommended that you review the documentation ↗ for the latest on what specific features are included with any of the Editions.

Further Reading#

• Snowflake Documentation ↗
• Kimball Group: Design Tip Library ↗