Skip to content
ML Academy

Core day 2 of 60 · Week 1 · Part I · application depth

Problem Framing and Data Modeling

Revenue management asks for a model that "predicts final bookings" so it can close the cheapest fare class early on flights that will fill. Before any model: what is one row, what exactly is the target, when is the forecast made, and which columns does the model really have at that moment?

By the end: For a forecasting request you write a one-page framing card (decision, grain, target, cutoff and horizon, feature availability, baseline, metric unit, action cost) and flag every column that would leak.

At a glance

Before any model, fix one row, one target, one cutoff and the columns that truly exist at that cutoff, then set the baseline every model must beat.

  • Cutoff

    Every forecast is made at a moment; here D-14, 14 days before departure.

  • Grain and target

    One row = one departure; the target is bookings_final, not passengers.

  • Availability test

    A column is a feature only if it existed at the cutoff, with that value.

  • Baseline

    Bookings on hand plus past pickup: 7.4 seats MAE on 2024 departures.

  • Leak alarms

    A perfect or suspiciously good score means the answer got into the inputs.

Recall · 5 min

Without looking back: name the six decision-first questions from Day 1, and say why a baseline computed on the evaluation period is not a fair bar for a model.

Open due reviews

Sign in to record this block.

Concept · 10 min

Every prediction is made at a moment, the cutoff. A column is a legitimate input only if it existed at the cutoff, with the value it had then. The training table hides this: every past flight has fares, passengers and load factor filled in. History shows final values; production shows only what was known at D-14.

A feature is usable only if it exists at the cutoff - the training table hides when each value appeared

Predict first

At a D-14 cutoff, which input is legitimate for forecasting final bookings?

Text description of the figure

A horizontal timeline from booking open (D-90) to departure (D-0) and settlement, with a movable cutoff and horizon. Feature rows (bookings on hand, calendar, competitor price, average fare, passengers) light up only from the moment each value exists. Columns that appear after the cutoff turn red when switched on.

The life of one departure Diagram
Booking opensabout D-90Cutoff: D-14decide: closeclass L?Label windowthe 14 days you forecastDeparture: D-0bookings_final knownSettlementrevenue weeks later
  1. Booking opensabout D-90
  2. Cutoff: D-14decide: close class L?
  3. Label windowthe 14 days you forecast
  4. Departure: D-0bookings_final known
  5. Settlementrevenue weeks later

Everything recorded before the cutoff is the observation window; values that appear later (fares paid, passengers, load factor) are outcomes. A later cutoff adds information but shortens the horizon: the decision fixes the cutoff, not the data. Calendar and schedule are known for the whole horizon.

The framing card, in this order Sequence
  1. Decision

    At D-14, close the lowest fare class (L) or keep it open. Owner: the RM analyst, daily.

  2. Grain

    One row per departure (flight_no x date). The booking curve has 13 snapshot rows each.

  3. Target

    bookings_final at D-0. Not passengers, not revenue. Capped by capacity (Day 28).

  4. Cutoff and horizon

    The D-14 snapshot; horizon 14 days; label window from cutoff to departure.

  5. Availability test

    For every column: did it exist at D-14, with that value?

  6. Baseline, metric, cost

    Pickup baseline; seats of absolute error; about EUR 80 per empty seat.

The cutoff splits every booking curve: known on the left, forecast on the right Illustration
Booking curves, IST-AYT050100150200D-90D-60D-30D-14D-0Days before departureBookings on handpickup +111cutoffunknown

Left of the cutoff is the observation window; everything to the right is the label window the model must forecast. The highlighted departure still has most of its bookings to come.

Final bookings vs bookings at D-140501001502000255075100125150175Bookings on hand at D-14Final bookings (D-0)limit 191 (capacity + 6%)naive: y = xadditive: + 80 seatsmultiplicative: x 1.93

Scored on the later 60 departures: naive 75.6, additive pickup 14.6, multiplicative pickup 23.1 seats of absolute error. Both use only departures that had flown. Points pile up at the booking limit: there the target is capped (Day 28).

Synthetic: 120 IST-AYT departures from the seeded generator behind the availability widget (Day 2). Fit on the first 60 departures, scored on the last 60.

The pickup baseline adds what similar departures gained after the cutoff. It is a model; the rows it is averaged over decide whether it is honest.

