Problem Framing and Data Modeling
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
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.
Concept
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.
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.
- Booking opensabout D-90
- Cutoff: D-14decide: close class L?
- Label windowthe 14 days you forecast
- Departure: D-0bookings_final known
- 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.
Decision
At D-14, close the lowest fare class (L) or keep it open. Owner: the RM analyst, daily.
Grain
One row per departure (flight_no x date). The booking curve has 13 snapshot rows each.
Target
bookings_final at D-0. Not passengers, not revenue. Capped by capacity (Day 28).
Cutoff and horizon
The D-14 snapshot; horizon 14 days; label window from cutoff to departure.
Availability test
For every column: did it exist at D-14, with that value?
Baseline, metric, cost
Pickup baseline; seats of absolute error; about EUR 80 per empty seat.
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.
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.
| Column | At D-14? | Why |
|---|---|---|
| cumulative_bookings, 14 days out | yes | a snapshot taken at the cutoff |
| route, weekday, holiday, capacity | yes | scheduled in advance |
| competitor_price | unclear | one value per departure; when was it observed? |
| avg_fare, fare_level, passengers, load_factor | no | outcomes of the period you forecast |
"Unclear" is not "yes". It becomes a feature only when a data contract states when the value was recorded.
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).
Application and workplace decision
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_finalon 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;
Mini-assessment
Technical depth
The prediction task as an equationThe 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.
- one row = one entity at one cutoff: a departure at D-14, an account on the 1st of the month
- everything loaded into the warehouse by the cutoff - the only legal input
- the feature pipeline; it must be computable from that history alone, in training and in serving
- the target as a function of the label window of length h: bookings gained, a churn flag, a default
- 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.
| Cutoff | Filter on event time only | Event AND load time <= cutoff | Plus freshness limit 30 days |
|---|---|---|---|
| Apr 10 | 655 (computed Apr 08 - but loaded Apr 12: leak) | 618 (computed Apr 02, loaded Apr 04) | 618 kept: 8 days old |
| May 20 | 655 (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.
| Object | Formal condition | Test in SQL or pandas | Example outside airlines |
|---|---|---|---|
| Grain | a key K with at most one row per value of K | COUNT(*) = COUNT(DISTINCT K) | one row per account x scoring month (collections) |
| Join keeps the grain | the join key is unique on the right-hand table | row count before = row count after | orders joined to a returns table with several rows per order (retail) |
| Outcome column | x is computed from events in the label window (t, t + h] | trace x to its source event; event time > t fails | discharge diagnosis used to predict readmission at admission (healthcare) |
| Later snapshot | x uses a record with load time > t | assert max(load_time) <= cutoff per row | sensor reading backfilled after the alarm (manufacturing) |
| Evaluation-period statistic | phi is fitted on rows whose label windows end after t | fit every average or encoder on rows that closed before t | target encoding of tariff fitted on all months (telecom) |
| Overwritten value | the stored v_j is a later version than the one loaded by t | a versioned table or a change log exists | a 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.
| Criterion | Same distribution | Same features |
|---|---|---|
| In symbols | training and serving rows share the joint law of (x, y) | phi is the identical function in training and serving |
| Breaks when | the world moves: a competitor's capacity step, a new route mix | training uses a SQL backfill, serving a separate service |
| Guarded by | monitoring 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.
| Step | Rows | SUM(order_value), EUR | COUNT(*) vs COUNT(DISTINCT order_id) |
|---|---|---|---|
| orders: O1 40, O2 25, O3 60 (grain: one row per order) | 3 | 125 | 3 = 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_id | 4: O1 twice | 40 + 40 + 25 + 60 = 165 | 4 vs 3: the grain broke |
| Fix: aggregate returns to one row per order (returned_lines), then join | 3 | 125 | 3 = 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 windowsHow the observation window, the cutoff and the label window fit together for one departure.
- Booking opensabout D-90
- Cutoff: D-14decide: close class L?
- Label windowthe 14 days you forecast
- Departure: D-0bookings_final known
- 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 filterHow one missing filter turns a fair table into a leak.
- flight_day1 row per departure
- to Join on date, flight
- booking_curve13 snapshot rows per departure
- no filter: to No snapshot filter
- Filter: 14 days out1 row per departure
- to Join on date, flight
- No snapshot filterevery snapshot, or MAX = latest
- latest picked: to Row D: MAE 0.00
- Join on date, flightgrain kept
- Departure tablebookings at D-14 + target
- 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 computedThe formula, a three-departure computation by hand, additive against multiplicative pickup, and what row C did wrong.
- bookings on hand for departure f at the D-14 snapshot
- average pickup (D-14 to D-0), only over departures that had flown when the forecast was made
- 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.
Collect flown departures
Same route and weekday, flown before the cutoff. (bk at D-14, final): (95, 138), (102, 141), (88, 127).
Pickup per departure
final - bk_d14: 43, 39, 39 seats gained after the cutoff.
Average the pickup
(43 + 39 + 39) / 3 = 40.3 seats: the structure history adds to a new departure.
Forecast
New departure has 97 on hand at D-14: 97 + 40.3 = 137.3, so forecast 137 seats.
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.
| Criterion | Additive | Multiplicative |
|---|---|---|
| Forecast | bookings at D-14 + average seats gained | bookings at D-14 x average growth ratio |
| Wins when | late demand is similar in size across departures | late demand scales with early demand |
| Breaks when | routes or seasons with very different volumes are pooled | early counts are small and noisy: the ratio amplifies the noise |
| In the demo | lower error on later IST-AYT departures | higher 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.
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 priceOutcome columns, later snapshots, evaluation-period statistics and overwritten values; how much fake accuracy each buys.
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.
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.
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 joinsadvancedEvent time, load time and freshness; the as-of rule and the pandas defaults that silently break it.
- MeaningWhat the value measures and its unit: competitor lowest fare, EUR, one-way.
- Event timeWhen it happened in the world: the competitor fare was observed at 06:00 on D-15.
- Load timeWhen it reached the table; later corrections must not overwrite earlier values.
- FreshnessHow old a value may be at the cutoff before it is treated as missing.
- 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 cutoff of the row being built (D-14 for departure f)
- event time (observed in the world) and load time (arrived in the table) of record i
- the value of the latest record loaded by the cutoff, not the latest record overall
- 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.
| Argument | Default | For a cutoff join |
|---|---|---|
| direction | 'backward' | keep: take the last value at or before the cutoff |
| allow_exact_matches | True | decide: is a value stamped exactly at the cutoff known when the decision runs? |
| tolerance | None | set to the contract's freshness, or a year-old value joins silently |
| by | None | set the entity key (route, flight) so values never cross entities |
| on | required, sorted | join 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 costWhat each outcome of the fare-class decision costs.
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 gapadvancedThe decisive test for a suspect feature, then leakage before drift.
- Beats baseline by a lot?
- yes: to One feature dominates?
no - Normal reviewno leak alarm yet
- One feature dominates?
- yes: to Rebuild it as of cutoff
no - Check later-snapshot joinsand evaluation-period averages
- Rebuild it as of cutoffrerun on the same rows
- Gain survives?
- yes: to Keep it
no - Treat as leakdrop or rebuild
- 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.
- Live much worse?
- yes: to Check availability
no - Keep monitoringerrors and inputs (Day 19)
- Check availabilityevery feature at the cutoff
- Leak found?
- yes: to Rebuild as of cutoff
no - Check the periodnew routes, a break, other mix
- 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?
Prerequisites and related concepts
- Business vs analytical problems · day 1
- Baseline-first culture · day 1
- Cost of being wrong · day 1
- Unit of analysis and grain
- Target definition
- Horizon
- Point-in-time correctness
- Leakage
- Data contracts
- Event vs snapshot data and event vs processing time · day 7
- Holdout boundary before fitting · day 9
- Train / validation / test · day 15
- Business vs ML metric · day 16
- Exogenous variables and availability · day 24
- Capacity-censored demand · day 28
- Training-serving skew · day 19
References
- Leakage in data mining: Formulation, detection, and avoidancecore
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)optional
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 managementoptional
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
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
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.