Drill: Training Data with Point-in-Time Correctness

The prompt

The interviewer has told you the answer is leakage. Your job is to find where, design it out, and show that the design generalises to the next model.

Clarifying questions

• What exactly is the prediction and when is it made? "On the 1st of each month, for every active subscriber: will they cancel within the next 30 days?" → Prediction time is fixed and periodic; label window is [t, t+30d].
• How is churn recorded? "A cancelled_at on the subscription row, set when they cancel; sometimes backdated on refund." → Labels arrive up to 30 days late and can be revised.
• What features do they use? "Usage in the last 30/90 days, support tickets, payment failures, plan, tenure, and a 'days since last login'." → Rolling windows over event data plus slowly-changing attributes.
• Where do the features come from today? "A notebook querying the warehouse's current tables." → Almost certainly joins to current attribute values — the leak.
• Scale? "2M subscribers, ~40 features, 24 months of history." → Small; correctness dominates, not throughput.
• Retrain cadence? "Monthly." → The pipeline must produce a new versioned dataset each month with fully-matured labels.

Move 1 · Requirements & estimate

Where leakage comes from

Name the specific mechanisms; each is a place the current notebook is probably wrong.

• Current-state joins. Joining the training row for January to the subscriber's plan today. A subscriber who churned in February and downgraded to "free" in March now has "plan = free" as a feature for their January row. The model learns "free plan → churned" — which is the label, encoded.
• Windows that cross the prediction time. "Usage in the last 30 days" computed over a calendar month that straddles the 1st — including days after the prediction. Or a feature refreshed daily at 06:00 used for a 00:00 prediction.
• Label-derived features. days_since_last_login computed as of the dataset build date, not the prediction date: a churned user's last login is by definition before their cancel. Perfect predictor, zero value.
• Immature labels. Including the most recent month, where most churn hasn't been recorded yet — the model learns that recent months don't churn.
• Random splits on time-series. Training on March to predict February.

The board

  RAW (immutable)              HISTORY (time-aware)                     DATASET BUILD (per run)
  ┌───────────────┐   CDC    ┌──────────────────────┐
  │ subscriptions │────────▶ │ subscription_history │  SCD2: plan, status, valid_from/valid_to
  │ (Postgres)    │          └──────────┬───────────┘
  ├───────────────┤   stream ┌──────────▼───────────┐     ┌──────────────────┐   ┌─────────────────┐
  │ usage events  │────────▶ │ feature_history      │     │ SPINE            │   │ as-of join      │
  │ tickets       │          │ (entity, feature_ts, │────▶│ (sub_id, t,      │──▶│ feature_ts <= t │──▶ dataset
  │ payments      │          │  available_ts, value)│     │  label, label_v) │   │ avail_ts <= t   │    v2026-09
  └───────────────┘          └──────────────────────┘     └────────▲─────────┘   │ ttl bound       │    (Iceberg
        │                                                          │             └─────────────────┘     snapshot +
        │  labels: cancelled_at (T+≤30d, revisable) ───────────────┘                                     manifest)
        │                                                                                                     │
  ─────────────────────────────────────────────────────────────────────────────────────────────────────────── ▼
   GATES: label maturity ≥ 30d ◆  leakage checks (feature ⟂ future) ◆  schema/dist ◆  time-based split ◆  registry → model vN

The spine and the as-of join

The design's centre is a spine: one row per (entity, prediction_time, label). Everything else is joined to the spine, as of its timestamp. Attributes come from an SCD2 history (what was the plan on the 1st of January), windows are computed from events with event_ts < t, and each feature carries two timestamps — when it was true (event time) and when it became available to the system (the job that produced it). The join uses the stricter of the two.

