Aggregation Features
Per-entity counts, sums, rates and recency over windows. They dominate tabular models, and they are the main source of train/serve skew because they depend on a clock, a source and a null policy at once.
The problem, the obvious approach, and why it breaks
Every lesson starts where the work starts: someone has a problem, and the first model that comes to mind looks fine offline.
Why do a handful of windowed counts per entity outperform every other feature, and why are those same features the ones that break between training and serving?
A food-delivery platform predicts whether an order will be late so it can warn the customer and re-route couriers. The best features by far are counts: the restaurant's orders in the last hour, its late orders in the last week, the courier's deliveries today. After launch the model warns far too rarely during the dinner rush, exactly when it matters.
Counts and sums per entity over recent windows are the obvious features, they are cheap, and they work. Compute them in the warehouse for training and in the stream for serving; a count is a count.
The warehouse computed restaurant_orders_1h as of the end of the hour, so an order placed at 18:05 saw the count for 17:00–18:00. The stream computes the trailing sixty minutes, so the same order sees 17:05–18:05. During the rush, the two differ by most of an hour's orders, and the model, trained on the calendar-bucket version, under-reads the load.
- The warehouse computed
restaurant_orders_1has of the end of the hour, so an order placed at 18:05 saw the count for 17:00–18:00. The stream computes the trailing sixty minutes, so the same order sees 17:05–18:05. During the rush, the two differ by most of an hour's orders, and the model, trained on the calendar-bucket version, under-reads the load. - A restaurant with no orders in the window is
0in the warehouse and missing from the stream's state store; the serving path fills missing with the training mean, which during the rush is a large number. Quiet restaurants look busy and busy ones look average. - The stream has a few minutes of lag, so the most recent — most predictive — orders are absent from the count at serving time, and were present in training, where the warehouse had the whole hour.
What is being predicted, and from what data
This domain leads with these two. A target nobody defined precisely is a label nobody can trust, and a dataset nobody can describe is a model nobody can debug.
- Predict at order placement whether delivery will exceed the promised time. The label is the actual delivery timestamp against the promise.
- The decision is a warning and a re-routing, both of which are only useful if made when the order is placed.
- One example is one order at placement, joined to restaurant and courier aggregates over several windows.
- Training aggregates were computed in the warehouse from a deduplicated, hour-partitioned orders table, as of the end of each hour.
- Serving aggregates are computed by a stream processor over raw order events with a few minutes of lag, and the counter for a restaurant with no events in the window is absent rather than zero.
How it actually works
Precisely enough to predict its behaviour — not a framework API.
- An aggregate is a function of an entity, a window, a source and an evaluation time: count the events for this entity whose timestamp falls in
[t - w, t), as known at timet. Each component is a place to disagree. Change the window anchor (calendar bucket versus trailing), the source (deduplicated table versus raw stream), the lag (what is known att), or the empty-window policy, and the feature has changed while its name has not. - Aggregates dominate tabular models because they compress an entity's history into the model's input — the restaurant's current load, the courier's fatigue — which no single-row attribute carries. A gradient-boosted model over a few dozen well-chosen aggregates is usually the strongest thing available on this kind of data (Gradient Boosting).
- They are the main skew source for the same reason: they are the features with the most moving parts, they are the ones most likely to be implemented twice, and their values move fastest, so any lag or boundary difference shows up as a large disagreement precisely at the busiest moments.
- In training, computing them correctly is a point-in-time join: for each order at time
t, count events with timestamp beforet— never the whole table, and never a snapshot taken at the end of the day (Point-in-Time Correctness).
Four ways the same count differs
The feature was restaurant_orders_1h in both places. The warehouse computed it as the count in the calendar hour before the order's hour; the stream computed the trailing sixty minutes at request time. The warehouse read a deduplicated table; the stream read raw events including retries. The warehouse saw the whole hour; the stream saw everything up to three minutes ago. The warehouse wrote 0 for a quiet restaurant; the stream had no entry.
None of these is a bug in isolation. Each is a reasonable choice for its tool. Together they make the serving feature a different distribution from the training feature, and the difference is largest during the rush, when counts are large and recent orders matter most.
looks like A count with the same name and the same one-hour window in the training SQL and the stream job.
why it leaks It does not leak; it skews. The window anchor, the dedup policy, the visible lag and the empty-window value were each chosen separately in the two implementations, so the same order gets a different number in training and in serving.
fix One definition with an explicit trailing window [t - 1h, t), an explicit as_of = t - lag, one dedup policy, and zero for a known entity with no events; a replay test stratified by hour.
1-- one row per order at placement; the window ends at what serving could SEE2SELECT o.order_id, o.restaurant_id, o.placed_at,3 COUNT(e.event_id) AS restaurant_orders_1h4FROM orders_to_score o5LEFT JOIN order_events e6 ON e.restaurant_id = o.restaurant_id7 AND e.event_ts >= o.placed_at - INTERVAL '1 hour' - INTERVAL '3 minutes'8 AND e.event_ts < o.placed_at - INTERVAL '3 minutes' -- serving lag9 AND e.is_duplicate = FALSE -- same dedup as the stream10GROUP BY 1, 2, 3;11-- COUNT over a LEFT JOIN yields 0 for a known restaurant with no events,12-- which must be what the serving state store returns for the same case.The three-minute offset is the uncomfortable line: it deliberately makes the training feature staler so it matches what serving can compute. Leaving it out trains a model on recency it will never receive.
Why aggregates dominate, and what that costs
A single order row says almost nothing about lateness. The restaurant's current load, its recent late rate, the courier's deliveries today — the entity's history compressed into a few numbers — say nearly everything. That is why a tree ensemble over a few dozen aggregates is hard to beat on this kind of data, and why teams keep adding windows.
Each window is another computation to keep equivalent, another state store entry, another distribution to monitor. The trade-off below is between richer aggregates and an operational surface that grows with every one.
| Option | Quality | Latency | Cost | Operational | Note |
|---|---|---|---|---|---|
| Batch precompute, serve from a table | Aggregates refreshed hourly or daily; identical to training by construction; stale by up to the refresh interval. | ||||
| Stream-computed, separate implementation | Fresh to within minutes; the second implementation is where every skew in this lesson comes from; needs a replay test. | ||||
| One definition compiled to both paths | Fresh and equivalent; requires a feature platform or a compiler and a team to own it. | ||||
| Serve from stream state, train on the log | Training sees exactly what serving saw; the training set depends on the stream's retention and uptime, and the first model waits for the log. |
caveat The scores cannot express that the right choice flips with the lag the business tolerates: an hourly batch table is fine for a weekly-churn model and useless for a delivery-lateness model. Nor can they say that the "separate implementation" option is where most teams start and most skew incidents happen.
Empty is not average
A restaurant with no orders in the last hour is quiet. A restaurant the state store has never seen is unknown. Neither is "typical". The serving path that fills an absent counter with the training mean turns the quietest restaurants into average ones at exactly the moment the feature should be saying "no load here", and turns unknown ones into busy ones during the rush.
The null policy has to be part of the definition and the same on both sides: zero for a known entity with no events, null with an indicator for an unknown entity, and a monitor on how often each case occurs so a state-store outage that makes every entity "unknown" is caught by the missingness rate before it is caught by customers (Missing Data).
Strong recall of late orders on a time-split holdout built from warehouse aggregates.
Warnings during peak hours are far rarer than validation implied; off-peak the model behaves; customers of overloaded restaurants get no warning.
- 1Calendar-bucketed training windows versus trailing serving windows disagree most when counts are large and changing fast — the rush.
- 2Absent stream counters filled with the training mean turn quiet restaurants into average ones and unknown ones into busy ones, blurring the load signal at the peak.
- 3Serving lag hides the last few minutes of orders that the training features contained, and those minutes carry the most recent load.
Every aggregate is computed over the same trailing window from equivalently deduplicated events with the same visible lag and the same empty-window value in training and serving.
holds when One definition generates both computations, or a replay test stratified by hour of day is green; training features are built as-of with the serving lag; the empty-window and unknown-entity cases are distinguished and identical.
breaks when A stream restart rebuilds partial state; the warehouse job moves to calendar buckets for speed; the stream's lag grows under load; retries are deduplicated on one side only; the state store's retention drops below the longest window.
respond Fix the diverging component — anchor, dedup, lag or empty value — on one side and re-run the replay before touching the model.
How to build it
Most important first.
- Anchor every window to the row's own evaluation timestamp — trailing, not calendar-bucketed — in both paths, unless the serving path genuinely computes calendar buckets, in which case training must too.
- Make the empty-window value explicit and identical: zero for a count over a known entity, null for an unknown entity, and never "fill with the mean".
- Model the lag: if serving can only see events up to
t - 3min, compute training features as oft - 3min, or the model learns from recency it will never have (Feature Freshness). - Prefer one computation: serve the aggregate from the same stream state that is logged for training, or compute both from one definition compiled to SQL and to the stream (Feature Stores exist mostly for this).
What to measure
Which number actually maps to the decision — and which numbers look relevant and are not.
- Per-feature replay mismatch between the training path and the serving path, stratified by time of day; a mismatch that is small on average and large at the peak is the signature of a window-boundary difference.
- Warning rate and late-delivery recall during peak hours specifically, once labels arrive; the average over the day hides the failure.
- The offline number, computed on warehouse aggregates, measures a feature path production does not run.
What must stay true after deployment
The field this whole domain exists for. A model is a set of assumptions with weights attached; these are the ones a monitor or a test should be checking.
- For every aggregate, the window anchor, boundary inclusivity, source deduplication, empty-window value and visible lag are the same in training and serving, and a replay test stratified by time of day confirms it.
- The serving state store holds events for at least the longest window used, and its value for an entity with no events is distinguishable from an entity it has never seen.
- The lag between an event and its visibility in serving aggregates is stable and no larger than what the training features assumed.
- Offline: rebuild the training aggregates with an explicit
as_of = t - lagand confirm the model's metric does not collapse; if it does, the model was learning from recency serving cannot provide. - Offline: replay a day of production requests through the warehouse definition and diff against the logged serving values, hour by hour.
- Online: log every aggregate at request time; monitor its distribution against training by hour of day, and alert on the peak-hour divergence rather than the daily mean.
What can go wrong
- The definitions are unified and the sources are not: the warehouse table is deduplicated and the stream double-counts retried events, so the count is right in training and inflated in serving on a busy night.
- The stream processor restarts and rebuilds its state from a checkpoint, and for a few minutes every aggregate is a partial count that looks like a quiet evening (Serving Fallbacks).
- Training is fixed to use trailing windows computed as-of, and the warehouse job becomes too slow to run nightly, so the team quietly moves back to hourly buckets "for now".
- Point-in-time trailing windows in SQL are far slower than bucketed group-bys, and on large event tables the training job's cost rises sharply.
- Computing training features with the serving lag deliberately throws away real information, lowering the offline number to the level production can actually deliver.
- A single computation via stream state shared with training makes the training set depend on the stream processor's uptime and retention.
- "A count is a count; the implementations cannot differ." Window anchor, dedup, lag and empty-window policy are four independent choices, and each is made silently by whoever writes the second implementation.
- "Fill missing aggregates with the mean, as with any missing value." An absent counter means zero or unknown, not average. Filling with the mean makes quiet entities look typical at exactly the moment the feature should say they are quiet.
- "The model is wrong at peak; retrain on more peak data." The features differ at peak. More training data on warehouse aggregates teaches the model more about a feature path it never receives.
Where this applies
ML advice is stated as universal far more often than it is. These labels say what each claim is specific to — and where CONTESTED appears, the note gives the strongest form of the opposing view.
- DATA-SPECIFICWindowed aggregates dominate when examples are entities with event histories — orders, users, devices, accounts. For independent single-shot examples such as an image or a one-off document there is no history to aggregate and the family does not apply.
- SCALE-SPECIFICAt small volume a single Python function can compute every aggregate for both training and serving from one table and skew is nearly impossible; the problem arrives when latency forces a stream-computed serving path and grows with the number of windows.
Where the depth lives
This domain teaches the model and hands the rest off by name.