Grain, and the wrong number nobody notices

The grain of a table is what a single row means. One sentence:

One row per order line per order. One row per customer per day. One row per payment attempt.

If you cannot say it in that form, you do not yet know what your table is — and neither will anyone who queries it.

Why this is not pedantry

Take orders, one row per order line. Each row carries order_total, repeated across every line of that order.

Now sum order_total. You get a number. It is wrong — inflated by however many lines each order has — and it is wrong in a way that looks entirely plausible. Revenue is up. Nobody queries it. The mistake surfaces a quarter later when someone reconciles against finance.

This is the single most common serious error in analytics, and it is always the same shape: an aggregate applied at a grain other than the one the measure lives at.

The join that changes the grain under you

You join orders to shipments, one order having three shipments. Your order rows have just tripled. Every order-level measure in that result is now overstated by three, and nothing warned you.

The habit worth building: after every join, say the new grain out loud. If it changed, either that was on purpose or you have a bug. There is no third case.

Checking it

The grain claim is testable in one query:

SELECT customer_id, day, count(*)
FROM daily_customer
GROUP BY customer_id, day
HAVING count(*) > 1;

Zero rows means the grain holds. Anything else means it does not, and you have found it now rather than in a board meeting.

Run this against tables you did not build before you trust them. It is thirty seconds, and the answer is surprising more often than you would like.

Where the measure lives

Every measure has exactly one grain at which summing it is correct.

order_total lives at order grain. line_amount lives at line grain. Sum the one that matches the rows you have; if the grains differ, aggregate to the right level first and then sum.

State the grain in your table comments so the next person does not have to work it out — or, more likely, not work it out.