Skip to Content

Why compute features in SQL with shift-correct as of ts < cutoff?

Category: SQL for AI Engineering

Answer

A feature aggregation that accidentally includes “future” data leaks the label. SQL’s WHERE ts < cutoff makes leakage hard to introduce accidentally because the cutoff lives in the same query as the aggregation. Pair with feature-store tooling for production.

Concrete examples from the fca project context

Example 1

Label leakage: features computed USING rows after the label time leak. Use WHERE ts < label_ts.

Example 2

Snapshot tables: features_snapshots(date) = features for that date; query as of date.

Example 3

SQL templating: query(“SELECT … WHERE ts < :cutoff”) always with a cutoff.

Data flow / flow chart

aggregate -> WHERE ts < cutoff -> feature row
  (never compute with rows after the label)

Takeaway

Cutoff in the same query as the aggregation. Time-leak bugs become grep-able.