The availability test at D-14 Table
ColumnAt D-14?Why
cumulative_bookings, 14 days outyesa snapshot taken at the cutoff
route, weekday, holiday, capacityyesscheduled in advance
competitor_priceunclearone value per departure; when was it observed?
avg_fare, fare_level, passengers, load_factornooutcomes of the period you forecast

"Unclear" is not "yes". It becomes a feature only when a data contract states when the value was recorded.

Today's query: four predictors, one honest Chart
  • A naive: bookings on handignores the pickup to come48.45 seats
  • B pickup baseline (2023)the bar for Days 9-167.4 seats
  • C pickup averaged on 2024leak: scored on its own rows7.06 seats
  • D latest snapshotleak: the target itself0 seats

Mean absolute error on 6,688 departures from 2024-01-15 to 2024-12-31 (airline sample database, synthetic).

  • Write a framing card before any model: decision, Grain (unit of analysis), target, cutoff and horizon, availability, baseline, metric unit, action cost. You reuse it on Days 9-16.
  • Key terms: label window, pickup forecast, leakage, data contract and point-in-time correctness.
  • Inputs known in advance (calendar, holidays, scheduled capacity) are legitimate for the whole horizon (Day 24).

Assumptions

  • Snapshots are recorded when they claim to be and are never overwritten by later corrections; if the source system backfills, even the D-14 snapshot can leak.

  • Past pickup represents the evaluation period; the competitor capacity step on 2024-03-01 (three routes) is a reason to check this per route.

  • The target is bookings, not demand; capped bookings suit this decision but not a pricing what-if (Day 5).

Sign in to record this block.

Application and workplace decision · 10 min

Run the query on the airline sample database:

  • It builds the departure-grain table with the D-14 snapshot and scores four predictors of bookings_final on departures from 2024-01-15 to 2024-12-31.
  • Forecasts for those departures are made from 2024-01-01 onward, so every 2023 departure had already flown and may feed the baseline.
  • Before running it, predict which row will look best, and whether you could use it.

SQL (airline sample database)

WITH dep AS (             -- grain: one row per flight departure (flight_no x date)
  SELECT f.date, f.flight_no, f.route, f.weekday, f.bookings_final,
         b.cumulative_bookings AS bk_d14                 -- known at the cutoff
  FROM flight_day AS f JOIN booking_curve AS b
    ON b.date = f.date AND b.flight_no = f.flight_no
   AND b.days_to_departure = 14                          -- the snapshot filter
),
latest AS (               -- the same join WITHOUT the snapshot filter, keeping the latest value
  SELECT date, flight_no, MAX(cumulative_bookings) AS bk_latest
  FROM booking_curve GROUP BY date, flight_no
),
pickup_2023 AS (          -- departures that had all flown before any 2024 forecast was made
  SELECT route, weekday, AVG(bookings_final - bk_d14) AS pickup
  FROM dep WHERE date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY route, weekday
),
pickup_eval AS (          -- the same average computed on the evaluation departures themselves
  SELECT route, weekday, AVG(bookings_final - bk_d14) AS pickup
  FROM dep WHERE date BETWEEN '2024-01-15' AND '2024-12-31' GROUP BY route, weekday
),
ev AS (
  SELECT d.*, p.pickup AS pickup_pit, q.pickup AS pickup_leak, l.bk_latest
  FROM dep AS d
  JOIN pickup_2023 AS p ON p.route = d.route AND p.weekday = d.weekday
  JOIN pickup_eval AS q ON q.route = d.route AND q.weekday = d.weekday
  JOIN latest AS l ON l.date = d.date AND l.flight_no = d.flight_no
  WHERE d.date BETWEEN '2024-01-15' AND '2024-12-31'
)
SELECT 'A naive: bookings on hand at D-14' AS method, COUNT(*) AS departures,
       ROUND(AVG(ABS(bookings_final - bk_d14)), 2) AS mae_seats FROM ev
UNION ALL SELECT 'B baseline: D-14 bookings + 2023 pickup', COUNT(*),
       ROUND(AVG(ABS(bookings_final - (bk_d14 + pickup_pit))), 2) FROM ev
UNION ALL SELECT 'C leak: pickup averaged over 2024 itself', COUNT(*),
       ROUND(AVG(ABS(bookings_final - (bk_d14 + pickup_leak))), 2) FROM ev
