Fact grain and measure additivity

Last updated: 6 Oct 20265 min read

tutorial

A fact grain states what one row represents; a measure is additive only across dimensions where summing preserves its business meaning.

Declare a row before the schema

A payment fact may be one row per captured payment, not per order or customer. An order with two captures must therefore have two payment rows. Store the stable capture ID and test its uniqueness. Joining order lines directly to captures creates a many-to-many multiplication unless each side is reduced to a compatible grain. Join cardinality is an executable contract.

Classify measures by direction

Captured cents can be summed across customers and days if corrections and currency are handled consistently. An end-of-day account balance can be summed across accounts for one day, but summing balances across days double-counts the same money; it is semi-additive. A conversion rate is a ratio and cannot be summed at all. Store numerator and denominator at a clear grain.

Separate transactions from snapshots

An event fact records that something happened at a time. A periodic snapshot records state at a cutoff. Mixing them in one table invites a dashboard to add balances to transaction amounts. If both are needed, publish separate fact tables with distinct names, keys and time columns. The run manifest should name the cutoff for each snapshot.

Handle corrections explicitly

A captured payment may later be reversed. Choose whether the fact contains a signed reversal event or a mutable current status, and define how finance totals use it. A silent overwrite can make last month change with no trace. A new correcting event retains the transaction trail but demands a clear netting rule. CDC carries the source mutation; the mart defines its analytical meaning.

Prove totals at two grains

Reconcile source captures to fact rows by capture ID, then aggregate fact cents by day and compare with the authorized ledger for the same cutoff and currency. Test a split capture, a reversal and a zero-activity day. A row-count match alone cannot detect duplicated amounts or wrong signs.

Implementation

captures = [
    {"capture_id": "cap-47", "order_id": "ord-9", "day": 47, "cents": 4200},
    {"capture_id": "cap-48", "order_id": "ord-9", "day": 47, "cents": 1700},
    {"capture_id": "rev-49", "order_id": "ord-9", "day": 48, "cents": -900},
]

def net_cents_by_day(rows):
    if len({row["capture_id"] for row in rows}) != len(rows):
        raise ValueError("capture grain violated")
    totals = {}
    for row in rows:
        totals[row["day"]] = totals.get(row["day"], 0) + row["cents"]
    return totals

assert net_cents_by_day(captures) == {47: 5900, 48: -900}

Performance and operating cost

One pass over N captures takes O(N) expected time and O(D + N) memory for D days plus the uniqueness set. Warehouse aggregation may push this work into the query engine, but a badly chosen grain creates incorrect results regardless of compute speed.

Common Mistakes

Do not sum periodic balances across dates.
Do not join facts at incompatible grains without a cardinality rule.
Do not call a rate an additive fact or average rates without denominators.