Detect when a required reporting period has no valid historical state.
Coverage gaps can drop fact rows, remove attributes and create incomplete snapshots even when every individual history row appears valid.
The problem
A required reporting date has no matching historical row.
Historical models often assume that every required period is covered by a valid state. In practice, histories may start too late, end too early or contain missing intervals.
Each individual row can be structurally valid while the complete timeline remains discontinuous.
Observed timeline
One month has no valid historical state.
2025-01-012025-03-012025-04-01Future
Covered
No state
Covered
Customer C1 has two valid rows, but no row covers the period between them.
Why this is difficult
A discontinuity is not automatically a defect.
The data establishes that an uncovered period exists. It does not establish whether that period violates the business model.
The same two rows can represent a correct timeline when periods without state are allowed, or a broken timeline when continuous coverage is required.
Business interpretation
The coverage policy decides whether the gap is good or bad.
Good case
Intentional validity gap
Business rule
Customers may legitimately have no entitlement or active state during the uncovered period.
Expected result
Intentional valid-time discontinuity
Preserve the uncovered period
Do not create artificial history
Document the optional coverage rule
Bad case
Missing required coverage
Business rule
Every reporting day must resolve to one valid historical state.
Expected result
Historical validity coverage gap
Reporting dates can lose their match
Verify missing source or historization
Repair only the affected period
Shared evidence
Both interpretations use the same historical rows.
Coverage gaps usually come from incomplete source or historization rules.
✓ Source history starts too late✓ Existing interval ends too early✓ A version was deleted or skipped✓ Backfill omitted a required period✓ Cross-system histories use different coverage✓ Boundary semantics create a hidden gap
Detection
Compare each interval end with the next interval start.
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;
Resolution
Repair only gaps that violate an explicit coverage rule.
✓ Confirm whether continuous coverage is required✓ Verify the interval boundary convention✓ Backfill missing source history where possible✓ Use an unknown member only by explicit policy✓ Preserve intentional business gaps✓ Document expected coverage per entity type
Validation
Validate coverage against the dates reports actually use.
✓ Detect gaps between adjacent intervals✓ Compare fact dates with dimension coverage✓ Validate snapshot completeness✓ Check required periods per business key✓ Verify cross-source temporal coverage✓ Separate intentional from invalid gaps
Why it matters
Coverage rules protect both correctness and meaning.
Filling every uncovered period can invent states that never existed. Ignoring every uncovered period can silently remove facts from reporting.
Reliable historical models preserve intentional absence and repair only missing coverage that violates a confirmed business rule.
Analyze historical data
Check whether an uncovered period is intentional or incomplete.
Start with one of the scenarios above or describe your own historical timeline. The assistant will confirm interval semantics, evaluate the coverage rule and distinguish intentional absence from missing history.