Back to Learn
Composite pattern

Snapshot Reproducibility

Decide whether rebuilding an old report should reproduce the original result or show corrected truth.

Reproducible snapshots prevent late-arriving data, historical corrections and mutable dimensions from silently changing previously published reporting results.

The problem

The same month-end report produces different numbers when rebuilt later.

Historical reports often depend on source data that continues to change after publication. Late records, corrections and overwritten dimensions can therefore alter an old result.

One reporting date, two possible meanings

The correct result depends on the reporting contract.

March 31 - report published at 1.2M
June - historical correction arrives
As-known rebuild remains 1.2M
Corrected-truth rebuild becomes 1.3M
The decision

Define which historical truth the report must reproduce.

What was known at publication timeThe latest corrected business truthThe exact persisted report outputA regulated or audited reporting state
Reproducible model

Preserve the reporting date and the knowledge boundary.

contract_id | premium | snapshot_date | visible_from | visible_to
C-1001     | 1200000 | 2024-03-31    | 2024-03-31   | 2024-06-15
C-1001     | 1300000 | 2024-03-31    | 2024-06-15   | 9999-12-31

The first version reproduces what was known at the end of March. The second version represents the later corrected knowledge.

As-known lookup

Constrain the query by both reporting date and knowledge date.

select
  contract_id,
  premium
from contract_snapshot_history
where snapshot_date = :reporting_date
  and :knowledge_date >= visible_from
  and :knowledge_date <  visible_to;
Static visualization

The reporting date stays fixed while the knowledge boundary changes.

Snapshot reproducibility depends on separating the business period being reported from the information that was visible when the report was produced.

REPORTING DATE: 2024-03-31

Knowledge timeline
------------------

2024-03-31                          2024-06-15
Published result                    Correction arrives
Premium = 1.2M                      Premium = 1.3M
      |                                   |
      v                                   v

Visible-time history
--------------------

[2024-03-31, 2024-06-15)  ->  Premium = 1.2M
[2024-06-15, infinity)    ->  Premium = 1.3M

Queries
-------

Reporting date = 2024-03-31
Knowledge date = 2024-03-31  ->  1.2M

Reporting date = 2024-03-31
Knowledge date = 2024-06-15  ->  1.3M
Test case

Analyze this Snapshot Reproducibility example

Compare a reproducible target with an output that stores only the latest rebuilt result.

  1. Choose the result that matches the reporting contract.
  2. Open the example in the Historical Data Assistant.
  3. Review whether the original knowledge boundary survives.
  4. Compare the diagnosis and recommended corrective action.
Good example

Reproducible target table

The original published state and the later corrected state both remain queryable through visible-time versions.

Bad example

Current rebuild only

The target contains only today's rebuilt value. The originally published result and knowledge cutoff were lost.

Detection SQL

Detect snapshots that cannot preserve the original published state.

The first check finds snapshot rows that have no visible-time boundary. Such rows can only represent the current rebuild and cannot distinguish original knowledge from later corrections.

select
  contract_id,
  snapshot_date,
  count(*) as version_count
from contract_snapshot_history
where visible_from is null
   or visible_to is null
group by
  contract_id,
  snapshot_date;

The next check verifies that visible-time versions form a continuous, non-overlapping sequence for each entity and reporting date.

with ordered_versions as (
  select
    contract_id,
    snapshot_date,
    visible_from,
    visible_to,
    lag(visible_to) over (
      partition by contract_id, snapshot_date
      order by visible_from
    ) as previous_visible_to
  from contract_snapshot_history
)
select
  contract_id,
  snapshot_date,
  previous_visible_to,
  visible_from
from ordered_versions
where previous_visible_to is not null
  and previous_visible_to <> visible_from;

A reproducibility regression should also compare a rebuilt result against the originally published output using the original cutoff.

select
  published.contract_id,
  published.snapshot_date,
  published.premium_amount as published_amount,
  rebuilt.premium_amount as rebuilt_amount
from published_snapshot published
join rebuilt_snapshot rebuilt
  on rebuilt.contract_id = published.contract_id
 and rebuilt.snapshot_date = published.snapshot_date
where rebuilt.premium_amount <> published.premium_amount;
Resolution

Preserve the original reporting state before changing historical data.

Confirm whether the report requires as-known or corrected-truth semanticsIdentify the reporting date and the original knowledge cutoffPreserve or reconstruct the originally published resultIntroduce visible-time versions for later correctionsUse closed-open visible-time intervals consistentlyPrevent later corrections from overwriting prior knowledge statesRecord the cutoff and reporting logic version used for publicationBackfill only after the expected historical outputs are documented
Implementation options

Choose the smallest mechanism that satisfies the reporting requirement.

Persist immutable snapshot factsTrack visible-time versionsFreeze published report outputsUse bitemporal as-known joinsVersion reporting logic when requiredRecord the applied reporting cutoff
Validation checklist

Prove reproducibility across publication, correction and rebuild.

The reporting date is stored or supplied explicitlyThe original knowledge cutoff is stored or recoverableEvery correction creates a new visible-time versionVisible-time intervals do not overlapVisible-time intervals do not contain accidental gapsThe original published state remains queryableA rebuild with the original cutoff matches the published outputA rebuild with a later cutoff returns the corrected truthNo future record is visible before its visible_from boundaryReporting logic changes are versioned when they affect resultsRow-level comparisons accompany aggregate total comparisonsLate-arriving data and historical corrections are regression-tested
Investigate the issue

Check why an old report changes when rebuilt.

Describe the reporting behavior or provide an implementation. The assistant will help distinguish as-known reporting from corrected truth and identify missing reproducibility controls.

Open Historical Data Assistant