State Modeling for Historical Reporting
Use valid-time intervals to answer which business state was true at a specific reporting date.
State modeling is the foundation behind SCD2 dimensions, point-in-time joins, snapshot reports and most historical data quality checks.
Historical reporting asks a different question from current-state reporting.
Operational systems usually answer what is true now? Historical reporting must answer what was true then?
A contract can be active in May and cancelled in August. A customer can move from Bronze to Gold. A product can change category. If an old report reads the latest row, later updates silently rewrite earlier business results.
One current row cannot preserve more than one historical truth.
Current-state table
- C1 · Cancelled
- One latest row
- May may be rewritten
- Historical reporting is unstable
Historized state table
- C1 · Active · Jan–Jun
- C1 · Cancelled · Jul onward
- May resolves to Active
- August resolves to Cancelled
One entity, multiple valid states
The reporting date determines which version should be selected. The business key alone is not enough.
State modeling stores one row per business state version.
Each version uses a stable business key and a valid-time interval. The interval states when that version was true in the business timeline.
contract_id | status | valid_from | valid_to C1 | Active | 2024-01-01 | 2024-07-01 C1 | Cancelled | 2024-07-01 | 9999-12-31
Use explicit intervals and one documented boundary convention.
Business key
The stable entity identifier that groups all state versions of the same business object.
valid_from
The first date or timestamp on which the state version is valid.
valid_to
The end boundary of the state version. Its inclusivity must be explicit.
Open-ended row
The current version commonly ends at 9999-12-31 or another documented open-end value.
Boundary convention
Closed-open intervals make adjacent versions unambiguous: start included, end excluded.
One intended state
Every required reporting date should resolve to exactly one applicable state.
Most state modeling failures are semantic, not schema errors.
Overlapping states
Two rows are valid for the same entity and reporting date, making point-in-time resolution ambiguous.
Missing coverage
No row covers a required reporting date, so the entity silently disappears from reports.
Current-state leakage
A query joins to the latest state instead of the version that was valid at the reporting date.
Boundary confusion
Inclusive and exclusive valid_to semantics are mixed, creating hidden gaps or overlaps.
A historical join must match both the key and the time interval.
Joining only on the business key is a current-state lookup. A historical lookup also checks whether the reporting date falls inside the intended valid-time interval.
-- Point-in-time state lookup select f.snapshot_date, f.contract_id, s.status from fact_snapshot f join contract_state s on f.contract_id = s.contract_id and f.snapshot_date >= s.valid_from and f.snapshot_date < s.valid_to;
Every reporting date should resolve to exactly one intended state.
Analyze this State Modeling example
Use the sample target tables to test whether the reporting dates resolve through historized state intervals or through a current-state shortcut.
- Choose one of the target tables below.
- The example is copied and prepared for analysis.
- Start the investigation.
- Check whether May resolves to Active and August to Cancelled.
Historized state target
Expected output: May is Active and August is Cancelled.
contract_id,status,snapshot_date,valid_from,valid_to,state_modeling_status C1,Active,2024-05-31,2024-01-01,2024-07-01,historized_state C1,Cancelled,2024-08-31,2024-07-01,9999-12-31,historized_state
Current-state target
Risky output: the latest Cancelled state rewrites the May report.
contract_id,status,snapshot_date,valid_from,valid_to,state_modeling_status C1,Cancelled,2024-05-31,2024-01-01,9999-12-31,current_state_used C1,Cancelled,2024-08-31,2024-01-01,9999-12-31,current_state_used
Add another pattern when history has more than one independent timeline.
Relationship History
Use it when ownership, assignment or membership changes independently from the entities.
State–State Alignment
Use it when two independently historized state sources must be joined and split at every boundary.
Snapshot Reproducibility
Use it when the originally published result must remain queryable after later corrections.
Bitemporal Modeling
Use it when you must distinguish what was true from when the system knew or published it.
State modeling prevents reports from being rewritten by today's data.
Without explicit state intervals, historical reports often use current values by accident. The result is subtle: pipelines stay green, row counts look stable, but old numbers change.
A good state model lets engineers reconstruct the intended state for any reporting date and validate whether a historical table is safe to use.
Design or review a historical state model.
Describe what you are modeling or paste an existing implementation. The assistant will guide you through semantics, risks and validation.
Open Historical Data Assistant