The staging model aliased EES's total_number_places_offered column as
published_admission_number, but PAN is the school's published capacity
(not exposed by EES at school level) — what we actually have is the
count of places offered in a given admissions round. The misnomer
propagated to the mart, SQLAlchemy model, API response, TS types, and
UI copy ("places per year", "(PAN)").
Rename end-to-end and fix the UI labels:
- "29 places for 42 first-choice applications"
→ "29 places offered for 42 first-choice applications"
- "Reception/Year 7 places per year"
→ "Reception/Year 7 places offered"
- drop the misleading "(PAN)" suffix in the secondary view
Also add a comment in stg_ees_admissions clarifying this is the number
of places offered, not PAN. Requires dbt to rebuild fact_admissions
(marts are materialized as tables) before the backend can start.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
dim_school.sql was checking for int_ofsted_latest in target.schema (wrong schema)
due to the custom generate_schema_name macro using literal schema names. The
model lives in 'intermediate', so ofsted_grade/date/framework were always NULL
in dim_school, causing all list cards to show 'Not yet inspected'.
Fix 1: data_loader.py joins marts.fact_ofsted_inspection with DISTINCT ON to
get latest inspection per school — no pipeline re-run needed.
Fix 2: dim_school.sql uses schema='intermediate' so future dbt runs correctly
denormalise the Ofsted summary into dim_school.
Replaces computed means from our school dataset with the published DfE
national headline figures for the KS2 chart reference line.
- tap-uk-ees: new EESKs2NationalStream fetches the stable EES data-catalogue
CSV (one row per year, England national total, AllSchools filter)
- dbt staging: stg_ees_ks2_national normalises columns, casts to float,
filters to years >= 201617
- dbt mart: fact_ks2_national_averages — one row per year, official figures
- backend/models: Ks2NationalAverage SQLAlchemy model
- backend/app: /api/national-averages queries the mart for KS2 by_year;
secondary by_year stays computed (no DfE KS4 national dataset yet)
- DAG: extract_ks2_national task added to school_data_annual_ees,
runs in parallel with the main EES extract
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
The staging model was mapping EES column ``proportion_1stprefs_v_totaloffers``
straight onto ``first_preference_offer_pct``. That raw column is not a
percentage — it is a ratio of first-preference applications to total offers
(an oversubscription indicator, >1 means oversubscribed), so OLQH rendered
as "1%" when the true first-choice success rate is 27/42 = 64%.
The frontend display code is not at fault and is not patched here —
data-quality issues must be fixed at the source.
- stg_ees_admissions: compute ``first_preference_offer_pct`` as
``100 * number_1st_preference_offers / times_put_as_1st_preference`` —
of families who listed this school first, the % that received an offer
(0–100). Guard against divide-by-zero.
- stg_ees_admissions: expose the legitimate EES ratio as the new column
``oversubscription_ratio`` (1st-preference applications per place) for
future use, clearly named.
- fact_admissions, FactAdmissions model, data_loader: propagate the new
``oversubscription_ratio`` column.
- SchoolAdmissions type: document both columns inline.
- buildSchoolSummary: reword the oversubscription clause so it reads
sensibly across the whole 0–100 range (no more "just 64%").
- Hero chip subtitle: clearer phrasing "X% of first-choice applicants
offered a place".
Requires a dbt run of stg_ees_admissions and fact_admissions on deploy
so the new column materialises.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
The new phase inference can legitimately leave ~1100 independent schools with
null phase (no GIAS phase, no statutory ages, name gives no hint). That's a
known data quality gap, not a pipeline failure — the UI already handles null
by showing no pill. Downgrade the test to warn so it stays visible in dbt
output without blocking the DAG.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
The case-insensitive "Not Applicable" fix caught schools where GIAS publishes
statutory ages, but some independent schools leave those blank too — they fall
through every branch and end up with null phase and no pill in the UI.
Add a third tier that infers phase from the school name
(Primary/Infant/Junior/Prep vs Secondary/High/Grammar/Senior/Upper) and also
normalise "Not Applicable" handling with trim() + "unknown"/"" exclusion, so
the final else branch can safely return null instead of the catch-all string.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
GIAS provides 'Not Applicable' (capital A) but the check used 'Not applicable',
so the case-sensitive != matched true and skipped the age-range inference.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Independent schools have phase='Not applicable' in GIAS. Now infer
phase from statutory age range: <=11 → Primary, >=11 → Secondary,
spans both → All-through. Falls back to original value if no age data.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Root cause: the UNION ALL query in data_loader.py produced two rows per
all-through school per year (one KS2, one KS4), with drop_duplicates()
silently discarding the KS4 row. Fixes:
- New dbt mart `fact_performance`: FULL OUTER JOIN of fact_ks2_performance
and fact_ks4_performance on (urn, year). One row per school per year.
All-through schools have both KS2 and KS4 columns populated.
- data_loader.py: replace 175-line UNION ALL with a simple JOIN to
fact_performance. No more duplicate rows or drop_duplicates needed.
- sync_typesense.py: single LATERAL JOIN to fact_performance instead of
two separate KS2/KS4 joins.
- app.py: remove drop_duplicates (no longer needed); add PHASE_GROUPS
constant so all-through/middle schools appear in primary and secondary
filter results (were previously invisible to both); scope result_filters
gender/admissions_policies to secondary schools only.
- HomeView.tsx: isSecondaryView is now majority-based (not "any secondary")
and isMixedView shows both sort option sets for mixed result sets.
- school/[slug]/page.tsx: all-through schools route to SchoolDetailView
(renders both SATs + GCSE sections) instead of SecondarySchoolDetailView
(KS4-only). Dedicated SEO metadata for all-through schools.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
tap-uk-ees: EESCensusStream now declares 27 data columns (FSM %, EAL %,
ethnicity breakdowns, pupil counts) with clean Singer field names mapped
from the verbose CSV column names (e.g. '% of pupils known to be eligible
for free school meals' → fsm_pct) via a new _column_renames mechanism on
the base stream class.
stg_ees_census: materialised as table, applies safe_numeric to all
percentage/count columns, filters to numeric URNs.
int_pupil_chars_merged + fact_pupil_characteristics: pass all columns
through from staging (previously stubs with only 3 columns).
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Pipeline:
- EES tap: split KS4 into performance + info streams, fix admissions filename
(SchoolLevel keyword match), fix census filename (yearly suffix), remove
phonics (no school-level data on EES), change endswith → in for matching
- stg_ees_ks4: rewrite to filter long-format data and extract Attainment 8,
Progress 8, EBacc, English/Maths metrics; join KS4 info for context
- stg_ees_admissions: map real CSV columns (total_number_places_offered, etc.)
- stg_ees_census: update source reference, stub with TODO for data columns
- Remove stg_ees_phonics, fact_phonics (no school-level EES data)
- Add ees_ks4_performance + ees_ks4_info sources, remove ees_ks4 + ees_phonics
- Update int_ks4_with_lineage + fact_ks4_performance with new KS4 columns
- Annual EES DAG: remove stg_ees_phonics+ from selector
Backend:
- models.py: replace all models to point at marts.* tables with schema='marts'
(DimSchool, DimLocation, KS2Performance, FactOfstedInspection, etc.)
- data_loader.py: rewrite load_school_data_as_dataframe() using raw SQL joining
dim_school + dim_location + fact_ks2_performance; update get_supplementary_data()
- database.py: remove migration machinery, keep only connection setup
- app.py: remove check_and_migrate_if_needed, remove /api/admin/reimport-ks2
endpoints (pipeline handles all imports)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Fix publication slugs (KS4, Phonics, Admissions were wrong)
- Split KS2 into two streams: ees_ks2_attainment (long format) and
ees_ks2_info (wide format context data)
- Target specific filenames instead of keyword matching
- Handle school_urn vs urn column naming
- Pivot KS2 attainment from long to wide format in dbt staging
- Add all ~40 KS2 columns the backend needs (GPS, absence, gender,
disadvantaged breakdowns, context demographics)
- Pass through all columns in int_ks2_with_lineage and fact_ks2
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
- Remove optional flag from total_pupils (Typesense requires default
sorting field to be non-optional)
- Add latitude/longitude columns to dim_location computed from PostGIS
geom, for direct use by backend and Typesense sync
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
PostGIS extension lives in public schema; marts schema can't resolve
unqualified ST_MakePoint/ST_Transform calls.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
GIAS grid references are the actual school location — far more accurate
than postcode centroids. Remove geocode_postcodes.py from the daily DAG
and the postcode-not-null filter from dim_location.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Convert GIAS British National Grid coordinates (EPSG:27700) to WGS84
(EPSG:4326) directly in the dbt model. The geocode script backfills
schools missing easting/northing via Postcodes.io.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Lineage map includes predecessor URNs for closed schools, which are
correctly excluded from dim_school (status = 'Open').
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
- Declare all 34 columns needed by dbt in GIAS tap schema (target-postgres
only persists columns present in the Singer schema message)
- Use nullif() for empty-string-to-integer/date casts in staging models
- Scope daily DAG dbt build to GIAS models only (stg_gias_establishments+
stg_gias_links+) to avoid errors on unloaded sources
- Scope annual EES DAG similarly; remove redundant dbt test steps
- Make dim_school gracefully handle missing int_ofsted_latest table
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Port extraction logic from integrator scripts into Singer SDK taps:
- tap-uk-parent-view: scrapes Ofsted open data portal, parses survey responses (14 questions)
- tap-uk-fbit: queries FBIT API per-URN with rate limiting, computes per-pupil spend
- tap-uk-idaci: downloads IoD2019 XLSX, batch-resolves postcodes→LSOAs via postcodes.io
Update dbt models to match actual tap output schemas:
- stg_idaci now includes URN (tap does the postcode→LSOA→school join)
- stg_parent_view expanded from 8 to 13 question columns
- fact_deprivation simplified (no longer needs postcode→LSOA join in dbt)
- fact_parent_view expanded to include all 13 question metrics
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Tudor Sitaru
tudor