What one row stands for

Why is the first question to ask about a table what a single row of it actually represents?

Given a table to design, most people begin with columns: what do we need to know about a sale? Date, product, store, quantity, amount, discount, customer.

But a column list quietly presupposes something it never states — that everyone agrees what a row is. And "a sale" is at least four different things. One row per scanned item? Per receipt? Per shipment? Per day per store? Every one of those is a defensible reading of "a sale", they produce different tables, and a column that is perfectly sensible under one is meaningless under another.

So the claim to test is that this question comes first, before the columns and before the dimensions. Why should the order matter that much?

Take the discount column. If a row is one scanned item, a discount is the money off that item. If a row is one receipt, is it the sum of item discounts, or a basket-level voucher, or both? If a row is one store-day, a discount column has no clear referent at all — discounts happened, but not to the thing the row describes.

Notice what happened. The meaning of a column is not a property of the column. It is a property of the column relative to what the row is. Which is why the grain has to be settled first: until it is, no column can be given a definition, and any definition offered is really a guess about the grain smuggled in.

The same is true of the dimensions you can attach. A row that is one receipt cannot carry a product — a receipt has many products. A row that is one store-day cannot carry a customer. You can only attach a dimension whose value is singular at the grain, and so the grain determines the shape of the whole table, not just its size. This is why Kimball's dimensional design method puts declaring the grain as the second of four steps, before identifying either dimensions or facts: both of the later steps are answerable only once it is fixed.

Now the deeper reason, which is about what you can ask later. Suppose you choose the coarse grain — one row per store per day — because that is what the current report needs. It is smaller and faster. Then someone asks which products are bought together. The information is not merely inconvenient to retrieve; it is gone. Aggregation is one-way. Every question you might ask of the fine grain can be answered by summing it, and no amount of cleverness recovers detail from a total. That is the argument for storing the atomic grain: not that detail is always needed, but that choosing coarseness is choosing which future questions to make unanswerable.

Then the failure that actually hurts people. What goes wrong if a table contains a mixture of grains — most rows one item, but a shipping charge stored as its own row for the whole order?

Sum the amount column and the total is right. Count the rows and the item count is wrong. Average the amount and the answer is meaningless. The table looks fine, every constraint passes, and every consumer of it is silently computing something slightly different from what they intended. Mixed grain is the single most damaging modelling error precisely because nothing complains.

And a related trap that has nothing to do with mixing. Averages do not aggregate. Consider four stores with 900, 100, 800 and 200 transactions and average baskets of 20, 60, 25 and 55. The average of those four averages is 40. The true average basket across all 2000 transactions is 27.50 — because the two stores with high averages have very few transactions. The mistake is grain: an average computed at store grain cannot be averaged again at chain grain. Store the additive components — total value and transaction count — and derive the ratio at whatever grain the question asks for.

An honest note on where the rule bends: aggregate tables at coarser grain are legitimate and common, built for speed. What makes them safe is that they are derived from an atomic table that still exists, and are labelled as such. The danger is not coarse data; it is coarse data that has replaced its source.

Three refinements.

First, the grain must be stated in the business's language, not the database's. "One row per primary key" says nothing; the key was chosen to enforce the grain, not to define it. "One row per scanned line on a completed receipt, excluding voided lines" is a testable sentence — someone can look at a row and say whether it obeys.

Second, the grain is a constraint you can actually enforce, and should. If a row is one scanned line, then the combination that identifies a scanned line must be unique, and a duplicate is a defect rather than a curiosity. Most double-counting incidents are grain violations that nobody was checking for.

Third, know your measure types. Some facts add across every dimension. Some are semi-additive — an account balance adds across accounts but not across days, since summing Monday's and Tuesday's balances is nonsense. And ratios, percentages and averages are non-additive: they must be recomputed from their components at the required grain, never summed or averaged upward.

• Factless fact tables, where the grain records an event with no measure at all.
• Aggregate navigation: serving coarse summaries safely while keeping the atomic table authoritative.
• Semi-additive measures and why period-ending balances need their own handling.

Every term the collection defines is gathered in the glossary.