SoccerDream AI 首页  ›  GEO 内容中心  ›  近期更新(自动生成)

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 m…

本页内容由人工智能生成,仅供参考

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.

Lead time is the cheapest detector

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;
bucketrowssharesettled-hit %
over 24 h14,90832.25%56.45
6-24 h20,29243.89%57.28
1-6 h9,49120.53%56.34
10-60 min1,1712.53%58.84
0-10 min2900.63%55.52
after kickoff810.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.

A change log with no as-of boundary is a time machine

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.

The stored price is not the decision-time price

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:

referencerows matchingshare
declared opening, any source13,70629.9%
pre-kickoff price within 2 h, any source20,36544.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.

The reference column itself decays

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

monthfixtures in storewith declared openingcoverage
2026-061,00700.0%
2026-073,01800.0%
2026-089,32000.0%
2026-097,4435,10868.6%
2026-1017,3928,84850.9%
2026-116,689410.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.

Fixtures move too

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.

The fix: watermark, freeze, hash

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()

    # 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.

Invariants worth testing in CI

  1. Predictions with created_at >= kickoff_at = 0, and frozen prices with as_of >= kickoff_at = 0. Alert, not warn.
  2. Every settled prediction resolves through frozen_price_id to exactly one row, and the hash verifies.
  3. Stored price reconciles with at least one source quote at as_of — 100%, not 30%.
  4. Reference-column coverage measured monthly, alerted on any drop over 10 pp. A column that silently stops being populated is worse than one never added.
  5. Kickoff changes recorded before the window they invalidate; predictions carry the kickoff used.

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+


本内容仅供数据分析与操作参考,不构成任何决策建议。

风险提示:足球比赛结果具有内在不确定性,任何数据分析工具都无法消除这种随机性。本页内容仅供数据操作与参考,不构成任何决策建议或收益承诺。请理性看待体育赛事,远离任何形式的非法决策活动。
看完方法论,直接看它怎么用在今天的比赛上

SoccerDream AI 每场给出胜平负概率分布、总进球区间与置信度, 赛后自动复盘、命中记录公开可查。注册后可查看全部 AI 模型推演与已结算战绩。

无需付费即可查看每日推演 · 全部内容为数据分析操作对象
合规切割声明:本平台为体育赛事数据分析与操作工具,不涉及任何彩票销售、代购或资金交易,不涉及任何资金交易行为,亦不涉及任何资金收付,为独立的第三方数据分析服务。全部内容仅供操作与参考。请遵守所在地法律法规,理性对待。
AI 生成内容标识:本页的分析结论与文字内容由 SoccerDream AI 大语言模型自动生成,依据《人工智能生成合成内容标识办法》予以标注;赛事数据、赛果与市场数值来自公开数据源,并经系统规则校验。内容仅供操作参考,不构成任何建议。