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

A football analytics platform operator audited 209 models and 46,233 settled predictions for time leakage and traced it to three schema problems rather than model bugs. The audit found 81 predictions written after kickoff (settling at 69.14% versus 56.9% for the rest), 62,922 post-kickoff odds ticks across 15,259 fixtures in a 15.4-million-row price store, and only 29.9% of stored "opening" prices matching a declared opening quote. The operator argues data freshness should be enforced as a schema property, since nothing currently prevents rows from being written after the event they describe.

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; | bucket | rows | share | settled-hit % | |---|---|---|---| | over 24 h | 14,908 | 32.25% | 56.45 | | 6-24 h | 20,292 | 43.89% | 57.28 | | 1-6 h | 9,491 | 20.53% | 56.34 | | 10-60 min | 1,171 | 2.53% | 58.84 | | 0-10 min | 290 | 0.63% | 55.52 | | after kickoff | 81 | 0.18% | 69.14 | 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: | reference | rows matching | share | |---|---|---| | declared opening, any source | 13,706 | 29.9% | | pre-kickoff price within 2 h, any source | 20,365 | 44.5% | 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: | month | fixtures in store | with declared opening | coverage | |---|---|---|---| | 2026-06 | 1,007 | 0 | 0.0% | | 2026-07 | 3,018 | 0 | 0.0% | | 2026-08 | 9,320 | 0 | 0.0% | | 2026-09 | 7,443 | 5,108 | 68.6% | | 2026-10 | 17,392 | 8,848 | 50.9% | | 2026-11 | 6,689 | 41 | 0.6% | 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: python 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 the first freeze wins, forever 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+ 本内容仅供数据分析与研究参考，不构成任何决策建议。