Columnar engines like ClickHouse, Snowflake, BigQuery, and Redshift make denormalization cheap and joins fast, which reshapes the classic star-vs-snowflake-schema trade-off. Here is how to choose in 2026.
Almost every "star schema vs snowflake schema" article you will find repeats the same textbook trade-off: star schemas are faster to query, snowflake schemas save storage, pick your poison. That framing dates to the era of row-oriented databases on spinning disks, where every extra join meant another random read and storage was genuinely scarce. It is not wrong, but on a modern columnar engine most of its assumptions no longer hold.
If you are running analytics on ClickHouse, Snowflake (the product), BigQuery, or Redshift, the math has shifted. Compression makes the "wasteful" duplication of a denormalized star schema close to free, and join algorithms have improved to the point where the join penalty that justified heavy normalization is much smaller than it used to be. This post walks through where the old advice still applies, where columnar storage rewrites it, and how to actually decide in 2026.
One disambiguation up front, because it trips people up constantly. The snowflake schema is a data modeling pattern. Snowflake is also a cloud data warehouse product. They are unrelated. When this post means the product, it says "Snowflake the product." Everywhere else, "snowflake schema" refers to the modeling pattern.
Star and Snowflake Schemas, Defined
A star schema is a dimensional model with one central fact table holding measurements (sales amounts, event counts, durations) joined directly to a set of denormalized dimension tables (customer, product, date), each represented by a single flat table. Queries join the fact table to one or two dimensions, which is why the layout is fast and simple to reason about.
A snowflake schema is a star schema whose dimension tables have been normalized into multiple related sub-tables. A product dimension might split into product, category, and supplier tables linked by foreign keys. This removes redundancy in large hierarchies at the cost of additional joins per query and a more complex model for analysts to navigate.
Both come from Ralph Kimball's dimensional modeling approach, introduced in the 1990s and still the dominant way to structure analytical data (ml4devs). Kimball's own guidance was to avoid snowflaking unless there is a concrete reason for it, such as an unusually large dimension, because the extra joins hurt query performance and confuse users (Star schema, Wikipedia).
Here is a short DDL sketch to make the difference concrete. A star schema keeps the product attributes inline:
CREATE TABLE dim_product (
product_id UInt32,
product_name String,
category String, -- denormalized: repeated for every product in a category
supplier String -- denormalized
) ENGINE = MergeTree ORDER BY product_id;
The snowflake version splits the hierarchy out:
CREATE TABLE dim_product (product_id UInt32, product_name String, category_id UInt16);
CREATE TABLE dim_category (category_id UInt16, category String, supplier_id UInt16);
CREATE TABLE dim_supplier (supplier_id UInt16, supplier String);
A query against the star schema joins the fact table to one dimension. The same query against the snowflake schema chains three joins to reach the supplier name.
The Textbook Argument (and Where It Stops)
The classic comparison lines up like this, and every top search result stops here.
| Dimension | Star schema | Snowflake schema |
|---|---|---|
| Normalization | Denormalized dimensions | Normalized into sub-dimension tables |
| Storage | More redundancy, larger dimension tables | Less redundancy, smaller dimension tables |
| Joins per query | Few (1-2 per dimension) | Many (chained through the hierarchy) |
| Query/join cost | Lower, fewer joins | Higher, more joins |
| Maintenance & integrity | Updates touch repeated values | Single source of truth per attribute |
| Analyst usability | Simple, intuitive | Harder to navigate |
| Classic verdict | Use for speed | Use to save storage |
The problem is the two columns people care about most, storage and query cost, were both measured on assumptions that columnar engines break. The storage savings from snowflaking are usually tiny to begin with, because dimension tables are small relative to the fact table. Wikipedia's own worked example shows a snowflake schema cutting total records by roughly 0.02% versus the equivalent star schema (Snowflake schema, Wikipedia). You are normalizing the smallest tables in the warehouse to save space, while paying a join tax on every query.
How Columnar Storage Changes the Math
Three properties of modern analytical engines undermine the textbook trade-off.
Compression makes denormalization cheap. Column stores group values of the same type and similar content together, so repeated values (the "redundant" category name copied across every product row) compress extremely well under codecs like LZ4 and ZSTD. ClickHouse reports that 10x to 20x compression on a fact table is typical, with dimension tables compressing even more aggressively because their values are small and repetitive (ClickHouse). The storage argument for snowflaking, already thin, mostly evaporates: the duplication you were trying to avoid largely disappears after compression.
Vectorized scans make wide tables fast. Columnar engines read only the columns a query touches and process them in batches through SIMD-friendly loops. A wide, denormalized table is not the liability it would be on a row store, where every row read pulls in all columns. Reading 5 columns out of 80 costs roughly what reading 5 columns out of 5 would, so flattening dimensions into the fact table (or into wide dimension tables) does not penalize unrelated queries.
Joins are far faster than they were. This is the big one. ClickHouse ships multiple join algorithms (parallel hash, grace hash, full sorting merge) and a planner that picks among them by table size and memory. Recent versions added global join reordering in 25.9 and runtime bloom filters in 25.10; on TPC-H the bloom filters alone delivered a 2.1x speedup with a 7x memory reduction, and join reordering turned one six-table query from 3,903 seconds into 2.7 seconds (ClickHouse). In a head-to-head star schema benchmark of 17 mostly-join queries across 721 million to 7.2 billion rows, ClickHouse Cloud was faster and cheaper than Snowflake the product and Databricks at every scale, with no query or engine tuning; at the 1-billion-row scale one query scanned 1.7 billion rows in half a second while competitors took 5 to 13 seconds (ClickHouse).
The takeaway is not "joins are free." It is that the gap between a star schema's few joins and a snowflake schema's many joins is much smaller than it was, while the gap between a star schema and a fully denormalized wide table is now small enough that denormalization is often the cheaper, simpler choice. For more on laying out tables for these engines, see our guide to ClickHouse schema optimization.
When Each Schema Still Wins
The economics changed, but the trade-offs did not vanish. Choose deliberately.
Star schema (or a denormalized wide table) wins when:
- You need sub-second BI dashboards. Fewer joins means lower latency and more predictable query plans, which matters when a dashboard fires dozens of concurrent queries.
- A semantic layer or BI tool sits on top. Star schemas map cleanly to tools like Power BI, which is explicitly built around the star pattern (Microsoft Learn).
- Dimensions are small. Normalizing a 10,000-row dimension to save space is pure overhead on a columnar engine.
Snowflake schema (or fuller normalization) earns its keep when:
- A dimension is genuinely huge or has high-cardinality, deeply shared hierarchies, so the redundancy is real rather than negligible.
- Governance and integrity demand a single source of truth per attribute, where updating a value in one place beats rewriting it across millions of denormalized rows.
- A sub-dimension is reused across many fact tables and you want one authoritative definition rather than several drifting copies.
A practical middle ground is the hybrid: keep the frequently queried, small dimensions denormalized for speed, and normalize only the sparse or oversized hierarchies. The galaxy (or fact constellation) schema, where multiple fact tables share conformed dimensions, is the natural extension when you have several business processes to model. Denormalized facts with a handful of normalized dimensions cover a large share of real warehouses.
Engine-Specific Notes for 2026
The right answer depends on which engine you run, because each has its own join machinery and layout knobs.
- ClickHouse rewards keeping joins shallow. Its planner is strong, but the cleanest results come from star schemas or wide tables; dictionaries are a good fit for small lookup dimensions, sidestepping joins entirely. The 25.9 and 25.10 join improvements narrow the cost of the joins you do keep. Our ClickHouse vs Snowflake comparison digs into where each engine fits.
- Snowflake the product handles both patterns well; clustering keys on the fact table matter more for performance than the choice between star and snowflake modeling. See what Snowflake is for the product overview.
- BigQuery offers nested and repeated fields, which let you collapse one-to-many dimensions into a single table and avoid joins in a way neither star nor snowflake assumes.
- Redshift still benefits from classic star-schema discipline; its optimizer and distribution keys are tuned for fact-to-dimension joins.
Across all of them, the decision is less "star or snowflake" and more "how flat can I make this before integrity or maintenance costs outweigh the query savings." If you are still choosing the engine itself, our guide on how to choose a database for your next project is a good starting point.
Key Takeaways
- "Snowflake schema" (a modeling pattern) and "Snowflake" (a product) are different things; do not conflate them.
- The textbook trade-off (star for speed, snowflake for storage) was calibrated for row stores. On columnar engines, both assumptions weaken.
- Compression often makes denormalized data nearly as compact as normalized data, so the storage case for snowflaking is usually weak; dimension tables are small to begin with.
- Faster join algorithms (parallel hash, grace hash, runtime bloom filters, global reordering) shrink the penalty that justified heavy normalization.
- Default to a star schema or a wide denormalized table on columnar engines. Reach for the snowflake pattern only for genuinely large dimensions, strict integrity requirements, or dimensions reused across many facts.
- Match the model to the engine: dictionaries and shallow joins on ClickHouse, clustering keys on Snowflake the product, nested fields on BigQuery, distribution keys on Redshift.
