Grain: What One Row of a Fact Table Means

An order with four lines and a twenty-dollar shipping charge produces four fact rows. Sum the shipping column across those rows and the answer is eighty dollars. Nothing in the schema objected: the column is numeric, the rows are rows, and the aggregate is the one the query tool offers by default. The error is not in the query. It sits in a decision made before any column was named, namely what one row of the table is supposed to represent.

That decision is the grain, and in dimensional design it is stated as a sentence of the form “one row per X per Y”. A fact table whose grain has not been written down does not have an implicit grain; it has whatever grain the loader happened to produce, which changes when the loader changes.

The declaration that has to come first

Dimensional design proceeds in four steps: choose the business process, declare the grain, identify the dimensions, identify the measures. The order is not stylistic. Each step narrows the next.

Choosing the process fixes the subject — order fulfilment, not “sales” in general. Declaring the grain fixes the population of rows — one row per order line, not one row per order and not one row per line per day. Only with the population fixed can the dimensions be identified, because a dimension may appear only if its key is functionally determined by the grain. A customer dimension is legitimate on a line-grain fact table because the order line belongs to exactly one customer. A warehouse-location dimension is not, if an order line can be split across two warehouses during fulfilment; forcing it in either duplicates the line or loses the split.

Measures come last because additivity is a property of the grain, not of the column. Quantity is additive at line grain. Extended price is additive at line grain. A discount percentage is not additive at any grain, and a balance is additive across accounts but not across time. Placing a measure on a table whose grain contradicts its additivity produces numbers that are wrong in a way no schema can flag.

Additivity is inherited, not chosen

A measure is additive over a dimension if summing it across that dimension’s members preserves meaning. Revenue summed across all stores in a region is the region’s revenue. A headcount summed across stores is not the region’s headcount, because one employee can appear in two stores’ rows; headcount is semi-additive — additive across some dimensions, not across others, and never additive across time. Inventory balance behaves the same way: the balance at the end of each day cannot be summed over a month.

Ratios are the common trap. Storing a unit price or a margin percentage on each row and then averaging the column gives every row equal weight, so a line with one unit counts as much as a line with ten thousand. The correct form is to store the numerator and denominator as separate additive measures and divide after aggregation:

-- weights rows by their own volume: the ratio of sums
SELECT
  SUM(extended_price) / NULLIF(SUM(units), 0) AS avg_realized_price
FROM fact_sales_line
WHERE order_date_key BETWEEN 20250101 AND 20250131;

-- equal weight per row: usually not the question anyone meant
SELECT AVG(unit_price) FROM fact_sales_line WHERE ...;

NULLIF matters because a denominator of zero is possible on a return line, and division by zero aborts the query in engines that follow the SQL standard’s error behaviour rather than returning null.

Distinct counts are the other class that never aggregates upward. The number of distinct customers who bought on any day of a month is not the sum of the daily distinct counts; it is at most that sum and usually far less. A fact table can support the monthly figure only by counting distinct keys from the underlying rows, which means the query has to reach the grain the table actually stores.

Fan-out: joining at the wrong level

The shipping charge in the opening example is a header-level measure on a line-level table. Three designs handle it, and only one of them is free.

The first is to repeat the header value on every line, accepting that any sum over it is inflated by the number of lines per order. This is tolerable if the column is never summed — if it exists only to be displayed next to a line — but the display case is rare, and the column will eventually be summed by someone who reads the name and not the grain.

The second is a separate fact table at header grain, sharing the same dimension keys. Order-level charges live there, line-level revenue lives on the line table, and a report that needs both aggregates each table at its own grain and combines the results by dimension. This is the standard resolution, and it is why a warehouse commonly holds several fact tables for one business process. Aggregating separately and combining is what the shared dimension keys make possible; without them the two tables cannot be added up at all.

The third is allocation: divide the header value across lines so the parts sum to the whole. Allocation needs an arithmetic rule and a residue rule. Splitting 20.00 across lines of 12.00, 30.00 and 8.00 in proportion to extended price gives 4.80, 12.00 and 3.20. Splitting 20.00 across three equal lines gives 6.666…, and rounding each share to cents leaves a cent unassigned. The usual fix is to round every share down and add the remaining cents to the largest line, so the allocated column sums exactly to the header value. Without that step, a report that compares allocated charges to the billing system disagrees by a few cents per order, and the discrepancy grows with order count.

A related fan-out appears when two fact tables are joined directly rather than aggregated first. Joining a line-grain table to another line-grain table on order_id alone matches every line of an order to every other line of the same order: an order with four lines contributes sixteen rows. The join is legal SQL and the resulting sum is meaningless. The check is cheap — compare COUNT(*) before and after the join, and compare it to the count of distinct grain keys — and it belongs in the query review rather than in a comment.

Where the grain is written down

The grain has two physical homes. The first is the fact table’s key: the combination of dimension foreign keys plus any degenerate dimension, which is a business key with no attributes of its own and therefore no dimension table. An order number that appears on line rows but carries no descriptive columns is a degenerate dimension, and it belongs in the fact table as a column.

The second home is a uniqueness constraint over that key combination. In row-store engines this is a primary key that the database enforces, so a loader that emits the same line twice fails instead of silently doubling revenue. In several analytical engines the primary key is declarative only: it is recorded in metadata, used by some optimizers, and not checked on write. There the constraint documents intent and the loader must enforce the grain itself, typically by deleting the affected grain keys before inserting or by merging on the key.

Neither home is the grain statement itself. A table with a correct key and a wrong loader has the right shape and the wrong contents, and the only defence is an assertion in the pipeline that compares row counts to the count of distinct grain keys in the source.

What a grain makes unanswerable

A grain is a resolution, and resolution is lossy. A fact table at daily account grain cannot answer what a balance was at 14:32, and no amount of query ingenuity recovers it. A fact table at order grain cannot answer which warehouse shipped each line, because the order has one row and the shipment has several.

The asymmetry matters more than the loss. Refining a grain later is possible but expensive: the history has to be reloaded at the finer grain from a source that still retains it, and if the source has already been purged, the finer history does not exist anywhere. Coarsening is cheap by comparison, because aggregation is always available from a finer table. The practical rule that follows is to declare the finest grain the source can support and the business can justify, since a coarse table can be derived from it but not the reverse.

Grain also determines which of the three fact table types is being built. A row per transaction event is a transaction fact table. A row per account per day, with balances, is a periodic snapshot. A row per order, updated as the order moves through picking, shipping and delivery, is an accumulating snapshot, and its grain is stable while its contents change. All three can describe the same process at different resolutions; none of them substitutes for another.

The grain statement costs one sentence and constrains everything downstream: which dimensions attach, which measures may be summed, what a join is allowed to do, and which questions the table will refuse. Getting it wrong is not a modelling error that shows up as an awkward query. It shows up as a plausible number that is off by a factor nobody can explain.