{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"leakage-in-a-join","title":"Leakage: when a feature knows the future","url":"https://addaly.com/learn/data-and-sql/leakage-in-a-join","position":54,"of":66,"minutes":10,"answer":"Leakage enters a feature table through an unbounded aggregate, a join to a current value, a column written after the outcome, a target encoding that includes the row itself.","keyIdea":"Leakage enters a feature table through an unbounded aggregate, a join to a current value, a column written after the outcome, a target encoding that includes the row itself, or a split that puts one entity on both sides; a timestamp check catches the first two and a person who knows the source systems catches the rest."},"body":{"format":"markdown","text":"## The model that scored 99% and failed on Monday\n\nA churn model is trained, evaluated on held-out rows, and reports 0.99 accuracy. It goes live and predicts almost nothing useful. Nothing in the model is broken. One of its features contained the answer, in a form that was available in the training table and is not available on the day a prediction is needed. That is leakage, and in tabular work it almost always enters through a join or an aggregate in the feature query.\n\n## Five ways it gets in\n\n**An aggregate over all history.** `sum(amount) AS total_spend` computed over every order the customer ever placed, including the ones after `as_of`. A customer whose total spend keeps rising after the snapshot is, by definition, not churning. The model learns \"high total spend means not churned\" and is right in training and useless live, where the future orders do not exist yet. The previous lesson's `o.placed_at < s.as_of` is the fix, on every join, every time.\n\n**A join to a current dimension value.** `customers.segment` is joined in for every snapshot, and the segment is set to `'lapsed'` by a nightly job when a customer has not ordered for 60 days. For a snapshot from last year, the segment now says what the customer became, which is the label. The as-of join from the time module is the fix: the segment that was true on `as_of`, from a history table, or no segment at all if no history exists.\n\n**A column that is updated after the outcome.** `last_contacted_at`, `account_status`, `refund_count`, `support_tickets`. Each is edited by a process that happens because of the outcome: the retention team contacts customers who look like leaving, the refund is issued when they complain on the way out. Any column that can change after `as_of` is suspect unless its history is stored and joined as of.\n\n**Target encoding on the whole table.** The categories lesson computed `avg(churned)` per city and used it as a feature. If that average includes the row it is then attached to, the row's own label has leaked into its feature, a little for a big city and a lot for a city with three customers. Compute it on training rows only, and exclude the current row: `(sum(churned) - churned) / (count(*) - 1)` over the group.\n\n**The same customer on both sides of the split.** Rows for one customer at January and February snapshots are near-duplicates. Put one in training and one in test, and the model is evaluated on rows it has effectively seen. The split must be by customer, or by time with all training snapshots before all test snapshots, which is also the split that resembles production.\n\n## Detecting it\n\nSome leaks announce themselves. A feature with an almost perfect correlation with the label is a leak until proved otherwise; real signals are weaker than that. A model that is far better than the previous one after a change to the feature query is suspect in proportion to the improvement.\n\nOthers can be found mechanically. For each feature, the query should be able to state the source table and the timestamp column it was bounded by. A check query asserts it:\n\n> Figure: The same four features, written twice. Knows the future: sum(amount) over every order the customer ever placed; customers.segment, which last night's job set to 'lapsed'; A city's churn rate whose average includes this row's own label; January and February snapshots of one customer, split across training and test. Knows only the past: sum(amount) where placed_at is strictly before as_of; The segment that was true on as_of, from a history table; The rate over training rows only, with this row subtracted out; A split by customer, or by time with every training snapshot first. A feature with an almost perfect correlation with the label is a leak until proved otherwise; real signals are weaker than that. A timestamp check finds the first two of these. The other two are found by a person who knows which process writes each column, and when.\n\n```sql\n-- must return no rows: no feature source row is at or after its snapshot\nSELECT s.customer_id, s.as_of, max(o.placed_at) AS latest_used\nFROM snapshots s JOIN orders_used_for_features o USING (customer_id, as_of)\nGROUP BY 1, 2\nHAVING max(o.placed_at) >= s.as_of;\n```\n\nRun it beside the feature query, in the same file as the validation checks from earlier in this module.\n\n## The leaks a timestamp check cannot see\n\nA `notes` field that a support agent fills in only when a customer calls to cancel. A `data_entry_timestamp` that is populated only for accounts that went through a manual review, which happens mostly to accounts about to be closed. The timestamps of these columns are before `as_of`, and they still carry the future, because the *fact that they exist* is caused by the outcome. No query can catch this. It is found by a person who knows how the data is produced reading the feature list and asking, of each column, \"what process writes this, and does that process know the outcome?\" That conversation is the most valuable hour in a modelling project, and it is not a technical hour.\n\n## The habit\n\nBefore a feature table is used, two things happen. The timestamp check runs and returns nothing. And somebody who understands the source systems reads the column list and signs off that no column is written by a process downstream of the outcome. The second is the one that gets skipped, and it is the one that would have caught the 99%."},"check":{"question":"A churn model's strongest feature is `total_lifetime_spend`, computed as `sum(amount)` over all of a customer's orders. Evaluation accuracy is 0.98; live performance is poor. What is the mechanism?","options":[{"text":"Lifetime spend is dominated by a few large customers, and the model overfits to them; scaling the column to a 0 to 1 range will fix it.","correct":false,"tempting":"Skew affects model quality, but it does not produce near-perfect evaluation followed by collapse; that pattern is information available in training and absent live."},{"text":"Customers who churn have their orders deleted by a retention process, so their lifetime spend is artificially low in the training table and the model learns that low spend means churn.","correct":false,"tempting":"Invents a deletion process; the feature is wrong even when no row is deleted, because it includes orders placed after the snapshot."},{"text":"The sum includes orders placed after each snapshot, so a customer whose spend continues to grow is one who did not churn, and the feature encodes the label.","correct":true},{"text":"`sum(amount)` skips NULL amounts, so customers with incomplete order data appear to spend less and are labelled as churners.","correct":false,"tempting":"NULL skipping changes a total slightly; it does not make a feature nearly equal to the label."}]},"prev":{"slug":"building-a-feature-table","title":"A feature table: one row per entity, as of a moment","url":"https://addaly.com/learn/data-and-sql/building-a-feature-table"},"next":{"slug":"indexes-and-slow-queries","title":"Why a query is slow, and what an index does","url":"https://addaly.com/learn/data-and-sql/indexes-and-slow-queries"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Leakage: when a feature knows the future', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/leakage-in-a-join (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Leakage: when a feature knows the future","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Leakage enters a feature table through an unbounded aggregate, a join to a current value, a column written after the outcome, a target encoding that includes the row itself, or a split that puts one entity on both sides; a timestamp check catches the first two and a person who knows the source systems catches the rest.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"left":{"title":"Knows the future","items":["sum(amount) over every order the customer ever placed","customers.segment, which last night’s job set to ‘lapsed’","A city’s churn rate whose average includes this row’s own label","January and February snapshots of one customer, split across training and test"]},"type":"compare","right":{"title":"Knows only the past","items":["sum(amount) where placed_at is strictly before as_of","The segment that was true on as_of, from a history table","The rate over training rows only, with this row subtracted out","A split by customer, or by time with every training snapshot first"]},"title":"The same four features, written twice","caption":"A feature with an almost perfect correlation with the label is a leak until proved otherwise; real signals are weaker than that. A timestamp check finds the first two of these. The other two are found by a person who knows which process writes each column, and when."},"alt":"Figure: The same four features, written twice. Knows the future: sum(amount) over every order the customer ever placed; customers.segment, which last night’s job set to ‘lapsed’; A city’s churn rate whose average includes this row’s own label; January and February snapshots of one customer, split across training and test. Knows only the past: sum(amount) where placed_at is strictly before as_of; The segment that was true on as_of, from a history table; The rate over training rows only, with this row subtracted out; A split by customer, or by time with every training snapshot first. A feature with an almost perfect correlation with the label is a leak until proved otherwise; real signals are weaker than that. A timestamp check finds the first two of these. The other two are found by a person who knows which process writes each column, and when."}},"blocks":[{"t":"h2","id":"the-model-that-scored-99-and-failed-on-monday","runs":[{"text":"The model that scored 99% and failed on Monday"}]},{"t":"p","runs":[{"text":"A churn model is trained, evaluated on held-out rows, and reports 0.99 accuracy. It goes live and predicts almost nothing useful. Nothing in the model is broken. One of its features contained the answer, in a form that was available in the training table and is not available on the day a prediction is needed. That is leakage, and in tabular work it almost always enters through a join or an aggregate in the feature query."}]},{"t":"h2","id":"five-ways-it-gets-in","runs":[{"text":"Five ways it gets in"}]},{"t":"p","runs":[{"text":"An aggregate over all history.","b":true},{"text":" "},{"text":"sum(amount) AS total_spend","code":true},{"text":" computed over every order the customer ever placed, including the ones after "},{"text":"as_of","code":true},{"text":". A customer whose total spend keeps rising after the snapshot is, by definition, not churning. The model learns “high total spend means not churned” and is right in training and useless live, where the future orders do not exist yet. The previous lesson’s "},{"text":"o.placed_at < s.as_of","code":true},{"text":" is the fix, on every join, every time."}]},{"t":"p","runs":[{"text":"A join to a current dimension value.","b":true},{"text":" "},{"text":"customers.segment","code":true},{"text":" is joined in for every snapshot, and the segment is set to "},{"text":"'lapsed'","code":true},{"text":" by a nightly job when a customer has not ordered for 60 days. For a snapshot from last year, the segment now says what the customer became, which is the label. The as-of join from the time module is the fix: the segment that was true on "},{"text":"as_of","code":true},{"text":", from a history table, or no segment at all if no history exists."}]},{"t":"p","runs":[{"text":"A column that is updated after the outcome.","b":true},{"text":" "},{"text":"last_contacted_at","code":true},{"text":", "},{"text":"account_status","code":true},{"text":", "},{"text":"refund_count","code":true},{"text":", "},{"text":"support_tickets","code":true},{"text":". Each is edited by a process that happens because of the outcome: the retention team contacts customers who look like leaving, the refund is issued when they complain on the way out. Any column that can change after "},{"text":"as_of","code":true},{"text":" is suspect unless its history is stored and joined as of."}]},{"t":"p","runs":[{"text":"Target encoding on the whole table.","b":true},{"text":" The categories lesson computed "},{"text":"avg(churned)","code":true},{"text":" per city and used it as a feature. If that average includes the row it is then attached to, the row’s own label has leaked into its feature, a little for a big city and a lot for a city with three customers. Compute it on training rows only, and exclude the current row: "},{"text":"(sum(churned) - churned) / (count(*) - 1)","code":true},{"text":" over the group."}]},{"t":"p","runs":[{"text":"The same customer on both sides of the split.","b":true},{"text":" Rows for one customer at January and February snapshots are near-duplicates. Put one in training and one in test, and the model is evaluated on rows it has effectively seen. The split must be by customer, or by time with all training snapshots before all test snapshots, which is also the split that resembles production."}]},{"t":"h2","id":"detecting-it","runs":[{"text":"Detecting it"}]},{"t":"p","runs":[{"text":"Some leaks announce themselves. A feature with an almost perfect correlation with the label is a leak until proved otherwise; real signals are weaker than that. A model that is far better than the previous one after a change to the feature query is suspect in proportion to the improvement."}]},{"t":"p","runs":[{"text":"Others can be found mechanically. For each feature, the query should be able to state the source table and the timestamp column it was bounded by. A check query asserts it:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- must return no rows: no feature source row is at or after its snapshot\nSELECT s.customer_id, s.as_of, max(o.placed_at) AS latest_used\nFROM snapshots s JOIN orders_used_for_features o USING (customer_id, as_of)\nGROUP BY 1, 2\nHAVING max(o.placed_at) >= s.as_of;"},{"t":"p","runs":[{"text":"Run it beside the feature query, in the same file as the validation checks from earlier in this module."}]},{"t":"h2","id":"the-leaks-a-timestamp-check-cannot-see","runs":[{"text":"The leaks a timestamp check cannot see"}]},{"t":"p","runs":[{"text":"A "},{"text":"notes","code":true},{"text":" field that a support agent fills in only when a customer calls to cancel. A "},{"text":"data_entry_timestamp","code":true},{"text":" that is populated only for accounts that went through a manual review, which happens mostly to accounts about to be closed. The timestamps of these columns are before "},{"text":"as_of","code":true},{"text":", and they still carry the future, because the "},{"text":"fact that they exist","i":true},{"text":" is caused by the outcome. No query can catch this. It is found by a person who knows how the data is produced reading the feature list and asking, of each column, “what process writes this, and does that process know the outcome?” That conversation is the most valuable hour in a modelling project, and it is not a technical hour."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Before a feature table is used, two things happen. The timestamp check runs and returns nothing. And somebody who understands the source systems reads the column list and signs off that no column is written by a process downstream of the outcome. The second is the one that gets skipped, and it is the one that would have caught the 99%."}]}],"check":{"question":[{"text":"A churn model’s strongest feature is "},{"text":"total_lifetime_spend","code":true},{"text":", computed as "},{"text":"sum(amount)","code":true},{"text":" over all of a customer’s orders. Evaluation accuracy is 0.98; live performance is poor. What is the mechanism?"}],"options":[{"text":[{"text":"Lifetime spend is dominated by a few large customers, and the model overfits to them; scaling the column to a 0 to 1 range will fix it."}],"correct":false,"tempting":[{"text":"Skew affects model quality, but it does not produce near-perfect evaluation followed by collapse; that pattern is information available in training and absent live."}]},{"text":[{"text":"Customers who churn have their orders deleted by a retention process, so their lifetime spend is artificially low in the training table and the model learns that low spend means churn."}],"correct":false,"tempting":[{"text":"Invents a deletion process; the feature is wrong even when no row is deleted, because it includes orders placed after the snapshot."}]},{"text":[{"text":"The sum includes orders placed after each snapshot, so a customer whose spend continues to grow is one who did not churn, and the feature encodes the label."}],"correct":true,"tempting":null},{"text":[{"text":"sum(amount)","code":true},{"text":" skips NULL amounts, so customers with incomplete order data appear to spend less and are labelled as churners."}],"correct":false,"tempting":[{"text":"NULL skipping changes a total slightly; it does not make a feature nearly equal to the label."}]}],"order":[3,0,1,2],"why":null},"prev":{"slug":"building-a-feature-table","title":"A feature table: one row per entity, as of a moment","minutes":10},"next":{"slug":"indexes-and-slow-queries","title":"Why a query is slow, and what an index does","minutes":8}}}