COMPOSITE PATTERN

Snapshot Fact Modeling

Capture business metrics exactly as they existed at fixed reporting points such as day-end, month-end or quarter-end.

Snapshot facts trade storage for deterministic reporting, faster point-in-time queries and stable historical analytics.

Problem

Business reporting often needs stable facts for fixed reporting dates.

Operational systems usually store events, current state or historized records, while reporting asks for a stable result at a specific cutoff.

Typical questions include how many contracts were active at month-end, what the portfolio value was on 31 December or which customers belonged to a segment at quarter-end.

Reconstructing these answers repeatedly from mutable source history can create unstable reporting unless the snapshot process is deterministic.

Missing snapshot datesDuplicate snapshot rowsUnstable month-end reportingCurrent-state leakage

Interactive example

A contract premium is resolved into deterministic daily and month-end snapshot facts.

Switch between snapshot cadences and a later rebuild of the same reporting date.

Source history

01 Jan – 14 Mar

Contract premium1,200

From 15 Mar

Contract premium1,350

Snapshot result

One resolved fact row at month-end

2024-03-31Premium 1,350

The month-end snapshot uses the defined reporting calendar and the entity state valid at the cutoff.

Key idea

The snapshot grain is explicit: one entity, one reporting date and one resolved fact result.

Core concepts

Snapshot facts persist the resolved reporting state.

A snapshot fact table stores one fact row per entity and reporting date after all required historical state and dimension logic has been resolved.

Define snapshot dates

Create an explicit calendar of required reporting cutoffs.

Resolve historical state

Determine the entity state valid at each snapshot date.

Calculate measures

Derive balances, counts, premiums or other reporting facts.

Persist the snapshot

Store one deterministic row per entity and reporting date.

Snapshot models simplify recurring reporting by moving temporal reconstruction into a controlled production step instead of repeating it in every analytical query.

Why it happens

Source history and reporting calendars are different.

Source records change whenever business events happen. Reporting usually runs on fixed dates such as daily close, month-end or quarter-end.

Snapshot Fact Modeling bridges the two timelines by resolving the source history at each required reporting cutoff.

Month-end reportingPortfolio snapshotsAs-of entity stateHistorical dimensionsLate-arriving dataFrozen vs rebuilt reports

Common modeling approaches

Make the reporting date explicit in the fact table.

Periodic snapshot

Persist facts on a fixed cadence such as daily, monthly or quarterly.

Accumulating snapshot

Update one lifecycle row as milestones are reached when the business process requires it.

Point-in-time resolution

Resolve source state and dimensions with explicit temporal predicates.

Published snapshot

Freeze the official output when released reporting must remain unchanged.

Snapshot grain

snapshot_date | contract_id | premium
2024-01-31    | C1          | 1200
2024-02-29    | C1          | 1200
2024-03-31    | C1          | 1350

Validation checks

Validate uniqueness, coverage and reproducibility.

✓ One row per entity per snapshot date✓ All required snapshot dates are covered✓ No current-state leakage✓ Historical dimensions resolve correctly✓ Rebuilt snapshots reproduce expected totals✓ Snapshot cadence is explicit✓ Cutoff and publication rules are documented

Why it matters

Snapshot facts turn complex history into simple, repeatable reporting.

Snapshot facts make recurring reporting dates explicit and reduce repeated temporal joins in analytical workloads.

They provide a stable basis for KPIs, regulatory reporting, portfolio analysis and period-over-period comparisons.

The trade-off is additional storage and the need to govern rebuild behavior carefully.

RELATED TEMPORAL MODELS

How Snapshot Fact Modeling connects to other historical patterns

Snapshot facts consume historical state, dimensions and reporting cutoffs to produce one stable result per reporting period.

Snapshot Reproducibility

Validates that a rebuilt snapshot matches the original result.

State Modeling

Provides the business-valid entity state resolved at each snapshot date.

Bitemporal Modeling

Adds as-known knowledge cutoffs when the snapshot must reflect historical system visibility.

Historical Backfill

Populates missing historical snapshot periods using controlled reconstruction.

Related Patterns

Snapshot Reproducibility State Modeling Bitemporal Modeling Historical Backfill Dimension Completion

Try it

Design or review a historical snapshot fact model.

Use the Historical Data Assistant to reason about snapshot cadence, reporting cutoffs, historical joins and deterministic rebuilds.

Open Historical Data Assistant →