UNION ALL SELECT 'D leak: latest snapshot instead of D-14', COUNT(*),
       ROUND(AVG(ABS(bookings_final - bk_latest)), 2) FROM ev;

This code is executed in the repository's checks (pnpm lesson:py / lesson:sql). It is not run on this site; copy it into your notebook.

Sign in to record this block.

Mini-assessment · 8 min

Your first answer is your evidence. You can re-answer later in practice mode; that is labelled practice.

Question 1 · easy

You forecast `bookings_final` for each departure at a D-14 cutoff. Which column is a legitimate input?
Options

How confident are you? (optional, used for calibration)

Question 2 · medium

`flight_day` has one row per departure; `booking_curve` has 13 snapshot rows per departure (90, 60, ..., 0 days out). An analyst joins them on `date` and `flight_no` without filtering `days_to_departure`, then sums `bookings_final` by route for 2024. By what factor is each route total inflated?
times the true total

How confident are you? (optional, used for calibration)

Question 3 · hard

A telecom churn model scores customers on the 1st of each month; the target is "cancels within the next 30 days". Offline ROC-AUC is 0.97; in the first live month it is 0.71. The strongest feature is "retention calls in the last 30 days", computed for every customer on the day the training table was extracted. What is the most likely cause, and the first fix?
Options

How confident are you? (optional, used for calibration)

Question 4 · medium

You forecast final bookings for a departure on 2024-03-20 at its D-14 cutoff, which is 2024-03-06. The baseline adds the average pickup (bookings gained between D-14 and D-0) for the same route and weekday. Which departures may feed that average?
Options

How confident are you? (optional, used for calibration)

Question 5 · hard

The data owner tells you `competitor_price` is scraped nightly and each departure's value is overwritten by the latest scrape, so a past flight holds the price seen on its last scrape before departure (usually D-0 or D-1). Your model forecasts at D-14. What do you do with the column?
Options

How confident are you? (optional, used for calibration)

Sign in to complete lessons and keep your progress.

Technical depth · optional · 29 min in total

Open only what you need. Nothing here is required to complete the day; the assessment covers the core above.

The prediction task as an equation5 minThe supervised task as an equation, an as-of join worked on three bureau records, the leak types as testable conditions, and a grain break on three orders.
The supervised prediction task, written down once Formula
f^=arg⁡min⁡f  1n∑iL(f(xi),yi)xi=ϕ(H≤ti)yi=g(H(ti, ti+h])\begin{aligned} \hat f &= \arg\min_{f}\; \tfrac{1}{n} \textstyle\sum_{i} L\big(f(\mathbf{x}_i), y_i\big) \\ \mathbf{x}_i &= \phi\big(\mathcal{H}_{\le t_i}\big) \\ y_i &= g\big(\mathcal{H}_{(t_i,\, t_i + h]}\big) \end{aligned}
(i, ti)(i,\, t_i)
one row = one entity at one cutoff: a departure at D-14, an account on the 1st of the month
H≤ti\mathcal{H}_{\le t_i}
everything loaded into the warehouse by the cutoff - the only legal input
ϕ\phi
the feature pipeline; it must be computable from that history alone, in training and in serving
g,  hg,\; h
the target as a function of the label window of length h: bookings gained, a churn flag, a default
LL
the loss; stating its unit (seats, EUR, calls) is the metric line of the framing card

Leakage in one line: any part of phi that reads history beyond the row's cutoff. The framing card is this equation with every symbol given a business name.

Worked by hand: a bureau score joined as of two cutoffs (collections model) Table
CutoffFilter on event time onlyEvent AND load time <= cutoffPlus freshness limit 30 days
Apr 10655 (computed Apr 08 - but loaded Apr 12: leak)618 (computed Apr 02, loaded Apr 04)618 kept: 8 days old
May 20655 (computed Apr 08)655 (loaded Apr 12)missing: 42 days old

Hypothetical records: scores 642 (Mar 03, loaded Mar 05), 618 (Apr 02, loaded Apr 04), 655 (Apr 08, loaded Apr 12). Verified with pandas 3.0 merge_asof on load time. Event-time filtering alone backfills a value the desk never had.

