Star Schema vs One Big Table: What Actually Costs Money on a Columnar Warehouse

Every eighteen months, someone publishes a benchmark showing that a denormalized “One Big Table” (OBT) beats a traditional Kimball star schema on Snowflake or BigQuery. In response, teams rush to collapse all their clean dimension tables into massive 200-column flat tables.

A year later, the reckoning arrives: their nightly dbt build takes four hours, correcting a customer’s postal code requires rewriting 900 million historical rows, and analysts can’t tell which of the 45 different “status” columns to use in Tableau.

💡 The Library Analogy (In Plain English)

• Star Schema (The Card Catalog): Think of a library. The book loans table (fact_orders) only contains loan IDs, dates, and card barcodes (customer_id, product_id). Details about each author and customer live on separate index cards (dim_customers). When an author changes their pen name, you erase one single index card — the millions of loan records remain untouched.
• One Big Table (The Giant Scrapbook): Instead of separate cards, you photocopy the author’s entire biography and current address at the bottom of every single receipt across 10 years. Queries run fast because you never look up an index card, but if an author moves to a new city, you must rewrite 500,000 historical receipts!

The 30-Second Architect’s Rubric

If your primary need is…	Choose…	The Core Reason
Self-serve BI & ad-hoc exploration	Star Schema (Kimball)	Clean entity definitions, reusable dimensions, no duplicate naming chaos.
High-concurrency customer-facing app	One Big Table (OBT)	Zero join overhead, predictable single-digit millisecond response times.
Frequent retroactive dimension updates	Star Schema (Kimball)	Update 1 row in dim_customer without rewriting 100M fact rows.
Best of both worlds (The Hybrid)	Star + Purpose-built OBT	Maintain Star in your Gold marts, and generate lightweight OBT views for specific dashboards.

flowchart TD
  subgraph Star["Star Schema: Clean & Modular"]
    fact[Fact: fct_orders] --> dim1[Dim: dim_customers]
    fact --> dim2[Dim: dim_products]
    fact --> dim3[Dim: dim_dates]
  end

  subgraph OBT["One Big Table: Flat & Fast"]
    wide[fct_orders_denormalized: 180+ Columns with duplicated customer & product data]
  end

Joins are not the expense you think

Here is the measurement that ends most of these arguments. On a Snowflake MEDIUM warehouse, against a 1.4-billion-row fact table:

Query	Bytes scanned	Elapsed	Credits
Star, 4 dimension joins, 1 month filter	3.9 GB	4.1s	0.009
OBT, same columns, 1 month filter	4.4 GB	3.6s	0.008
Star, 4 joins, full 3-year scan	141 GB	96s	0.21
OBT, full 3-year scan	158 GB	88s	0.20

A 9 to 12 percent difference. Real, measurable, and almost never worth a redesign. The reason is that dimension tables are small and modern optimizers broadcast them: dim_customer at 4 million rows compresses to a few hundred megabytes, fits in memory on every worker, and the join becomes a hash probe rather than a shuffle.

Where joins genuinely hurt is a narrower set of cases than folklore suggests: a dimension too large to broadcast (tens of millions of rows with wide attributes), a fact-to-fact join, or a query with so many joins that the optimizer picks a bad order. The first is rare, the second is a modeling error regardless of your approach, and the third is fixable with a pre-joined intermediate model.

The OBT is also slightly worse on scanned bytes here, which surprises people. Denormalizing repeats every dimension attribute on every fact row, and while dictionary encoding makes that cheap, it is not free. My 190-column OBT was 3.2 TB where the equivalent star was 2.4 TB across all tables — 33% more storage, which at roughly $23/TB/month was about $18 a month. Storage is not the argument for or against anything.

Column pruning is the real reason wide tables work

The thing that makes OBT viable at all is that a columnar warehouse does not charge you for width you do not select.

-- 190-column table. This query reads three columns.
-- Bytes scanned is a function of the columns and partitions touched,
-- not of the table definition.
select region, sum(net_revenue), count(distinct customer_sk)
from analytics.gold.obt_sales
where order_date between '2026-04-01' and '2026-04-30'
group by 1;

That is why “the table has 200 columns” is not an argument against it. The BI tool selecting eight columns pays for eight columns. This is also why adding a column to a wide table is cheap for readers and only expensive for the writer.

But pruning only helps if the partitions prune too, and that is the part teams get wrong. Both models live or die on physical layout: the partition or clustering column has to match the predicate your users actually write. A star and an OBT with the same clustering behave almost identically; an OBT with no clustering behaves like a full scan every time. If that is not already solid, fix it first — micro-partitions and pruning for Snowflake, or the partitioning and clustering guide for BigQuery.

