A fact table holds the measurements of a business process - the amounts, counts, and quantities you sum and average. Its design starts with one decision: declaring the grain, the precise meaning of a single row. Get the grain right and every measure and dimension key follows from it; get it wrong and the table answers questions inconsistently. The Kimball fact-table patterns differ along two axes - what each row represents, and whether rows are written once or updated in place.
Event-grain tables
The transaction fact table is the most common pattern: one row per business event - a sale, a click, a payment. Rows are inserted once and never touched again, and the measures are fully additive, so you can sum them across any dimension. The grain is the event itself, which makes these tables both the simplest to reason about and the largest by row count.
Snapshot-grain tables
Not every question is about events. A periodic snapshot fact table records one row per entity per regular period - a daily account balance, a month-end inventory level. The measures here are semi-additive: you can sum an inventory level across warehouses but not across days, because a balance carried forward isn't an event that accumulates.
The accumulating snapshot fact table tracks a process with a defined lifecycle - an order that moves from placed to shipped to delivered. Each process instance gets one row with several date columns, and that row is updated in place as the order hits each milestone. This is the one pattern that revisits rows, which makes it the natural home for lag and duration measures between steps.
Performance tables
An aggregate fact table pre-summarizes a base fact at a coarser grain - daily sales by product rolled up from per-transaction rows - so common queries scan far less data. The cost is consistency: an aggregate must reconcile exactly with the base table it summarizes, or reports drift apart depending on which one a query hits.
Additivity
How a measure behaves under summation is the property that pins each pattern to its use. Additive measures (sales amount, units sold) sum across every dimension and define transaction tables. Semi-additive measures (balances, levels, counts-on-hand) sum across all dimensions except time, and define periodic snapshots. Non-additive measures (ratios, percentages, unit prices) can't be summed at all; store their additive components - numerator and denominator - and compute the ratio at query time.
Picking a pattern
Start from what a single row should mean, not from the report you want:
- One discrete event? Transaction fact table.
- A status or level sampled on a schedule? Periodic snapshot.
- A multi-step process you measure end to end? Accumulating snapshot.
- A known-slow rollup of an existing fact? Aggregate, kept in lockstep with its base.
All four share the same dimensions - they connect through conformed dimensions so a date or product means the same thing across every table. Fact patterns are one half of the Kimball dimensional model; the dimensions are the other.