-- 1. spine: subscribers active at t, with a matured label
WITH spine AS (
  SELECT h.subscriber_id, m.month_start AS t,
         CASE WHEN h2.cancelled_at > m.month_start
               AND h2.cancelled_at <= m.month_start + INTERVAL '30 days' THEN 1 ELSE 0 END AS label
  FROM months m
  JOIN subscription_history h
    ON h.valid_from <= m.month_start AND m.month_start < h.valid_to      -- state as of t (SCD2)
   AND h.status = 'active'
  LEFT JOIN subscription_current h2 ON h2.subscriber_id = h.subscriber_id
  WHERE m.month_start + INTERVAL '60 days' <= CURRENT_DATE                 -- label maturity gate
),
-- 2. windowed features from events strictly before t
usage AS (
  SELECT s.subscriber_id, s.t,
         COUNT(*) FILTER (WHERE e.event_ts >= s.t - INTERVAL '30 days') AS sessions_30d,
         COUNT(*) FILTER (WHERE e.event_ts >= s.t - INTERVAL '90 days') AS sessions_90d,
         s.t - MAX(e.event_ts)                                             AS days_since_login   -- as of t, not today
  FROM spine s LEFT JOIN usage_events e
    ON e.subscriber_id = s.subscriber_id AND e.event_ts < s.t
  GROUP BY 1,2
)
-- 3. slowly-changing attributes as of t, precomputed features as of availability
SELECT s.*, u.sessions_30d, u.sessions_90d, u.days_since_login,
       h.plan, h.region,
       f.payment_failures_90d
FROM spine s
JOIN usage u USING (subscriber_id, t)
JOIN subscription_history h
  ON h.subscriber_id = s.subscriber_id AND h.valid_from <= s.t AND s.t < h.valid_to
ASOF JOIN feature_history f
  MATCH_CONDITION (s.t >= f.available_ts)                                -- what the system KNEW at t
  ON s.subscriber_id = f.subscriber_id
WHERE s.t - f.feature_ts <= INTERVAL '7 days';                            -- staleness bound

Three lines carry the whole answer: the SCD2 range predicate, event_ts < s.t, and available_ts in the as-of join. Point at them. Depth: Deep dive 02.

Versioning the dataset

A training set is an artifact, not a query. The build job writes an immutable dataset — an Iceberg table snapshot, or Parquet files plus a manifest of paths and content hashes — tagged with the run (churn/v2026-09), the feature-definition versions, the label version, the code commit and the raw-partition watermarks it read. The model registry entry points at that tag. This is what makes "rebuild model v14" a command rather than an archaeology project, and what lets you diff two datasets when a metric moves. Because labels are revisable, the dataset records the label snapshot too; a later rebuild with corrected labels is a new version, never an in-place edit.

Splits, sampling & the label delay

• Time-based split. Train on months 1–18, validate on 19–21, test on 22–24. Never random. Report the gap between validation and test performance — that gap is the drift you will see in production.
• Entity leakage across splits. The same subscriber appears in many months. Fine for a churn model where each month is a real prediction; wrong for a model that should generalise to unseen entities — then split by entity.
• Label maturity. The last two months are excluded or held out with the caveat that labels are partial. Say the number: "fully-matured labels lag by 30 days, so the freshest training month is two months back."
• Class balance. Monthly churn ~3%; keep all positives, sample negatives to a ratio, record the weights so probabilities can be recalibrated.
• Leakage tests as gates. Automatically flag any feature with implausible univariate power, any feature whose max event time exceeds t, and any feature whose distribution differs between labelled and unlabelled rows.

Trade-offs

What the interviewer is listening for

• That you diagnosed the leak from the metric and named the mechanism before designing.
• The spine as the organising idea; every feature joined as of the spine timestamp.
• SCD2 for attributes, event-time windows strictly before t, availability time in the as-of join, a staleness bound.
• Label maturity as a gate with a number; revisable labels versioned.
• The dataset as an immutable, addressable artifact linked from the model registry.
• Time-based splits and automated leakage checks — and the reusable step for the next three models.

Follow-up probes

• "Data science wants to add a feature that needs 24 months of history. Tomorrow." — Backfill from raw events by month partition, idempotent, writing feature_history with the correct feature_ts. The honest available_ts is the backfill time, but the as-of join on available_ts would then never see that history. Either set available_ts = feature_ts + normal job latency and accept knowingly that the model would not have had the feature then, or train only on rows after the live job began.
• "Cancellations get backdated on refund. Does that break anything?" — It changes labels after the fact. Store the label snapshot per dataset version; rebuild as a new version; compare. Report label-revision rate as a metric.
• "How would you prove to the scientists it was the data?" — Rebuild the dataset with the as-of pipeline, retrain the same model, show AUC drop to a plausible ~0.75 and the feature-importance shift away from the leaking columns. Then show that number holds in production. Evidence, not argument.
• "This is 8 GB. Why not just do it in pandas?" — You can; the value is the discipline, not the engine. Do it in SQL on the warehouse so the same job runs monthly, is versioned, and the next model reuses the spine step.

