Grain, Facts, and the Arithmetic That Quietly Goes Wrong

A revenue figure on a dashboard is eleven percent higher than the one finance produced. Both queries look correct. Both run without error. The difference turns out to be a join to a shipment table: orders that shipped in two parcels appear twice, and their revenue is counted twice with them. Nobody wrote a bug. Someone joined two tables whose rows meant different things, and SQL did exactly what it was asked.

This is the characteristic failure of analytical data, and it has a single root: a row’s meaning was never pinned down, so combining rows produced arithmetic nobody intended. This article is about pinning it down — what declaring the grain commits you to, how the three shapes of fact table differ in what they can answer, which measurements you are allowed to add up and which you are not, and the specific mechanism by which a number silently inflates. The examples are invented.

The grain is a contract

The grain of a fact table establishes exactly what a single row represents. Not approximately, and not “sales data” — one row is one order line, or one shipment, or one account on one day. Kimball’s guidance calls declaring it the pivotal step in a dimensional design and describes it as a binding contract on that design, and the word contract is the useful part: it is a promise that constrains everything added later.

The rationale for declaring it early is stated plainly rather than left to taste. The grain must be declared before choosing dimensions or facts because every candidate dimension or fact must be consistent with it. Which converts an open-ended design discussion into a test that any proposal either passes or fails.

Kimball’s own illustration is the clearest way to see the test working. In a fact table at the grain of one retail sales transaction line, the quantity of a product sold and its extended price are good facts. The store manager’s salary is disallowed — not because it is uninteresting, but because it is not a property of one transaction line. The same principle is stated more generally: a fact table corresponds to a physical observable event, and not to the demands of a particular report.

That last sentence is worth keeping, because the pressure runs the other way constantly. Someone needs a number, the number is nearly available, and adding a column to an existing table is the cheapest-looking path. Two years of that produces a table with several grains living in it and no way to tell which rows carry which. The rule is explicit: different grains must not be mixed in the same fact table, and each grain results in a separate physical table. Two tables is the right answer more often than it feels like it is.

A practical corollary: model at the atomic grain — the lowest level at which the business process captures data — even when today’s reports are all summaries. An atomic table can produce any summary. A summary table cannot produce the detail, and the question that needs the detail always arrives.

Facts and dimensions, and why the split matters

The split between fact and dimension tables divides two kinds of information. Facts are the measurements that result from a business process event and are almost always numeric, with each row maintaining a one-to-one relationship to a measurement event. Dimensions carry the descriptive context — who, what, where, when, which version of the product catalog.

The reason this split is load-bearing rather than cosmetic is that the two behave differently under aggregation. Facts get summed, averaged, counted. Dimension attributes get grouped and filtered by. A number stored in a dimension is a trap for exactly this reason: it sits where nothing is expected to be added, and eventually something adds it.

The useful diagnostic when deciding where a value belongs is to ask what it is a property of. If it is a property of the event — this sale, this shipment — it is a fact. If it is a property of a thing that participates in many events — this product, this customer — it belongs in a dimension, however numeric it looks. A product’s list price is a property of the product; the price actually paid is a property of the sale, and those are different numbers that get confused with expensive regularity.

Three shapes of fact table

Most modeling arguments about “how should we store this” are really arguments about which of three shapes fits, and they differ in what a row represents and therefore in what questions they can answer.

	Transaction	Periodic snapshot	Accumulating snapshot
One row is	One measurement event	One entity over one standard period — a day, week, or month	One instance of a process, from start to finish
Rows arrive	When something happens	Every period, whether or not anything happened	Once, at the start
Rows change	No	No	Yes — revisited and updated as the process advances
Dates	One	One, the period	Several — one per milestone
Answers well	What happened, in any slice	What the state was at regular intervals	How long each step takes, and where things are stuck
Answers badly	The state on a given day, which has to be rebuilt from an opening balance and every later change	What individual events made up the period	What the process looked like at a past moment, since the row now holds the latest state

