Grain: one row equals one what?

How do you declare what one row is, and why should you store the finest grain the source emits?

CaseConstructed case · Nadia at Fernwood Outfitters

Monday counted twice

Nadia opens the table behind the marketing dashboard, daily_sales. It looks like this:

day	product	revenue
Mon	tent	400
Mon	stove	100
Mon	ALL	500
Tue	tent	300

The dashboard runs SELECT SUM(revenue) FROM daily_sales. Monday contributes 400 + 100 + 500 = 1,000, but Monday's true revenue is 500. Some days have an ALL subtotal row, some days do not. Nobody remembers why. This is part of the 14%. In the meeting someone says: "We should normalize this table." Someone else says: "No, denormalize it, joins are slow." She suspects both statements are out of date.

How it ends →

Grain means: one row equals one what? Everything else in this mechanism is scaffolding around that sentence.

The test for a grain declaration is syntactic, so anyone can apply it:

A clean declaration reads "one row per shipment line per shipment" and nothing more.
If the declaration needs an "and" joining two different things, or an aggregate word ("per customer per month, plus totals"), the table has mixed grain. Sums over it will double count.

daily_sales has mixed grain: its rows are "one per product per day" and also "one per day, all products". That is the double count in Nadia's first number.

Two consequences follow mechanically:

Grain determines dimensionality. The set of foreign keys that together identify a row is the grain, restated. For daily_sales the identifying set is (day, product).
Grain is lossy downward only. You can aggregate a fine grain into a coarse one at query time. You cannot recover a fine grain from a stored coarse one at any price.

So the decision is asymmetric. Coarsening saves storage that is linear in row count. It costs an unbounded amount later, because "unbounded" here means a remodel, plus a backfill, plus a restatement of every published number.

Now compare prices. Object storage costs roughly $0.023 per GB per month. If one order-line event takes about 100 bytes, 1 billion events are about 100 GB, so about $2.30 per month. A day of human time spent on a remodel costs far more than years of that storage. Therefore: store at the grain the source emits, and aggregate later.

Note what kind of claim this is. It is economic contingency, not a permanent law. In 1995, storage was so expensive that the arithmetic pointed the other way. That is why the older dimensional-modeling literature is full of "build aggregates first" advice that sounds wrong now. It was not wrong. The prices moved.

Aggregate tables and aggregate navigation. Aggregates still exist, as performance tools. The correctness rule belongs to modeling: a summary must be drillable to its base grain. That means every aggregate row can be traced to the base rows it sums, with the same dimension definitions, and a query tool that chooses between the base table and an aggregate must give the same answer from either. An aggregate that cannot be reconciled with its base is just a second, competing definition.