Order-to-cash could be modelled as transaction, periodic snapshot or accumulating snapshot facts — how do you choose?

## The three shapes answer three different questions For one business process — order to cash — the same events support three fact tables: | Shape | Grain | Answers | |---|---|---| | Transaction | one row per event (order line, shipment, invoice, payment) | what happened, how much, at what detail | | Periodic snapshot | one row per account or order per day/week/month | what was outstanding at period end | | Accumulating snapshot | one row per order line, milestones filling in | how long the pipeline takes, where work is stuck | Asked to "choose", the strong answer refuses the false choice and then explains sequencing and cost. ## Rule one: the atomic transaction table is not optional Everything else is derivable from atomic events; atomic events are derivable from nothing. If you build only a monthly summary because that is what today's dashboard shows, then next quarter's question — margin by channel by promotion — is unanswerable, and the history to answer it was never stored. Capture the atomic grain first, retain it, and treat it as the reconciliation baseline. It is also the audit record: derived tables are rebuildable, so they carry no independent obligation to be preserved. ## Rule two: add a periodic snapshot for position questions or unobservable state Two triggers justify the storage: - **Cost of reconstruction.** Deriving "balance at month end" by summing every movement since inception gets slower every year, and every consumer reimplements it. A snapshot makes it a row lookup and fixes the definition once. - **State that is never an event.** Revaluations, corrections, externally supplied levels and accruals may never appear as transactions. If the ledger only publishes balances, the snapshot is not a convenience, it is the only source. The cost is density: rows equal entities times periods, forever, regardless of activity. That is a capacity decision — period grain and retention window — and it should be made explicitly rather than discovered in a bill. ## Rule three: add an accumulating snapshot for cycle time and work in progress If the business steers on days-to-cash, on-time fulfilment or bottleneck analysis, an accumulating snapshot — one row per order line with a date key per milestone and stored lags — turns those from windowed self-joins into simple aggregates. If nobody asks those questions on a schedule, it is a mutable table you have signed up to maintain for nothing. ## Rule four: cost the derived assets honestly Each additional fact table is not just storage. It is a load to schedule, a set of tests, a definition that can drift from the atomic table, an owner, and a thing to fix at 3am. The governing question is not "would this be useful?" — everything is useful — but "which standing question does this answer, who asks it, and how often?" Three fact tables for one process is a legitimate mature design; three fact tables built speculatively before anyone asked is technical debt with a schedule attached. ## Rule five: derive, and reconcile Where the snapshots can be derived from the atomic facts, derive them — one source of truth, one place a definition lives. Then reconcile continuously: the snapshot's period-over-period change should equal the transactions in that period, and the accumulating snapshot's milestone dates should match the corresponding events. Publish the reconciliation as a test, because the failure mode of derived facts is silent drift, not a broken pipeline. Where derivation is impossible because the source only publishes state, say so explicitly in the documentation, since that table cannot be rebuilt from history if it is lost. ## Rule six: decide the consumption path with the model Three fact tables for one process means an analyst can compute revenue three ways and get three answers. Decide, and publish, which table is authoritative for which question, and route self-service tools at the intended one — typically a semantic layer or curated mart exposing measures rather than raw tables. Without that, you have not built three assets, you have built three versions of the truth. ## What a strong answer sounds like "Atomic transaction facts always, because they are the only thing I cannot reconstruct later. Then a daily snapshot if receivables position is a standing question or the ledger only gives us balances, sized by accounts times retention. Then an accumulating snapshot if order-to-cash cycle time drives a target. Each derived table gets an owner, a reconciliation test against the atomic facts, and a named consumer, or I do not build it." ## Common mistakes Presenting the three as mutually exclusive and picking one. Building the summary first because it is what the current dashboard needs, and losing the detail. Building all three up front on the assumption that a complete warehouse means every shape exists. And leaving three tables published with no statement of which is authoritative, which turns a modelling success into a trust problem.