--- title: Partition and re-bless the drifted data-view fixture aliases type: task status: completed description: >- 144 of 310 data-view fixtures store a CTE alias the tree no longer emits, up from 7 of 256 on 2026-09-01. Both query-match comparison paths normalize hashes positionally, so nothing reports it, and a moved alias invalidates cached redis entries and saved views keyed on it. base_uri: user:task/league/data-views/re-bless-drifted-data-view-fixture-aliases.md created_at: '2026-09-17T16:35:58.090Z' entity_id: b5029dbd-4e1d-4d16-8a11-94384c5dd276 finished_at: '2026-09-17T19:16:18.810Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - relates [[user:task/league/data-views/reconcile-pinned-denominator-year-with-row-axis.md]] tags: - user:tag/league-xo-football.md updated_at: '2026-09-24T18:36:55.243Z' visibility_analyzed_at: '2026-11-05T23:48:57.839Z' --- ## Problem The data-view fixture corpus stores a `expected_query` per fixture, and every join in it carries a `get_table_hash` CTE alias. Both comparison paths — `normalize_sql_for_comparison` in the CLI and `test/utils/compare-queries.mjs` in the mocha harness — rewrite every distinct 32-character hash to a positional `table_0`, `table_1` before comparing. An alias that merely MOVES is therefore invisible to both, and the suite reports green over it. That matters because the alias is a cache key. Per `docs/create-data-view-test.md`, a moved alias "silently invalidates every cached redis entry and saved view keyed on it". `scripts/audit-data-view-alias-drift.mjs` exists for exactly this and is deliberately NOT wired into CI, on the reasoning that a red master defers every session's push fleet-wide and an alias moves legitimately during any column migration. The consequence is that nothing surfaces the drift; it is found only when someone runs the script. ## Measurement Run 2026-09-17 against league `8ebf9c34d` in a detached worktree, with a database (the audit under-reports without one — 31 fixtures cannot be generated at all): - 310 fixtures loaded, 166 identical, **144 drifting**, 0 unmeasured. For scale, the first sweep recorded in `docs/create-data-view-test.md` (2026-09-01) found **7 of 256**. The drifting population has grown roughly twentyfold in two and a half weeks. The same audit at the head of the row-axis work reported 119 drifting and 23 `sql_differs`, with **zero newly drifting** — that change moved no alias, and the 23 it re-blessed were all already in the drifting set. Re-derived 2026-09-17 against league `ec23704b1`, at/after the resolver fixes and the re-blessings that followed them: - 312 fixtures loaded, 192 identical, **118 drifting**, 2 `sql_differs`, 0 unmeasured. So 26 of the 144 were absorbed by that re-blessing rather than partitioned, which is the outcome the continuation warned about — the signal was spent without the cause being recorded. The 118 below are what remains, and they still need the split. Measured against the production database from the shared tree rather than a detached worktree; the tree carried no uncommitted data-view work at the time, but a re-run from a pinned worktree is still the reading to trust before blessing anything. ## Why this is not just churn The 2026-09-01 sweep split its findings, and the split is the useful part: of six investigated, 2 were ordinary pre-pin residue reproducible at some earlier clock, while **4 were reproducible at no clock at all** — meaning the alias input had changed in code and nothing could report it. The clock half is already solved by the unconditional pin in `test/utils/golden-clock.mjs`. The code half is what the script is for, and it is the half that indicates a real defect rather than a stale blessing. So 144 drifting fixtures is not automatically 144 re-blessings. Each one is a question: did the alias input change legitimately, or did a column migration silently repartition the cache? ## Plan RESOLVED 2026-09-17 (league `55ac0ea42`, `1b15543ac`). `ALIAS DRIFT: 0`, down from 144. What the work actually found is below; the checklist it replaces assumed a mixed clock/code partition and a bless tool that worked. - [x] Partition by cause. **All 118 were code-caused** — swept against 117 candidate clocks across two years, not one is reproducible at any instant, so none is stale-blessing residue. The 2 clock / 4 code split of 2026-09-01 did not generalize. - [x] Name the mechanism. It is the one `libs-shared/common-column-params.mjs` already documents at its head: every from-plays alias keys on the MEMBERSHIP of `nfl_plays_column_params`, so adding any play-filter param moves every from-plays alias at once. That is the twentyfold growth, and it is one cause rather than 118. - [x] Check the collision consequence. An ADD is churn; a REMOVE is churn plus a wrong-answer collision. No fixture lost an alias, all 118 carry the same count before and after, and the three alias-separation fixtures stay green — so this was the safe direction and no cached entry was repartitioned onto a wrong CTE. - [x] Re-bless. 118 fixtures re-pinned, full suite 10,462 passing. - [x] A cheaper standing signal. Not a CI gate, for the reason the audit's header already gives. What was actually missing was the ability to ACT: see below. ## Why it had grown, and what now stops it Two defects, both in the tooling rather than in the corpus: - **The bless tool could not bless an alias.** `update-data-view-snapshots.mjs` skipped every fixture that passed `compare_queries`, and that comparator normalizes alias hashes away before comparing — so a hash-only drift was unreachable by the one tool meant to clear it. Stale hashes could only accumulate. It blesses on a byte difference now. - **Blessing needs the SUITE's database, not any database.** 31 fixtures resolve content while generating. Without a database they skip harmlessly; against a bare schema they generate from empty tables and fall back, blessing SQL the suite rejects — measured here, 27 blessed that way turned 24 corpus cases red before being reverted. `--seeded` runs the suite's own `mochaGlobalSetup`. `scripts/partition-data-view-alias-drift.mjs` answers the clock-or-code question directly, so the next sweep does not have to re-derive it by hand. Two `sql_differs` fixtures remain and are a DIFFERENT class — real SQL divergence rather than alias drift, pre-existing, and green under the suite. They are the audit reading production while the suite reads seeded data, which is worth confirming before anyone treats them as a defect. ## Key locations - `repository/active/league/scripts/audit-data-view-alias-drift.mjs` — the audit, takes `--json` - `repository/active/league/docs/create-data-view-test.md` § "What a query-match test CANNOT check" — the contract and the 2026-09-01 sweep - `repository/active/league/test/utils/compare-queries.mjs` — the positional hash normalization that makes this invisible - `repository/active/league/test/utils/golden-clock.mjs` — the clock pin that closed the residue half - `repository/active/league/test/data-view-queries/` — the fixture corpus