Part 1 of 2 for audit findings C2 (2023/24 results missing) and H2 ("vs LA avg" uses the wrong average). Spec: docs/superpowers/specs/2026-10-06-ks4-2023-24-and-la-averages-design.md; plan: docs/superpowers/plans/2026-10-06-ks4-2023-24-and-la-averages.md.
What changes
C2 had three causes.
KS4 results. DfE's 2024/25 results file re-publishes 2022/23 and 2023/24 under current column names. The 2023/24 release's own file (re-issued March 2026 with older names) was read last and upserted blank rows over the good ones. The KS4 results stream now lets the newest release own every year it contains (release_precedence.py, opt-in for this stream only — a general rule would wipe KS2, whose 2024/25 file holds 10% of 2023/24's rows). Release slugs with a suffix (2025-26-revised) now read as their year.
KS4 school information. 2023/24 exists only under the older names; ks4_info.py maps the 11 of them.
KS2 school information. DfE writes 2023/24 percentages as 34%; safe_numeric now accepts a trailing %.
H2. A new stream ees_ks4_la keeps the LA rows of the DfE data set the England stream already reads (they match DfE's published LA averages exactly, all 152 LAs). New mart marts.fact_ks4_la_averages, keyed by GIAS LA code. The API still computes its own average until PR 2.
Data tests.assert_ks4_years_have_results (≥50% of a year's rows have Attainment 8; DfE's files reach 82%), assert_ks2_info_percentages_loaded (≥90% of rows with pupil counts have a disadvantaged %; DfE 96–97%), assert_ks4_la_averages_cover_las (≥145 LAs in the latest year), and a warning, assert_ks4_la_averages_current, if the LA averages fall behind the school results (the summary data set's id is fixed and will go stale when DfE publishes 2025/26).
Evidence
A local end-to-end run of the changed tap against DfE through meltanolabs target-postgres, then dbt (17/17 tests pass):
log: Skipping 57090 rows for 202324 from release b76a938a…: a newer release supplied that year
Bishop Stopford (137086) 2023/24: Attainment 8 64.1, Progress 8 1.02, English and maths 91.7%; information 217 pupils, KS2 average 108.1, SEN 6.5%, "Well above average", gaps 4.6 / 0.14
school 147411 2023/24: disadvantaged 34%, EAL 56%, SEN support 20%
per-year coverage identical to DfE's files; LA mart 150–152 LAs a year; Kensington and Chelsea 54.5, Wandsworth 51.8, Westminster 53.2
CI Python suite 363 passed; dbt unit and data tests seen failing on fixtures that reproduce each bug before passing
After merge
Run school_data_annual_ees on staging. Expect /api/schools/137086 2023/24 Attainment 8 64.1 / Progress 8 1.02, and /api/schools/147411 2023/24 disadvantaged 34.
Then merge PR 2 (site), so its new E2E journeys run against loaded data. Promote PR 2 only after the EES DAG has also run on production.
Optional cleanup, once per environment:delete from raw.ees_ks4_performance where time_period = '202324' and pupil_count is null; — removes the 34,254 old-label sub-group rows the re-issued file left behind (new-format rows always have pupil_count). Nothing reads them.
Part 1 of 2 for audit findings **C2** (2023/24 results missing) and **H2** ("vs LA avg" uses the wrong average). Spec: `docs/superpowers/specs/2026-10-06-ks4-2023-24-and-la-averages-design.md`; plan: `docs/superpowers/plans/2026-10-06-ks4-2023-24-and-la-averages.md`.
## What changes
**C2 had three causes.**
1. **KS4 results.** DfE's 2024/25 results file re-publishes 2022/23 and 2023/24 under current column names. The 2023/24 release's own file (re-issued March 2026 with older names) was read last and upserted blank rows over the good ones. The KS4 results stream now lets the newest release own every year it contains (`release_precedence.py`, opt-in for this stream only — a general rule would wipe KS2, whose 2024/25 file holds 10% of 2023/24's rows). Release slugs with a suffix (`2025-26-revised`) now read as their year.
2. **KS4 school information.** 2023/24 exists only under the older names; `ks4_info.py` maps the 11 of them.
3. **KS2 school information.** DfE writes 2023/24 percentages as `34%`; `safe_numeric` now accepts a trailing `%`.
**H2.** A new stream `ees_ks4_la` keeps the LA rows of the DfE data set the England stream already reads (they match DfE's published LA averages exactly, all 152 LAs). New mart `marts.fact_ks4_la_averages`, keyed by GIAS LA code. The API still computes its own average until PR 2.
**Data tests.** `assert_ks4_years_have_results` (≥50% of a year's rows have Attainment 8; DfE's files reach 82%), `assert_ks2_info_percentages_loaded` (≥90% of rows with pupil counts have a disadvantaged %; DfE 96–97%), `assert_ks4_la_averages_cover_las` (≥145 LAs in the latest year), and a warning, `assert_ks4_la_averages_current`, if the LA averages fall behind the school results (the summary data set's id is fixed and will go stale when DfE publishes 2025/26).
## Evidence
A local end-to-end run of the changed tap against DfE through meltanolabs target-postgres, then dbt (17/17 tests pass):
- log: `Skipping 57090 rows for 202324 from release b76a938a…: a newer release supplied that year`
- Bishop Stopford (137086) 2023/24: Attainment 8 64.1, Progress 8 1.02, English and maths 91.7%; information 217 pupils, KS2 average 108.1, SEN 6.5%, "Well above average", gaps 4.6 / 0.14
- school 147411 2023/24: disadvantaged 34%, EAL 56%, SEN support 20%
- per-year coverage identical to DfE's files; LA mart 150–152 LAs a year; Kensington and Chelsea 54.5, Wandsworth 51.8, Westminster 53.2
- CI Python suite 363 passed; dbt unit and data tests seen failing on fixtures that reproduce each bug before passing
## After merge
1. Run `school_data_annual_ees` on staging. Expect `/api/schools/137086` 2023/24 Attainment 8 64.1 / Progress 8 1.02, and `/api/schools/147411` 2023/24 disadvantaged 34.
2. Then merge PR 2 (site), so its new E2E journeys run against loaded data. Promote PR 2 only after the EES DAG has also run on production.
3. **Optional cleanup, once per environment:** `delete from raw.ees_ks4_performance where time_period = '202324' and pupil_count is null;` — removes the 34,254 old-label sub-group rows the re-issued file left behind (new-format rows always have `pupil_count`). Nothing reads them.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
A '2025-26-revised' release read as an unknown year went last, behind the
'2025-26' release, which then owned the year and dropped every revised row.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
This PR fixes the empty 2023/24 KS4 and KS2 data in three ways. For KS4 results, the newest release now owns each year. It maps DfE's older 2023/24 KS4 information column names. It teaches safe_numeric to read 34%. It also adds an ees_ks4_la stream, a stg_ees_ks4_la model and a fact_ks4_la_averages mart, plus data tests. The change is well tested and scoped, and I found nothing that would break production or corrupt data. The notes below are minor.
🟡 Minor
pipeline/transform/tests/assert_ks4_la_averages_cover_las.sql: The test fails when the mart is empty, and the mart reads raw.ees_ks4_la, which only exists after the first EES DAG run loads it. Any dbt invocation that builds all models or marts before that run (CI, a staging deploy, another DAG) will error on the missing relation or fail this test. Check that no other build path selects the new models before the EES DAG has run on staging.
pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py: slug_to_time_period now accepts suffixed slugs for every EES stream, not just the opted-in KS4 results stream. Previously those releases got time_period=None. If get_all_releases output is used for anything besides ordering, KS2 and census behaviour changes, and no test covers that. Confirm nothing else imported the removed _slug_to_time_period or depends on None for suffixed slugs.
pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/release_precedence.py: When two releases share a year (a revised release and the first release), the first one processed owns the whole year. Any rows the second release has for that year, including sub-groups missing from the first, are dropped. This is consistent with the intended design, but it is a data-completeness trade-off worth noting.
pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py: ees_ks4_national and ees_ks4_la each download the same CSV, so one EES run fetches it twice. Caching the download for the run would avoid the duplicate request.
## 🤖 AI Code Review (Claude Code)
This PR fixes the empty 2023/24 KS4 and KS2 data in three ways. For KS4 results, the newest release now owns each year. It maps DfE's older 2023/24 KS4 information column names. It teaches `safe_numeric` to read `34%`. It also adds an `ees_ks4_la` stream, a `stg_ees_ks4_la` model and a `fact_ks4_la_averages` mart, plus data tests. The change is well tested and scoped, and I found nothing that would break production or corrupt data. The notes below are minor.
### 🟡 Minor
- **pipeline/transform/tests/assert_ks4_la_averages_cover_las.sql**: The test fails when the mart is empty, and the mart reads `raw.ees_ks4_la`, which only exists after the first EES DAG run loads it. Any dbt invocation that builds all models or `marts` before that run (CI, a staging deploy, another DAG) will error on the missing relation or fail this test. Check that no other build path selects the new models before the EES DAG has run on staging.
- **pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py**: `slug_to_time_period` now accepts suffixed slugs for every EES stream, not just the opted-in KS4 results stream. Previously those releases got `time_period=None`. If `get_all_releases` output is used for anything besides ordering, KS2 and census behaviour changes, and no test covers that. Confirm nothing else imported the removed `_slug_to_time_period` or depends on `None` for suffixed slugs.
- **pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/release_precedence.py**: When two releases share a year (a revised release and the first release), the first one processed owns the whole year. Any rows the second release has for that year, including sub-groups missing from the first, are dropped. This is consistent with the intended design, but it is a data-completeness trade-off worth noting.
- **pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py**: `ees_ks4_national` and `ees_ks4_la` each download the same CSV, so one EES run fetches it twice. Caching the download for the run would avoid the duplicate request.
tudor
merged commit 3bd1dbd276 into main2026-10-06 12:42:19 +00:00
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Part 1 of 2 for audit findings C2 (2023/24 results missing) and H2 ("vs LA avg" uses the wrong average). Spec:
docs/superpowers/specs/2026-10-06-ks4-2023-24-and-la-averages-design.md; plan:docs/superpowers/plans/2026-10-06-ks4-2023-24-and-la-averages.md.What changes
C2 had three causes.
release_precedence.py, opt-in for this stream only — a general rule would wipe KS2, whose 2024/25 file holds 10% of 2023/24's rows). Release slugs with a suffix (2025-26-revised) now read as their year.ks4_info.pymaps the 11 of them.34%;safe_numericnow accepts a trailing%.H2. A new stream
ees_ks4_lakeeps the LA rows of the DfE data set the England stream already reads (they match DfE's published LA averages exactly, all 152 LAs). New martmarts.fact_ks4_la_averages, keyed by GIAS LA code. The API still computes its own average until PR 2.Data tests.
assert_ks4_years_have_results(≥50% of a year's rows have Attainment 8; DfE's files reach 82%),assert_ks2_info_percentages_loaded(≥90% of rows with pupil counts have a disadvantaged %; DfE 96–97%),assert_ks4_la_averages_cover_las(≥145 LAs in the latest year), and a warning,assert_ks4_la_averages_current, if the LA averages fall behind the school results (the summary data set's id is fixed and will go stale when DfE publishes 2025/26).Evidence
A local end-to-end run of the changed tap against DfE through meltanolabs target-postgres, then dbt (17/17 tests pass):
Skipping 57090 rows for 202324 from release b76a938a…: a newer release supplied that yearAfter merge
school_data_annual_eeson staging. Expect/api/schools/1370862023/24 Attainment 8 64.1 / Progress 8 1.02, and/api/schools/1474112023/24 disadvantaged 34.delete from raw.ees_ks4_performance where time_period = '202324' and pupil_count is null;— removes the 34,254 old-label sub-group rows the re-issued file left behind (new-format rows always havepupil_count). Nothing reads them.🤖 Generated with Claude Code
🤖 AI Code Review (Claude Code)
This PR fixes the empty 2023/24 KS4 and KS2 data in three ways. For KS4 results, the newest release now owns each year. It maps DfE's older 2023/24 KS4 information column names. It teaches
safe_numericto read34%. It also adds anees_ks4_lastream, astg_ees_ks4_lamodel and afact_ks4_la_averagesmart, plus data tests. The change is well tested and scoped, and I found nothing that would break production or corrupt data. The notes below are minor.🟡 Minor
raw.ees_ks4_la, which only exists after the first EES DAG run loads it. Any dbt invocation that builds all models ormartsbefore that run (CI, a staging deploy, another DAG) will error on the missing relation or fail this test. Check that no other build path selects the new models before the EES DAG has run on staging.slug_to_time_periodnow accepts suffixed slugs for every EES stream, not just the opted-in KS4 results stream. Previously those releases gottime_period=None. Ifget_all_releasesoutput is used for anything besides ordering, KS2 and census behaviour changes, and no test covers that. Confirm nothing else imported the removed_slug_to_time_periodor depends onNonefor suffixed slugs.ees_ks4_nationalandees_ks4_laeach download the same CSV, so one EES run fetches it twice. Caching the download for the run would avoid the duplicate request.