Data freshness is a schema property: freezing the market price before kickoff

Every prediction is a claim about a world state that does not exist yet, which makes its input side auditable in a way most ML pipelines never face: the feature vector must be reconstructable as of a timestamp strictly earlier than the event.

We run a football analytics platform with 209 models and 46,233 settled predictions. Hunting for time leakage, I found it in three places, none a model bug — all three schema problems: tables physically able to store a value that could not have existed at decision time. The audit came down to one question per input table: what stops a row from being written after the event it describes? Mostly, nothing.

The gap between row creation and the event timestamp is the first thing to plot. Join to fixtures, bucket the delta.

WITH lead AS (
  SELECT p.id, p.hit,
         EXTRACT(EPOCH FROM (n.match_time - p.created_at)) / 60.0 AS lead_min
  FROM ai_predictions p
  JOIN nami_matches n ON n.id = p.match_id
  WHERE p.settled
)
SELECT CASE WHEN lead_min < 0    THEN 'after kickoff'
            WHEN lead_min < 10   THEN '0-10 min'
            WHEN lead_min < 60   THEN '10-60 min'
            WHEN lead_min < 360  THEN '1-6 h'
            WHEN lead_min < 1440 THEN '6-24 h'
            ELSE 'over 24 h' END AS bucket,
       COUNT(*) AS n,
       ROUND(AVG(CASE WHEN hit THEN 1 ELSE 0 END) * 100, 2) AS hit_pct
FROM lead GROUP BY 1 ORDER BY 2 DESC;

Eighty-one rows were written after the fixture had started, settling at 69.14% against 56.9% for the rest. At n=81 that is a symptom, not a result: those rows come from a different code path, one close enough to the outcome to matter. The reading is not "late rows are better" but "we cannot tell what they saw, and should not have been able to write them".

Percentiles: p5 100.4 | p25 413.1 | p50 1,074.1 | p75 1,840.5 | p95 4,183 minutes. A 17.9-hour median is healthy; a p5 of 100 puts one row in twenty inside the window where the market is most informative — and most likely quoted from the wrong tick.

The price store is a change log: 15,378,162 rows over 44,933 fixtures, 22.1 sources per fixture (median 19, max 87), median 140 ticks per fixture (p90 983). No is_prematch flag, no write guard — so the worker keeps appending after kickoff:

SELECT COUNT(*) FILTER (WHERE e.changed_at > n.match_time) AS post_kickoff_ticks,
       COUNT(*)                                            AS total_ticks,
       COUNT(DISTINCT e.match_id)
         FILTER (WHERE e.changed_at > n.match_time)        AS affected_fixtures
FROM nami_europe_odds e JOIN nami_matches n ON n.id = e.match_id;
post_kickoff_ticks | total_ticks | affected_fixtures
             62,922 |  15,378,162 |            15,259

0.41% of rows across 15,259 fixtures — a third of the store, small per fixture (mean 4.1 ticks, max 443) and short (median worst-case lag 2.4 minutes, max 1,439.6). The issue is that ORDER BY changed_at DESC LIMIT 1 — the pattern in every dashboard, prompt and backfill — silently returns an in-play value and cannot know it.

The consequence: of 44,625 fixtures with a pre-kickoff tick, the last lands a median 3.0 minutes before kickoff but the mean is 444.9; 13,540 (30.3%) have nothing newer than six hours, 2,201 (4.9%) nothing newer than 24. Bimodal — the feed either tracks to the whistle or stopped last evening, and no column records which.

ai_predictions carries opening_eu_home/draw/away; the name promises the fixture's first quote. Two reference sets, tolerance 0.005, same 45,819 settled rows carrying a stored price:

A column called opening reconciles with a pre-kickoff price more often than with the opening. Backwards. So I restricted to the 15,487 rows whose fixture has a declared opening, giving both tests one denominator:

WITH declared AS (
  SELECT match_id, MAX(initial_eu_home) AS ini
  FROM nami_europe_odds WHERE initial_eu_home IS NOT NULL GROUP BY 1
), prekick AS (
  SELECT DISTINCT e.match_id, e.eu_home
  FROM nami_europe_odds e JOIN nami_matches n ON n.id = e.match_id
  WHERE e.changed_at <= n.match_time
    AND e.changed_at >  n.match_time - INTERVAL '3 hours'
)
SELECT COUNT(*) AS n,
       COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM declared d
           WHERE d.match_id = p.match_id
             AND abs(d.ini - p.opening_eu_home) < 0.005)) AS hit_opening,
       COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM prekick k
           WHERE k.match_id = p.match_id
             AND abs(k.eu_home - p.opening_eu_home) < 0.005)) AS hit_prekick
