State ↔ State Alignment

Two historized sources can both be correct but still fail when joined.

Historical reporting often combines two sources that both change independently over time. Joining only by business key is not enough. The model must determine which versions were valid together at each reporting date.

When one side changes inside the other side's interval, the combined history must split at that boundary. Otherwise the result attributes a state combination to a period in which it did not actually exist.

Contract state and customer state change on different dates.

The joined history must be stable on both timelines.

State ↔ State Alignment compares two independently historized timelines and derives the periods in which both resolved states remain unchanged.

Analyze this State ↔ State Alignment example

Use these sample target tables to test the Investigator:

1. Select one of the target tables below.
2. Start the prepared investigation.
3. Review the supplied target output.
4. Check whether all source boundaries were preserved.

entity_id,customer_key,contract_status,valid_from,valid_to,alignment_method
C-1001,Customer A,Active,2024-03-01,2024-03-31,interval_split
C-1001,Customer A,Active,2024-04-01,2024-04-30,interval_split
C-1001,Customer A,Active,2024-05-01,2024-05-31,interval_split
C-1001,Customer A,Active,2024-06-01,2024-06-30,interval_split
C-1001,Customer A,Changed,2024-07-01,2024-07-31,interval_split
C-1001,Customer A,Changed,2024-08-01,2024-08-31,interval_split
C-1001,Customer A,Changed,2024-09-01,2024-09-30,interval_split
C-1001,Customer B,Changed,2024-10-01,2024-10-31,interval_split
C-1001,Customer B,Changed,2024-11-01,2024-11-30,interval_split
C-1001,Customer B,Changed,2024-12-01,2024-12-31,interval_split

entity_id,customer_key,contract_status,valid_from,valid_to,alignment_method
C-1001,Customer A,Active,2024-03-01,2024-03-31,overlap_join_only
C-1001,Customer A,Active,2024-04-01,2024-04-30,overlap_join_only
C-1001,Customer A,Active,2024-05-01,2024-05-31,overlap_join_only
C-1001,Customer A,Active,2024-06-01,2024-06-30,overlap_join_only
C-1001,Customer B,Changed,2024-07-01,2024-07-31,overlap_join_only
C-1001,Customer B,Changed,2024-08-01,2024-08-31,overlap_join_only
C-1001,Customer B,Changed,2024-09-01,2024-09-30,overlap_join_only
C-1001,Customer B,Changed,2024-10-01,2024-10-31,overlap_join_only
C-1001,Customer B,Changed,2024-11-01,2024-11-30,overlap_join_only
C-1001,Customer B,Changed,2024-12-01,2024-12-31,overlap_join_only

Each source owns an independent timeline and change frequency.

A contract may change status in July while its customer relationship changes in October. A product category may change at a different time from its price or entitlement state.

A simple overlap join identifies intersecting rows but does not automatically create the stable subintervals required by the combined historical state.

Join on business key and overlapping valid-time intervals.

left.business_key = right.business_key
AND intervals_overlap(
  left.valid_from,
  left.valid_to,
  right.valid_from,
  right.valid_to,
  boundary_convention
)

The exact overlap predicate depends on the interval convention. The important rule is that both sources and the resulting split intervals use the same boundary semantics consistently.

Validate the joined history, not only each source table.

The Investigator can surface state-to-state alignment risks.

State-to-state joins are a common source of historical reporting bugs.

Each source can be valid in isolation while the combined history still produces gaps, duplicates or incorrect attribution.

Validating the final aligned timeline is therefore often more important than validating either source independently.

How State ↔ State Alignment relates to neighboring patterns