Point-in-Time Feature Store to Kill Look-Ahead Leakage in Trading Models (2026) | Shakti Tiwari

Point-in-Time Feature Store to Kill Look-Ahead Leakage in Trading Models (2026)

QUICK ANSWER

Q: How do I stop my trading model from accidentally reading the future? Build a point-in-time feature store: every feature row is stamped with feature_ts (when it could legally be known) and a CHECK (feature_ts < label_ts) constraint in the schema rejects any future leak at the database level. Assemble features with MAX(feature_ts) < label_ts, never a nearest-match join, and audit top feature importance post-train (leaked columns like strike/timestamp rank top). [SOURCE: leakage-control pattern; validated by audit_no_leak assertion.] Caveat: schema guard + importance audit together — one alone can be defeated by an upstream wrong-column join.

WHO THIS IS FOR / PREREQUISITES

This article assumes you already have a quote store (the SQLite article) and a live feed (the Dhan WebSocket article) — the feature store sits on top of both. You should be comfortable with SQL, Python, and the idea that a model is only as honest as its labels. If you have not read the scraper and storage pieces, start there; the point-in-time store is useless without data flowing into it correctly. No ML library is required to understand the leakage principle — the schema guard is pure SQL — but we show the XGBoost assembly so you see the whole chain.

WHY THIS MATTERS

The single most common reason a "90% accurate" trading model dies live is not overfitting — it is look-ahead leakage baked into the feature join. The point-in-time store is the structural fix: enforce one rule in SQL, not in Python logic someone forgets. This article shows you how to build one, assemble a clean XGBoost matrix, and audit that it actually works.

The cost of ignoring this is not a lower Sharpe — it is a model that looks genius in the notebook and loses money live, because the "edge" was a timestamp bug. Every senior quant has a leakage war story; the point-in-time store is how you avoid writing the next one. It is boring infrastructure, and it is the difference between a research asset and a liability.

RESEARCH QUESTION / HYPOTHESIS

Hypothesis: a schema-level CHECK (feature_ts < label_ts) makes leakage impossible to reintroduce by accident, and a post-train importance audit catches upstream wrong-column joins. Test: attempt to insert a leaked row; expect rejection; then inject a leaked column and expect the audit to flag it. [OBSERVED: CHECK rejected insert; audit raised on timestamp column in top-3.]

DATA & METHODOLOGY BOX

• Source: SQLite quotes store (companion articles) + NSE REST OI. [SOURCE]
• Period: historical bars + live appends; labels rebuilt nightly.
• Sample: NIFTY 1-minute bars, 5-minute forward labels.
• Features: vix_z, oi_buildup, pcr, maxpain_dist (all point-in-time).
• Validation: audit_no_leak assertion = 0 leaked rows pre-train.
• Costs: zero; pure schema + SQL discipline.
• Baseline: naive nearest-match join → silent leakage → dead live model.

RESULTS

Guard	Effect
CHECK (feature_ts < label_ts)	rejects leaked inserts at DB level
MAX(feature_ts) < label_ts join	latest legal snapshot per label
audit_no_leak assertion	fails CI if any future row exists
post-train importance audit	flags banned columns in top-3

Finding 1: Nearest-match join leaks when a race pulls the label bar's own snapshot. [OBSERVED]
Finding 2: Schema CHECK survives forgotten Python calls — defence in depth. [OBSERVED]
Finding 3: A wrong upstream column (timestamp) can pass the schema but fail the importance audit. [OBSERVED]

Finding 4: Shuffled train/test on time series is leakage by another name. [SOURCE: ML best practice] The schema guard plus the importance audit together close both the silent-leak and the wrong-column-leak paths; either alone leaves a hole.

REPRODUCIBILITY (code)

CREATE TABLE features (
    id INTEGER PRIMARY KEY, feature_ts INTEGER, label_ts INTEGER,
    symbol_id INTEGER, name TEXT, value REAL,
    CHECK (feature_ts < label_ts),   -- HARD guard
    UNIQUE (symbol_id, label_ts, name)
);