Grain, keys and the four leaks, each written as a condition you can test Table
ObjectFormal conditionTest in SQL or pandasExample outside airlines
Graina key K with at most one row per value of KCOUNT(*) = COUNT(DISTINCT K)one row per account x scoring month (collections)
Join keeps the grainthe join key is unique on the right-hand tablerow count before = row count afterorders joined to a returns table with several rows per order (retail)
Outcome columnx is computed from events in the label window (t, t + h]trace x to its source event; event time > t failsdischarge diagnosis used to predict readmission at admission (healthcare)
Later snapshotx uses a record with load time > tassert max(load_time) <= cutoff per rowsensor reading backfilled after the alarm (manufacturing)
Evaluation-period statisticphi is fitted on rows whose label windows end after tfit every average or encoder on rows that closed before ttarget encoding of tariff fitted on all months (telecom)
Overwritten valuethe stored v_j is a later version than the one loaded by ta versioned table or a change log existsa credit limit restated after default (banking)

The left column is the framing card's vocabulary; the middle column is the same rule as task-eq above, one symbol at a time. A leak is any row that fails a test in the third column.

Every offline score rests on two assumptions Comparison
CriterionSame distributionSame features
In symbolstraining and serving rows share the joint law of (x, y)phi is the identical function in training and serving
Breaks whenthe world moves: a competitor's capacity step, a new route mixtraining uses a SQL backfill, serving a separate service
Guarded bymonitoring inputs and errors after launch (Day 19)one shared feature pipeline or feature store

The task equation above holds only if both are true. A broken second assumption is training-serving skew (Day 19); it looks like drift but appears on the first live day.

Worked by hand: a join that breaks the grain (hypothetical online shop) Table
StepRowsSUM(order_value), EURCOUNT(*) vs COUNT(DISTINCT order_id)
orders: O1 40, O2 25, O3 60 (grain: one row per order)31253 = 3: key unique
returns: O1 has 2 returned lines, O3 has 1, O2 none (grain: one row per returned line)3-3 vs 2: not unique on order_id
orders LEFT JOIN returns ON order_id4: O1 twice40 + 40 + 25 + 60 = 1654 vs 3: the grain broke
Fix: aggregate returns to one row per order (returned_lines), then join31253 = 3: grain kept

Revenue inflated by EUR 40 (32%) with no error message. The rule: a join keeps the left grain only if its key is unique on the right; test it with the two counts before any SUM. Verified in pandas.

Cutoff, horizon and windows3 minHow the observation window, the cutoff and the label window fit together for one departure.
The life of one departure Diagram
Booking opensabout D-90Cutoff: D-14decide: closeclass L?Label windowthe 14 days you forecastDeparture: D-0bookings_final knownSettlementrevenue weeks later
  1. Booking opensabout D-90
  2. Cutoff: D-14decide: close class L?
  3. Label windowthe 14 days you forecast
  4. Departure: D-0bookings_final known
  5. Settlementrevenue weeks later

Everything recorded before the cutoff is the observation window; values that appear later (fares paid, passengers, load factor) are outcomes. A later cutoff adds information but shortens the horizon: the decision fixes the cutoff, not the data. Calendar and schedule are known for the whole horizon.

Grain and the snapshot filter3 minHow one missing filter turns a fair table into a leak.
One missing filter turns a fair table into a leak Diagram
flight_day1 row per departurebooking_curve13 snapshot rows perdepartureFilter: 14 days out1 row per departureJoin on date, flightgrain keptDeparture tablebookings at D-14 +targetNo snapshot filterevery snapshot, or MAX =latestRow D: MAE 0.00the D-0 count is thetargetno filterlatest picked
  1. flight_day1 row per departure
    • to Join on date, flight
  2. booking_curve13 snapshot rows per departure
    • no filter: to No snapshot filter
  3. Filter: 14 days out1 row per departure
    • to Join on date, flight
  4. No snapshot filterevery snapshot, or MAX = latest
    • latest picked: to Row D: MAE 0.00
  5. Join on date, flightgrain kept
  6. Departure tablebookings at D-14 + target
  7. Row D: MAE 0.00the D-0 count is the target

Without the filter the join either multiplies rows (Day 7) or silently picks a later snapshot, which is how row D scored 0.00. State the grain of both tables before any join.

