One Big Table: the wide denormalised model, and the bill it sends later

Also called: OBT, the wide table, the flat table, full denormalisation

The fastest model to build and the easiest to query, chosen honestly by teams who know exactly what they are trading away — and chosen by accident by teams who do not.

What it is

One Big Table means exactly what it says: one wide table per subject, with every join already done. Customer name, product category, store region and the order line itself all sit on the same row, so the person asking the question never writes a join and never has to understand a key.

It has a real following, and for good reasons. Columnar engines — BigQuery, Snowflake, Redshift, a clustered columnstore in SQL Server — only read the columns a query touches, so a table three hundred columns wide costs nothing extra for a query that reads four of them. Meanwhile the join that a star schema does at query time was done once, at load time.

The critical word is per subject. One wide table for orders is a methodology. One wide table for the entire business is the thing people are picturing when they tell you OBT does not scale, and they are right about that one.

How the tables are actually shaped

What a row looks like

WideSales
  SalesKey             BIGINT
  OrderDate            DATE
  OrderNumber          NVARCHAR
  Quantity, LineTotal, UnitPrice
  -- everything below is repeated on every row of that customer's orders
  CustomerNumber, CustomerName, CustomerCity, CustomerSegment
  ProductCode, ProductName, SubcategoryName, CategoryName
  StoreCode, StoreName, RegionName

The load walks the source’s foreign keys outward from the transaction table, pulling each referenced table’s columns in and naming them after where they came from. It is a star schema with the joins resolved in advance and the dimension tables thrown away.

It is rebuilt, not updated

There is no incremental load worth the name and no history. The table is cleared and rebuilt each run — truncate and reload — which is the trade the methodology openly makes. A row is a snapshot of what everything looked like when the load ran.

That also makes it safe to rebuild wholesale: nothing points at a wide table, so there are no foreign keys to break and no surrogate keys anybody else is holding.

The one thing to record properly

Because every pulled-in column is named after the table it came from, it is tempting to work out later where each one belongs by reading its name. A name says which table a column came from; it never says what to join on. Record the path — which table, through which key, in which order — when the wide table is designed, or the next person rebuilding it is guessing.

When it fits, and when it does not

OBT is a genuinely good answer to a narrow question and a genuinely bad answer to a broad one. The width of the question is the whole test.

Those two lists are Netune’s own judgement of this methodology, copied from the rule file it scores a real database with.

What it costs

The mistakes that are expensive

One table for everything, rather than one per process

The usual way this goes wrong. Sales, support tickets and inventory get merged into one enormous table because they all mention customers, and now every row has two thirds of its columns empty and nobody can say what one row means. One wide table per business process; if two processes have different grains, they are different tables.

Correcting a customer’s name

In a star schema that is one row of one dimension. In a wide table it is every row that customer ever appeared on — and if the table is rebuilt from the source anyway, it is a source-system fix plus a full reload. Cheap if the rebuild is nightly and routine; painful if the table took six hours to build.

Throwing away a star that was already there

If the source already has clean facts and dimensions, flattening them into a wide table discards a model somebody already paid for, and buys query simplicity that a BI tool would have hidden anyway. OBT earns its place where the source is a pile of unrelated extracts, not where it is well modelled.

How it compares

One Big Table or a star schema?

The trade is correction and history against joins. A star lets you fix a customer once and keep history per dimension, at the price of a join per dimension — which every BI tool writes for you anyway. OBT removes the joins and removes both of those abilities. For one process with a nightly rebuild, OBT is defensible. For several processes sharing customers, the star wins on almost every axis.

One Big Table or Activity Schema?

Both are single-table models, and they answer opposite questions. OBT is wide and holds one row per transaction with everything about it. Activity Schema is narrow and holds one row per thing that happened, in sequence. If the questions are “how much, by whom”, OBT. If they are “what happened before what”, Activity Schema.

Is OBT the same as a gold table in Medallion?

Often, in practice. Plenty of gold layers are wide denormalised tables, which means those teams have chosen OBT as their model without naming it — and inherited its trade-offs without discussing them.

Questions people ask