Alignment Pattern

State ↔ State Alignment

State ↔ State Alignment joins two historized state sources across overlapping valid-time intervals.

Problem

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

Historical reporting often requires joining two sources that both describe changing state over time. The challenge is not just joining by business key. The model must decide which versions were valid at the same reporting date.

Multiple overlapping matchesMissing valid-time coverageJoin explosionsIncorrect point-in-time results

Example

Contract state and customer state change on different dates.

Contract state

Active

Changed

Customer state

Customer A

Customer B

Reporting question

What should the joined history look like when either side changes? A correct model must split the result at every relevant state boundary.

✓

Expected Result (Recommended)

2024-03-01 → 2024-06-30

C-1001

Active / Customer A

2024-07-01 → 2024-09-30

C-1001

Changed / Customer A

2024-10-01 → 2024-12-31

C-1001

Changed / Customer B

Common Wrong Result (Risk)

2024-03-01 → 2024-06-30

C-1001

Active / Customer A

2024-07-01 → 2024-12-31

C-1001

Changed / Customer B

Key idea

The joined table should only contain periods where both source states are stable. If one side changes, the joined interval must split.

Test case

Try this State ↔ State Alignment case in Target Table Validation

Use these sample target tables to test the validator:

Copy one of the target tables below.
Open Target Table Validation.
Paste the copied table as your target output.
Check whether the result is aligned or too coarse.

Aligned target table

Copy this table to validate the expected aligned output.

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

Wrong target table

Copy this table to validate a risky output that does not split at all state boundaries.

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

Open Target Table Validation →

Why it happens

Each source has its own timeline and change frequency.

A contract can change status over time. A customer can change segment over time. A product can change category or price over time. When both sides are historized, the join must align two timelines instead of simply matching keys.

Independent source timelinesDifferent change datesTemporal join predicatesCoverage gapsOverlapping versionsBoundary semantics

Common modeling approaches

Join on business key and overlapping valid-time intervals.

Overlap join

Join records where the business keys match and the valid-time intervals overlap.

Interval splitting

Split joined results into stable intervals when either source changes within the overlap.

Coverage handling

Define what should happen when one source has no valid row for a required reporting period.

Cardinality validation

Validate that the join produces the expected number of matches per entity and reporting date.

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 your interval convention, for example closed daily intervals or half-open timestamp intervals. The important rule is that every temporal join uses the same boundary semantics consistently.

Validation checks

Validate the joined history, not only each source table.

✓ Detect overlapping versions per business key✓ Detect gaps in required reporting periods✓ Count matches per entity and reporting date✓ Validate one expected match where the model requires one✓ Check whether joined intervals produce unintended splits

Detectable by Historical Modeling Workbench

The Workbench can surface state-state alignment risks.

✓ JOIN_GAP✓ JOIN_AMBIGUITY✓ NO_VALID_MATCH✓ MULTIPLE_MATCHES

Why it matters

State-state joins are one of the most common sources of historical reporting bugs.

The data can look correct in each source independently, but the combined history can still produce gaps, duplicates or incorrect attribution.

Validating the join result is often more important than validating each source table in isolation.

Related Patterns

State ↔ Event Alignment Historical Match Ambiguity Historical Coverage Gap Historical Conformance Relationship History

Try it

Validate temporal joins before they reach reporting.

Use the Historical Modeling Workbench to detect gaps, ambiguous matches, overlapping versions and unintended temporal join splits.

Open Historical Modeling Workbench →