Case Study 1: Six Weeks Investigating a Model, One Afternoon Finding a Join

"By week four we had tried three architectures, two regularization schemes, and a feature-selection pass. Every one of them produced exactly the same result, which should have told us something."

Executive Summary

Kestrel's churn model scored AUC 0.83 in offline evaluation and 0.59 in production, from the day it was deployed.

Six weeks were spent investigating the model. Three architectures, two regularization approaches, a feature-selection pass, and a hyperparameter search — every one reproduced the same gap, which in hindsight was the diagnosis rather than a series of failed experiments.

The cause was a two-table join on a primary key, containing no timestamp, no window function, and nothing that looks wrong:

SELECT l.customer_id, l.churned, f.orders_to_date
  FROM labels l
  JOIN customer_features f USING (customer_id);

customer_features held the current value. The labels described events months old. The model was trained on a feature computed after the outcome it was predicting.

The corrected model scores 0.591 offline and 0.59 in production — the same number the deployed model had been producing all along. The model never degraded. It was never that good.

Skills applied: point-in-time correctness (§32.4); the diagnostic that separates immediate failure from gradual failure (§32.2); the time-split check; and the organizational problem of a defect diagnosed by the wrong specialty.

Background

The model was built properly by competent people. A churn model over 1,904,221 customers, twelve features, gradient-boosted trees, five-fold cross-validation, a held-out test set.

The offline evaluation was clean:

cross-validation AUC (5-fold)     0.831 +/- 0.004
held-out test set                 0.830
calibration                       good
feature importances               plausible

Nothing in that block is a warning sign. The variance across folds is tight, the test set agrees with cross-validation, and the top features — orders_to_date, days_since_last_order, revenue_90d — are exactly what a domain expert would predict.

It was deployed behind a retention campaign: customers scoring above a threshold received an offer.

The Problem

The production monitoring reported 0.59 within the first week, once enough labels had matured.

The reaction was reasonable and wrong. The model had been validated at 0.83; production said 0.59; therefore something about production was different. That framing sent the investigation into the model, and it stayed there for six weeks.

week 1   distribution shift?      feature means compared     no material difference
week 2   overfitting?             stronger regularization    CV 0.828, prod 0.59
week 3   architecture?            logistic regression        CV 0.821, prod 0.59
                                  random forest              CV 0.826, prod 0.59
week 4   feature selection        top 5 features only        CV 0.812, prod 0.59
week 5   hyperparameter search    400 configurations         CV 0.834, prod 0.59
week 6   ...

⚠️ Failure Mode — the constant that should have been the answer

Look down the production column. It is 0.59 in every row.

Five substantially different models — different architectures, different regularization, different feature counts, one with only five features — all produced identical production performance while their cross-validation scores moved around.

That pattern has exactly one explanation, and it is not a model problem. If changing the model changes offline performance and does not change production performance, then offline and production are not evaluating the same thing. Something present offline is absent in production, and it is worth more than every modeling change combined.

Why six weeks of smart people missed it: each week's experiment was designed to test a hypothesis about the model, and each week's result was read as "that hypothesis was wrong, try the next one." Nobody read the column. The invariant across experiments was in the data, and reading it required stepping outside the frame the investigation had adopted in week one.

The generalizable diagnostic: when a series of experiments produces the same result, the constant is the finding. It is the same reasoning as a control group, and it is remarkably easy to miss from inside a sequence of individually-sensible experiments.

The Analysis

The break came from someone outside the modeling team. A data engineer asked to see the training query — not because they suspected anything, but because they had been asked to help productionize the retraining job.

Two questions, in order:

"What timestamp are these features as of?" No answer. The feature table had no timestamp column at all; it was rebuilt nightly by a full refresh.

"When were the labels generated?" From a churn definition applied to a historical window — events between four and fourteen months old.

That is the entire diagnosis and it took under twenty minutes.

🔎 Read the Plan — the two-line check that confirmed it

Before proposing a fix, the engineer confirmed it with a measurement rather than an argument, which mattered because the modeling team had a well-supported belief and a single afternoon's assertion was not going to move it.

The check: train on data up to a cutoff, evaluate strictly after it.

text evaluation method AUC ───────────────────────────────────────────────────── 5-fold cross-validation (random split) 0.831 held-out test set (random split) 0.830 time split: train <= day 200, test > day 200 0.594 <--

The collapse from 0.830 to 0.594 under a time split is the signature of leakage, and it is the single most useful diagnostic in this chapter.

Why the random splits could not see it: the leak is present in every row, so it is present in every fold. Cross-validation measures consistency, not correctness — five folds agreeing to ±0.004 means the leak is reliably present, not that the model is reliably good.

The number that ended the argument was 0.594 against production's 0.59. Not "there might be leakage" but "here is a validation method that reproduces production exactly, and here is the one that does not." Reproducing the failure is worth more than explaining it.

Then the mechanism, which the team needed in order to believe a join could do this:

Churners stop ordering. A customer who churned on day 150 has an orders_to_date today that is essentially their day-150 value. A customer who did not churn has kept ordering for another six months.

                        as of the prediction point    as of today
churned customers                       5.0                6.9
retained customers                      6.5               16.3

At the prediction point the two groups barely separate — 5.0 against 6.5 — which is why the honest AUC is 0.591. Today they are 2.4x apart, and that gap is the label.

code/pit_join.py reproduces the whole thing on 20,000 synthetic customers: AUC 0.830 with the naive join and 0.591 with the as-of join, differing on 97.1% of rows.

