The same query twice, against a dimension that keeps history and one that does not.
SELECT t.txn_id, t.amount, c.risk_band, t.is_fraud
FROM transactions t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.txn_ts >= '2026-01-01';
SELECT t.txn_id, t.amount, c.risk_band, t.is_fraud
FROM transactions t
JOIN customer_history c
ON c.customer_id = t.customer_id
AND t.txn_ts >= c.valid_from
AND t.txn_ts < c.valid_to
WHERE t.txn_ts >= '2026-01-01';
The same rule applies to any aggregate, and there the trap is subtler:
SELECT t.txn_id,
( SELECT count(*)
FROM transactions p
WHERE p.customer_id = t.customer_id
AND p.txn_ts < t.txn_ts
AND p.txn_ts >= t.txn_ts - INTERVAL '30 days' ) AS txns_30d
FROM transactions t;
What the first query taught the model is that a raised risk band predicts fraud,
which is true and useless, because in production the band is raised after the
fraud rather than before. The tell is a large gap between offline and online
performance with every input distribution matching — the features are fine, their
timestamps are not.
Current-state tables are the default because they are what operational systems
naturally produce, so leakage of this kind is the normal condition rather than an
unusual mistake. Fixing it means asking the owning system to record when each
value became true, and that request usually arrives too late to help the first
model.
The self-exclusion in the aggregate is the detail candidates omit. A window
defined as "the last thirty days" evaluated on a training row includes the row's
own transaction, so a feature meant to describe prior behaviour partly describes
the event itself. It is a small leak with a large effect on a rare-event model,
and it survives review because the SQL reads correctly.
The cost of doing this properly is history tables, larger storage, and joins that
are considerably more expensive than an equality join. Pay it, and check the
offline-to-online gap as the assurance that you did.