{"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":"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","position":53,"of":66,"minutes":10,"answer":"A feature table has one row per entity per as-of moment, every feature computed from strictly before that moment and the label from strictly after.","keyIdea":"A feature table has one row per entity per as-of moment, every feature computed from strictly before that moment and the label from strictly after, built by one parameterised query that also serves predictions so training and live features cannot drift apart."},"body":{"format":"markdown","text":"## The table a model is trained on has a very specific shape\n\nOne row per thing you are predicting about, at a moment in time. Every column except one is something you knew at that moment. The one exception is the label: what happened afterwards, which is what the model is learning to predict. This lesson builds that table in SQL, because most of the work of a model is this query, and most of the ways a model goes wrong are ways this query was wrong.\n\n## Pick the moment first\n\nSay the prediction is \"will this customer place no orders in the next 60 days\". The moment is an `as_of` date. The features are computed from orders strictly before it. The label is computed from orders in the 60 days after it. A customer can appear many times, once per `as_of` date, and each appearance is a separate training example: the same customer on 1 January and 1 February are two rows with different features and possibly different labels.\n\nGenerate the moments with a spine, as the earlier module did for months:\n\n```sql\nWITH snapshots AS (\n  SELECT c.id AS customer_id, d::date AS as_of\n  FROM customers c\n  CROSS JOIN generate_series(date '2025-01-01', date '2026-03-01', interval '1 month') d\n  WHERE c.created_at < d       -- only customers who existed at that moment\n)\n```\n\nTen thousand customers and fifteen monthly snapshots give 150,000 rows, each a moment somebody could have been asked the question.\n\n> Figure: One row of a feature table, and which side of the moment each column comes from. Timeline: as_of − 90d, The feature window opens. Orders from here onwards are counted; as_of − 1d, The last order a feature is allowed to see; as_of, The moment the question is asked. Nothing from here may enter a feature; as_of + 1d, The label window opens; as_of + 60d, The label window closes, and only now may this row be used for training. Read the finished query and, for every column, say which side of as_of its data comes from. Every feature: before. The label: after. The last event is the one people forget, and forgetting it labels every recent customer as churned because their future has not happened yet.\n\n## Features from the past only\n\n```sql\nfeatures AS (\n  SELECT s.customer_id, s.as_of,\n         count(o.id)                                   AS orders_90d,\n         coalesce(sum(o.amount), 0)                    AS spend_90d,\n         s.as_of - max(o.placed_at)::date              AS days_since_last_order,\n         s.as_of - c.created_at::date                  AS tenure_days\n  FROM snapshots s\n  JOIN customers c ON c.id = s.customer_id\n  LEFT JOIN orders o\n    ON  o.customer_id = s.customer_id\n    AND o.placed_at <  s.as_of\n    AND o.placed_at >= s.as_of - interval '90 days'\n  GROUP BY s.customer_id, s.as_of, c.created_at\n)\n```\n\nEvery condition on `o.placed_at` is strictly before `as_of`. The LEFT JOIN keeps customers with no recent orders, which are precisely the ones most likely to churn and would be missing from an inner join. `days_since_last_order` is NULL for a customer who never ordered in the window, and the missing-values lesson says what to do with that: keep a flag, fill in this table, and nowhere else.\n\n## The label from the future only\n\n```sql\nlabels AS (\n  SELECT s.customer_id, s.as_of,\n         (count(o.id) = 0)::int AS churned_60d\n  FROM snapshots s\n  LEFT JOIN orders o\n    ON  o.customer_id = s.customer_id\n    AND o.placed_at >= s.as_of\n    AND o.placed_at <  s.as_of + interval '60 days'\n  GROUP BY s.customer_id, s.as_of\n)\nSELECT f.*, l.churned_60d\nFROM features f JOIN labels l USING (customer_id, as_of)\nWHERE l.as_of + interval '60 days' <= current_date;   -- the label window must have closed\n```\n\nThe final WHERE is easy to forget and matters: a snapshot taken 30 days ago does not yet know whether the customer churned, and including it labels every such customer as churned because their future has not happened yet.\n\n## The same query at prediction time\n\nWhen the model is used, somebody has to compute the same features for today. If the training query used a column that a nightly job fills in three hours after the prediction runs, the live features are different from the trained ones, and the model's accuracy in production falls without any error being raised. This is called train/serve skew, and the defence is structural: one query, parameterised by `as_of`, produces both the training rows (with many historical dates) and the live rows (with today's date and no label). If the query cannot run for today, it should not be the training query.\n\n## Where it lives\n\nWrite the result to a table with `as_of` as part of its key, and never overwrite it. Next month's run appends new snapshots. A model trained in March can be compared with one trained in June on the same rows, and a mysterious change in accuracy can be traced to a change in the features or in the data rather than in both at once. DuckDB on a laptop handles this comfortably for millions of rows, and `COPY features TO 'features.parquet'` hands the result to Python or to whatever trains the model.\n\n## The habit\n\nRead the finished query and, for every column, say which side of `as_of` its data comes from. Every feature: before. The label: after. Anything you cannot place is the subject of the next lesson."},"check":{"question":"A churn feature table is built with monthly snapshots up to 1 March 2026, a 60-day label window, and the query is run on 20 March 2026. What is wrong with the 1 March rows?","options":[{"text":"Their features include orders from March, because the 90-day window is measured backwards from the run date rather than from `as_of`.","correct":false,"tempting":"The feature join is bounded by `as_of`, not by the run date; the problem is on the label side."},{"text":"Their 60-day label window has not closed, so customers who have not yet ordered are labelled churned when their future simply has not happened.","correct":true},{"text":"They duplicate the 1 February rows, since a customer with no orders in either month produces identical features and the join cannot separate them.","correct":false,"tempting":"`as_of` is part of the key, so the rows differ on that column even when every feature matches."},{"text":"They are missing entirely, because `generate_series` stops before the last date when the interval does not divide the range evenly.","correct":false,"tempting":"generate_series includes the end date when it falls on a step, and 1 March is a step from 1 January."}]},"prev":{"slug":"categories-and-encoding","title":"Categories: canonical values, mapping tables and encoding for a model","url":"https://addaly.com/learn/data-and-sql/categories-and-encoding"},"next":{"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"},"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) 'A feature table: one row per entity, as of a moment', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/building-a-feature-table (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":"A feature table: one row per entity, as of a moment","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"A feature table has one row per entity per as-of moment, every feature computed from strictly before that moment and the label from strictly after, built by one parameterised query that also serves predictions so training and live features cannot drift apart.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"timeline","title":"One row of a feature table, and which side of the moment each column comes from","events":[{"when":"as_of − 90d","what":"The feature window opens. Orders from here onwards are counted"},{"when":"as_of − 1d","what":"The last order a feature is allowed to see"},{"when":"as_of","what":"The moment the question is asked. Nothing from here may enter a feature"},{"when":"as_of + 1d","what":"The label window opens"},{"when":"as_of + 60d","what":"The label window closes, and only now may this row be used for training"}],"caption":"Read the finished query and, for every column, say which side of as_of its data comes from. Every feature: before. The label: after. The last event is the one people forget, and forgetting it labels every recent customer as churned because their future has not happened yet."},"alt":"Figure: One row of a feature table, and which side of the moment each column comes from. Timeline: as_of − 90d, The feature window opens. Orders from here onwards are counted; as_of − 1d, The last order a feature is allowed to see; as_of, The moment the question is asked. Nothing from here may enter a feature; as_of + 1d, The label window opens; as_of + 60d, The label window closes, and only now may this row be used for training. Read the finished query and, for every column, say which side of as_of its data comes from. Every feature: before. The label: after. The last event is the one people forget, and forgetting it labels every recent customer as churned because their future has not happened yet."}},"blocks":[{"t":"h2","id":"the-table-a-model-is-trained-on-has-a-very-specific-shape","runs":[{"text":"The table a model is trained on has a very specific shape"}]},{"t":"p","runs":[{"text":"One row per thing you are predicting about, at a moment in time. Every column except one is something you knew at that moment. The one exception is the label: what happened afterwards, which is what the model is learning to predict. This lesson builds that table in SQL, because most of the work of a model is this query, and most of the ways a model goes wrong are ways this query was wrong."}]},{"t":"h2","id":"pick-the-moment-first","runs":[{"text":"Pick the moment first"}]},{"t":"p","runs":[{"text":"Say the prediction is “will this customer place no orders in the next 60 days”. The moment is an "},{"text":"as_of","code":true},{"text":" date. The features are computed from orders strictly before it. The label is computed from orders in the 60 days after it. A customer can appear many times, once per "},{"text":"as_of","code":true},{"text":" date, and each appearance is a separate training example: the same customer on 1 January and 1 February are two rows with different features and possibly different labels."}]},{"t":"p","runs":[{"text":"Generate the moments with a spine, as the earlier module did for months:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH snapshots AS (\n  SELECT c.id AS customer_id, d::date AS as_of\n  FROM customers c\n  CROSS JOIN generate_series(date '2025-01-01', date '2026-03-01', interval '1 month') d\n  WHERE c.created_at < d       -- only customers who existed at that moment\n)"},{"t":"p","runs":[{"text":"Ten thousand customers and fifteen monthly snapshots give 150,000 rows, each a moment somebody could have been asked the question."}]},{"t":"h2","id":"features-from-the-past-only","runs":[{"text":"Features from the past only"}]},{"t":"code","lang":"sql","label":"SQL","text":"features AS (\n  SELECT s.customer_id, s.as_of,\n         count(o.id)                                   AS orders_90d,\n         coalesce(sum(o.amount), 0)                    AS spend_90d,\n         s.as_of - max(o.placed_at)::date              AS days_since_last_order,\n         s.as_of - c.created_at::date                  AS tenure_days\n  FROM snapshots s\n  JOIN customers c ON c.id = s.customer_id\n  LEFT JOIN orders o\n    ON  o.customer_id = s.customer_id\n    AND o.placed_at <  s.as_of\n    AND o.placed_at >= s.as_of - interval '90 days'\n  GROUP BY s.customer_id, s.as_of, c.created_at\n)"},{"t":"p","runs":[{"text":"Every condition on "},{"text":"o.placed_at","code":true},{"text":" is strictly before "},{"text":"as_of","code":true},{"text":". The LEFT JOIN keeps customers with no recent orders, which are precisely the ones most likely to churn and would be missing from an inner join. "},{"text":"days_since_last_order","code":true},{"text":" is NULL for a customer who never ordered in the window, and the missing-values lesson says what to do with that: keep a flag, fill in this table, and nowhere else."}]},{"t":"h2","id":"the-label-from-the-future-only","runs":[{"text":"The label from the future only"}]},{"t":"code","lang":"sql","label":"SQL","text":"labels AS (\n  SELECT s.customer_id, s.as_of,\n         (count(o.id) = 0)::int AS churned_60d\n  FROM snapshots s\n  LEFT JOIN orders o\n    ON  o.customer_id = s.customer_id\n    AND o.placed_at >= s.as_of\n    AND o.placed_at <  s.as_of + interval '60 days'\n  GROUP BY s.customer_id, s.as_of\n)\nSELECT f.*, l.churned_60d\nFROM features f JOIN labels l USING (customer_id, as_of)\nWHERE l.as_of + interval '60 days' <= current_date;   -- the label window must have closed"},{"t":"p","runs":[{"text":"The final WHERE is easy to forget and matters: a snapshot taken 30 days ago does not yet know whether the customer churned, and including it labels every such customer as churned because their future has not happened yet."}]},{"t":"h2","id":"the-same-query-at-prediction-time","runs":[{"text":"The same query at prediction time"}]},{"t":"p","runs":[{"text":"When the model is used, somebody has to compute the same features for today. If the training query used a column that a nightly job fills in three hours after the prediction runs, the live features are different from the trained ones, and the model’s accuracy in production falls without any error being raised. This is called train/serve skew, and the defence is structural: one query, parameterised by "},{"text":"as_of","code":true},{"text":", produces both the training rows (with many historical dates) and the live rows (with today’s date and no label). If the query cannot run for today, it should not be the training query."}]},{"t":"h2","id":"where-it-lives","runs":[{"text":"Where it lives"}]},{"t":"p","runs":[{"text":"Write the result to a table with "},{"text":"as_of","code":true},{"text":" as part of its key, and never overwrite it. Next month’s run appends new snapshots. A model trained in March can be compared with one trained in June on the same rows, and a mysterious change in accuracy can be traced to a change in the features or in the data rather than in both at once. DuckDB on a laptop handles this comfortably for millions of rows, and "},{"text":"COPY features TO 'features.parquet'","code":true},{"text":" hands the result to Python or to whatever trains the model."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Read the finished query and, for every column, say which side of "},{"text":"as_of","code":true},{"text":" its data comes from. Every feature: before. The label: after. Anything you cannot place is the subject of the next lesson."}]}],"check":{"question":[{"text":"A churn feature table is built with monthly snapshots up to 1 March 2026, a 60-day label window, and the query is run on 20 March 2026. What is wrong with the 1 March rows?"}],"options":[{"text":[{"text":"Their features include orders from March, because the 90-day window is measured backwards from the run date rather than from "},{"text":"as_of","code":true},{"text":"."}],"correct":false,"tempting":[{"text":"The feature join is bounded by "},{"text":"as_of","code":true},{"text":", not by the run date; the problem is on the label side."}]},{"text":[{"text":"Their 60-day label window has not closed, so customers who have not yet ordered are labelled churned when their future simply has not happened."}],"correct":true,"tempting":null},{"text":[{"text":"They duplicate the 1 February rows, since a customer with no orders in either month produces identical features and the join cannot separate them."}],"correct":false,"tempting":[{"text":"as_of","code":true},{"text":" is part of the key, so the rows differ on that column even when every feature matches."}]},{"text":[{"text":"They are missing entirely, because "},{"text":"generate_series","code":true},{"text":" stops before the last date when the interval does not divide the range evenly."}],"correct":false,"tempting":[{"text":"generate_series includes the end date when it falls on a step, and 1 March is a step from 1 January."}]}],"order":[0,3,2,1],"why":null},"prev":{"slug":"categories-and-encoding","title":"Categories: canonical values, mapping tables and encoding for a model","minutes":9},"next":{"slug":"leakage-in-a-join","title":"Leakage: when a feature knows the future","minutes":10}}}