Skip to content
ML Academy

SQL, data modeling, data quality, EDA, visualization · insight-1050

Store validity intervals half-open, and join with start <= t < end

Evidence type
practitioner heuristic
Difficulty
medium
Roles
Data analyst, Data scientist, ML engineer
Industries
cross-industry

Summary: situation, action, reason, boundary

  1. Situation

    A type 2 dimension stores valid_from and valid_to as dates, and one version ends on 2024-03-01 while the next begins on 2024-03-01.

  2. Action

    Define validity as half-open: start inclusive, end exclusive, with the next row's start equal to the previous row's end and a far-future end date for the current row. Add tests for overlaps and gaps within each key, and join with t >= valid_from AND t < valid_to.

  3. Reason

    Half-open intervals tile the time axis without overlap, so each timestamp matches exactly one version.

  4. Boundary

    Type 2 dimensions, price lists, contracts, rate cards and status histories.

Symptom or risk

A BETWEEN valid_from AND valid_to join matches both versions for that day and duplicates facts, or, with timestamps, misses the last second when an inclusive end such as 23:59:59 is used.

Exceptions

Business calendars where the source defines inclusive ends; convert at the boundary and document.

Trade-offs

Readers used to inclusive end dates may misread the last day.

Pitfalls

Using NULL as the open end and forgetting that comparisons with NULL are not true.

Lessons and concepts

day 7
Slowly changing dimensions and backfillsPoint-in-time correctness

Claims and their sources