The Decision

Four changes.

One: the feature table becomes append-only with validity ranges. Chapter 20's Type 2 pattern.

{{ config(materialized='incremental', unique_key=['customer_id','valid_from']) }}
SELECT customer_id,
       order_ts                               AS valid_from,
       LEAD(order_ts) OVER (PARTITION BY customer_id
                            ORDER BY order_ts) AS valid_to,
       COUNT(*) OVER (PARTITION BY customer_id
                      ORDER BY order_ts)       AS orders_to_date
  FROM {{ ref('fct_orders') }}

Two: training uses an as-of join. One implementation, in platform/ml/asof.py, tested against the invariant that no returned value may have a timestamp after its event.

Three: a time split is mandatory in evaluation, alongside cross-validation, and both are reported.

Four: the nightly full-refresh feature table is deleted, because its existence is what made the mistake available.

📐 Design Decision — deleting the table, rather than documenting it

The first proposal was to keep the current-value table and document that it must not be used for training. It was rejected, and the argument is worth recording because the instinct to document is strong.

Three reasons:

  • The wrong table is the easier one to use. A one-line join on a primary key versus a lateral join with a timestamp predicate. Documentation does not change relative difficulty, and under deadline the easier path wins.
  • The failure is silent and delayed. Using the wrong table produces a better number, weeks before anyone can tell it is wrong. A control that relies on someone not doing the easy thing, and that gives no feedback when they do, is not a control.
  • The table's legitimate users did not need it. Two dashboards read it, and both were correctly served by an as-of query with event_ts = current_timestamp — the current value is a special case of the as-of query, so nothing was lost.

The general principle, and it recurs throughout this book: prefer removing the wrong option to documenting it. Chapter 27's read-only staging role, Chapter 31's dropped columns, and this table are the same move — make the mistake unavailable rather than discouraged.

The honest caveat: this worked because the wrong table had almost no legitimate use. When it does, you cannot delete it, and you are back to documentation plus a test. Kestrel added the test anyway — an assertion that any table joined to labels has a validity column — which is the durable half.

What Happened

Before After
Offline AUC (cross-validation) 0.830 0.591
Offline AUC (time split) not computed 0.594
Production AUC 0.59 0.59
Offline/production gap 0.24 0.00
Feature table nightly full refresh append-only, Type 2
Training joins one-line, by key as-of, one implementation
Time-split evaluation none required

The measured model performance got worse and the model got better, which took some explaining internally and is exactly the right outcome. The 0.83 was never real.

What happened next is the part the team considers most important.

With an honest baseline, the modeling work that had been wasted for six weeks became productive. Features that had been dismissed as unimportant — they added nothing on top of a leaked feature that already encoded the label — turned out to matter. The model reached 0.68 within a month, which is genuinely better than 0.591 and genuinely worse than the fictional 0.83.

The retention campaign was re-evaluated. It had been targeting on a 0.59-quality score while everyone believed it was 0.83-quality, and the campaign's own measured lift had been disappointing for months without anyone connecting the two.

Lessons

  1. A model good offline and bad in production immediately is a data problem. Gradual decay is drift; an instant gap is leakage or skew. The distinction is the first question to ask and it redirects the entire investigation.

  2. ⚠️ When a series of experiments produces the same result, the constant is the finding. Five different models, five different CV scores, one identical production number — and nobody read the column.

  3. The leak was invisible at every checkpoint. The query is a primary-key join. The data passes every assertion. Cross-validation is tight. The metric is excellent — and the failure mode of leakage is good news, which nobody investigates.

  4. 🔎 Train to a cutoff, evaluate strictly after it. 0.830 → 0.594 was the whole diagnosis, and 0.594 against production's 0.59 is what ended the argument. Reproducing a failure beats explaining it.

  5. Cross-validation measures consistency, not correctness. Five folds agreeing to ±0.004 means the leak is reliably present.

  6. The mechanism generalizes: any feature whose trajectory differs by outcome will leak if joined at its current value. Churners stop ordering, so the gap between then and now is the label.

  7. 📐 Prefer removing the wrong option to documenting it. The wrong table was a one-line join and the right one a lateral join with a predicate — documentation does not change relative difficulty, and the failure gives no feedback for weeks.

  8. The current value is a special case of the as-of query, so deleting the current-value table cost its legitimate users nothing.

  9. A correct baseline makes modeling work productive again. Six weeks of experiments had been measuring nothing; the same effort against an honest 0.591 reached 0.68 in a month.

  10. The defect was found by someone outside the specialty that owned the symptom, in twenty minutes, by asking what timestamp the features were as of. That question belongs in every ML data review.

Questions for Discussion

  1. Six weeks were spent inside a frame chosen in week one. What mechanism would surface "we may be investigating the wrong layer" earlier — and what does it cost when it fires falsely?

  2. The team believed a 0.83 model. Deleting that belief cost something. How would you deliver this finding to a team that has been working on it for six weeks?

  3. Cross-validation and the time split disagreed by 0.236. Should a time split be mandatory for every model, or are there cases where it is the wrong evaluation?

  4. The current-value table was deleted. Construct a case where it cannot be, and design the control you would use instead.

  5. The retention campaign had been underperforming for months. Who should have connected the campaign's lift to the model's quality, and what would have made that connection routine?

  6. The honest model reached 0.68. Was the six weeks wasted, or was it a necessary cost of finding out?

  7. orders_to_date leaked because churners stop ordering. Name three other features with the same structure — and say how you would detect the property in general rather than case by case.