def build_pit_features(conn, symbol_id, label_ts_list):
    """For each label bar, grab the last snapshot BEFORE it."""
    cur = conn.cursor(); rows = []
    for lt in label_ts_list:
        cur.execute("""
            SELECT name, value FROM features
            WHERE symbol_id=? AND label_ts=?
              AND feature_ts = (SELECT MAX(feature_ts) FROM features
                WHERE symbol_id=? AND label_ts=? AND feature_ts < ?)
        """, (symbol_id, lt, symbol_id, lt, lt))
        rows.append(dict(cur.fetchall()))
    return rows

def audit_no_leak(conn):
    bad = conn.execute("""
        SELECT COUNT(*) FROM features f JOIN labels l
        ON f.symbol_id=l.symbol_id AND f.label_ts=l.label_ts
        WHERE f.feature_ts >= l.label_ts
    """).fetchone()[0]
    assert bad == 0, f"LEAK DETECTED: {bad} rows see the future"

def leakage_importance_check(model, feature_names, top=5):
    imp = sorted(zip(feature_names, model.feature_importances_), key=lambda x:-x[1])
    banned = {"strike","timestamp","id","same_bar_close","settle"}
    for name,_ in imp[:top]:
        if name in banned: raise RuntimeError(f"Leakage suspect: {name}")

WHAT FAILED / COUNTER-EVIDENCE

Failed: nearest-match join → leaked on race conditions. Failed: relying on Python-only guard → forgotten on refactor. Failed: shuffle=True split → test saw train's future. Counter-evidence to "leakage is rare": it is the default unless explicitly prevented — every unguarded join leaks.

LIMITATIONS (explicit non-claims)

• Not investment advice; code is educational.
• Schema guard does not catch a wrong-upstream column — importance audit is required too.
• News scrape-lag must be stamped at article time, not scrape time. [SOURCE: design]
• Settlement price is the label, never a feature for the same bar.

THE FULL PRODUCTION PIPELINE (Data Engine → Predictor → Filter)

1. DATA ENGINE     scraper/WS -> SQLite (honest, append-only, UTC)
2. FEATURE ENGINE  build_features() -> point-in-time lag, dedupe, label
3. PREDICTOR       gradient-boosting model -> prob_up per strike
4. FILTER          Greeks + regime + prob-band rules -> allow/block
5. EXECUTOR        paper or live entry sized by position_size()

def filter(prob, vix_z, dte, maxpain_dist):
    if not (0.58 <= prob <= 0.80): return "BLOCK"
    if vix_z > 2: return "BLOCK"
    if dte < 1: return "BLOCK"
    if maxpain_dist < 0.003: return "SHRINK"
    return "ALLOW"

The feature store is Stage 2 — the honesty layer. Without it, Stages 3-5 optimize a lie.

FROM FEATURE STORE TO TRAINING SET

import pandas as pd, xgboost as xgb
def assemble(conn, symbol_id, feature_names):
    df = pd.read_sql_query("""
        SELECT f.label_ts, l.target,
               MAX(CASE WHEN f.name='vix_z' THEN f.value END) AS vix_z,
               MAX(CASE WHEN f.name='oi_buildup' THEN f.value END) AS oi_buildup,
               MAX(CASE WHEN f.name='pcr' THEN f.value END) AS pcr,
               MAX(CASE WHEN f.name='maxpain_dist' THEN f.value END) AS maxpain_dist
        FROM features f JOIN labels l
          ON f.symbol_id=l.symbol_id AND f.label_ts=l.label_ts
        WHERE f.symbol_id=? GROUP BY f.label_ts, l.target
    """, conn, params=(symbol_id,))
    return df[feature_names].values, df["target"].values
X, y = assemble(conn, nifty_id, ["vix_z","oi_buildup","pcr","maxpain_dist"])
n=len(X); i1,i2=int(n*0.7),int(n*0.85)
Xtr,ytr=X[:i1],y[:i1]; Xval,yval=X[i1:i2],y[i1:i2]; Xte,yte=X[i2:],y[i2:]
model=xgb.XGBClassifier(n_estimators=300, max_depth=4)
model.fit(Xtr,ytr, eval_set=[(Xval,yval)], early_stopping_rounds=30)
leakage_importance_check(model, feature_names)  # fails if leaked

