SQL, data modeling, data quality, EDA, visualization · insight-0045
Partition lag features by entity and shift them by the forecast horizon, not by one row
- Evidence type
- practitioner heuristic
- Difficulty
- medium
- Roles
- Data scientist, ML engineer, Data analyst
- Industries
- aviation, retail, energy
Summary: situation, action, reason, boundary
Situation
A training table adds LAG(passengers) OVER (ORDER BY date) as a feature for a model that forecasts 14 days ahead.
Action
Partition by the entity (flight_no or route), order by date, and lag by at least the horizon in time, not rows. Verify on a few entities that the feature equals the value from the correct earlier date.
Reason
A feature is legal only if it existed at the forecast cutoff; the cutoff is the horizon before the target date.
Boundary
Panel data, demand forecasting, churn and any time-indexed feature built in SQL.
Symptom or risk
Without PARTITION BY, the lag takes another route's value; with a one-day lag, the feature uses information not available 14 days before departure, inflating offline accuracy.
Exceptions
Nowcasting tasks where the same-day value is known at prediction time.
Trade-offs
Longer lags carry less signal; that is the honest cost of the horizon.
Pitfalls
Computing rolling means that include the current target row.