Extract only what changed, and expect late rows

In From notebook to pipeline you split Tomás's notebook into steps. The first one, extract, decides what data the rest of the night works with. The simplest version is a full extract: read all two years of daily_sales every night. Mangrove has about 2.5 million active store-product pairs, so two years is up to about 1.8 billion rows if every pair sold every day (fewer in practice, since many products don't sell daily). Reading that much takes a large share of the 90-minute budget between 23:15 and 01:30, and it gets slower every month as history grows.

An incremental extract reads only the rows that are new or changed since the last successful run. To know where the last run stopped, it keeps a watermark: the highest value of an ordered column that has been processed, stored in a small table. Tonight's run reads the rows above the watermark and, when it has finished, moves the watermark up.

Which column? The tempting choice is sale_date: "give me sales after the last day I saw". That breaks at Mangrove. The tills keep selling when the internet drops and upload when it returns (Case study: syncing POS sales from hundreds of stores). About 2% of a day's transactions arrive after midnight, roughly 8,000 of 400,000 (approximate), and a till that is offline all weekend uploads Saturday's sales on Monday. A sale_date watermark has already moved past Saturday, so those rows are never read. Put the watermark on loaded_at instead: the time the row reached the warehouse, set by the warehouse's clock. It only moves forward, and every late row gets a fresh loaded_at whatever its sale date. When late transactions change a day's total, the warehouse rewrites that daily_sales row and its loaded_at changes with it.

Finding late rows is half the job; the days they belong to also need rebuilding. Every night the pipeline rebuilds a reprocessing window of the last 3 sale dates from scratch: the cleaned daily sales for today, yesterday and the day before are recomputed and their partitions overwritten. Overwriting a partition makes the step safe to re-run, the idempotency rule from ml-01. Three days covers nearly all of Mangrove's late data, because most offline tills reconnect within a day. Rows older than the window are counted and logged by the extract, and the weekly Sunday run rebuilds any older day that changed before training starts. A store that was offline for a week gets a manual backfill, triggered by the sync monitor's alert.

Two rules keep the watermark honest. Move it last, after the output is written, to the cut-off you actually read up to; a watermark set to now() at the start of a run that then fails skips rows without any error. And read with a 10-minute overlap, because a row committed during the extract can carry a loaded_at slightly before the cut-off. Reading a few rows twice does no harm when the next step rebuilds whole days.

Validate the data before the model sees it

A model never complains about its inputs. If three stores' tills didn't sync, their rows are missing, the features read as zero sales, and the model forecasts almost nothing for those stores. Tomorrow's order is short, and nobody notices until the shelves are empty. The fix is a validation gate: a step between extract and features that runs checks and decides what happens next. Each check has four parts: a rule, a threshold, a severity, and an action on failure.

There are three actions, and choosing between them is the real design work. Fail stops the run, pages the owner, and lets the fallback take over: the stores order from last night's forecast shifted by one day (Serving: batch and online builds that chain). Quarantine removes only the affected stores or products from tonight's run, gives them the fallback, and alerts their owner, so one offline store doesn't stop the other 319. Warn lets the run continue and records the problem for someone to look at in the morning. Pick by two questions: how much of the output would be wrong, and would the model be fooled? Missing stores fool the model but only for those stores, so quarantine. A null in a feature column fools the model everywhere, so fail. Fifty returns that net to negative units are odd but harmless, so warn.

Set thresholds from history: compute each check's value for the last 90 nights, put the threshold outside the normal range, and review false alarms monthly. Festival nights need a wider band or a comparison with last year's festival (Monitoring, drift and retraining). Write every result to a check_result table with the run_id, so you see a value creeping towards its limit before it crosses it. Tools such as Great Expectations and pandera let you declare checks and get readable reports, but plain SQL queries that each return a value and a pass flag are enough to start, and they run in minutes inside the warehouse.

Feature tables and point-in-time joins

Once the data passes the gate, the features step computes every feature for every store-product pair and writes them to a feature table: store_article_features(store_id, article_id, as_of_date, …). The key is the entity (store and product) plus the as-of date: each row holds the feature values as they were known when that night's run started at 23:15. One night adds about 2.5 million rows, so two years is about 1.8 billion (approximate). A warehouse handles that easily when the table is partitioned by as_of_date.

Why store features instead of recomputing them for training? Because the table records what you knew. In Features and leakage you saw the "same timing" problem: at 23:15 a store with an offline till has only part of today's sales, while a month later the warehouse shows the complete day. If training recomputes features from today's warehouse, it sees complete days that the nightly run never had. The feature table freezes the partial value the model actually used. So the reprocessing window rebuilds the cleaned sales, never an old feature partition. Labels are the opposite: the units actually sold should be the final, complete numbers. Rebuild facts as late data arrives; never rebuild what you knew.