flowchart TD
  raw["Raw / silver"] --> dims["Conformed dimensions"]
  raw --> facts["Fact tables"]
  dims --> star["Gold star schema"]
  facts --> star
  star --> obt1["Wide table: exec dashboard"]
  star --> obt2["Wide table: customer-facing API"]
  star --> adhoc["Ad hoc SQL and semantic layer"]

The star is the source of truth. Wide tables are derived, disposable, and rebuilt from it.

What one big table actually costs

Three costs, none of which appear in a query benchmark.

Rebuild time. A star rebuilds dimension by dimension; each is small and independent, and a change to dim_product touches 200,000 rows. The equivalent change in an OBT touches every fact row that references that product. My 2.1-billion-row OBT took 70 minutes for a full rebuild on a LARGE warehouse. The star it was derived from took nine minutes. Incremental strategies help, but only for append-only facts — and the whole point of dimensions is that they are not append-only.

Restatement. This is the one that hurts. Someone reassigns 4,000 accounts from EMEA to a new MEA region, effective retroactively. In a star, you update dim_customer and every historical query is correct immediately. In an OBT, you rewrite every row for those accounts across three years. I have run that job. It took 40 minutes, cost about $22 in compute, and had to be re-run twice because the first version got the effective-date logic wrong.

-- The star version of a retroactive region change.
-- One statement, 4,000 rows, sub-second.
update analytics.gold.dim_customer
set region = 'MEA'
where country_code in ('AE', 'SA', 'EG', 'ZA')
  and is_current;

-- The OBT version. 900M-row rewrite, and you have to decide
-- whether history should change at all.
update analytics.gold.obt_sales
set region = 'MEA'
where customer_country in ('AE', 'SA', 'EG', 'ZA');

Notice the second statement forces a decision the first one lets you defer: does history change, or not? An OBT with dimension attributes baked in has already answered that question by accident, usually with “whatever the values were at load time,” which is a Type 2 behavior nobody designed. If history matters, model it deliberately — the options are laid out in slowly changing dimensions.

Ambiguity. My 190-column table had four columns containing a customer name and three containing a date that could be called “the order date.” Every one of them existed for a defensible reason at the time. Nobody could safely delete any of them because nobody knew what consumed them. A star schema does not prevent this, but it localizes it: dim_customer has one customer_name, and if you need a second one you have to justify it in a small, reviewable file.

Where one big table clearly wins

Three situations, and they are specific.

A tool that cannot join well. Some BI layers, embedded analytics SDKs, and most spreadsheet exports handle a single flat table far better than a model with relationships. If your consumer is a dashboard tool with a weak modeling layer, or an external API returning rows to a customer, flatten for it.

Extreme concurrency on a fixed query shape. My customer-facing product served 3,000 dashboard loads an hour with the same seven query shapes. Pre-joining removed the optimizer from the equation entirely and made p99 latency predictable, which mattered more than average speed. Predictability, not throughput, was the win.

Event and clickstream data with no real dimensions. A semi-structured event stream where the “dimensions” are ten low- cardinality attributes does not need a star. Modeling dim_device with six rows is ceremony.

Note what all three have in common: a known, bounded set of consumers. An OBT is a serving structure. It works when you know who reads it and how. It fails as a general-purpose exploration layer, because general-purpose means every column must be defensible forever.

The hybrid I actually ship

Star schema in gold as the source of truth. Purpose-built wide tables downstream, each owned by a named consumer, each rebuilt from the star, each disposable.

# models/gold/schema.yml — the star is tested as the contract
models:
  - name: fct_order_lines
    description: Grain is one row per order line. Never aggregate above this.
    columns:
      - name: order_line_id
        tests: [unique, not_null]
      - name: customer_sk
        tests:
          - relationships:
              to: ref('dim_customer')
              field: customer_sk

  - name: obt_exec_dashboard
    description: >
      Derived from the star for the exec Looker dashboard only.
      Safe to drop and rebuild. Do not add columns without an owner.
    config:
      materialized: table
      cluster_by: ['order_month', 'region']
    meta:
      owner: analytics-eng
      consumer: looker/exec_overview
      review_by: 2026-11-01     # unused columns get deleted at review

Two rules keep this from degenerating. Every wide table names exactly one consumer, and every wide table has a review date. When the review comes up, I pull the query history, find columns nobody has selected in 90 days, and drop them.

