Snapshot fact table

What is a snapshot fact table?

A snapshot fact table records the state of something at a fixed moment, not the individual events behind it. A transaction fact table keeps one row per event: one sale, one payment, one stock movement. A snapshot keeps the standing position instead: how many units sat in each warehouse at each day's close, what each account held at month end.

The idea comes from Ralph Kimball's dimensional modelling. In a star schema a fact table sits at the centre, surrounded by dimension tables that carry the context. Kimball describes three fact table types, and two of them are snapshots: the periodic snapshot and the accumulating snapshot. You reach for them when the question is about a level at a point in time, awkward to answer by re-summing every transaction since day one.

Periodic snapshot: one row per entity per period

A periodic snapshot writes one row for every entity in every period, on a fixed schedule. At each day, week, or month end you record the standing value for each product or account. Even when nothing moved that period a row still lands, carrying a zero or the previous value, so the table grows predictably: entities times periods. The grain is "one product per warehouse per day", never "one movement". An inventory table loaded nightly with each product's end-of-day balance is the classic example.

These measures are semi-additive: you may sum them across some dimensions but not across time. Account balances add up across customers, so the total is the sum of the individual balances, yet they do not add up across periods. If a customer holds 1,000 euro at the end of each of three months, the quarter is not 3,000 euro. Along the time axis you take the closing value or an average, never the sum, which is why Power BI and DAX have dedicated patterns for it.

Accumulating snapshot: one row per process instance

The third type fits a process with a clear start, a clear end, and a few milestones in between, such as order fulfilment or a claims process. You create one row per instance and update it in place as the instance moves through the steps. This is the only fact table type where you overwrite existing rows instead of only appending.

Such a row carries several date foreign keys, one per milestone: order date, ship date, delivery date, empty at first. Each time a milestone is reached you fill its date and recompute the lag measures between steps, so the table answers throughput time questions directly: how many orders sit between shipped and delivered, and where delays build up. The row count stays small because progress overwrites the row instead of adding new ones.

How the three types differ

The three are not rivals. A mature model runs all three side by side, each for its own questions. The cleanest way to tell them apart is to ask when a row is written and whether it later changes.

• Transaction. One row per event, written when the event happens, never touched again.

• Periodic snapshot. One row per entity per period, written on the schedule, never touched again.

• Accumulating snapshot. One row per process instance, written at the start and revisited in place until the process ends.

With either snapshot, pin down the grain and agree how you summarise the measures over time before building a report on it.