Building a training set means joining labels to features. For data that changes on a schedule, like the nightly feature partitions, a plain join on date works. Much data changes at irregular times instead: a price changes when a buyer decides, a store is refitted, a promotion plan is edited. These live in history tables with a valid_from date, and for each label you need the value that was valid on that day. A point-in-time join (also called an as-of join) does exactly that: for each label row, it picks the latest value whose time is on or before the label's time.

Join every label to today's price instead, and a sale from 6 October gets a price decided on 20 October: time leakage, and a price effect that never existed. Join with equality on date, and most labels find no price, because the history only has rows on change days. In PostgreSQL you write the as-of join with LATERAL (a subquery that can see the current label row) and ORDER BY valid_from DESC LIMIT 1, or with DISTINCT ON; pandas has merge_asof.

Where do the features live when something needs them fast? The warehouse tables are the offline store: full history, for training and the nightly batch. The ordering screen's what-if endpoint needs one store's features in a few milliseconds, so the nightly run also copies tonight's values into Redis, the online store (Caching covers the pattern). A feature store such as Feast packages these ideas: a registry of feature definitions, point-in-time joins to build training sets, and a job that copies the latest values to the online store (Feast calls it materialization). For one model with a nightly batch and a small endpoint, warehouse tables and a Redis load are enough; a feature store pays off when several models or teams share features.

Training/serving skew: same model, different inputs

Training/serving skew is when the model receives a feature at prediction time that was computed differently from the one it learned on. ds-02 named it. Three mechanisms cause most of it, and each looks innocent in code review:

• Windows that end at different moments. Training computes avg_last_7 in SQL over 7 complete days. The ordering screen's endpoint recomputes it at 10:00 from the last 7 days including this morning, when about 15% of the day's sales have happened (approximate). One of the seven days is nearly empty, so the average comes out about 12% low and so does the what-if answer.
• Time zones. Training uses the POS business day, set when the store opens. A serving job derives the date from a UTC timestamp. In a city three hours behind UTC, every sale after 21:00 local time lands on tomorrow's date, so "today" loses its evening rush.
• Defaults for missing values. Training fills a missing promotion with 0; serving leaves it null. The model has never seen a null there, so it sends it down whichever branch its library uses for missing values. The example box below shows what that did in the pilot.

Skew is dangerous because nothing breaks. The backtest is fine, the job succeeds, the numbers look plausible. Only the live error in some stores or products creeps up. There are four fixes, and you want all of them:

• One feature definition. One function in one package, imported by the training build, the nightly run, every backfill and the online endpoint. Better still, compute features once in the pipeline and let serving read the feature table or Redis, so there is nothing to recompute.
• Log what the model saw. For the nightly batch, tonight's feature partition is the log, tagged with run_id. The online endpoint writes its inputs to a served_features table; at about 20 requests a second at peak, that is cheap.
• Compare every night. For each feature, compare tonight's null share, mean and range with the training data. A null share that goes from 0% to 4% is a skew alarm, whatever the error says. ml-05 adds a drift score (PSI) on the same logs.
• A parity test in CI. For a sample of 50 store-products and 3 dates, build features through every path and require identical values. The test fails the pull request that changes one path but not the other.

Backfills and versioned features

Features change. Tomás wants avg_last_28 to skip days when the product was out of stock, because those days hold sales down (the censored demand from Framing a problem as a model). If he edits the function in place, two things break. The champion model, trained on the old meaning, receives the new meaning tonight: skew he created himself. And the feature table now mixes two meanings, old partitions with one and new partitions with the other.

The safe way is a feature version: add avg_last_28_v2 next to v1 and leave v1 alone. Then run a backfill: the feature step for past dates, with the same code and the same --run-date parameter from ml-01, over the two-year training window. That is about 730 partitions. At roughly 3 minutes each, one worker needs about 36 hours; 8 parallel workers take about 5 hours (approximate). Run it outside the nightly window, in chunks of a month, with the gate checks on every partition, so a failed chunk can be re-run on its own.

Next, train a challenger on v2 and compare it with the champion through the gate in Experiments, versions and the model registry. If it wins, the model and its feature version switch together, and the registry records that model version 15 reads v2. Keep v1 as long as any model you might roll back to needs it, then drop it.

A backfill has its own point-in-time trap: it must read each past date's sources as they were known that night. Out-of-stock days come from stock_snapshot, which keeps one row per day, so that history is honest. Sales are slightly optimistic, since the backfill sees complete days where the night run saw about 98% of the latest one; measure the gap against the frozen v1 partitions. A source that keeps only its current value can't be backfilled honestly at all. Start recording its history today.