Periodic Snapshot Fact Table

A periodic snapshot fact table records the state of something at regular intervals. The Kimball Group describes each row as summarizing many measurement events over a standard period, such as a day, a week, or a month, and states the design’s defining property directly: “The grain is the period, not the individual transaction.”

A daily account balance table, with one row per account per day, is the typical example. So are month-end inventory by warehouse and product, and active subscriptions by plan per week.

Rows exist even when nothing happened

The feature that distinguishes a periodic snapshot from a transaction table is that a row is normally inserted for each period even if no activity took place, with a zero or null for the facts. The Kimball Group notes that this makes the table uniformly dense in its foreign keys: every account has a row for every day.

That density is what makes state questions simple. “Average daily balance across all accounts in March” has one row for every account-day to average over, and does not quietly skip the accounts that saw no transactions.

Why not rebuild state from transactions?

State can be reconstructed from events: start from a trusted opening balance or earlier snapshot and apply every transaction after it, the same idea Martin Fowler describes for event sourcing, where state is rebuilt by replaying events from a snapshot rather than from the beginning. That is correct as long as the transaction history is complete. The periodic snapshot’s advantage is not that it answers something otherwise impossible. It does the rebuild once, when the period closes, instead of repeating it inside every query, and it generates the rows for quiet days explicitly instead of leaving each query to invent them.

What to decide when building one

• The period. Daily snapshots answer more questions and cost more storage: 1 million accounts produce 365 million rows a year daily, or 12 million monthly. Choose the finest period anyone genuinely asks about.
• Which facts belong. Any measurement consistent with the period grain is allowed, so these tables tend to accumulate many facts. Many will be semi-additive — balances, quantities on hand — and must not be summed across periods.
• When a period is final. If late transactions can still change last month, decide whether the snapshot is regenerated and how consumers learn that it was.

A periodic snapshot usually sits alongside a transaction fact table over the same process rather than replacing it: the transactions answer “what happened,” the snapshot answers “what was the state then.” A third shape, the accumulating snapshot, tracks a process through its milestones. How the three compare is worked through in Grain, Facts, and the Arithmetic That Quietly Goes Wrong.

References: Kimball Group, Periodic Snapshot Fact Tables; Martin Fowler, Event Sourcing.