The answer at four levels

Next, the other half of the correctness problem — keeping features fresh and consistent when predictions happen in milliseconds: 05 · Drill: Real-Time Features for Fraud & Ranking.

✓ Check yourself

Answer from memory before opening each one.

1. A loan-default model joins each January application to the customer's credit limit as it is today. A customer who defaulted in March had the limit cut to zero. What does the model learn, and how do you build the join so it cannot?

   Show answer

   It learns that a zero limit predicts default, which is the label encoded in a feature. Build the attribute from a slowly changing history with validity ranges and join on valid_from at or before t and t before valid_to. See where leakage comes from and the spine.

2. Churn is the label over the next 30 days, and cancellations can be recorded up to 30 days late. Today is 15 October. What is the newest month-start whose rows you may train on, and why?

   Show answer

   1 August. A month needs its 30-day window plus 30 days of recording delay to be mature, so month_start plus 60 days must be on or before today. 1 August plus 60 days is 30 September; 1 September plus 60 days is 31 October. See splits and the label delay.

3. You backfill a feature for 24 months and write available_ts as the day of the backfill. What does the as-of join do with those rows, and what are your options?

   Show answer

   The join sees no available value for any historical prediction time, so the backfilled history is invisible. You can set available_ts to feature_ts plus the normal job latency and accept that the model would not truly have had the feature then, or train only on rows after the live job began. Say which one you chose. See the follow-up probes.

4. A new model must score prospects who have never been customers. Which split do you use, and what is wrong with reusing the churn model's split?

   Show answer

   Split by entity, and still order by time. In the churn split the same subscriber appears in many months, which is fine when every month is a real prediction. For a model that must generalise to unseen entities, that repeat lets entity-specific patterns leak from train into validation. See splits and the label delay.

5. Someone proposes deduplicating and compacting the raw events table in place to save storage. What breaks in this design?

   Show answer

   The dataset's recorded raw-partition watermarks then point at data that no longer exists, so a past training set cannot be rebuilt or diffed. Keep raw append-only and do all cleaning downstream in versioned layers. See versioning the dataset.

6. Data science argues that 0.92 AUC proves the model is good and that production is a monitoring problem. How do you test that claim?

   Show answer

   Treat the number as evidence of leakage, since 0.92 is too good for churn. Rebuild the dataset with the as-of pipeline, retrain the same model, and look for an AUC fall to a plausible level and a shift in feature importance away from the leaking columns. Then confirm the lower number holds in production. See the follow-up probes.

Takeaway

• A metric that is too good, such as 0.92 AUC on churn, is evidence of leakage. Name the mechanism before you design.
• Organise the pipeline around a spine of one row per entity, prediction time and label, and join every feature as of that time.
• Attributes come from slowly changing history, windows use events strictly before t, and the as-of join uses availability time with a staleness bound.
• Labels need a maturity gate with a number. Revised labels create a new dataset version rather than an in-place edit.
• A training set is an immutable artifact, a snapshot or a manifest of hashes, that the model registry points at. It rests on an append-only raw layer.
• Split by time, and by entity when the model must generalise to new entities. Run leakage checks as automated gates.

Training sets are one half of correctness. The other half is serving fresh features in milliseconds. → Drill: Real-Time Features for Fraud & Ranking

Further reading

• ASOF JOIN — Snowflake documentation. The as-of join syntax behind the chapter's spine query.
• Point-in-time joins — Feast documentation. The same idea as a feature-store API, including the timestamp rules.
• Branching and Tagging — Apache Iceberg documentation. Tagging a snapshot so a model can point at an immutable dataset.
• Cross-validation: evaluating estimator performance — scikit-learn documentation. Includes the time-series split and why shuffled folds mislead on ordered data.
• Rules of Machine Learning — Google for Developers. Guidance on training-serving skew and testing a model on data from after its training period.