Moves six GIAS classification fields (TypeOfEstablishment, EstablishmentStatus, PhaseOfEducation, OfficialSixthForm, ReligiousCharacter, AdmissionsPolicy) from repeated name strings to official DfE integer codes in the marts, with code→name dictionaries in application code.
Dictionaries generated from the live GIAS bulk CSV by pipeline/scripts/generate_gias_codes.py (never hand-typed): canonical backend/gias_codes.py, byte-identical pipeline/scripts/gias_codes.py (CI parity test), dbt seed gias_code_names.
Tap ingests the (code) columns (names stay in raw); staging exposes int _code columns; dim_school/dim_location store codes only (status filter in (1, 3), phase cascade emits GIAS codes, sixth-form flag from code).
API contract unchanged: translate_gias_code_columns() runs right after pd.read_sql, so every payload/filter serves today's exact name strings. Typesense sync translates before indexing. No frontend or e2e changes.
Drift loop: a warn-severity dbt test compares live raw (code, name) pairs against the seed; the daily DAG selector includes gias_code_names+ so the seed + test run nightly. On a warning: rerun the generator, commit the regenerated files.
Deploy — no outage window: if the marts predate the migration, the backend detects the undefined _code column and falls back to a legacy name-column query (_MAIN_QUERY_LEGACY_NAMES), so the API works against both schemas. Codes go live on the next nightly school_data_daily run; trigger it manually if you want them sooner.
Tests: 21/21 backend pytest (11 new across dictionaries + translation + schema fallback), dbt schema tests updated (accepted_values per code column from the seed), full final review clean.
Moves six GIAS classification fields (TypeOfEstablishment, EstablishmentStatus, PhaseOfEducation, OfficialSixthForm, ReligiousCharacter, AdmissionsPolicy) from repeated name strings to official DfE integer codes in the marts, with code→name dictionaries in application code.
Spec: `docs/superpowers/specs/2026-07-09-gias-code-dictionaries-design.md` · Plan: `docs/superpowers/plans/2026-07-09-gias-code-dictionaries.md`
**How it works**
- Dictionaries generated from the live GIAS bulk CSV by `pipeline/scripts/generate_gias_codes.py` (never hand-typed): canonical `backend/gias_codes.py`, byte-identical `pipeline/scripts/gias_codes.py` (CI parity test), dbt seed `gias_code_names`.
- Tap ingests the `(code)` columns (names stay in raw); staging exposes int `_code` columns; `dim_school`/`dim_location` store codes only (status filter `in (1, 3)`, phase cascade emits GIAS codes, sixth-form flag from code).
- **API contract unchanged**: `translate_gias_code_columns()` runs right after `pd.read_sql`, so every payload/filter serves today's exact name strings. Typesense sync translates before indexing. No frontend or e2e changes.
- Drift loop: a warn-severity dbt test compares live raw (code, name) pairs against the seed; the daily DAG selector includes `gias_code_names+` so the seed + test run nightly. On a warning: rerun the generator, commit the regenerated files.
**Deploy — no outage window**: if the marts predate the migration, the backend detects the undefined `_code` column and falls back to a legacy name-column query (`_MAIN_QUERY_LEGACY_NAMES`), so the API works against both schemas. Codes go live on the next nightly `school_data_daily` run; trigger it manually if you want them sooner.
**Tests**: 21/21 backend pytest (11 new across dictionaries + translation + schema fallback), dbt schema tests updated (accepted_values per code column from the seed), full final review clean.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
This PR moves six GIAS classification fields (school_type, status, phase, religious_character, admissions_policy) from strings to DfE integer codes throughout the pipeline (tap -> staging -> marts), while translating back to name strings at the API boundary (backend/data_loader.py) and in the Typesense sync script, keeping the external API/UI contract unchanged. The implementation is thorough and internally consistent — dictionaries, seed CSV, dbt accepted_values tests, and SQL derivations all line up, and there's good test coverage (dictionary parity, drift detection, translation behavior) — but the rollout strategy has a real production-availability gap.
🔴 Severe (blocks merge)
docs/superpowers/specs/2026-07-09-gias-code-dictionaries-design.md: The new backend query (_MAIN_QUERY in backend/data_loader.py) selects *_code columns that only exist in dim_school/dim_location after the dbt pipeline rebuilds the marts. Deploying the backend image is fully decoupled from that pipeline run, so as soon as the new backend code goes live against the still-old-schema marts, every query hitting dim_school/dim_location will fail with an undefined-column error, breaking search/filtering site-wide. The only mitigation is a manual runbook step ('merge -> deploy -> trigger school_data_daily immediately') with no automated gate, feature flag, or backward-compatible fallback query — if the DAG trigger is delayed, forgotten, or the run fails, production stays broken until someone notices and reruns it.
🟡 Minor
backend/gias_codes.py: translate() calls logger.warning() once per row (via df[col].map(...)) for every occurrence of an unrecognized code, rather than once per unique unknown code. On a full ~25k-school reload where GIAS introduces a new code, this floods the logs with thousands of duplicate warnings instead of a handful of distinct ones.
## 🤖 AI Code Review (Claude Code)
This PR moves six GIAS classification fields (school_type, status, phase, religious_character, admissions_policy) from strings to DfE integer codes throughout the pipeline (tap -> staging -> marts), while translating back to name strings at the API boundary (backend/data_loader.py) and in the Typesense sync script, keeping the external API/UI contract unchanged. The implementation is thorough and internally consistent — dictionaries, seed CSV, dbt accepted_values tests, and SQL derivations all line up, and there's good test coverage (dictionary parity, drift detection, translation behavior) — but the rollout strategy has a real production-availability gap.
### 🔴 Severe (blocks merge)
- **docs/superpowers/specs/2026-07-09-gias-code-dictionaries-design.md**: The new backend query (_MAIN_QUERY in backend/data_loader.py) selects *_code columns that only exist in dim_school/dim_location after the dbt pipeline rebuilds the marts. Deploying the backend image is fully decoupled from that pipeline run, so as soon as the new backend code goes live against the still-old-schema marts, every query hitting dim_school/dim_location will fail with an undefined-column error, breaking search/filtering site-wide. The only mitigation is a manual runbook step ('merge -> deploy -> trigger school_data_daily immediately') with no automated gate, feature flag, or backward-compatible fallback query — if the DAG trigger is delayed, forgotten, or the run fails, production stays broken until someone notices and reruns it.
### 🟡 Minor
- **backend/gias_codes.py**: translate() calls logger.warning() once per row (via df[col].map(...)) for every occurrence of an unrecognized code, rather than once per unique unknown code. On a full ~25k-school reload where GIAS introduces a new code, this floods the logs with thousands of duplicate warnings instead of a handful of distinct ones.
tudor
merged commit 84dfc6c1bb into main2026-07-09 13:32:44 +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.
Moves six GIAS classification fields (TypeOfEstablishment, EstablishmentStatus, PhaseOfEducation, OfficialSixthForm, ReligiousCharacter, AdmissionsPolicy) from repeated name strings to official DfE integer codes in the marts, with code→name dictionaries in application code.
Spec:
docs/superpowers/specs/2026-07-09-gias-code-dictionaries-design.md· Plan:docs/superpowers/plans/2026-07-09-gias-code-dictionaries.mdHow it works
pipeline/scripts/generate_gias_codes.py(never hand-typed): canonicalbackend/gias_codes.py, byte-identicalpipeline/scripts/gias_codes.py(CI parity test), dbt seedgias_code_names.(code)columns (names stay in raw); staging exposes int_codecolumns;dim_school/dim_locationstore codes only (status filterin (1, 3), phase cascade emits GIAS codes, sixth-form flag from code).translate_gias_code_columns()runs right afterpd.read_sql, so every payload/filter serves today's exact name strings. Typesense sync translates before indexing. No frontend or e2e changes.gias_code_names+so the seed + test run nightly. On a warning: rerun the generator, commit the regenerated files.Deploy — no outage window: if the marts predate the migration, the backend detects the undefined
_codecolumn and falls back to a legacy name-column query (_MAIN_QUERY_LEGACY_NAMES), so the API works against both schemas. Codes go live on the next nightlyschool_data_dailyrun; trigger it manually if you want them sooner.Tests: 21/21 backend pytest (11 new across dictionaries + translation + schema fallback), dbt schema tests updated (accepted_values per code column from the seed), full final review clean.
🤖 Generated with Claude Code
🤖 AI Code Review (Claude Code)
This PR moves six GIAS classification fields (school_type, status, phase, religious_character, admissions_policy) from strings to DfE integer codes throughout the pipeline (tap -> staging -> marts), while translating back to name strings at the API boundary (backend/data_loader.py) and in the Typesense sync script, keeping the external API/UI contract unchanged. The implementation is thorough and internally consistent — dictionaries, seed CSV, dbt accepted_values tests, and SQL derivations all line up, and there's good test coverage (dictionary parity, drift detection, translation behavior) — but the rollout strategy has a real production-availability gap.
🔴 Severe (blocks merge)
🟡 Minor