Why does an as-of join need a lower bound on how far back it reaches, not only an upper bound at the quote instant?
answer
- the upper bound is only half of it
- dormant devices write nothing
- an ancient version still matches
- stale served as current, unmarked
- explicit marker beats a silent fallback
basics
~20 sWithout a lower bound, the join matches the newest version that exists, however ancient. A driver who stopped uploading two years ago gets a two-year-old aggregate presented as current, with nothing in the column marking it as stale.
solid answer
~50 sThe upper bound stops the row reading the future; it does nothing about reading the distant past. A dormant device produces no new versions, so an as-of join bounded only at `quoted_at` happily returns whatever was last written - possibly two years earlier - and the training column then mixes fresh and ancient values with no marker distinguishing them. That is dishonest in two directions. The model learns from a feature whose meaning silently varies with how long ago it was produced, and the row describes a decision the pricing path would not make: faced with a two-year-old aggregate, quoting would treat the driver as having no usable telematics history. Bound the join at `quoted_at - L` as well, and where no version falls inside the interval emit an explicit no-recent-history marker instead of the stale value.
go deeper
Remember that selecting the newest version at or before a timestamp can still return something very old, because nothing in that predicate asks how old.
State both bounds explicitly and explain what each one prevents, then say what should be emitted when no version falls inside the interval.
Make the population argument: a row using a two-year-old aggregate describes a case the live pricing path routes down a different branch, so training and serving stop describing the same drivers.
Own the age bound as a stated policy per feature, with a rationale for each number and one place it is defined, rather than a constant repeated in whichever assembly query happened to need it.
## The bound everyone writes and the one they forget An as-of join predicate usually grows one condition at a time. First comes entity equality, then the upper bound: do not read anything newer than the row's own quote instant. That much stops the future leaking in, and it is where most implementations stop. The predicate is still satisfied by a version written years earlier. Selecting `the latest version at or before quoted_at` says nothing about *how much* earlier, and in a telematics fleet there are always drivers for whom the answer is `a very long time`. ## Where ancient versions come from - A device fails, is unplugged, or is never re-fitted after a service, and simply stops reporting. - A policy lapses and later resumes, leaving a long hole in the middle of that driver's history. - A vehicle is sold or laid up, and the driver reappears at renewal with a new car. - Connectivity fails persistently in one region, so a cohort of devices goes quiet together. None of this produces an error. The feature pipeline has nothing to write, so it writes nothing, and the last version stays open-ended and keeps matching. ## Two things go wrong at once 1. **The column stops meaning one thing.** Most rows carry a 30-day aggregate computed days before the quote; some carry one computed two years before it, and the two are indistinguishable in a single numeric column. Any relationship the model learns is an average over that mixture, and the mixture's composition changes over time. 2. **The row describes a decision nobody would make.** A pricing path handed a two-year-old aggregate is expected to treat the driver as having no usable telematics history and price them on the non-telematics path instead. A training row that quietly uses the ancient value is teaching the model about a case the live system routes elsewhere. The second point is the one that separates a good answer from a complete one: the problem is not only statistical dishonesty, it is that training and the live decision are describing different populations. ## The bound and the marker The fix is two changes to the same predicate: - **Add a lower bound.** Require the selected version to be no older than `quoted_at - L`, where `L` is a stated maximum acceptable age for that feature. - **Make the miss explicit.** When no version falls inside the interval, do not fall through to the newest older row. Emit a distinct no-recent-history marker, so the model can learn that absence of recent telematics is itself informative, rather than learning it through a two-year-old number. How `L` is chosen is a judgment about the feature, not about the join: a 30-day driving aggregate goes stale in weeks, while a vehicle characteristic barely goes stale at all. The same rule must hold wherever the feature is read, or training and pricing part company about which drivers count as having history. ## Do not confuse it with the aggregate's own window These are different numbers with different jobs, and this is the mistake to watch for: | | the aggregate's window | the join's lower bound | |---|---|---| | measured in | event time | age relative to the row's quote instant | | what it sets | how much driving is summarised | how old a version may be and still be used | | set by | the feature definition | the assembly rule and the serving policy | | changing it | changes what the feature means | changes which rows get a value at all | A 30-day window says nothing about whether the 30 days in question ended last Tuesday or in a different year. That question is the lower bound's, and only the lower bound's.
- Why emit a no-recent-history marker rather than leaving the column empty?An empty cell is ambiguous: it could mean a dormant device, a brand-new driver, or an assembly bug, and downstream handling will impute something without knowing which. An explicit marker states the reason, so the same condition can be recognised at pricing time and the two sides agree about which drivers have usable telematics history.
- How would you pick the maximum acceptable age for a 30-day driving aggregate?Ask how fast the quantity itself moves and how quickly a decision made on a stale value becomes indefensible. A trailing behaviour aggregate loses relevance in weeks, so an age bound of a few weeks is defensible; the honest way to set it is to measure how much the value changes over candidate gaps and pick the point where the change stops being material to the price.
saying these in an interview costs you the question
- Thinks an upper bound at the quote instant is the whole of correctness.
- Lets the join fall through to the newest older version silently.
- Confuses the aggregate's 30-day window with the join's age limit.
- Treats a dormant device as identical to a low-risk driver.
- Assumes a missing recent value should simply be imputed to the mean.