← Back to Pattern Catalog
Reporting Pattern

Snapshot Fact Modeling

Snapshot Fact Modeling creates reproducible fact rows for fixed reporting dates such as month-end, quarter-end or daily snapshots.

Problem

Business reporting often needs stable facts for fixed reporting dates.

Operational systems usually store events, current state or historized records, but reporting often asks for a stable view at a fixed reporting date.

Business users ask questions like: how many active contracts did we have at month-end, what was the portfolio value on December 31, or which customers belonged to each segment at quarter-end?

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

Contract C1 is resolved into one fact row per month-end snapshot.

2026-01-31
Active
Premium = 100
2026-02-28
Active
Premium = 100
2026-03-31
Cancelled
Premium = 0
Snapshot fact grain

One row per entity and snapshot date. The fact table stores the reporting state at the reporting date, making month-end reporting stable and repeatable.

Why it happens

Source history and reporting calendars are not the same thing.

Sources change whenever business events happen. Reporting usually happens on fixed dates. Snapshot Fact Modeling bridges that gap by resolving source history into one fact row per entity and snapshot date.

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.

Define snapshot dates
Create an explicit calendar of required reporting dates such as month-end or quarter-end.
Resolve entity state
Determine the state of each entity as of every required snapshot date.
Join historically
Join dimensions using point-in-time or bitemporal as-of logic instead of current values.
Persist facts
Store one fact row per entity and snapshot date to stabilize recurring reporting.
Validation checks

Validate uniqueness, coverage and reproducibility.

✓ Validate one row per entity per snapshot date✓ Detect missing snapshot coverage✓ Compare snapshot totals against source or published reports✓ Check dimension coverage for every snapshot fact row✓ Validate reproducibility after source corrections
Why it matters

Snapshot facts turn complex historical behavior into simple, repeatable reporting.

Snapshot facts are often the bridge between complex historical source behavior and simple business reporting.

They make recurring reporting dates explicit, reduce repeated as-of logic and create a stable basis for KPIs.

Related Patterns
Snapshot ReproducibilityEvent-to-State ProjectionDimension CompletionBitemporal ModelingHistorical Backfill
Try it

Explore snapshot modeling risks in the Workbench.

Use the Historical Modeling Workbench to reason about snapshot dates, historical joins, dimension coverage and reproducible reporting.

Open Historical Modeling Workbench →