Fact Table Grain and Aggregation

In an illustrative scenario, 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 a common root is that a row’s meaning was never pinned down, so combining rows produced arithmetic nobody intended — though declaring the grain does not by itself stop a badly written join. A declared grain constrains which measurements belong together, which can be added, and which joins preserve their meaning.

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 of what it recorded — not of data it never captured, such as a campaign that was not collected or history that was overwritten. A summary table cannot produce the detail, and questions that need the detail tend to arrive.

Facts and dimensions, and why the split matters

The split between fact and dimension tables divides two kinds of information. Facts describe measurements at the declared grain and are almost always numeric. A transaction fact row corresponds to a measurement event; a periodic snapshot summarizes measurements over a standard period, and an accumulating snapshot follows a process instance through its milestones. 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 therefore a normal choice that needs care: it sits where nothing is expected to be added, and a tool that sums it by default produces nonsense.

The useful diagnostic when deciding where a value belongs is to ask what it is a property of at the declared grain, and how it will be used. A value measured by the event — the price actually paid on this sale — is a fact. A value describing a thing that takes part in many events — a product’s list price — can be a dimension attribute when it is used to filter and group (products listed under 20), a fact when it is used in calculations at the event’s grain (list price times quantity, beside the price paid), or both; Kimball’s guidance on numeric values treats list price exactly this way. What goes wrong is not the numeric attribute itself but a sum over it at a grain where it repeats. The list price and the price paid are different numbers, and they are easily confused.

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	Not in normal loading; corrections and late data follow an explicit policy	Not in normal loading; a period can be regenerated when late data arrives	Yes — revisited and updated as the process advances, as its normal loading
Dates	Usually one main event date, though others such as the recording date can sit beside it	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	Past states the milestone dates do not record — repeated steps, earlier attribute values, what was known when — since the row 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. What that row holds on a quiet day depends on the measure and on what is known. A flow, such as deposits made, is zero only when the complete feed for that day arrived and shows no activity; a level, such as the balance, carries the last known state forward when there is evidence nothing changed it; and a day whose data has not arrived is null or flagged as unobserved rather than zero — missing data is not the same as no activity. Filling the balance with zero on a day without transactions would turn a balance of 100 into 0 and halve a two-day average. Done that way, the 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 day each account was open 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 fulfillment analysis. The cost is in the last row of the table above: the row holds current state. Its milestone dates still answer some past-state questions — an order picked on 4 March and shipped on 6 March was picked but unshipped on 5 March, and an order whose shipped date is still empty has no shipment recorded yet, which means not shipped only if the table’s contract says an empty date means the milestone was not reached and the data is known to be current — but repeated steps, attributes updated in place, and what was known at the time are gone. If those questions matter, they need their own structure, such as the milestone events kept as transactions or a periodic snapshot of pipeline state.

These are not competing options for one slot. Which a process needs depends on the questions asked of it: a process with clear milestones and questions about duration benefits from an accumulating snapshot, one that is never finished may not, and a model can hold two or three over the same process, each answering 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 accounts on the same date, provided they share a currency and no account is counted twice, but not across 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 and presenting the total as a balance answers no question anyone has, although the same sum is a legitimate intermediate step as the numerator of an average. Summing balances across customers on one day is correct, as long as the currency is the same and a joint account is not assigned to each of its holders; summing one customer’s balances across days is not a balance. 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, and it comes with a condition. Where possible, 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 condition is that the components really are additive: a conversion rate over distinct users has a numerator and denominator that cannot be summed across days — visitors {A, B} and {A, C} on two days, with A converting on both, give 2/4 from summed daily counts but 1/3 for the period — so it has to be recomputed from the detail or from a combinable set of identifiers. The reason for the prescription 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 rule is a defense for BI tools that generate SQL without knowing how many rows each key matches. A direct join between two fact tables is not wrong in itself: if both are at one row per order and the keys match, the join is one-to-one and preserves each total. The danger is joining detail rows through shared dimension keys, where each side has many rows per key. 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. It also assumes the grouping keys are never null. A null customer key does not equal another null in an equality join, so an unknown customer’s orders and shipments in one month come back as two rows rather than one. Either give unknown members a real key, such as an unknown-customer row in the dimension, or, if the report deliberately groups unknowns together, compare with a null-safe operator such as IS NOT DISTINCT FROM — which groups them for reporting without claiming they are the same customer. The same applies to a missing month. 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, so detection has to look at the data rather than the query: a reconciliation against a known figure, and the direct checks below.

Some cheap checks catch much of it before anyone sees a report. Comparing the row count before and after a join is a first signal, but only a signal: a fact that matches twice and another that matches nothing leave the count unchanged while the total moves from 150 to 200, and a count that falls may only mean an inner join dropped unmatched facts. The direct checks are the number of matches for each fact key, the keys that found no match, the dimension’s uniqueness on its join key, and the totals of the measures that should be preserved. 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 within the period the model says it covers, and every fact row resolves to exactly one version.

Questions that reveal the wrong choice

Question	If the answer is unclear, check
What does one row of this fact table represent, in one sentence?	Whether aggregates are silently wrong: test the key’s uniqueness at the grain assumed, and reconcile totals to a known figure
Does every column here describe that one event?	Whether two grains are sharing the table with no way to tell the rows apart
Is the table at the atomic grain, or only at the summary someone needed?	Whether questions needing detail would force a rebuild, and whether that detail still exists anywhere
Which measures are semi-additive or non-additive, and is that written down?	Whether balances are being summed across time or rates averaged
Are ratios stored, or computed from stored components after aggregation?	Whether stored ratios are being averaged, weighting every row equally regardless of size
Does any query join two fact tables directly, and is the match one-to-one?	Whether fan-out is inflating the totals: count the matches per key on each side
Is each dimension unique on the key facts join to — one row per member, or one row per version where history is kept?	Whether duplicate rows are multiplying the facts joined to them
Do the dimensions used across processes have the same names and contents?	Whether cross-process reports are aligning 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
• Kimball Group, Multipass SQL to Avoid Fact-to-Fact Table Joins
• Kimball Group, Drilling Across
• Kimball Group, Numeric Values as Attributes or Facts
• PostgreSQL Documentation, Comparison Functions and Operators