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

Your table grew 44 columns after row one: auditing schema drift before it rewrites your backtest

A prediction log looks like a stable object. You query it, you get numbers, the numbers move when the model changes. Tha…

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

A prediction log looks like a stable object. You query it, you get numbers, the numbers move when the model changes. That is the story you tell yourself.

Here is the same table, read honestly. ai_predictions holds 48,737 rows written between 2026-06-21 and 2026-11-21, across 130 columns. Eight of those columns have never held a single value. Forty-four were born after the first row was inserted. Eleven stopped being written before this month began.

Every one of those facts changes what a WHERE clause means, and none appear in the query you wrote six weeks ago.

All counts are from production, not an example schema.

The inventory query

Postgres knows which columns exist. It does not know when each one started carrying data, or whether it still does. That is one pass over the catalog plus one aggregate per column, and you cannot write it as a single static SELECT because a column name cannot be a parameter. Build one statement per column: on 48k rows the sweep takes about 90 seconds.

import psycopg2

DSN = "postgresql://user:pass@127.0.0.1:5433/analytics"
conn = psycopg2.connect(DSN)
cur = conn.cursor()

cur.execute("""
  SELECT column_name FROM information_schema.columns
  WHERE table_name = 'ai_predictions' ORDER BY ordinal_position
""")
cols = [r[0] for r in cur.fetchall()]

cur.execute("SELECT min(created_at)::date FROM ai_predictions")
birth = cur.fetchone()[0]

inventory = []
for col in cols:
    cur.execute(f"""
      SELECT min(created_at)::date, max(created_at)::date, count({col})
      FROM ai_predictions WHERE {col} IS NOT NULL
    """)
    first, last, n = cur.fetchone()
    inventory.append({"col": col, "n": n, "first": first, "last": last})

never  = [r["col"] for r in inventory if r["n"] == 0]
late   = [r for r in inventory if r["first"] and r["first"] > birth]
dead   = [r for r in inventory if r["last"] and str(r["last"]) < "2026-11-01"]

print(f"columns={len(cols)} never_written={len(never)} "
      f"born_late={len(late)} stopped={len(dead)}")
print("never_written:", never)

Output, verbatim from the run:

columns=130 never_written=8 born_late=44 stopped=11
never_written: ['user_id', 'iron_rule_results', 'cache_expires_at',
                'gate_want_visible', 'eu_band', 'eu_band_hit_baseline',
                'direction_gate', 'gate_reason']

Eight dead columns out of 130 is 6.2% of the schema. Four — eu_band, eu_band_hit_baseline, direction_gate, gate_reason — read like a band-based gating feature that was designed, migrated, and never wired up. They document an intention, not behaviour.

When the columns arrived

Grouping all 122 written columns by the month of their first non-null value gives the shape of the project rather than the shape of the data. Seventy-eight were present on day one.

first seennew columns
2026-06-21 (first row)78
rest of 2026-0612
2026-076
2026-0813
2026-0910
2026-102
2026-111

The 12 that landed in late June are typical: goal_line_direction, training_word_version, opening_assessment, dtl_at. The 13 in August are the second-market block plus the veto experiment. October's two are free-text reasoning fields. Each is a legitimate feature; each also carved a hole. Before the feature existed the column is null, and a null in a column that was not yet born is indistinguishable from a null in a column that was measured and came back empty.

That is the whole problem in one sentence. NULL means three different things and Postgres has one symbol for it:

  1. never measured — the feature did not exist yet
  2. not applicable — the feature existed, this row had no input
  3. measured, no value — the pipeline wrote nothing where it should have

Only the third is a bug. The first is your history. If you cannot tell them apart, you cannot audit anything.

Presence is not availability

ah_v5_p_up is the probability output of a v5 scoring pass. Count non-null rows per month:

monthrowsah_v5_p_up non-null
2026-062,7350
2026-072,6371
2026-081640
2026-099,75743
2026-1021,88610,073
2026-1111,55810,615

The column was added in July. It became real in October. Between those two facts, 5,536 rows were written and 1 of them carries the v5 value.

So WHERE ah_v5_p_up IS NOT NULL GROUP BY month returns a table whose first three rows are empty. Not an error, not a warning. An empty result that looks like "v5 had no disagreements in June", when v5 did not exist in June. Anyone reading the output without the row counts beside it will draw the wrong conclusion, and the conclusion will be self-consistent.

The fix is not a filter, it is a habit: never publish a monthly series without the denominator next to it, and never let a filter quietly reduce the denominator to zero.

Availability is not comparability

The second failure is worse: the column is present and populated the whole time.

ah_tier exists from the first row and holds 46,826 values. It is a small integer severity bucket. Filter ah_tier >= 3 and measure the settlement rate per month:

