What you will build
You will convert raw transactions into customer features using a fixed prediction cutoff and detect the leakage that makes offline models look unrealistically good.
Nila’s model accidentally sees the future
Nila is predicting whether a customer will purchase in October. If her training features include an October transaction, the model receives information that would not have existed at prediction time. That is target leakage.
A feature is a model-ready signal
Raw transaction rows are events. Useful customer features may include order count over 30 days, total spend over 90 days, days since last purchase or average order value. The definition must include an entity, calculation, time window and cutoff.
Build features with an explicit cutoff
01WITH parameters AS (02SELECT DATE '2026-10-01' AS cutoff_date03)04SELECT c.customer_id,05COUNT(t.transaction_id) FILTER (06WHERE t.transaction_date >= p.cutoff_date - INTERVAL '30 days'07AND t.transaction_date < p.cutoff_date08) AS orders_30d,09COALESCE(SUM(t.amount) FILTER (10WHERE t.transaction_date >= p.cutoff_date - INTERVAL '90 days'11AND t.transaction_date < p.cutoff_date12), 0) AS spend_90d,13COALESCE(STDDEV_SAMP(t.amount) FILTER (14WHERE t.transaction_date < p.cutoff_date15), 0) AS spend_variability16FROM customers AS c17CROSS JOIN parameters AS p18LEFT JOIN transactions AS t19ON t.customer_id = c.customer_id20AND t.transaction_date < p.cutoff_date21GROUP BY c.customer_id;
CURRENT_DATE would change every day and make a historical training set hard to reproduce. A stored cutoff makes the observation time explicit. COALESCE handles customers with no rows and the NULL returned by sample standard deviation when there are too few values.
Example feature output
| customer_id | orders_30d | spend_90d | spend_variability |
|---|---|---|---|
| C101 | 7 | ₹18,420 | ₹640.50 |
| C102 | 2 | ₹3,100 | ₹0.00 |
Point-in-time correctness is more than a WHERE clause
Features from changing profiles, prices or account states must use the version known at the cutoff. Training and online prediction must also apply the same definitions; otherwise training-serving skew appears even without target leakage.
Real-world application: churn prediction
A churn model might use support-ticket count, payment failures and recent usage. Every input must be reconstructed as it was before the prediction moment. The eventual churn label belongs after that moment and must never leak into the feature columns.
Validate the feature table
Check null rate, range, freshness, duplicate entities and distribution changes. SQL can express many checks, but whether SQL or Python is faster depends on data size, engine, network movement, implementation and workload. Benchmark instead of making a blanket claim.
Common feature-engineering mistakes
Common mistakes
- Using
CURRENT_DATEwhen rebuilding historical training examples. - Including events at or after the prediction cutoff.
- Treating
NULLas zero without a documented business meaning. - Computing features differently in training and production.
- Reporting model accuracy gains without a reproducible experiment.
Practice point-in-time thinking
Create days_since_last_order for a cutoff of 2026-10-01. Decide what value represents a customer who has never ordered, and document that decision.
Choose an answer, inspect the explanation and explain the idea in your own words.
Why is a fixed cutoff date important for training data?
Interview-ready explanation
What is point-in-time correctness?
For every training example, each feature must be reconstructed using only information available at that example’s prediction time. This prevents future events or later record updates from leaking into model inputs.
Questions beginners usually ask
Is every missing value zero? No. Missing may mean no activity, unavailable data or a pipeline problem. Decide and document the meaning before filling it.
Is leakage the same as training-serving skew? No. Leakage exposes unavailable future information during training; skew means training and production compute or source features differently.
What to remember
Key takeaways
- Feature definitions need an entity, calculation, window and observation cutoff.
- Point-in-time correctness prevents future information from entering training data.
- Null handling is a modelling decision, not only a SQL convenience.
- Reuse or rigorously align feature logic across training and serving.
Next Byte: allow natural-language questions while keeping generated SQL inside enforceable safety boundaries.