Definition
Table grain is the level of detail at which a table records its data, stated as what one row represents. It is usually a combination of things: "one row per day, platform, account and campaign" is a grain, and so is "one row per customer per month". A table has one grain, or it should; a table that mixes daily rows and monthly adjustment rows will be summed wrongly sooner or later.
In the guide to designing a marketing data model, each business process gets a fact table at its natural grain, the level at which that process actually records events, with the grain written at the top of the table's documentation. A finer table can sit beside a coarser one when an analysis needs more detail, but each keeps its own stated grain.
Why mismatched grain double-counts
Two mistakes account for most grain errors. The first is counting rows at the wrong grain: counting rows in an order-lines table counts lines, not orders, and a total of daily users summed across a month counts returning people more than once. The second is joining tables at different grains. When a key appears once on one side of a join and several times on the other, every row on the first side is repeated for each match, so a customer-level value joined to a session-level table is multiplied by the number of sessions per customer. Neither mistake stops the query. The tutorial on checking AI-written SQL puts the grain check before every other check for this reason, and compares distinct counts with row counts at each step to show where duplication starts.
The usual cure is to aggregate each table to the grain of the join before joining, so both sides have one row per key. The tutorial on building an attribution model in SQL lists joins at the right grain among the SQL patterns a reader needs before touches, sessions and orders are combined.
In practice: writing the grain down
Grain belongs in three places. In a query, the analyst writes down what one row represents at each step and checks that the final measure matches the final grain. In a metric definition, the grain sits beside the formula, filters and owner: the guide to the metrics layer and metric definitions states the grain a metric is computed at and the grains it can be rolled up to, since spend and revenue add up freely while ratios and distinct counts must be recomputed rather than summed or averaged. In a model, the grain is the time step and unit every row shares; the tutorial on building a growth model in a spreadsheet names it at the start, usually the calendar month to match the cohort table, alongside the output measure and the horizon.
A storage firm's revenue per customer halves after a grain fix
Nornisk Self Storage reported average revenue per customer of £186 a month, about double what its unit prices suggested. The analyst checked the query. It joined an invoices table, one row per customer per month, to an access log, one row per customer per day on which they opened their unit, on customer and month. Customers visited on about two days a month on average, so each invoice row was repeated once for every visit day. Counting distinct invoice IDs gave 1,410; the joined table had 2,830 rows. The analyst aggregated the access log to one row per customer per month, with a count of visit days, before joining. Revenue per customer fell to £93, matching the invoices, and the grain of both tables went into the query's notes.
Related terms
A Data WarehouseA database designed for analysis that stores data loaded from many systems, such as analytics, ad platforms and the CRM, so it can be joined and queried. holds the fact and dimension tables whose grain the analyst must know before joining them. CardinalityThe number of distinct values a dimension or field holds; high-cardinality dimensions, such as page paths with IDs, can exceed report row limits. describes how many distinct values a field holds, and a key whose values repeat on one side of a join is exactly what makes rows multiply. A CohortA group of customers who share a starting event and period, tracked together over time. table is built at a chosen grain, such as customers grouped by their first month, and the models that read it have to share that grain.
Common confusions
- Grain is the same as granularity in a report. A report's date range or breakdown is a viewer's choice; table grain is fixed by what each stored row represents.
- A query that runs is a query that counts correctly. Grain errors produce clean, plausible numbers; only a check of row counts against distinct counts, or a reconciliation to an independent total, shows them.
- Finer grain is always better. Finer tables answer more questions but are easier to over-count; the right grain is the one the process records and the question needs.
- Ratios can be averaged across rows. A rate computed per day cannot be averaged into a monthly rate; recompute it from the monthly totals.
- Grain only matters to data engineers. Anyone who counts, sums or joins data, including in a spreadsheet, depends on knowing what a row represents.
