A feature table: one row per entity, as of a moment

The table a model is trained on has a very specific shape

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.

Pick the moment first

Say 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.

Generate the moments with a spine, as the earlier module did for months:

WITH snapshots AS (
  SELECT c.id AS customer_id, d::date AS as_of
  FROM customers c
  CROSS JOIN generate_series(date '2025-01-01', date '2026-03-01', interval '1 month') d
  WHERE c.created_at < d       -- only customers who existed at that moment
)

Ten thousand customers and fifteen monthly snapshots give 150,000 rows, each a moment somebody could have been asked the question.

Features from the past only

features AS (
  SELECT s.customer_id, s.as_of,
         count(o.id)                                   AS orders_90d,
         coalesce(sum(o.amount), 0)                    AS spend_90d,
         s.as_of - max(o.placed_at)::date              AS days_since_last_order,
         s.as_of - c.created_at::date                  AS tenure_days
  FROM snapshots s
  JOIN customers c ON c.id = s.customer_id
  LEFT JOIN orders o
    ON  o.customer_id = s.customer_id
    AND o.placed_at <  s.as_of
    AND o.placed_at >= s.as_of - interval '90 days'
  GROUP BY s.customer_id, s.as_of, c.created_at
)

Every 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.

The label from the future only

labels AS (
  SELECT s.customer_id, s.as_of,
         (count(o.id) = 0)::int AS churned_60d
  FROM snapshots s
  LEFT JOIN orders o
    ON  o.customer_id = s.customer_id
    AND o.placed_at >= s.as_of
    AND o.placed_at <  s.as_of + interval '60 days'
  GROUP BY s.customer_id, s.as_of
)
SELECT f.*, l.churned_60d
FROM features f JOIN labels l USING (customer_id, as_of)
WHERE l.as_of + interval '60 days' <= current_date;   -- the label window must have closed

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.

The same query at prediction time

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 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.

Where it lives

Write 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.

The habit

Read 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.