A periodic snapshot fact table records one row per entity per regular time period - a daily account balance, a month-end inventory level, a weekly subscription count. Unlike an event-grain table, it doesn't wait for something to happen; it samples a state on a fixed schedule whether or not anything changed. That sampling grain is what makes it the right tool for "what was the level at the end of each period" questions.

Sampling state on a heartbeat

The grain is entity-plus-period: account × day, product × month, portfolio × week. Every period, the ETL writes a fresh row capturing the current state, even for entities with no activity that period. A bank with 2 million accounts writes 2 million rows every day - the table grows by a predictable amount on a schedule, which makes capacity planning easy but the row counts large.

fact_account_balance_daily (
  date_key, account_key,
  balance,            -- the sampled level (semi-additive)
  interest_accrued,
  transaction_count   -- additive within the period
)
-- one row per account per day, written every day

Semi-additive measures

The measures in a periodic snapshot are usually semi-additive: you can sum a balance across accounts (total deposits today) but not across days, because a balance carried from yesterday isn't new money. Summing 30 daily balances doesn't give a meaningful monthly figure - the correct cross-time aggregation is an average or an end-of-period pick, not a sum. Any measure that's a level or a balance behaves this way; genuinely additive measures like a period's transaction count can still be summed freely.

When to choose it

Reach for a periodic snapshot when the question is about status over time and reconstructing that status from events would be expensive or impossible. Computing every account's daily balance by replaying every transaction since account opening is slow and fragile; sampling the balance once a day and storing it makes "balance trend over the last year" a single-table scan. It complements rather than replaces the transaction fact table - the transaction table holds the events, the periodic snapshot holds the resulting levels.

Frequently asked questions

Periodic vs. accumulating snapshot? A periodic snapshot writes a new row each period and never updates old rows; an accumulating snapshot keeps one row per process instance and updates it in place as the process advances. Periodic is for recurring state sampling; accumulating is for a bounded lifecycle.

Do I write rows for entities with no activity? Yes - that's the point. The snapshot captures the level for every entity each period, so unchanged entities still appear and trends have no gaps.

How do I aggregate balances over time? Don't sum them. Use an average, a period-end value, or a minimum/maximum, because the measure is semi-additive across the time dimension.

Won't the table get huge? It grows by a fixed amount per period, which is predictable. Partition by the period and, if needed, summarize older periods with an aggregate fact table.