As-of feature computation

You meet it when#

An offline metric is suspiciously good. AUC 0.97 on a churn problem is not a triumph — it is a bug report.

The pattern#

Build a table. One row per feature, one column for the moment its value becomes known.

Feature	Known at	Safe for a 1 March cutoff?
`tenure_days`	continuously	yes
`orders_90d`	continuously, if windowed to before the cutoff	yes, if computed as-of
`days_since_last_login`	continuously	yes, if computed as-of
`cancellation_reason`	at cancellation	no — it is the outcome
`total_lifetime_orders`	continuously	no as usually written — includes post-cutoff orders
`csat_score`	when surveyed, often after resolution	no for a resolution-time model

Twenty minutes, and it is the highest-value twenty minutes in the project.

The four shapes leakage takes#

Target leakage. A field that is a consequence of the outcome. Obvious once named, invisible in a column list.

Backfilled fields. Null when the row is created, populated later. Present in your training snapshot, null at scoring time.

Aggregate leakage. A statistic computed over the whole dataset — a mean for imputation, a target encoding — so test-set information reaches training. The fix is putting every transformation inside the pipeline so it is refit per fold.

Group leakage. The same user in train and test. The model memorises the user rather than the pattern.

Writing the feature correctly#

sql

-- Wrong: every order this account ever placed.
select account_id, count(*) as orders
from orders
group by 1;

-- Right: as of the scoring date, windowed.
select a.account_id,
       a.as_of_date,
       count(o.order_id) as orders_90d
from scoring_dates a
left join orders o
       on o.account_id = a.account_id
      and o.ordered_at <  a.as_of_date
      and o.ordered_at >= a.as_of_date - interval '90 days'
group by 1, 2;

The as_of_date is a column in the training table, not a constant. That single change makes the whole feature set reproducible at scoring time.