The pickup baseline, honestly computed5 minThe formula, a three-departure computation by hand, additive against multiplicative pickup, and what row C did wrong.
The pickup baseline Formula
y^f=bf,14+pˉ r(f), w(f)\hat y_{f} = b_{f,14} + \bar p_{\,r(f),\,w(f)}
bf,14b_{f,14}
bookings on hand for departure f at the D-14 snapshot
pˉ\bar p
average pickup (D-14 to D-0), only over departures that had flown when the forecast was made
r(f), w(f)r(f),\, w(f)
route and weekday of departure f

The structure is a model; the rows it is computed from decide whether it is honest. Row C used the same formula on the wrong rows.

The pickup baseline by hand: three flown departures, one forecast Sequence
  1. Collect flown departures

    Same route and weekday, flown before the cutoff. (bk at D-14, final): (95, 138), (102, 141), (88, 127).

  2. Pickup per departure

    final - bk_d14: 43, 39, 39 seats gained after the cutoff.

  3. Average the pickup

    (43 + 39 + 39) / 3 = 40.3 seats: the structure history adds to a new departure.

  4. Forecast

    New departure has 97 on hand at D-14: 97 + 40.3 = 137.3, so forecast 137 seats.

  5. Multiplicative variant

    Ratios 1.453, 1.382, 1.443 average 1.426, so 97 x 1.426 = 138.3. Ratios amplify noise when early counts are small.

Redo it on paper, then check today's SQL does the same thing at scale. Verified in Python; synthetic numbers.

Additive or multiplicative pickup? Comparison
CriterionAdditiveMultiplicative
Forecastbookings at D-14 + average seats gainedbookings at D-14 x average growth ratio
Wins whenlate demand is similar in size across departureslate demand scales with early demand
Breaks whenroutes or seasons with very different volumes are pooledearly counts are small and noisy: the ratio amplifies the noise
In the demolower error on later IST-AYT departureshigher error: low D-14 counts get multiplied

Practitioner heuristic, not a law. Fit both on departures that had flown and keep the one that wins on a later period.

Today's query: four predictors, one honest Chart
  • A naive: bookings on handignores the pickup to come48.45 seats
  • B pickup baseline (2023)the bar for Days 9-167.4 seats
  • C pickup averaged on 2024leak: scored on its own rows7.06 seats
  • D latest snapshotleak: the target itself0 seats

Mean absolute error on 6,688 departures from 2024-01-15 to 2024-12-31 (airline sample database, synthetic).

Four kinds of leakage, and their price4 minOutcome columns, later snapshots, evaluation-period statistics and overwritten values; how much fake accuracy each buys.
Four ways the answer gets into the inputs Overview
  • Outcome columns

    avg_fare, passengers, load_factor: realized during the label window.

  • Later snapshots

    A join without the snapshot filter picks a value from after the cutoff.

  • Evaluation-period statistics

    A baseline averaged over the rows it is scored on.

  • Overwritten values

    A column updated after the fact holds today's value, not the cutoff's.

What a leak buys on paper, measured in seats Illustration
Reported error, by the snapshot used01020D-30D-21D-14D-7D-3D-0Snapshot the inputs were taken fromMAE, seatshonest: 15.1after the cutoff: leak0.0 = the target

At the D-14 cutoff the honest error is 15.1 seats. Every day of look-ahead makes the report look better and the live model no better; the last snapshot is the target itself.

Phantom gain, seats per departure0.01.02.0510204060Departures per averaging cellSeats of fake gain1.620.950.520.400.31

Row C's leak in general: averaging the pickup over the rows being scored pulls the baseline toward their own answers. The fewer departures per cell, the larger the phantom gain; it never appears live.

Synthetic: 480 departures on 4 routes from the seeded Day 2 generator; route-level pickup fitted on the first 240 departures and scored on the last 240. Right: 200 random cells per size.

A later snapshot leaks smoothly: each day of look-ahead buys a little fake accuracy, so a mild leak looks like a good model, not like a bug. Evaluation-period averages leak in proportion to how few rows each cell holds, so fine-grained lookups (route x weekday x month) leak the most.

Data contracts and point-in-time joinsadvanced4 minEvent time, load time and freshness; the as-of rule and the pandas defaults that silently break it.
What a data contract states for each column Stack
  1. MeaningWhat the value measures and its unit: competitor lowest fare, EUR, one-way.
  2. Event timeWhen it happened in the world: the competitor fare was observed at 06:00 on D-15.
  3. Load timeWhen it reached the table; later corrections must not overwrite earlier values.
  4. FreshnessHow old a value may be at the cutoff before it is treated as missing.
  5. OwnerWho answers questions and announces changes.