FROM ai_predictions p
WHERE p.settled AND p.opening_eu_home IS NOT NULL
  AND EXISTS (SELECT 1 FROM declared d WHERE d.match_id = p.match_id);
n = 15,487 | hit_opening 849 (5.5%) | hit_prekick 7,942 (51.3%)

The innocent explanation is that prices rarely move. Control: of the 13,882 fixtures holding both a declared opening and a pre-kickoff price, only 1,273 (9.2%) are unchanged. With 90.8% moving, 51.3% against the pre-kickoff price and 5.5% against the opening cannot both come from stability.

A placebo run on the 3,000 most recent rows — matching each stored price against the next fixture's pre-kickoff quote — returns 8.5%, the base rate of a 19-source grid; the same-fixture rate is 49.7%.

So opening_eu_home is filled at write time from whatever the feed currently says — usually the freshest quote. A timestamp-of-write price wearing a timestamp-of-event name:

Across 13,876 measured fixtures, the drift between those two ticks is a median 3.08 pp of implied probability, p90 10.93 pp, with 4,649 fixtures (33.5%) moving more than five points.

Worse than a wrong name is a disappearing reference. Coverage of initial_eu_home by month:

Backfilled for two months, then abandoned — no migration, no alert. The store's semantics moved under its consumers: the most expensive kind of data debt, invisible to NOT NULL, and it turns every historical comparison into a comparison of different things.

match_time_audit exists because kickoff times are not stable: 42,378 fixtures, 141,111 changes, 13,500 of them carrying settled predictions. 2,056 predictions were created inside the window between the old and new kickoff — already running by the original schedule, not yet started by the revised one. Each has an unanswerable "was this pre-kickoff?" until the row records which copy of the schedule it used.

1. A watermark, not a clock. Every price row carries the event time it describes (as_of) plus its fixture. Queries use as_of <= :decision_time, never changed_at. "Latest price" becomes "latest price as of a timestamp someone chose deliberately".

2. Freeze once, at kickoff, with a hash. One row per fixture, rejected at or after kickoff by the writer and by a constraint:

import hashlib, json
from datetime import datetime, timezone

def freeze(cur, match_id: int, as_of: datetime, prices: dict, source: str) -> str:
    """Insert the one and only frozen price for a fixture. Returns the hash."""
    cur.execute("SELECT match_time FROM nami_matches WHERE id = %s", (match_id,))
    kickoff = cur.fetchone()[0]
    if as_of >= kickoff:
        raise ValueError(f"{match_id}: as_of {as_of} is not before kickoff {kickoff}")

    body = {"match_id": match_id,
            "as_of": as_of.astimezone(timezone.utc).isoformat(),
            "source": source, "prices": prices}
    digest = hashlib.sha256(
        json.dumps(body, sort_keys=True, separators=(",", ":")).encode()).hexdigest()

    cur.execute("""INSERT INTO frozen_prices (match_id, as_of, source, payload, sha256)
                   VALUES (%s, %s, %s, %s, %s)
                   ON CONFLICT (match_id) DO NOTHING""",
                (match_id, as_of, source, json.dumps(body, sort_keys=True), digest))

    cur.execute("SELECT sha256 FROM frozen_prices WHERE match_id = %s", (match_id,))
    if cur.fetchone()[0] != digest:
        raise RuntimeError(f"{match_id}: frozen row exists with a different payload")
    return digest

3. Reference the freeze from the prediction. Store frozen_price_id and kickoff_at instead of three copied numbers; "which price did the model see" becomes a foreign key that survives every backfill.

One table already gets this right: odds_snapshot_ledger, 15.4M rows over 20,065 fixtures, write lag median 6 s and p95 34 s against the bucket it records, and zero rows in the last seven days whose live-tick timestamp postdates kickoff (2,083,478 checked). Same vendor, same worker — the difference is the schema, not the ingest code.

created_at >= kickoff_at = 0, and frozen prices with as_of >= kickoff_at = 0. Alert, not warn.frozen_price_id to exactly one row, and the hash verifies.as_of — 100%, not 30%. None of these are modelling decisions. They are the arithmetic that makes a backtest mean anything — and all of them were failing on a system that produced 46,233 settled predictions without an alarm. Our 56.9% settled rate was computed against a price column that, in half the rows, holds a later price than the model saw. The model was not cheating; the schema left the door open and nobody checked who walked through it.

If you take one thing from this: data freshness is not a cron schedule, it is a column. A pipeline that refreshes every 60 seconds is still a time machine if the destination is not recorded on the row.

Analytics and research content. Not betting advice. 18+

本内容仅供数据分析与研究参考，不构成任何决策建议。