skip to content

Why does joining each quote row to a driver's latest trip aggregate, rather than an as-of join, inflate a telematics pricing model's offline score?

level: middleimportance: must knowfreq 74%

answer

  1. two clocks on one training row
  2. features frozen at the decision instant
  3. hindsight is not a feature
  4. join on quoted_at, never on now
  5. offline jumps, loss ratio does not

basics

~20 s

A latest-value join hands every training row aggregates computed from trips driven after that quote was priced, so the model learns from facts no quoting system could have held. The offline score then measures hindsight rather than skill.

solid answer

~50 s

A training row is a driver, an instant - the quote timestamp - and a label attached to what happened after that instant. A latest-value join reads the driver's feature row as it stands when the training job runs, which already folds in months of trips driven after the quote, including the weeks around the incident that produced the claim. Where that later driving correlates with the outcome, the model picks it up, and offline error drops sharply. The serving path cannot reproduce it: at quote time only the trips uploaded so far exist. The fix is an as-of join - for each row, select the feature version whose validity interval contains that row's own `quoted_at` and whose event window ends at or before it. The tell is an offline score far ahead of the incumbent with a live loss ratio that does not move.

code

sql · 12 lines
sql
SELECT q.quote_id,
       q.driver_id,
       q.quoted_at,
       f.harsh_braking_per_100km,
       q.claim_within_12m AS label
FROM   quotes q
LEFT JOIN feature_history f
       ON  f.driver_id        = q.driver_id
       AND f.valid_from      <= q.quoted_at
       AND (f.valid_to IS NULL OR f.valid_to > q.quoted_at)
       AND f.event_window_end <= q.quoted_at
       AND f.event_window_end >  q.quoted_at - INTERVAL '30' DAY

go deeper

for a junior

Hold on to the one-line rule: a training row may use only facts that existed when that row's decision was made. Be able to say why a feature table overwritten in place cannot honour it.

for a middle

Explain the join itself - each row carries its own timestamp, and the feature version is chosen by that timestamp rather than by the most recent write. Be ready to write the predicate on a whiteboard.

for a senior

Show the diagnosis and the expected outcome: rebuild with an as-of join, retrain, and predict that the offline gain evaporates. Add the per-row audit that the maximum event time never exceeds the row's quote instant.

for a principal

Treat it as a platform guarantee rather than a query someone remembers to write. Say who certifies that every training set was assembled point-in-time, and what a regulated pricing business loses when a model is trained on hindsight.

## The setting In telematics-based auto pricing, an in-car device summarises each trip - distance, night-time share, harsh-braking events - and uploads the summary when it can. A feature pipeline rolls those summaries into per-driver aggregates such as `harsh_braking_per_100km` over a trailing 30 days. A risk score is produced at two moments: when a quote is issued, and when a policy renews. The outcome that labels the row - did this policy produce a claim - settles months later. A training row is therefore three things bolted together: an **entity** (the driver), an **instant** (the quote timestamp, call it `quoted_at`), and a **label** attached to what followed that instant. ## The rule a training row must obey A training row may carry only values a system standing at `quoted_at` could have produced. Everything else is hindsight. This is **point-in-time correctness**, and it is a property of the *join*, not of the feature and not of the model. A latest-value join breaks it in the most ordinary way imaginable: it reads the feature table as it stands when the training job runs. | | latest-value join | as-of join | |---|---|---| | version selected | newest row per driver | the version valid at `quoted_at` | | trips included | everything uploaded so far | only what had arrived by `quoted_at` | | offline metric | flattered, often dramatically | honest | | reproducible at serving | no | yes | | same query next month | different values | identical values | ## Why the metric moves and the business result does not 1. The quote is priced in March; the claim arrives in August. 2. The training job runs in September and reads the driver's current aggregate, which now summarises March through September driving. 3. Drivers who crashed drove differently around the incident - a tow, a hire car, a long gap, a burst of unfamiliar night driving. The aggregate absorbs all of it. 4. The model finds that signal, because it is genuinely present in the future half of the feature. 5. Offline the model looks excellent. In production, the same feature at quote time contains none of that behaviour, so ranking barely improves and the loss ratio stays where it was. The asymmetry is what a design round is really testing: the offline number is not noisy, it is **systematically** wrong in one direction, and no amount of extra evaluation data corrects it. ## What the join actually has to select Two timestamps and two kinds of bound sit on one row: - an **upper bound in event time** - no trip that happened after `quoted_at` may enter the aggregate; - an **upper bound in knowledge time** - no version of the aggregate created after `quoted_at` may be selected, which is what a validity interval stored on the feature row gives you; - a **lower bound on the lookback** - a version far older than the quote should not be silently presented as current; - **one cutoff per row, not one per dataset** - a chronological train/test split fixes none of this, because every row on both sides still reads present-day values; - **the same rule at renewal** - one driver contributes several rows at different instants, and each one is entitled to a different value. ## Detecting it after the fact - Rebuild the training set with an as-of join bounded at each row's own `quoted_at` and retrain. If the gain collapses towards the incumbent, it was hindsight. - Assert per row that the maximum event time behind every feature is at or before that row's `quoted_at`. Violations cluster in the rows closest to the training cut, where the most restatement has happened. - Compare what the pricing path could have fetched for a given quote with what the training set recorded for that same quote. ## The part that is easy to get backwards Restating history is not the villain. A later, more complete aggregate is a better description of the driving that actually took place. It is simply not the value the pricing decision was made on, and a model that will price quotes must learn from what a quoting moment can see. Nor is this the same failure as fitting a transform on all rows before splitting: there the *pipeline order* leaks. Here every transform can be fitted impeccably and the row is still wrong, because the value it was handed never existed at the instant it claims to describe.

  • Offline AUC rose sharply and the live loss ratio is unchanged. How do you confirm point-in-time leakage rather than a serving defect?
    Rebuild the same training set with an as-of join bounded at each row's `quoted_at`, retrain, and compare. If the gain evaporates, the earlier number was hindsight. Then run a per-row audit: assert that the maximum event time behind every feature is at or before that row's quote instant. Leakage shows up as failing rows concentrated in the most recent quotes, where the aggregates have absorbed the most later driving.
  • The same driver is re-quoted every six months. Why does that make a latest-value join worse rather than better?
    Every row for that driver receives the same present-day aggregate, so rows that should differ are identical. The earliest row gets the most future information, and the column effectively encodes the driver's eventual behaviour rather than their behaviour at each pricing moment. An as-of join gives each renewal its own version, so the same driver's rows legitimately disagree with one another.

It is like re-reading last week's weather forecast in today's paper, after the paper has quietly rewritten it with what actually happened. The forecast looks uncannily accurate and predicts nothing.

saying these in an interview costs you the question

  • Says the leak is harmless because the label column itself was excluded.
  • Assumes a chronological train/test split makes the feature join correct.
  • Claims a much better offline score proves the feature is predictive.
  • Thinks recomputing the feature table nightly makes its history point-in-time correct.
  • Treats the upload timestamp and the driving timestamp as interchangeable.