Three Kinds of Fact Tables: Transaction, Snapshot and Accumulating
Most facts fit one of three patterns. Picking the right one decides whether questions like 'what was the balance on the 1st?' are easy or impossible.
Why the type matters
A fact table records measurements at a declared grain. Kimball-style modelling recognises three common shapes. Each answers different questions cheaply, and choosing the wrong one makes some questions expensive or impossible.
| Type | One row per | Typical measures | Rows change? |
|---|---|---|---|
| Transaction | event (order line, payment, click) | amount, quantity | No — insert only |
| Periodic snapshot | entity per period (account per day) | balance, stock on hand | No — new row each period |
| Accumulating snapshot | process instance (one order's lifecycle) | dates and durations per milestone | Yes — updated as milestones happen |
Transaction facts
The default. One row per business event at the finest grain you can get.
-- fact_order_line: one row per order line
order_line_id, order_date_key, customer_key, product_key, quantity, line_amountFully additive: you can SUM(line_amount) across any dimension. But "what was each customer's open balance on 1 March?" requires replaying every event up to that date.
Periodic snapshot facts
One row per entity per period, capturing state as of the end of the period.
-- fact_account_daily: one row per account per day
snapshot_date_key, account_key, closing_balance, transactions_countSnapshots trade storage for speed: point-in-time questions become a simple filter on snapshot_date_key.
Accumulating snapshot facts
One row per instance of a process with a clear start and end, updated as it moves through milestones.
-- fact_order_fulfilment: one row per order, updated in place
order_key, ordered_date_key, packed_date_key, shipped_date_key, delivered_date_key,
hours_to_ship, hours_to_deliverRendering accumulating snapshot milestones
Great for pipeline and lag analysis ("average hours from order to ship"), but it requires updates — use MERGE and keep it idempotent.
How to choose
- Need every event or flexible slicing? Transaction fact.
- Need state at regular points in time? Periodic snapshot.
- Need durations between known milestones? Accumulating snapshot.
Many warehouses use all three for the same domain: transaction facts as the source of truth, with snapshots derived from them for fast reporting.
Finished this note?