Gap Fill
GapFillSpec estimates sale and delivery dates for vehicles caught in a known
collection gap — a date range over which the upstream source stopped collecting, or
produced data known to be unreliable. Ranges are inclusive of both endpoints.
It applies to inventory data where first_seen is the vehicle's lot arrival date and
last_seen is the last date it was observed on lot.
The two affected populations
Two indicator columns select the records to correct, and both are added to the output.
last_in_gap—last_seenfalls within a gap, so the sale date is unknown.last_seenis replaced withfirst_seen + agg(last_seen - first_seen)over clean records in the samegroup_bysegment.first_in_gap—first_seenis the day after a gap ends andlast_seenis not the partition date, so the vehicle was delivered during the gap.first_seenis replaced withlast_seen - agg(last_seen - first_seen)over the same segment.
A vehicle that sold inside the gap is last observed on the day before the gap opened, and cannot be told apart from one that genuinely sold that day. That ambiguity is accepted: the estimate is what the correction buys.
The reference population
The aggregation excludes last_in_gap records, first_in_gap records and
still-on-lot vehicles, since a still-on-lot vehicle's observed duration is incomplete
and would bias the estimate upward. Rows outside group_filter count as clean records
and may contribute to it.
Estimates are clamped to the gap boundary. A last_in_gap estimate is held between the
observed last_seen and the gap end date; a first_in_gap estimate between the gap
start date and the observed first_seen. A record whose group has no clean reference
rows keeps its original dates, and its indicator column still marks it.