monthscored rowsrate, allrows with ah_tier>=3rate, filtered
2026-062,73562.30%0—
2026-072,63750.89%0—
2026-0816155.90%0—
2026-099,68158.18%1,02964.53%
2026-1021,71956.27%1,86658.31%
2026-1110,20456.13%72559.72%

The column is not null in June. The value is simply never 3 or higher. The tier assignment logic that produces 3+ went live in September, so the filter returns zero rows for the first three months and then jumps to a +6.35pp lift.

Read that as a result and you conclude the tier rule has predictive value. Read it as a schema event and you conclude the tier rule has no June comparison at all — and the +6.35pp is measured against a September baseline that is itself 4.12pp below June's 62.30%. The filtered September slice, 64.53%, is only 2.23pp above the unfiltered June number it is being quietly compared to. Two things changed at once: the population and the pipeline.

This is the failure mode I care about most, because no invariant catches it. ah_tier is non-null, in range, type-correct — and incomparable across the boundary where its generating rule changed. Only the month × filter cross-tab exposes it.

Backfilled versus forward-only

Not every later column is a hole. Two of them behave completely differently.

confidence_calibrated sits at 98–100% fill in every month back to June. That is not plausible for a later addition — the value was recomputed and written back over the whole history. A backfill.

training_word_version shows a different signature:

monthfill
2026-069.3%
2026-0710.5%
2026-0892.1%
2026-0999.7%
2026-10100.0%
2026-11100.0%

Nine percent is the fingerprint of a pipeline emitting the field incidentally — some code path that happened to serialize the object. The jump to 92% in August is deliberate adoption. Nothing before August can be segmented by training word version, and no backfill is possible: you cannot reconstruct which word a model carried eight weeks ago if nobody wrote it down.

Both columns pass a naive count(*) completeness check. Only the monthly breakdown says which history is real.

That gives a usable classification from two queries:

The eleven abandoned columns:

columnlast non-nullrows
hit_veto_ss, hit_veto_tag2026-10-3126,731 each
hit_1x2_semantic2026-10-0718,974
dict_win_rate2026-10-19394
rec_tier2026-10-19113
direction_dtl, hit_1x2_dtl, dtl_upper, dtl_lower, dtl_at2026-10-27340 each
tags2026-10-047

hit_veto_ss is the interesting one: 26,731 rows accumulated over two months, then switched off on the last day of October. A dashboard built on it still renders — frozen denominator, flat trend line, forever. Nobody gets an alert because the query does not fail.

The guard

The defect isn't that the schema changed. It's that the change was invisible at query time. So make it visible.

def assert_available(cur, table, col, month, min_fill=0.50):
    """Refuse to compare across a month where the column is mostly absent."""
    cur.execute(f"""
      SELECT count(*) AS n,
             count({col}) AS filled
      FROM {table}
      WHERE date_trunc('month', created_at) = date_trunc('month', %s::timestamp)
    """, (f"{month}-01",))
    n, filled = cur.fetchone()
    rate = filled / n if n else 0.0
    if rate < min_fill:
        raise RuntimeError(
            f"{table}.{col} at {month}: {filled}/{n} = {rate:.1%} "
            f"< {min_fill:.0%}. Column not available in this period."
        )
    return rate

Call it for every month before you plot it. Here, assert_available(cur, 'ai_predictions', 'ah_v5_p_up', '2026-06') raises on 0/2,735 and the chart never gets built. The failure moves from "a reader interprets a blank as a zero" to "the job crashes at 03:00 naming the column and the month". That is the trade you want.

A registry makes the same knowledge durable rather than tribal:

CREATE TABLE schema_drift_registry (
  table_name   text NOT NULL,
  column_name  text NOT NULL,
  status       text NOT NULL CHECK (status IN
                 ('core','backfilled','forward_only','abandoned','never_used')),
  first_seen   date,
  last_seen    date,
  n_observed   bigint,
  note         text,
  PRIMARY KEY (table_name, column_name)
);

Populate it from the inventory query and review the diff monthly. A column flipping from forward_only to abandoned is a change worth a ticket; one flipping to never_used after six months should be dropped from the schema instead of confusing the next reader.

What this table actually taught me

Three numbers, same database, same day: 130 columns declared, 122 ever written; 44 with no history before their birth date; 11 that stopped before this month began.

None of these are data-quality problems in the sense of wrong values. Every value that exists is correct. The defect is temporal: the shape of the record changed while the analyses kept treating it as one continuous population.

If you take one thing: NULL is not a value, it is a timestamp. Before you group by it, ask when that column started, when it stopped, and whether the WHERE clause you inherited is filtering rows — or filtering history.


Analytics and research content. Not betting advice. 18+

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

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

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

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