Skip to content
Pattern Draft

Process ledger fact

Lifecycle events, reinterpreted everywhere

An order moves through a lifecycle: placed, discounted, paid, shipped, returned. Those events arrive in different tables, at different grains, with different states. Every report that needs net sales rebuilds it by joining and filtering them.

One report forgets returns and overstates. Another reads the order's current status and, without meaning to, rewrites last month's history whenever an order changes.

Figures that never quite reconcile

  • Net figures differ by report, and each difference takes an afternoon to explain.
  • History moves when current state moves, so last month's number is not stable.
  • KPIs end up carrying modelling logic that belongs underneath them.
  • The pressure grows to precompute every KPI into one wide table, which then becomes hard to change or reuse.

One row per business event

Model a coherent business process as a ledger of atomic events at an explicit grain, where each event carries additive, signed observations with its provenance. Compose metrics and KPIs from that stable foundation instead of embedding the logic in each report.

The picture

A process ledger fact Order lifecycle events (placed, discounted, shipped, returned) flow into a ledger with one row per event for order line L1, each carrying a signed change in value: plus 40, minus 4, zero, minus 36, which sum to zero. From the ledger, metrics are composed: net sales is the sum of value changes and is additive; return rate is returns divided by gross sales and must be composed from sums rather than summed. Lifecycle events Order placed Discount applied Item shipped Item returned Ledger fact · one row per event LineEventDateΔ value L1placed3 Mar+40.00 L1discount3 Mar−4.00 L1shipped4 Mar0.00 L1returned11 Mar−36.00 Net for L10.00 Composed metrics Net sales Σ Δ value ADDITIVE Return rate returns ÷ gross sales RATIO: COMPOSE FROM SUMS
Every lifecycle event becomes an additive row. Net figures are sums, not report logic. Ratios are composed from sums.

An order line, start to finish

Illustrative example

Order line L1 is placed for 40.00, discounted by 4.00, shipped, then returned for the remaining 36.00. The ledger holds four rows. Net sales for L1 is the sum of the value changes: zero. No report needs to know the rules of returns to get it right.

Return rate is returns divided by gross sales. It is a ratio, so it is composed from the sums for whatever period and dimension are asked for, never added up from per-order rates.

When the business decides that net sales should exclude shipping charges, that is a change to the metric's definition. The ledger does not need to be rebuilt.

In detail

When it fits

Use it where several lifecycle events form one analytical process that people want to count, sum and reconcile over time: orders, payments, claims, subscriptions, stock movements.

Guardrails

  • Do not collapse different grains merely to make one table convenient.
  • Avoid nulls where the business provides a meaningful known value.
  • Use zero only where the measure is genuinely not applicable or the additive identity is semantically correct.
  • Keep changeable KPI composition separate from stable process facts.

Alongside other shapes

A ledger answers what happened, when, and what it added up to. A lifecycle snapshot answers where is each order now. Both can exist; the snapshot can be derived from the ledger rather than maintained as a second truth.

Not this

  • Not one universal table. The aim is a reliable foundation from which many metrics can be composed.
  • Not a place for targets or presentation logic.

Unresolved

  • Which events genuinely belong to the same process, and which only look related?
  • Who owns a ledger for a process that crosses several domains?

Join the argument

Agree, challenge or add evidence

Architecture improves when its assumptions are challenged. Say which kind of contribution you're making:

  • Challenge I disagree because…
  • Evidence We've seen this too…
  • Question How would this work when…?
  • Alternative Another way to approach this…
  • Extension This also implies…

Please keep clients, employers and colleagues unidentifiable. Strong contributions may be quoted and credited on this site. Read the contribution guidelines and the privacy notice.

Comments are provided by giscus and stored on GitHub. Posting requires a GitHub account.

Next in the argument Semantic layer Definitions live in report formulas, so buying a semantic-layer product looks like the fix. Put computation where it belongs

Connected thinking

Direction · draft Put computation where it belongs Recurring analytical work belongs in the data product's serving model and the query layer, not in each consumption tool. Concept · draft Measure, metric and KPI Separate what a number means, how it is evaluated, what it is judged against and how it is shown, and give each its own authority. Pattern · proposed Composable data product ecosystem Principle · draft Semantic authority is independent of consumption Business concepts and metrics need governed identities that exist independently of every tool that consumes them.