← Back to Pattern Catalog
Interactive Pattern

Historical Overlap

A Historical Overlap occurs when multiple records are valid for the same business entity at the same point in time.

Problem

One entity has multiple active states at the same reporting date.

Historized data models typically assume that a business entity has exactly one valid state at any reporting date.

When validity intervals overlap, multiple states become active at the same time. Historical joins, snapshots and aggregations can no longer determine a single historical truth.

Duplicate fact rowsJoin ambiguityIncorrect aggregationsMultiple active states
Example

Customer C1 has two active segment records from April to June.

Segment = Retail
Jan – Jun
Segment = Premium
Apr – Dec
Overlap
Apr – Jun has two active states
Reporting impact

A report for May cannot uniquely determine which customer segment should be used.

Test case

Try this Historical Overlap case in Target Table Validation

Use these sample target tables to test the validator:

  1. Copy one of the target tables below.
  2. Open Target Table Validation.
  3. Paste the copied table as your target output.
  4. Check whether overlapping valid-time intervals are detected.
Continuous target table

Copy this table to validate non-overlapping historical intervals.

customer_id,customer_segment,valid_from,valid_to
C1,Retail,2024-01-01,2024-04-01
C1,Premium,2024-04-01,2025-01-01
Overlap target table

Copy this table to validate a risky output with overlapping valid-time intervals.

customer_id,customer_segment,valid_from,valid_to,overlap_status
C1,Retail,2024-01-01,2024-06-30,overlap
C1,Premium,2024-04-01,2024-12-31,overlap
Open Target Table Validation →
Root causes

Overlaps usually come from incomplete or inconsistent historization rules.

Historical overlaps often remain hidden because each individual row can look valid. The problem appears when rows are compared within the same business key and timeline.

Missing end-datingDuplicate SCD2 rowsBackdated correctionsParallel source historiesIncorrect merge logicInclusive boundary mistakes
Typical solutions

Enforce one active state per entity and reporting date.

Fix interval boundaries
Correct valid_from and valid_to so adjacent records do not overlap unexpectedly.
Deduplicate versions
Remove or consolidate duplicate business-key versions that represent the same state.
Apply priority rules
When overlaps are expected, define deterministic rules for which version wins.
Validate before joins
Run overlap checks before using historized tables in temporal joins or snapshots.
Validation checks

Detect overlaps before they create downstream reporting errors.

✓ Detect overlapping validity intervals✓ Validate one active state per reporting date✓ Check temporal join cardinality✓ Detect duplicate historical matches✓ Validate dimension consistency
Detectable by Historical Modeling Workbench

The Workbench can surface overlapping historical intervals as validation findings.

OVERLAPHistorical OverlapMultiple Active StatesJoin Ambiguity Risk
Why it matters

Overlaps are one of the most important quality checks for historized dimensions.

Historical overlaps often remain hidden until a temporal join or snapshot is executed.

The overlap may look harmless in the source table but can cause large reporting errors downstream, especially duplicate facts, unstable KPIs and ambiguous joins.

Related Patterns
Dimension CompletionSnapshot ReproducibilityState ↔ State AlignmentState ↔ Event AlignmentHistorical Coverage Gap
Try it

Detect historical overlaps in your own historized data.

Use the Historical Modeling Workbench to find overlapping intervals, inspect timeline evidence and understand where temporal joins become ambiguous.

Open Historical Modeling Workbench →