Periodic Snapshot Fact

Operations asks what share of catalogue items were unavailable at 18:00 yesterday. The warehouse has an inventory-change event table with every stock movement in it, and the dashboard built on that table times out.

The reason is a grain mismatch, not a performance problem. A transaction fact holds one row per event, so state at a moment is not stored anywhere - it is a reduction over every row before that moment. Answering a question about 18:00 means replaying the full history for every entity, for every day on the chart. The query is correct and the model cannot support it.

A periodic snapshot fixes the grain to the question: one row per entity per period, carrying the measures as they stood at a declared measurement time. Yesterday at 18:00 becomes a filter and a count, joinable to dimensions like any other fact table. Kimball's three fact grains - transaction, periodic snapshot, accumulating snapshot - name this explicitly, and the snapshot is the one teams skip and then emulate badly with a window function inside a BI tool.

Why it matters

Every balance-shaped question is this grain: inventory on hand, account balance, headcount, open tickets, subscriptions active, items unavailable. Those are the questions management asks most often, and a platform built only from transaction facts answers them slowly, inconsistently and differently in each dashboard that attempts it.

The second reason is agreement. When state is computed in six dashboards, there are six definitions of "active" - inclusive of the boundary or not, in whose timezone, counting pending or not. A snapshot table makes that decision once, in one place, with a name.

Implementation patterns

• Declare the measurement time in the table's name and documentation. daily_availability_1800 is self-describing; daily_availability invites every reader to assume a different instant.
• Derive the snapshot from an interval table - one row per state run with valid_from and valid_to - built incrementally from the events. The snapshot is then a single valid_from <= t < valid_to predicate, and state logic lives in exactly one place.
• Snapshot only what exists. Entities the business does not currently carry do not need a row, which usually removes most of the theoretical row count.
• Or snapshot the exceptions only, treating absence as the normal state. This is the largest available reduction and it is safe only while the interval table remains the source for questions about the other state.
• Coarsen to the question. Daily until somebody demonstrates a need for hourly, because the row count scales directly with the period.
• Keep snapshots immutable. A snapshot that gets corrected in place stops being a record of what was known, which is usually the point of having it.

Industry example

The shape is visible in any retail or marketplace platform's availability reporting in production: 1800 stores carrying 60000 catalogue items each is 108 million store-item pairs, so a naive daily snapshot is 108 million rows a day and about 39 billion a year - larger than the transaction facts it serves, and growing with no relation to how much actually happened.

The same arithmetic is why finance snapshots balances monthly rather than daily for most reporting, and why period-end figures are a dedicated table rather than a query over the ledger: the question is asked constantly and the replay is expensive every time.

Failure scenarios

• Row-count explosion from snapshotting the full cartesian product of entities and periods, found in month two when the table is the largest in the warehouse.
• An undeclared measurement time, so one dashboard reads midnight UTC and another reads 18:00 local and the two disagree by a plausible-looking margin.
• State recomputed in the BI layer by a developer who did not know the snapshot existed, which restores the inconsistency the table was built to remove.
• Late-arriving events changing a state after the snapshot was taken, so yesterday's row is now wrong - and if snapshots are overwritten, no record remains of what was reported.
• Duration questions answered from snapshots by counting consecutive days, which is fragile and wrong across gaps; those questions belong to the interval table.

Trade-offs

The honest position is both tables, with one lineage: events to intervals to snapshot, so the cost is one derived build and the definition exists once.

When not to use it

If the event table is small - a few million rows - and the state question is asked a handful of times a month, answer it with a window function over events and build nothing. The snapshot earns its keep when state questions are frequent, or the history is large enough that replaying it is the cost.

Do not build one for duration questions. "Unavailable for more than three days" is one predicate on an interval table and an awkward multi-day pattern match on a snapshot. And if the measurement time is genuinely disputed between teams, resolve that argument first - a snapshot at the wrong instant is worse than no snapshot, because it is authoritative and wrong.

Interview question

Q: A dashboard asking "how many items were unavailable at 18:00" times out against a 2-billion-row inventory event table. Design the model, size it for 1800 stores and 60000 items, and tell me what you would do when the row count comes out larger than the fact table you started from.

What a strong answer covers: the grain mismatch, since state at an instant is a reduction over events rather than a stored row; an interval table as the computed truth with the snapshot derived from it so state logic exists once; the arithmetic reaching roughly 108 million rows a day; the reductions in order - carried pairs only, then exceptions only with the interval table retained, then a coarser period; the declared measurement time; and the handling of late-arriving events.

Quick check

Quiz: Why can a transaction fact table not answer "how many were unavailable at 18:00"? - State at an instant is not a row in it but a reduction over every prior event, so the question becomes a full replay per entity per day; a periodic snapshot stores the state at a declared time instead.

Flashcard: What is the dominant cost of a periodic snapshot and the two ways to cut it? - Rows scale with entities times periods regardless of activity - about 108 million a day for 1800 stores and 60000 items - cut by snapshotting only carried pairs or only the exceptions, with an interval table kept as the source.