Transaction Fact Table
A transaction fact table records individual business events. The Kimball Group’s definition is that a row “corresponds to a measurement event at a point in space and time”: one sale line, one payment, one shipment, one click.
Its defining properties
- Rows exist only when something happens. A product that sells nothing today has no row today. The Kimball Group notes that such tables can therefore be dense or sparse depending on activity.
- The grain is the event. Every numeric fact on the row must describe that one event — the quantity and amount of this sale line, not the store’s monthly target.
- Keys to its context. Foreign keys to each related dimension, optionally a precise timestamp, and often an identifier such as an order number carried directly on the row.
- Usually written once. An event that happened does not change, so a common design records corrections as further events — a cancellation or a reversing entry — rather than as edits to the original row. That is a correction policy rather than part of the definition: a transaction table that fixes a wrong amount in place under a stated policy is still at transaction grain.
At the atomic grain — the lowest level at which the process captures data — the Kimball Group describes transaction fact tables as offering the most dimensional expressiveness, because a later question can often be answered by aggregating the detail — within what was captured: attributes, history, and time interpretations that were never recorded cannot be recovered from it.
What it answers, and what takes more work
| Question | How a transaction table handles it |
|---|---|
| What happened between two dates, sliced any way? | Directly: filter by the event’s time and aggregate |
| What was the balance or stock level on a given day? | Only by rebuilding state from a trusted opening value plus every later event, and generating rows for days with no activity |
| How long did each step of a process take? | Only by pairing events for the same instance, which is awkward when steps repeat or are missing |
The second and third questions are what the other two fact table shapes exist for. A periodic snapshot stores state per period, and an accumulating snapshot keeps one row per process instance with a date for each milestone. A mature model over one process often has a transaction table plus one or both snapshots.
Time in a transaction table
Because each row is an event, the timestamp matters more than in any other shape, and more than one timestamp may be needed: when the event occurred, when it was recorded, and when it takes effect for the business can all differ. A late event is normally counted in the period its event time falls in, and attached to the dimension versions that were current then. How to carry those clocks and handle late and corrected events is worked through in Modeling Time: Three Clocks, History, and Corrections.
References: Kimball Group, Transaction Fact Tables; Kimball Group, Declare the Grain.
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.