-- Which columns of the OBT has anyone actually queried this quarter?
-- Snowflake: access_history gives you column-level lineage.
select
    value:"columnName"::string as column_name,
    count(*) as query_count,
    max(query_start_time) as last_used
from snowflake.account_usage.access_history,
     lateral flatten(input => base_objects_accessed) f,
     lateral flatten(input => f.value:"columns") 
where f.value:"objectName"::string = 'ANALYTICS.GOLD.OBT_SALES'
  and query_start_time > dateadd(day, -90, current_date)
group by 1
order by query_count asc;   -- the zeros at the top are your delete list

That query has saved me more money than any join optimization. On the 190-column table, 61 columns had zero reads in a quarter. Dropping them cut the rebuild from 70 minutes to 44.

The star also stays the thing your semantic layer and ad hoc analysts point at, because it is the model where a metric has one definition and a join path is declared once. If you are building anything that compiles queries — a BI semantic layer, or an LLM interface — it wants the star, not the flat table.

Where teams get this wrong

Benchmarking joins and concluding the architecture. A 10% query difference does not justify a model that cannot be restated. Benchmark the rebuild and the retroactive update too.

Flattening a Type 2 dimension without thinking. Joining a fact to a versioned dimension and materializing the result freezes point-in- time values into your OBT. That is sometimes exactly right and sometimes a silent bug. Decide explicitly.

One OBT for everyone. The moment a wide table has three unrelated consumers, it grows columns for all of them and can be changed for none of them. One table, one consumer, one owner.

Aggregating above the grain. A wide table encourages SUM on a column that is repeated across joined rows. This is the classic fan-out error, and denormalization makes it easier to commit because the join is no longer visible in the query. The mechanics are in SQL joins and fan-out.

Incremental OBT with mutable dimensions. An incremental strategy that only appends new facts will happily keep stale dimension attributes on old rows forever. If dimensions change, you need a full refresh cadence or a targeted rewrite; see incremental models in production.

No deletion path. Columns accumulate because adding one is a small PR and removing one is a scary PR. Without a scheduled review backed by access history, every wide table trends toward 200 columns.

FAQ

Does Kimball still apply on a cloud warehouse?

The dimensional thinking absolutely does — grain, conformed dimensions, additive facts, surrogate keys. The physical advice does not. Snowflake keys, index strategies, and aggressive aggregate tables were answers to storage engines that no longer exist. Keep the modeling, drop the physical prescriptions.

Are surrogate keys worth it now?

Yes, and for reasons that have nothing to do with storage. A surrogate key gives you a stable join target when the natural key changes, lets you represent Type 2 history at all, and gives you somewhere to hang an unknown-member row instead of a null. Use a hash of the natural key plus effective date rather than a sequence; it is deterministic and survives a rebuild.

How wide is too wide?

I get uncomfortable past about 120 columns, not because of query cost but because it stops being reviewable. If nobody on the team can describe what every column means without looking it up, the table has outgrown its documentation.

What about materialized views or dynamic tables instead?

Often the better answer. A wide table that a warehouse maintains incrementally for you removes the rebuild cost that is the main argument against OBT. The constraint is that they support a limited SQL surface and their refresh cost is real but less visible. Worth measuring before you write a custom rebuild DAG.

Should the OBT be in the gold layer or its own schema?

Its own schema, named after the consumer. Putting derived serving tables next to conformed dimensions invites people to treat them as sources of truth and build on top of them, which is how you get a dependency chain three flat tables deep.

How do I migrate from OBT back to a star?

Build the star alongside, reconcile totals for a month with a daily diff query, then repoint consumers one at a time. Do not attempt a cutover. The reconciliation step always finds two or three places where the OBT’s implicit business logic differed from what anyone believed, and those discoveries are the actual value of the migration.

What this means for your pipelines

The framing that has served me best is that a star schema is a model and a wide table is a cache. Models are things you reason about, test, restate, and expect to keep for years. Caches are things you build for a known consumer, measure, and throw away when the consumer changes. Confusing the two is what produces a 240-column table that nobody dares modify.

Practically, that means the decision is rarely either-or and the sequencing matters more than the choice. Build the star first, even if your only consumer today is one dashboard, because it is the artifact that makes every later decision reversible. Then flatten aggressively for specific consumers when you have a measured reason, and put a review date on each one.

The cost that will actually hurt you in two years is not milliseconds per query. It is the morning finance tells you a region assignment was wrong for the last eighteen months and asks how long the fix will take. In a star that is an afternoon. In a billion-row flat table it is a project, and the answer you give in that meeting is determined entirely by a modeling decision you made before anyone asked.