Training-Data Pipelines & Point-in-Time Correctness

Why the join is the whole problem

Building a training set sounds like the easy part: take the labels, join the features, write Parquet. In practice it is where the most expensive ML bugs are born, because the join has a dimension analytics joins don't: time, as it was known to the system at a moment in the past. A fraud training row for a payment made on 3 March may only use feature values that existed on 3 March. Not the account's chargeback count as of today — that includes the very chargeback you are trying to predict.

Get this wrong and you get leakage: the training set contains information that would not have been available at prediction time. Leakage produces a model that looks superb offline (it is reading the answer) and useless in production (the answer isn't there). It is the single most common reason a model's offline AUC of 0.97 becomes a production precision of 0.4, and it is almost always a data-pipeline bug, not a modeling one.

The spine: entity, prediction time, label

Every training set starts from a spine — the list of examples, before any features. Each row is an entity key, a prediction timestamp, and (once known) a label. Everything else is joined onto the spine, never the other way round.

-- One row per decision the model would have made: a payment attempt.
-- prediction_ts is the instant the API would have called the model.
CREATE TABLE ml.fraud_spine_v14 AS
SELECT
  p.payment_id                         AS entity_id,
  p.account_id,
  p.attempted_at                       AS prediction_ts,
  -- label: a chargeback within 90 days. NULL if the 90d window hasn't closed yet.
  CASE WHEN p.attempted_at < now() - INTERVAL '90 days'
       THEN COALESCE(cb.chargeback_id IS NOT NULL, FALSE) END AS label_chargeback_90d
FROM  gold.fct_payment p
LEFT JOIN gold.fct_chargeback cb
       ON cb.payment_id = p.payment_id
      AND cb.filed_at <= p.attempted_at + INTERVAL '90 days'
WHERE p.attempted_at BETWEEN DATE '2025-01-01' AND DATE '2026-06-01'
  AND p.decision = 'approved';           -- ⚠ see ch. 03: declined payments have no label

Three decisions are baked into those twenty lines and each is an architectural choice: the grain (one row per payment attempt, not per account), the prediction time (attempt time, not settlement time), and the label window (90 days, and rows whose window hasn't closed are unlabeled, not negative). Write these down in the dataset's registry entry. They are the definition of the problem.

The point-in-time join

With the spine fixed, every feature is joined as of prediction_ts. The offline store (ch. 04) keeps feature history as intervals — each value with a valid_from and valid_to — so the join is "find the last value whose valid_from ≤ prediction_ts". Modern engines have an ASOF JOIN for exactly this; where they don't, it is a window over a union.

SELECT
  s.entity_id, s.account_id, s.prediction_ts, s.label_chargeback_90d,
  f1.value AS acct_chargebacks_lifetime,     -- as of prediction_ts, NOT today
  f2.value AS acct_payment_velocity_1h,
  f3.value AS bin_country
FROM ml.fraud_spine_v14 s
ASOF JOIN fs.acct_chargebacks_lifetime f1
     MATCH_CONDITION (s.prediction_ts >= f1.valid_from) ON s.account_id = f1.account_id
ASOF JOIN fs.acct_payment_velocity_1h f2
     MATCH_CONDITION (s.prediction_ts >= f2.valid_from) ON s.account_id = f2.account_id
ASOF JOIN fs.card_bin_country f3
     MATCH_CONDITION (s.prediction_ts >= f3.valid_from) ON s.card_bin = f3.card_bin;

-- Spark / Trino without ASOF: window over the feature history
-- SELECT ... FROM spine s JOIN feat f ON keys AND f.valid_from <= s.prediction_ts
-- QUALIFY ROW_NUMBER() OVER (PARTITION BY s.entity_id, feature ORDER BY f.valid_from DESC) = 1

Two subtleties that separate a correct join from a plausible one:

• Availability time vs event time. A feature computed nightly from yesterday's rentals is not known until the batch finishes at 06:00. Its valid_from must be the time the value became available to the online store, not the end of the window it summarises. Otherwise you train on a value the API could not have seen for six hours. Feature stores call this the materialisation timestamp; if yours doesn't track it, add a lag equal to the pipeline's cadence.
• Late-arriving source data. If a telemetry sample for 09:00 lands at 09:40, a feature recomputed at 10:00 will differ from the one served at 09:05. Decide once whether the offline value reflects "as computed at the time" (correct for training) or "as recomputed with full data" (correct for analytics). For training, prefer the former — log the served value (ch. 08) and use it.

Splits, dedup and sampling

A random 80/20 split is the right answer for i.i.d. tabular data and the wrong answer for nearly everything at GridDP, where rows are correlated by entity and by time.

Dedup before splitting, keyed on (entity_id, prediction_ts); duplicate spine rows (a payment retried three times) will land on both sides of any split. Sampling is a cost lever — the 150 M-samples-a-day telemetry firehose cannot be a training set — but must be documented and, for rare positives like fraud (≈0.3% of payments), stratified: downsample negatives, keep every positive, and record the sampling weight so the model's calibration can be corrected.

The dataset is an artifact

The output of all this is not "a query you can re-run". Source tables change (late data, backfills, GDPR deletions), feature definitions get fixed, and a re-run six weeks later produces a different table with the same name. A training set must be materialised, immutable, hashed and registered, exactly like a model binary.

name: fraud_train
version: 14
spine: ml.fraud_spine_v14            # snapshot id: iceberg 7f3a…  (ch. 05)
prediction_window: [2025-01-01, 2026-06-01]
label: label_chargeback_90d          # censored after 2026-03-03 (window not closed)
features:                            # registry names + definition versions (ch. 04)
  - acct_chargebacks_lifetime@3
  - acct_payment_velocity_1h@2
  - card_bin_country@1
split: time; train < 2026-01-01; valid 2026-01-01..2026-03-01; group_by: account_id
sampling: negatives 1:20 stratified; weight column: sample_weight
rows: {train: 4_812_330, valid: 1_106_912}
storage: s3://griddp-ml/datasets/fraud_train/v14/   # parquet, immutable
sha256: 9c1e…                        # of the file list + manifest
built_by: dataset-builder 1.7.2, commit a41c0f, 2026-06-02T04:12Z

The model registry entry for fraud-xgb:2026-06-02 points at fraud_train@14, which points at Iceberg snapshot IDs and feature-definition versions. That chain — model → dataset → snapshots → definitions → code — is what "reproducible" means (ch. 09), and it is only possible because the dataset was frozen.

Worked example: the fraud set on each stack

On Stack A, the dataset builder is a Python job that reads the spine and feature tables from Iceberg via Spark, runs the window-based point-in-time join, writes Parquet to S3 and pins the Iceberg snapshot IDs it read in the manifest; Feast's get_historical_features() does the same join if the features are registered there. On Stack B, FeatureEngineeringClient.create_training_set() performs the point-in-time lookup against Delta feature tables with timestamp_lookup_key, and the Delta version is the snapshot. On Stack C, Snowflake's feature store generates the ASOF-joined dataset as a versioned Dataset object backed by an immutable stage. The primitives are the same everywhere: spine, interval-keyed feature history, as-of join, frozen output with a pointer back to what it read.

At GridDP's scale the fraud set is small (millions of rows). The capacity-forecasting set is not: 17,000 GPUs × hourly rollups × 18 months ≈ 220 M spine rows before features. That one is built incrementally — new months appended as new dataset versions that share earlier partitions — which is why chapter 05 cares so much about partition layout and snapshot semantics.

When to choose what

The mature answer names the flip condition: "For the nightly forecaster, a scheduled SQL job with interval-keyed rollups is enough. The moment a request-path model uses a feature refreshed more often than daily, I move dataset assembly onto logged served vectors, because reconstructing 'what the API knew at 09:05' from batch tables is a losing game."

The label column deserves its own chapter, because "when is the truth known?" is a design question: 03 · Labels, Ground Truth & Feedback Loops.

✓ Check yourself

Answer from memory before opening each one.

1. A teammate builds a fraud training set by joining each historical payment to the account's feature table as it stands today. Offline AUC is 0.97. What is wrong, and what should the join key include?

   Show answer

   Today's account features include events after the payment, such as the chargeback you are trying to predict, so the model reads the answer. Join each feature as of the row's prediction time, taking the latest value that was valid then. The key is entity plus prediction timestamp. See why the join is the whole problem.

2. A nightly feature summarises yesterday's rentals and the batch finishes at 06:00. A payment at 02:00 joins the value labelled with yesterday's date. What leaks, and which timestamp fixes it?

   Show answer

   The value was not available to the API at 02:00, so the model trains on something serving can never see. Set the feature's valid-from to when it reached the online store, the materialisation time, not the end of the window it summarises. If the store does not record it, add a lag equal to the pipeline cadence. See the point-in-time join.

3. A spine row for a payment made 20 days ago has no chargeback yet. Should its label be 0, and what should the pipeline do?

   Show answer

   No. The 90-day window has not closed, so the row is censored, not negative. Leave the label null and train only on rows where now is past prediction time plus the window. Calling it negative teaches the model that recent-looking behaviour is safe. See the spine.

4. Each account has many payments, and a random 80/20 split gives a validation AUC far above what you see in the first week of production. Name two causes and a split that removes both.

   Show answer

   The same accounts appear in train and validation, so the model memorises them, and a random split mixes periods, so validation never faces drift or label delay. A time-based split with a group split by account removes both, and it makes validation the next period, as in production. See splits, dedup and sampling.

5. Fraud is 0.3% of payments and you downsample negatives 1:20. What must you record, and what breaks if you do not?

   Show answer

   Record the sampling weight for each row. Without it the model's scores are calibrated to a population with far more fraud than production, so thresholds and expected-loss maths are wrong. Keep every positive, stratify the negatives, and correct calibration with the weight. See splits, dedup and sampling.

6. Your team says the training set is the SQL query in the repo, and anyone can re-run it. Name three ways a re-run six weeks later returns a different table, and what you freeze instead.

   Show answer

   Late-arriving source data, backfills or fixed feature definitions, and GDPR deletions all change the sources under the same query. Freeze a materialised dataset with a manifest, a content hash and the snapshot IDs and feature-definition versions it read. Then model, dataset, snapshot and code form one chain. See the dataset is an artifact.

Takeaway

• A training set is a set of snapshots, one per entity and prediction time, so build it from a spine of entity, prediction time and label with features joined as of that time.
• Join features by availability time, not event time, or the model trains on values the API could not have seen yet.
• Rows whose label window has not closed are censored, not negative, and the newest window of any spine is always unlabeled.
• Default to a time-based split, add a group split when entities repeat, and deduplicate on entity and prediction time before splitting.
• Downsample negatives only with the weight recorded, or the model's scores no longer match production base rates.
• Freeze the dataset with a manifest, hash and snapshot pointers; a query is not a dataset, and tests on the joined result catch leaks before training does.

Every spine row needs a label, and labels arrive late and biased. → Labels, Ground Truth & Feedback Loops

Further reading

• Point-in-time joins — Feast documentation, accessed 2026. How an open-source feature store defines the as-of join that keeps the future out of a training set.
• Rules of Machine Learning: Best Practices for ML Engineering — Google, accessed 2026. Rules on logging features at serving time and on training and serving skew.
• Apache Iceberg table spec — Apache Iceberg, accessed 2026. The snapshot and metadata model that lets a dataset manifest pin the exact data it read.
• Data Validation for Machine Learning — Breck et al., SysML 2019. Schema, skew and drift checks that catch a bad training set before it trains.