One Big Table, and the join that lies

There is a recurring argument in every warehouse I have worked on. One side wants a star schema: facts in the middle, conformed dimensions around them, joins at query time. The other wants One Big Table: flatten everything into one wide, denormalised table and let the columnar engine sort it out.

The OBT case is genuinely strong, and it is stronger than the modelling purists admit. Columnar storage only reads the columns a query touches, so width is close to free. There are no joins to get wrong. An analyst with a spreadsheet habit can self-serve without understanding cardinality. And on a lakehouse, the cost of a wide scan is often lower than the cost of a shuffle.

I use OBT deliberately, in the gold layer, and I would do it again. But it has one failure mode that is quiet, common, and expensive.

The join that lies

To build a wide table you join things together. The moment one of those joins is one-to-many, the rows on the "one" side are duplicated, once per matching row on the "many" side. That is not a bug in the join. It is what a join means.

The bug is what happens next: somebody sums a column that came from the "one" side.

A production order has an order quantity. It also has a routing: several operations, each a row. Join them and the order quantity appears once per operation. SUM(order_qty) now returns the true quantity multiplied by the number of operations, and it returns it confidently, with no error, in a number that looks entirely plausible.

Nobody makes this mistake on purpose. They make it because the wide table presents both columns identically (same table, same row, same look) while one is additive at that grain and the other is not.

One grain per table

The rule that actually prevents it is not "avoid OBT". It is:

A table has exactly one grain, and every additive column in it is additive at that grain.

A wide table at operation grain may carry operation minutes, operation setup, operation scrap. It must not carry order quantity, because order quantity is not a property of an operation. If you need both, you need two tables, or a column named so plainly that no one can mistake it, order_qty_repeated_per_op, which is really just documentation compensating for a modelling decision.

This is the same idea as the gold layer being a set of answers rather than a general-purpose warehouse. An OBT is a good gold table precisely when its grain is the grain of the question. "Minutes by cell by week" is a question with a grain. "Everything about production" is not.

Where each one earns its place

	Star schema	One Big Table
Best when	slicing is open-ended	the question is known
Grain	one fact table per grain	one table per question
Joins	at query time	done once, upstream
Fan-out risk	visible in the model	hidden in the columns
Semantic layer	relationships do the work	measures must defend themselves
Rebuild cost	low	cheap to throw away and redo

The pattern I settle on is boring: conformed, normalised tables in the middle layer where the grain is honest and the relationships are explicit, and OBT in gold, one per question, rebuilt rather than patched when the question changes.

The tell

If you inherit a wide table and want to know whether it is safe, do not read the DDL. Run this:

select count(*), count(distinct business_key)
from the_wide_table;

If those two numbers differ, the table has fan-out in it. That is not automatically wrong. It is the correct shape for an operation-grain table. But it means at least one column in there will lie to SUM, and it is worth finding out which before someone puts it in a board pack.