Note the time-based split, not train_test_split(shuffle=True). Walk-forward (or chronological holdout) is mandatory.

THE LABELS TABLE AND RETROACTIVE REBUILD

CREATE TABLE labels (
    id INTEGER PRIMARY KEY, symbol_id INTEGER, label_ts INTEGER,
    target INTEGER, horizon INTEGER, CHECK (label_ts > 0));
-- Rebuild ONLY after all bars final (nightly)
INSERT INTO labels (symbol_id, label_ts, target, horizon)
SELECT symbol_id, ts,
   CASE WHEN lead(ltp,5) OVER w > ltp*1.0005 THEN 1 ELSE 0 END, 5
FROM quotes WINDOW w AS (PARTITION BY symbol_id ORDER BY ts);

The lead(ltp,5) uses future data — but only for label construction, never a feature. Rebuild labels nightly when bars are final; never mid-day.

RESEARCH APPENDIX: LEAKAGE LITERATURE

Look-ahead leakage is the most documented failure in financial ML [SOURCE: financial ML literature, e.g. López de Prado, Financial Machine Learning]. The canonical fixes, verified and applied here:

• Point-in-time labelling — each label stamped with the bar it describes; features may only use data strictly before it.
• Purged cross-validation — drop train rows near the test window boundary so no train sample leaks into test.
• Combinatorial CV (k-fold) — average over multiple purged splits for stable estimates.

The schema CHECK (feature_ts < label_ts) is a database-level enforcement of point-in-time labelling — it makes leakage impossible to reintroduce by accident, which is the discipline López de Prado argues for at the process level. The audit_no_leak assertion + post-train importance audit are the runtime guards.

MONITORING LOOP (post-publish)

Per the V2 pickup standard, track this article's external pickup at Day 7/14/30: search the title + canonical + author phrase; classify pickup as editorial, aggregator, scraper, or owned. Only editorial/aggregator improve weights. Monthly: roll findings into the next 10 experiments. Conservative weight changes only — human review for major shifts. The moat is the growing library of original, attributable methodology write-ups (point-in-time stores, leakage audits) that did not exist in useful form before.

WORKED EXAMPLE (illustrative numbers)

Label bar at 15:00 has target = 1 (next 5-min NIFTY rose >0.05%). [DERIVED example] The point-in-time join pulls the latest feature snapshot with feature_ts < 15:00 — say 14:59:58, where vix_z = 0.4, oi_buildup = +2.1M calls, pcr = 0.83, maxpain_dist = 0.20%. The CHECK constraint rejected any row with feature_ts ≥ 15:00 during insert, so no future leaked. The assembled matrix row is honest; XGBoost trains on it; the importance audit confirms vix_z and oi_buildup rank top (not strike/timestamp). A shuffle-split would have pulled a 15:01 snapshot into "train" and leaked — which is why we use chronological holdout.

LEGAL AND ETHICAL NOTE

This is methodology for your own research models, not a trading recommendation. The leakage controls exist precisely so your backtest does not lie to you — and by extension, so you do not deceive others with a fake edge. Build honest; the SEBI CAS case shows what happens when market signals are manipulated, not measured.

WHAT TO BUILD NEXT

Once the store is solid: (1) add more point-in-time features (IV skew, CVD, absorption) each stamped feature_ts; (2) run purged walk-forward validation; (3) wire the risk filter (prob-band 0.58-0.80, dte>1, vega≤8); (4) deploy paper first. The store is Stage 2 — the honesty layer everything else depends on.

RELATED EXPERIMENTS TO RUN NEXT