Two details from the definitions deserve emphasis because they are where the shapes actually earn their place.

For the periodic snapshot, the grain is the period, not the individual transaction, and a row is typically inserted even if no activity took place, containing a zero or null for each fact. That padding is the feature rather than waste. It makes the table uniformly dense in its foreign keys, so “average daily balance across all accounts” has a row for every account-day and does not silently exclude the quiet ones. A transaction table can answer it only indirectly: rebuild each account’s balance for each day from a trusted opening balance or earlier snapshot plus every transaction since, and generate the quiet days explicitly, because there a missing row means nothing happened rather than that a balance of zero was recorded. That is correct if the transaction history is complete, but it repeats the rebuild for every question. The snapshot does the work once and makes the question a simple read.

For the accumulating snapshot, the row is revisited and updated as the process moves through its milestones, which Kimball notes is unique among the three types. With a date key per milestone, the lags between steps become subtraction, which is why this shape is the right one for pipeline and fulfilment analysis. The cost is in the last row of the table above: the row holds current state, so reconstructing what the pipeline looked like last Tuesday is not something it does. If that question matters, it needs its own answer rather than a workaround on this table.

These are not competing options to choose between. A mature model usually has all three over the same business process, each answering the questions the others cannot.

What you may add up

Storing a number in a fact table does not make it summable, and the three-way distinction here is the most practically useful piece of vocabulary in dimensional modeling.

• Additive measures can be summed across any of the dimensions associated with the fact table — described as the most flexible and useful facts. A sales amount is the standard case.
• Semi-additive measures can be summed across some dimensions, but not all. Account balances are the canonical example: additive across every dimension except time.
• Non-additive measures cannot be meaningfully summed at all, and ratios are the example given.

The semi-additive case is the one that produces confident nonsense, because the query succeeds and the number is enormous. Adding January’s closing balance to February’s closing balance answers no question anyone has. Summing balances across customers on one day is correct; summing one customer’s balances across days is not. What is wanted instead is a value at a chosen point in time — a period-end balance — or an average across the period, and which one is intended has to be stated rather than assumed, because the two differ and both are defensible.

Non-additive measures carry a specific prescription worth following literally. Rather than storing the ratio, store the fully additive components of the non-additive measure and sum those components into the final answer set before calculating the final non-additive fact, typically in the BI layer or OLAP cube. Store the discount amount and the gross amount; let the discount rate be computed after aggregation. The reason is arithmetic rather than style: the average of per-row ratios is not the ratio of the totals, and a stored rate column invites AVG() over it, which produces a number that looks plausible and weights every row equally regardless of size.

A worthwhile habit follows from all three cases: record additivity as a property of each measure where consumers will see it, alongside the definition of the metric itself. “Balance — semi-additive, do not sum across time” in a column description costs one line and prevents a class of error that is otherwise invisible until a reconciliation.

Where double counting comes from

The opening example is the general case, and it is worth working through mechanically, because the mechanism is simple and recognizing it is most of the defense.

Suppose an orders table at the grain of one order, and a shipments table at the grain of one shipment, with orders sometimes shipping in several parcels. Join them and each order row is duplicated once per shipment. Nothing is wrong with the join — it is faithful to the data. But the order amount, which was one value per order, now appears several times, and summing it inflates the total by exactly the fan-out. This is double counting, and it follows from join cardinality: a one-to-many join multiplies the rows on the one side.

-- Inflated: order_amount is repeated once per shipment
SELECT SUM(o.order_amount)
FROM orders o
JOIN shipments s ON s.order_id = o.order_id;

-- Correct: bring each fact table to the report's grain (month and customer), then join on that grain
WITH ordered AS (
    SELECT DATE_TRUNC('month', order_date) AS month, customer_key,
           SUM(order_amount) AS total_ordered
    FROM orders
    GROUP BY 1, 2
),
shipped AS (
    SELECT DATE_TRUNC('month', ship_date) AS month, customer_key,
           COUNT(*) AS shipment_count
    FROM shipments
    GROUP BY 1, 2
)
SELECT COALESCE(o.month, s.month)               AS month,
       COALESCE(o.customer_key, s.customer_key) AS customer_key,
       o.total_ordered,
       s.shipment_count
