One Big Table

When does pre-joining everything into one wide table minimize total cost, and what are its four costs?

One Big Table means: pre-join everything to fact grain into one wide table, so that consumers never write joins.

The argument is a total-cost argument, and it is sound. Joins are paid by every consumer, every time. Denormalization is paid by one pipeline, once. With column pruning, the unused width costs nothing to read. If the consumer population is large and mixed in skill, OBT minimizes total cost. (Imagine 200 analysts. If each writes the same five-way join 10 times a week, the join logic is written 2,000 times per week; if one pipeline does it once, it is written once.)

Four costs, stated precisely

1. Grain lock-in. An OBT is a materialization at one grain. Questions at a different grain need a different OBT. The characteristic failure is a family of near-duplicate wide tables whose relationships nobody can state.

2. Rebuild on dimension change. Changing one dimension attribute rewrites every fact row that carries it. This is the granularity-mismatch law: the change is one row, the stored copies are millions. Worked example: a product category rename touches 200 products. If those products appear in 5 million order lines each, that is 200 × 5,000,000 = 1 billion stored copies. A dimension attribute correction on a billion-row OBT is a full rewrite of a column at minimum, and often of the table.

3. Frozen history semantics. The values were joined at build time under one convention, usually as-was. Asking "restate under the current hierarchy" means rebuilding. So the alternative view is not available, only substitutable.

4. Vocabulary drift. Attribute names multiply across tables, and there is no single place to fix a definition. This is the "meaning not centralized" claim at the granularity of a column name.

The honest framing

Its break-even is exactly the precompute arithmetic of the periodic snapshot: read count versus rebuild cost. What it discards relative to a proper materialized view is three things: the refresh policy, the dependency tracking and the staleness contract (how old the data may be). Most of what goes wrong with OBT is the absence of those three, not the width.

Case, resolvedConstructed case · Nadia at Fernwood Outfitters

Three systems, three addresses, one auditor

After an acquisition, three source systems gave the same customer three addresses and an auditor asked what each system said and when, while the executives asked for one table with everything in it. Where it started: Binding styles →

The three systems. She loads each of the three systems into a Vault: a hub for customers keyed by hashed business key plus source system, and a satellite per source for addresses. Now the auditor can see all three addresses with their sources and load times. The choice "which address do we mail to?" becomes a visible rule in the presentation layer, written down once, instead of being buried in a loader. She builds a star on top, because analysts cannot be asked to join a dozen tables. She tells the executives plainly that the Vault is an addition: they pay for both.

The one big table. She does not choose between star and OBT by taste. She counts consumers. The executive dashboard has 60 regular readers with mixed SQL skill: she gives them a wide mart built at order-line grain, with a named owner, a nightly refresh and a documented staleness limit of 24 hours (the three things an OBT usually lacks). The finance team, who need as-was and as-is views, keep the star. She rejects anchor modeling for now, because the warehouse engine she uses does not guarantee table elimination.

The executive mart now shows every customer once, with the same name and the same definition of "customer" as everywhere else.