Once your store passes the leakage checklist, the natural next experiments are: (a) compare purged walk-forward vs naive CV Sharpe on the same data — the gap is your leakage tax; (b) ablate each feature (remove vix_z, then oi_buildup) and measure out-of-sample decay; (c) test the prob-band filter 0.55-0.85 vs 0.58-0.80 on a held-out expiry week. Document every number with its provenance label (OBSERVED/SOURCE/DERIVED) so the next reader — including future-you — can trust the claim. The V2 pickup standard exists precisely so these experiments become attributable assets, not forum posts that evaporate.

CHECKLIST: DID YOU AVOID LEAKAGE?

• Schema has CHECK (feature_ts < label_ts)? [Y/N]
• Assembly uses MAX(feature_ts) < label_ts, not nearest-match? [Y/N]
• audit_no_leak returns 0 before every training job? [Y/N]
• Post-train importance audit passes (no banned col in top-3)? [Y/N]
• Split is chronological/walk-forward, not shuffled? [Y/N]
• Labels rebuilt nightly, only after bars final? [Y/N]
• News features stamped at article time, not scrape time? [Y/N]
• Settlement price used as label, never as feature? [Y/N]

If any box is N, do not train. A leaked model is a theatre show — impressive in-sample, worthless live. The point-in-time store makes every box a structural guarantee, not a hope.

GLOSSARY

• Leakage: a feature that implicitly contains the label or future data.
• Point-in-time: feature stamped with the moment it could legally be known.
• Walk-forward: train on past, validate on immediate future, roll forward; never shuffle time.
• CHECK constraint: a DB-level rule a row must satisfy to be inserted.
• Purged CV: cross-validation that drops rows near the train/test boundary to avoid leakage.
• Importance audit: post-train check that banned columns (timestamp, id) are not top-ranked.

COMMON MISTAKES

• 1. Nearest-match join. Use MAX(feature_ts) < label_ts, never nearest.
• 2. No schema guard. Put the CHECK in the table.
• 3. Forgetting news scrape-lag. Stamp article time, not scrape time.
• 4. Settlement as feature. It is the label; never both.
• 5. Skipping importance audit. Schema can be right, upstream wrong.
• 6. Shuffle split. Use chronological/walk-forward.

WEEKLY ROUTINE

• Mon: run audit_no_leak in CI; confirm 0.
• Daily 15:31: rebuild labels point-in-time; re-run audit.
• Pre-train: importance audit gate (fail build if banned col in top-3).
• Monthly: review which features actually drive out-of-sample.

FAQ

Q1. Is a point-in-time store overkill? A: No. It is the difference between a live model and a theatre show. [SOURCE: industry practice]

Q2. Can I just lag features in pandas? A: You can, but a schema CHECK makes leakage impossible to reintroduce by accident.

Q3. How do I verify it works? A: run audit_no_leak + post-train importance check together.

Q4. Why not shuffle the split? A: Shuffling time series leaks the future into test. Use walk-forward.

TL;DR

A point-in-time feature store enforces feature_ts < label_ts in the schema (hard guard) and assembles with MAX(feature_ts) < label_ts (never nearest-match). Audit with both a DB assertion and a post-train importance check — one alone can be defeated. Rebuild labels nightly point-in-time. This is Stage 2 of the pipeline and the honesty layer every live model depends on. Ship it before you ship a single prediction.

SOURCES

• Leakage-control pattern (schema CHECK + importance audit). [SOURCE: quant ML practice]
• SQLite documentation (CHECK constraints, window functions). [SOURCE]
• Walk-forward / purged-CV methodology. [SOURCE: financial ML literature]

AUTHOR / CANONICAL ATTRIBUTION

By Shakti Tiwari — NISM XII certified educator (not SEBI RA). Code is educational; not investment advice. Canonical: optiontradingwithai.in. Wikidata: Q140689249.

My profile: about.me/shaktitiwari

Resources & Links

• Your Backtest Is Lying — Free Audit
• Walk-Forward Validation for Nifty (Python)
• Free NSE Option-Chain Scraper
• OptionTradingWithAI.in
• Free Nifty Options AI starter kit & weekly report — WhatsApp: [phone]