Historical Overlap

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

Historized models often assume that a business entity has exactly one intended state at any point in time.

When validity intervals overlap, a temporal lookup can return multiple rows for the same entity and reporting date.

The overlap is visible only when rows are compared together.

customer_id | segment | valid_from | valid_to
C1          | Retail  | 2024-01-01 | 2024-07-01
C1          | Premium | 2024-04-01 | 2025-01-01

Analyze a valid and an invalid historical timeline.

Both examples use closed-open valid-time intervals. Adjacent intervals may share a boundary, but two intervals must not cover the same point in time for the same customer.

customer_id,valid_from,valid_to,segment
C1,2024-01-01,2024-04-01,Retail
C1,2024-04-01,2024-07-01,Premium
C1,2024-07-01,9999-12-31,Enterprise

customer_id,valid_from,valid_to,segment
C1,2024-01-01,2024-07-01,Retail
C1,2024-04-01,9999-12-31,Premium

The bad case contains one objectively measurable overlap.

Under closed-open semantics, the Retail interval covers dates from 2024-01-01 up to but excluding 2024-07-01. The Premium interval starts on 2024-04-01.

Overlaps usually come from incomplete historization rules.

Historical overlap is usually created by conflicting change handling.

Each row may look valid on its own. The defect becomes visible only when intervals are compared within the same business key.

A common cause is an update that inserts a new historical version without closing the previous version at the same boundary.

Backdated corrections, parallel sources and inconsistent boundary conventions can create the same structural result: more than one state is valid for the entity at the same reporting date.

Compare each interval with the next interval per business key.

with ordered_history as (
  select
    customer_id,
    valid_from,
    valid_to,
    lead(valid_from) over (
      partition by customer_id
      order by valid_from, valid_to
    ) as next_valid_from
  from customer_history
)
select *
from ordered_history
where next_valid_from < valid_to;

Do not repair an overlap before confirming the intended business rule.

A reporting date should resolve to the intended number of states.

Historical overlap must be interpreted within the model's intended semantics.

An overlap is a structural fact, but whether it is a defect depends on the business invariant of the model.

One overlap can multiply rows across every downstream temporal join.

Historical overlap is not only a local data-quality issue. It can change join cardinality, duplicate fact rows and make point-in-time results depend on accidental query ordering.

The defect often remains hidden until the historized table is joined to transactions, snapshots or another state history. At that point, one overlapping entity can produce several competing historical matches.

A reliable model therefore makes the expected interval cardinality explicit and validates it before downstream reporting consumes the history.