Event time against load time is what lets you prove point-in-time correctness. The same two timestamps matter for a lab result drawn on Tuesday but loaded on Thursday (healthcare) and a sensor reading that arrives hours late (manufacturing).

The as-of rule for one feature Formula
xf(t)=vjwithj=arg⁡max⁡i { ℓi:ℓi≤t, ei≤t, t−ei≤Δ }\begin{aligned} x_f(t) &= v_j \quad \text{with} \\ j &= \arg\max_{i}\,\{\, \ell_i : \ell_i \le t,\ e_i \le t,\ t - e_i \le \Delta \,\} \end{aligned}
tt
the cutoff of the row being built (D-14 for departure f)
ei,ℓie_i, \ell_i
event time (observed in the world) and load time (arrived in the table) of record i
vjv_j
the value of the latest record loaded by the cutoff, not the latest record overall
Δ\Delta
freshness limit from the data contract; older values count as missing

Filtering on event time alone lets backfilled corrections leak; the load-time condition is what makes the join point-in-time. Feature stores implement the same rule: Feast joins each row to feature values stamped at or before the row's time, and can also filter on the created timestamp so backfills do not leak.

pandas merge_asof defaults, and what to set for a cutoff join (pandas 3.0) Table
ArgumentDefaultFor a cutoff join
direction'backward'keep: take the last value at or before the cutoff
allow_exact_matchesTruedecide: is a value stamped exactly at the cutoff known when the decision runs?
toleranceNoneset to the contract's freshness, or a year-old value joins silently
byNoneset the entity key (route, flight) so values never cross entities
onrequired, sortedjoin on load time, not only on event time

Checked against the installed pandas 3.0.6, including that exact-match and tolerance change which value is joined. On sorted keys an as-of join is one merge pass, O(n + m); recomputing features per cutoff naively is O(n x m), the cost feature stores exist to pay once.

The action cost2 minWhat each outcome of the fare-class decision costs.
The cost of each outcome of the D-14 decision Map

Lost cheap sales

About EUR 80 forfeited per seat left empty.

Right call

Seats sold at higher fares.

Right call

Cheap fares filled seats that would stay empty.

Displaced revenue

Cheap seats took the place of higher fares.

Today the cost is treated as linear in seats of error. Day 16 makes the two error types cost different amounts.

Diagnosing a too-good score or an offline-to-live gapadvanced3 minThe decisive test for a suspect feature, then leakage before drift.
Is this score too good to be true? Diagram
Beats baselineby a lot?One featuredominates?Rebuild it as ofcutoffrerun on the same rowsGain survives?Check later-snapshotjoinsand evaluation-periodaveragesKeep itwith a data-contractentryTreat as leakdrop or rebuildNormal reviewno leak alarm yetyesnoyesnoyesno
  1. Beats baseline by a lot?
    • yes: to One feature dominates?
  2. Normal reviewno leak alarm yet
  3. One feature dominates?
    • yes: to Rebuild it as of cutoff
  4. Check later-snapshot joinsand evaluation-period averages
  5. Rebuild it as of cutoffrerun on the same rows
  6. Gain survives?
    • yes: to Keep it
  7. Treat as leakdrop or rebuild
  8. Keep itwith a data-contract entry

A leak usually shows up as one feature carrying most of the gain; rebuilding that feature as of the cutoff is the cheapest decisive test.

Offline score far better than live? Diagram
Live muchworse?Check availabilityevery feature at thecutoffLeak found?Rebuild as of cutoffrerun on same rowsCheck the periodnew routes, a break,other mixKeep monitoringerrors and inputs (Day19)yesnoyesno
  1. Live much worse?
    • yes: to Check availability
  2. Keep monitoringerrors and inputs (Day 19)
  3. Check availabilityevery feature at the cutoff
  4. Leak found?
    • yes: to Rebuild as of cutoff
  5. Check the periodnew routes, a break, other mix
  6. Rebuild as of cutoffrerun on same rows

Leakage is the first suspect, not the only one. A shift between the evaluation period and live traffic produces the same symptom.

Memory anchor

Pick the moment first: one row, one cutoff, and only what existed then - a perfect score is a leak alarm.