FROM ordered o
FULL OUTER JOIN shipped s
  ON s.month = o.month AND s.customer_key = o.customer_key;

The second form generalizes into the rule that keeps cross-process reporting honest: do not join two fact tables directly. Kimball’s technique guidance puts it as a prohibition — a BI application must never issue SQL that joins two fact tables across their foreign keys, because the cardinality of the result cannot be controlled — and describes the alternative, drilling across, as producing a separate answer set from each fact table and combining them on common row headers. The detail that makes it work is the grain of those answer sets. Each fact table has to be aggregated to the grain of the final report — here, one row per month and customer — not merely summarized at its own storage grain. Aggregating orders per order and shipments per shipment and then joining would reproduce the same fan-out; only when both sides have exactly one row per shared key is the combining join one-to-one. The example assumes both tables carry a date and a customer key, and uses a full outer join so that a customer-month with orders but no shipments, or the reverse, is not dropped. Kimball’s framing of conformed dimensions is what makes this work — dimension tables conform when their attributes have the same column names and domain contents, and using conformed attributes as the row headers lets results from separate fact tables line up in one report.

That is the whole mechanism for combining orders and billing and support tickets in one view without inventing numbers: each aggregated separately to the same month-and-customer grain, aligned on those shared keys, never multiplied together. The conforming itself is described as being defined once in collaboration with the business’s data governance representatives, and reused rather than rebuilt — which is the essence of integration in this style of system, and also the point at which the work becomes organizational. Agreeing what “customer” means across three processes is a negotiation with a named owner on the other side of it, not a modeling exercise.

Two related sources of inflation are worth naming since they look different and behave the same. A dimension with more rows per join key than the join assumes — a customer table with duplicates from a botched merge, or a history-keeping dimension joined on the customer’s natural key alone, with nothing selecting the version that was in effect — fans out every fact joined to it. And a many-to-many relationship modeled as a direct join, where one fact legitimately relates to several dimension members, does the same unless the allocation is made explicit. In both cases the totals grow and nothing reports an error, which is why the only reliable detection is a reconciliation against a known figure rather than inspection of the query.

One cheap test catches most of it before anyone sees a report. After any join, compare the row count to the count before it. If a join to a dimension changed the number of rows, that dimension is not at the grain you assumed, and every sum downstream is suspect. For a dimension that keeps history by adding a row per change — the Type 2 technique — several rows for one product with different categories is the design working, not a duplicate, so the checks change shape: each version’s surrogate key is unique, one member’s effective date ranges neither overlap nor leave gaps, and every fact row resolves to exactly one version.

Questions that reveal the wrong choice

Question	If the answer is unclear
What does one row of this fact table represent, in one sentence?	Every aggregate over it is a guess, and errors do not surface as errors
Does every column here describe that one event?	Two grains are sharing a table and nobody can tell the rows apart
Is the table at the atomic grain, or only at the summary someone needed?	The first question needing detail forces a rebuild
Which measures are semi-additive or non-additive, and is that written down?	Balances get summed across time; rates get averaged
Are ratios stored, or computed from stored components after aggregation?	Averaged ratios weight every row equally regardless of size
Does any query join two fact tables directly?	Fan-out inflates the totals, silently and consistently
Is each dimension unique on the key facts join to — one row per member, or one row per version where history is kept?	Duplicate rows multiply every fact joined to them
Do the dimensions used across processes have the same names and contents?	Cross-process reports align on labels that do not mean the same thing

References

• Kimball Group, Declare the Grain
• Kimball Group, Facts for Measurement
• Kimball Group, Additive, Semi-Additive, Non-Additive Facts
• Kimball Group, Periodic Snapshot Fact Tables
• Kimball Group, Accumulating Snapshot Fact Tables
• Kimball Group, Conformed Dimensions