Useful for

  • Writing the framing card for any repeated prediction (demand, churn, fraud, maintenance) before data work starts.

  • Reviewing a colleague's model - the availability test finds most leaks in minutes.

  • Agreeing data contracts with source-system owners (timestamps, snapshots, freshness).

Not suitable for

  • One-off descriptive questions ("why did March revenue fall?") - there is no cutoff to protect; the Day 1 analysis route applies.

  • Choosing between models - the card fixes the target, baseline and metric; validation design is Days 9 and 15.

Traps

Joining a snapshot table without a snapshot filter

A join on the entity key (a departure, an account, a machine) returns every snapshot. Sums inflate by the number of snapshots, or a MAX/latest pick silently uses the post-cutoff value, which can equal the target.

How to avoid it

State the grain of both tables before joining and filter the snapshot that corresponds to the cutoff.

A baseline built with look-ahead

The baseline's averages include the evaluation period, so it looks better than it can be live - and a model compared to it may be judged unfairly in either direction.

How to avoid it

Compute the baseline with the same cutoff rules you impose on the model, from data that existed at forecast time.

Senior reasoning

A senior spends the first hour on the card, not the model. They insist on one grain, a target that matches the decision, an explicit cutoff, and a per-column availability verdict with a reason. They treat a suspiciously good score as a bug report. They price the baseline in the business unit so the team knows what a unit of error (a seat, a missed payment, a wasted call) is worth, and they turn every "unclear" column into a question for the data owner rather than a silent assumption.

Interview question

"Your offline model is great but fails in production. What do you check first?" Structure the answer.

Recall cards scheduled after completion

  • List the items of a framing card for a repeated prediction, using the D-14 fare-class decision as the example.
  • What single question detects most leakage, and why does a data contract matter for it?

Reviewed on a 1 · 3 · 7 · 14 · 30-day schedule; a miss returns the card to 1 day.

References

  • Leakage in data mining: Formulation, detection, and avoidance — Shachar Kaufman, Saharon Rosset, Claudia Perlich, Ori Stitelman, 2012core

    Defines leakage as information about the target that would not legitimately be available, and proposes separating the data available for learning from the prediction-time data (learn-predict separation). (Definition of leakage; learn-predict separation)

  • Point-in-time joins (Feast documentation) — Feast projectoptional

    How a feature store joins each training row only to feature values with timestamps at or before that row's time, and why created timestamps guard against backfilled values. (Point-in-time joins)

  • A comparison of forecasting methods for hotel revenue management — Larry R. Weatherford, Sheryl E. Kimes, 2003optional

    Pickup methods were among the lowest-error and most robust arrivals forecasts in two hotel datasets, which makes pickup a credible revenue-management baseline. (Abstract)

Optional sections

Deep dive · 10 min

Three targets, three decisions. bookings_final serves fare-class control; passengers (after cancellations and no-shows) serves catering and overbooking; unconstrained demand serves pricing and capacity what-ifs. The first two are observed; the third is not, because bookings stop at capacity (Day 28 estimates it). Choosing the target is choosing the decision. The same fork appears in every sector: churn flag or revenue lost (telecom), claim filed or claim cost (insurance), readmitted or bed-days used (healthcare).

Event time vs load time. A competitor price observed on 3 March but loaded on 10 March is available at a cutoff of 10 March, not 3 March. Point-in-time joins need both timestamps; a feature store such as Feast filters on the event timestamp and can also filter on the created timestamp so corrected or backfilled values do not leak. Day 7 builds this in SQL with event and snapshot tables.

Future-known vs future-unknown. Holidays, schedules and announced capacity are known before the horizon and can be used for its full length. Competitor prices and weather are not; at forecast time they must themselves be forecast or treated as scenarios (Day 24).

A minimal data contract. Grain and key; meaning and unit of each column; event and load timestamps; whether rows are ever updated (and how corrections are versioned); freshness and expected delivery time; owner and change-notification process. Anything not in the contract is an assumption you must write down.

Role lenses. An analyst checks the grain before any aggregate; a data scientist writes the card and the baseline; an ML engineer makes sure the serving pipeline computes the same D-14 features the training table used (training-serving skew, Day 19).

Interview practice · 10 min

A product manager wants "a model that predicts which customers will buy in the next 7 days" for an e-commerce push campaign. Frame the problem before modeling.

Content version 6 · last verified 2026-10-11 · depends on sqlite