Merge pull request 'feat(pipeline): one current Ofsted status per school (C1/M1, part 1 of 2)' (#183) from fix/ofsted-current-status-pipeline into main

Reviewed-on: #183

docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md

@@ -0,0 +1,257 @@
+# Ofsted Current Status — Design
+
+**Date:** 2026-10-05
+**Status:** approved design, not yet implemented
+**Scope:** `pipeline/transform` Ofsted models, `backend/data_loader.py`, list and
+detail API Ofsted fields, search and map badges, school-page Ofsted section,
+compare Ofsted rows, Typesense rating, sitemap
+**Fixes:** audit findings C1, M1 and (as a side effect) M2 and part of H3,
+from the 3 Oct 2026 accuracy audit
+
+## Goal
+
+Never show an Ofsted grade under a date it was not awarded or confirmed on, and
+always date "Inspected" by the school's latest visit.
+
+## The problem
+
+Ofsted's management information gives each school at most three inspections:
+the latest graded inspection (date G, an overall grade or "Not judged", area
+grades), the latest ungraded inspection (date U, an outcome sentence) and the
+latest report card (date RC).
+
+The site derives a grade and a date from these with two independent rules:
+
+- grade = the graded inspection's overall grade, or else the grade parsed from
+  the ungraded outcome ("School remains Good" → 2);
+- date = G, or else U.
+
+The two rules can pick different inspections. Every inspection from
+September 2024 to November 2025 was graded with "Not judged" overall, so the
+grade falls back to an older ungraded visit while the date stays the new one.
+
+- **C1.** Rabbsfarm Primary School (102408) shows "Good · 2025". Ofsted's
+  17 June 2025 inspection gave no overall grade and rated quality of education,
+  behaviour and leadership Requires Improvement. The "Good" comes from an
+  ungraded visit on 6 February 2020. Site-wide, 932 badges pair a
+  carried-forward grade with a newer inspection's year, and 147 of them say Good
+  or Outstanding while that inspection rated an area Requires Improvement or
+  Inadequate. Acre Wood Academy (151783) reads "Good · 2024" though the
+  October 2024 inspection rated all four areas Inadequate.
+- **M1.** When a newer ungraded visit exists, the page shows the older graded
+  date. Washwood Heath Academy (139888) reads "Inspected 3 Mar 2020 · 4+ years
+  ago"; Ofsted visited on 21 May 2025. 667 schools.
+
+Ofsted's own provider page for Rabbsfarm leads with the 2025 area judgements and
+"From September 2024, Ofsted no longer makes an overall effectiveness
+judgement". It shows no overall grade.
+
+The rule is also implemented four times: `dim_school.ofsted_grade` (feeds
+Typesense), the list SQL in `data_loader.py`, `_ofsted_block`, and two separate
+"latest row" picks over `marts.fact_ofsted_inspection` by `inspection_date`,
+which tie arbitrarily on duplicate monthly rows.
+
+## Non-goals
+
+- Predecessor inspections (audit M10): a grade Ofsted attributes to a previous
+  URN stays unlabelled.
+- Post-16 and ISI-inspected schools (H4) and the "Not yet inspected" label.
+- The compare page's broken Ofsted link (M4).
+- Report-card display, which is unchanged.
+
+## The rule
+
+Computed once per URN in `int_ofsted_latest`.
+
+**Latest visit:** the newest of RC, G and U.
+
+- `latest_visit_date`
+- `latest_visit_kind`: `report_card`, `graded` or `ungraded`
+- `latest_visit_outcome`: the ungraded outcome text when the kind is `ungraded`,
+  otherwise null
+
+**Current grade:** the overall grade still in force, if any.
+
+| Situation | `current_grade` | `current_grade_date` | `current_grade_basis` |
+|---|---|---|---|
+| A report card exists | null | null | null |
+| Latest is graded, overall 1–4 | that grade | G | `graded` |
+| Latest is graded, "Not judged" | null | null | null |
+| Latest is ungraded, outcome "School remains X…" (any qualifier) | X | U | `confirmed` |
+| Latest is ungraded, any other outcome ("Standards maintained", "Improved significantly", "Some aspects not as strong") | the graded inspection's overall grade if it is 1–4, else null | G when a grade is kept | `graded` when a grade is kept |
+| No inspection | null | null | null |
+
+A report card replaced overall grades, so no legacy grade stays in force beside
+one. The latest visit is read from the dates, not assumed: report cards began in
+November 2025, after the last legacy graded and ungraded inspections, and in
+Ofsted's 31 Aug 2026 data no school has a legacy visit newer than its report
+card, but the rule does not depend on that. Ties between G, U and RC on the same
+date resolve in the order report card, graded, ungraded.
+
+Invariants: `current_grade` is null or 1–4; `current_grade_date <=
+latest_visit_date`; `current_grade_basis` is null exactly when `current_grade`
+is null.
+
+### Expected results (Ofsted MI as at 31 Aug 2026)
+
+| URN | School | Ofsted data | Latest visit | Current grade |
+|---|---|---|---|---|
+| 102408 | Rabbsfarm Primary School | G 17 Jun 2025 Not judged; U 6 Feb 2020 remains Good | graded, 17 Jun 2025 | none |
+| 151783 | Acre Wood Academy | G 1 Oct 2024 Not judged; U 14 Mar 2023 remains Good (Concerns) | graded, 1 Oct 2024 | none |
+| 139888 | Washwood Heath Academy | G 3 Mar 2020 Good; U 21 May 2025 Standards maintained | ungraded, 21 May 2025, "Standards maintained" | Good, 3 Mar 2020, graded |
+| 104762 | Robins Lane Community Primary | G 7 Jan 2020 Good; U 18 Jul 2024 School remains Good | ungraded, 18 Jul 2024 | Good, 18 Jul 2024, confirmed |
+| 100094 | Royal Free Hospital Children's School | G 9 Oct 2019 Outstanding; U 5 Feb 2025 Some aspects not as strong | ungraded, 5 Feb 2025 | Outstanding, 9 Oct 2019, graded |
+| 136454 | Oakgrove School | U 13 Nov 2024 Standards maintained only | ungraded, 13 Nov 2024 | none |
+| 137086 | Bishop Stopford School | G 1 Apr 2025 Not judged | graded, 1 Apr 2025 | none |
+| 110048 | The Willink School | U 5 Oct 2023 remains Good; RC 6 May 2026 | report card, 6 May 2026 | none (report card shown) |
+| 149612 | St Michael's Catholic School | RC 10 Feb 2026 only | report card, 10 Feb 2026 | none (report card shown) |
+
+## What each page shows
+
+**Search and map badge** (`buildOfstedListBadge`), first match wins:
+
+1. Report card: "Report Card · *RC year*" (unchanged)
+2. Current grade: "*Grade* · *year of `current_grade_date`*"
+3. Latest visit: "Inspected · *year of `latest_visit_date`*"
+4. "Not yet inspected" (unchanged)
+
+**School page** (`OfstedSection`, both phases):
+
+- Title date: "Inspected *latest visit date*".
+- Headline: the report card; or the current grade with a source line
+  ("Graded inspection, 6 July 2016" or "Confirmed at an ungraded inspection,
+  14 March 2023"); or "No overall grade" with "Ofsted stopped giving overall
+  grades in September 2024".
+- "Latest visit" line when the latest visit is not the grade's source, e.g.
+  "Ungraded inspection, 13 Nov 2024: Standards maintained".
+- The area grid shows the graded inspection's judgements through
+  `ofstedLegacyAreas()`, dated by that inspection when it is not the latest
+  visit. The primary and secondary no-grade branches merge into one; the
+  secondary branch's four hard-coded areas (audit M2) go with it.
+
+**Compare:** `ofstedDisplay` returns `report_card`, `graded`, `confirmed`,
+`no_overall_grade` or `none`. The "Latest Ofsted inspection", "Result" and
+"Inspected" rows use the same fields as the school page.
+
+## Delivery
+
+Two pull requests. The mart columns exist before anything reads them, so
+neither needs compatibility code.
+
+### PR 1: pipeline (additive)
+
+- `stg_ofsted_inspections`: keep `graded_inspection_date`,
+  `ungraded_inspection_date` and `rc_inspection_date` as separate typed
+  columns, with the report-card date's existing guard. Keep `inspection_date`
+  (graded, else ungraded) for the current backend. Keep a row when any of the
+  three dates is present, so report-card-only schools are no longer dropped
+  (part of H3: 123 schools).
+- `int_ofsted_latest`: pick one row per URN by `latest_visit_date` descending,
+  then `rc_inspection_date`, `ungraded_inspection_date` and
+  `graded_inspection_date` descending (nulls last). A duplicate monthly row that
+  carries a newer report card therefore always wins. Add the five status
+  columns.
+- New mart `marts.fact_ofsted_latest`: one row per URN from `int_ofsted_latest`
+  with every column the pages need (status, area grades, report-card grades,
+  ungraded outcome, report URL). It does not join `dim_school`, so only the
+  monthly Ofsted DAG builds it.
+- `dim_school` is not changed in PR 1: the daily DAG does not rebuild
+  `int_ofsted_latest`, and reading a column that the monthly DAG has not yet
+  built would fail the daily run.
+- Visible effect: report-card-only schools gain their report card, because the
+  backend's existing reads of `fact_ofsted_inspection` now see their rows.
+  Nothing else changes.
+
+### PR 2: backend and UI (after the Ofsted DAG has run on PR 1)
+
+- `data_loader.py`: the list query and the batch query read
+  `marts.fact_ofsted_latest` instead of picking the latest row of
+  `fact_ofsted_inspection`. `_ofsted_block` reads the status columns and loses
+  its fallback to `ungraded_grade`.
+- List rows: `ofsted_grade` becomes `current_grade`; `ofsted_date` becomes
+  `latest_visit_date`; new `ofsted_grade_date`. `ofsted_rc_date` stays.
+- `ofsted` block: `overall_effectiveness` and `inspection_date` are the graded
+  inspection's own result and date (they label the area grid); new
+  `current_grade` `{grade, date, basis}` (or null) and `latest_visit`
+  `{date, kind, outcome}`; `grade_source` is removed. Report-card fields are
+  unchanged.
+- `dim_school.ofsted_grade` becomes `current_grade` (Typesense's rating follows
+  at the next sync); `ofsted_date` becomes `latest_visit_date`.
+- Sitemap: `lastmod` from `latest_visit_date`; `_PUBLISHABLE_FIELDS` also counts
+  a latest visit, so schools that lose a carried grade keep their sitemap entry.
+- Front end: `lib/types.ts`, `buildOfstedListBadge`, `OfstedSection`,
+  `PrimarySchoolSections`, `SecondarySchoolSections`, `compareLogic.ofstedDisplay`,
+  `CompareAtAGlance`, `CompareOfsted`. Place-page counts need no change.
+- Delete `buildOfstedHeroChip` and `buildSchoolSummary` in their own commit:
+  nothing renders them and they encode the old rule.
+
+## Testing
+
+**PR 1**
+
+- dbt unit tests on `int_ofsted_latest`, one per table row above plus
+  "report-card only" and "duplicate rows, newer report card wins".
+- Schema tests on `fact_ofsted_latest`: unique, not-null `urn`; accepted values
+  for `latest_visit_kind` and `current_grade_basis`; `current_grade` null or
+  1–4; `current_grade_date <= latest_visit_date`.
+- Run locally against a throwaway Postgres from `pgserver` (no Docker here). If
+  that fails, they still run in the Ofsted DAG's `dbt build`, which fails on any
+  broken case.
+- `pipeline/tests/test_dag_selectors.py` (PR #181) keeps passing.
+
+**PR 2**
+
+- pytest: contract test for the list and `ofsted` fields (style of
+  `test_school_page_flag_fields.py`); `_ofsted_block` from a
+  `fact_ofsted_latest` row; sitemap publishability.
+- Jest: a badge case per table row; `OfstedSection` for graded, confirmed, no
+  grade, the latest-visit line and a sixth-form area; `ofstedDisplay` kinds.
+  Rewrite tests that assert `carried_forward`.
+- E2E (same PR): Rabbsfarm's search row says "Inspected · 2025" and its page
+  says "No overall grade" with quality of education Requires Improvement; a
+  confirmed school says "Confirmed at an ungraded inspection". The existing
+  report-card journey stays.
+
+## Rollout and verification
+
+1. Merge PR 1. On staging, run `school_data_monthly_ofsted`, then:
+
+   ```sql
+   -- one row per school
+   select count(*) = count(distinct urn) from marts.fact_ofsted_latest;
+   -- the examples above
+   select urn, latest_visit_date, latest_visit_kind, latest_visit_outcome,
+          current_grade, current_grade_date, current_grade_basis
+   from marts.fact_ofsted_latest
+   where urn in (102408, 151783, 139888, 104762, 100094, 136454, 137086, 110048, 149612);
+   -- C1: a grade in force although the latest inspection gave none (expect 0)
+   select count(*) from marts.fact_ofsted_latest
+   where current_grade is not null and latest_visit_kind = 'graded'
+     and overall_effectiveness is null;
+   -- invariant (expect 0)
+   select count(*) from marts.fact_ofsted_latest where current_grade_date > latest_visit_date;
+   ```
+
+   Check through the API that St Michael's Catholic School (149612) shows its
+   report card.
+2. Promote PR 1 to production; run the Ofsted DAG there; repeat the checks.
+3. Merge PR 2. Let the daily DAG run (or trigger it) so `dim_school` and
+   Typesense pick up the change; run the E2E journeys; re-run the audit's C1,
+   M1 and M2 checks against staging: expect 0.
+4. Promote PR 2; repeat the audit checks on production.
+
+## Expected visible change
+
+About 932 schools change from a grade badge dated by a no-grade inspection
+("Good · 2025") to "Inspected · 2025". Counts of Good and Outstanding schools
+on place pages fall by the same schools, and Typesense's rating changes for
+them. Dates beside a grade can move earlier (to the inspection that awarded
+it); "Inspected" dates move later (to the latest visit).
+
+## Risks
+
+- Ofsted changes its MI columns most months. Unknown grade text parses to null
+  (`safe_numeric`), which degrades to "Inspected · year", never to a wrong grade.
+- PR 2 depends on the Ofsted DAG having run on the target environment after PR
+  1. If PR 2 is promoted first, the backend reads a missing table: promote in
+  order.

nextjs-app/__tests__/components/NearbySchoolsSection.test.tsx

@@ -8,7 +8,6 @@
 import { render, screen } from '@testing-library/react';
 
 import {
-  nearbyNoun,
   NearbySchoolsSection,
   shouldRenderNearby,
 } from '@/components/school/NearbySchoolsSection';
@@ -43,13 +42,7 @@ function school(overrides: Partial<NearbySchool> = {}): NearbySchool {
 
 function renderSection(nearby: NearbySchool[]) {
   return render(
-    <NearbySchoolsSection
+    <NearbySchoolsSection urn={100001} thisMetricValue={72} nearby={nearby} />,
-      urn={100001}
-      schoolName="Meadowbrook Primary School"
-      phase="Primary"
-      thisMetricValue={72}
-      nearby={nearby}
-    />,
   );
 }
 
@@ -90,15 +83,10 @@ describe('what the section claims', () => {
   });
 
   it('shows no chips at all when nothing is shared, rather than inventing one', () => {
-    const { container } = render(
+    const { container } = renderSection([
-      <NearbySchoolsSection
+      school({ shared: [] }),
-        urn={100001}
+      school({ urn: 100003, shared: [] }),
-        schoolName="Meadowbrook Primary School"
+    ]);
-        phase="Primary"
-        thisMetricValue={72}
-        nearby={[school({ shared: [] }), school({ urn: 100003, shared: [] })]}
-      />,
-    );
     // The card still carries its distance, name, type and figure — just no
     // claim of likeness.
     expect(container.querySelectorAll('li ul').length).toBe(0);
@@ -106,37 +94,6 @@ describe('what the section claims', () => {
   });
 });
 
-describe('what the lede calls the set', () => {
-  it.each([
-    ['Primary', 'primary schools'],
-    ['Middle deemed primary', 'primary schools'],
-    ['Secondary', 'secondary schools'],
-    ['Middle deemed secondary', 'secondary schools'],
-    ['All-through', 'all-through schools'],
-    // GIAS phase 6. Its candidates span the whole secondary group, so no
-    // single noun fits and it takes the honest general one.
-    ['16 plus', 'schools and colleges'],
-    ['', 'schools'],
-    [null, 'schools'],
-  ])('calls a %s school\'s neighbours "%s"', (phase, expected) => {
-    expect(nearbyNoun(phase)).toBe(expected);
-  });
-
-  it('never calls a sixth form college\'s neighbours primary schools', () => {
-    render(
-      <NearbySchoolsSection
-        urn={100001}
-        schoolName="Barnet Sixth Form College"
-        phase="16 plus"
-        thisMetricValue={null}
-        nearby={[school(), school({ urn: 100003 })]}
-      />,
-    );
-    expect(screen.getByText(/Other schools and colleges near Barnet Sixth Form College/)).toBeInTheDocument();
-    expect(screen.queryByText(/primary schools/)).not.toBeInTheDocument();
-  });
-});
-
 describe('cards', () => {
   it('links each school to its canonical slug', () => {
     renderSection([school(), school({ urn: 100003, school_name: 'Oakfield Primary School' })]);

nextjs-app/components/school/NearbySchools.module.css

@@ -1,8 +1,7 @@
 .heading { font-family: var(--font-display); font-size: 1.4rem; letter-spacing: -0.4px; margin: 0; }
-.lede { margin: 0.5rem 0 1.25rem; color: var(--text-secondary); max-width: 64ch; }
 .caption { margin: 1rem 0 0; font-size: 0.72rem; color: var(--text-muted); }
 
-.top { display: flex; align-items: flex-start; justify-content: space-between; gap: 1rem; }
+.top { display: flex; align-items: center; justify-content: space-between; gap: 1rem; margin-bottom: 1.25rem; }
 .arrows { display: flex; gap: 0.5rem; flex: none; }
 .arrow { width: 44px; height: 44px; display: grid; place-items: center; cursor: pointer; border: 1px solid var(--border-strong); border-radius: 999px; background: var(--bg-card); color: var(--brand); }
 .arrow:hover:not(:disabled) { border-color: var(--brand); background: var(--brand-bg); }
@@ -15,10 +14,10 @@
 .scroller { display: grid; grid-auto-flow: column; grid-auto-columns: calc((100% - 1.8rem) / 3); gap: 0.9rem; overflow-x: auto; scroll-snap-type: x mandatory; padding: 2px; margin: -2px; list-style: none; scrollbar-width: none; -ms-overflow-style: none; }
 .scroller::-webkit-scrollbar { display: none; }
 @media (max-width: 820px) { .scroller { grid-auto-columns: calc((100% - 0.9rem) / 2); } }
-/* Touch widths (MOBILE.md): the arrows would take 96px from a 328px card and
+/* Touch widths (MOBILE.md): the arrows would take 96px from a 328px card, for
-   crush the lede into four lines, for a control swiping already provides. They
+   a control swiping already provides. They go, and the documented right-edge
-   go, and the documented right-edge fade carries the affordance — lifting at
+   fade carries the affordance — lifting at the end of the travel, where there
-   the end of the travel, where there is nothing more to hint at. */
+   is nothing more to hint at. */
 @media (max-width: 640px) {
   .top { display: block; }
   .arrows { display: none; }

nextjs-app/components/school/NearbySchoolsCarousel.tsx

@@ -4,7 +4,7 @@
  * The scroller and its arrows.
  *
  * `children` are the server-rendered cards and `header` the server-rendered
- * heading and lede: both stay server components, passed through, so this file
+ * heading: both stay server components, passed through, so this file
  * owns a DOM ref and nothing else. That is what keeps all six links in the
  * initial HTML — a carousel that mounted cards on click would put four of the
  * six beyond a crawler and beyond a reader with no JavaScript.

nextjs-app/components/school/NearbySchoolsSection.tsx

@@ -13,8 +13,10 @@
  * reached on their behalf.
  *
  * There is deliberately no "how these are chosen" panel: the method is already
- * visible in the lede, the chips and the distances. The single caption line is
+ * visible in the chips and the distances. The single caption line is not a
- * not a method note — it is the one thing a card cannot self-correct.
+ * method note — it is the one thing a card cannot self-correct.
+ *
+ * Nor is there a lede: "Other primary schools near X" only restated the heading.
  */
 
 import Link from 'next/link';
@@ -32,28 +34,6 @@ export function shouldRenderNearby(nearby?: NearbySchool[] | null): boolean {
   return (nearby?.length ?? 0) >= MINIMUM;
 }
 
-/**
- * What the lede calls the set of schools it is showing.
- *
- * Derived from the school's own GIAS phase rather than the template it renders
- * with, because those disagree for "16 plus" (GIAS phase 6): a sixth-form
- * college renders the primary template — computeSchoolFlags tests for the
- * substring "secondary" — while the backend correctly matches it against the
- * secondary group. Taking the noun from the template would print "Other primary
- * schools near <sixth form college>" above a row of secondaries.
- *
- * A 16-plus school's candidates span the whole secondary group, so no single
- * noun fits and it gets the honest general one.
- */
-export function nearbyNoun(phase: string | null | undefined): string {
-  const text = (phase ?? '').trim().toLowerCase();
-  if (text === 'all-through') return 'all-through schools';
-  if (text === '16 plus') return 'schools and colleges';
-  if (text.includes('secondary')) return 'secondary schools';
-  if (text.includes('primary')) return 'primary schools';
-  return 'schools';
-}
-
 function metricLabel(key: string): string {
   return key === 'attainment_8_score' ? 'Attainment 8' : 'Reading, writing & maths';
 }
@@ -65,25 +45,16 @@ function formatMetric(value: number | null, key: string): string {
 
 export function NearbySchoolsSection({
   urn,
-  schoolName,
-  phase,
   thisMetricValue,
   nearby,
 }: {
   urn: number;
-  schoolName: string;
-  /** The school's own GIAS phase, not the template it renders with. */
-  phase: string | null | undefined;
   thisMetricValue: number | null;
   nearby?: NearbySchool[] | null;
 }) {
   if (!shouldRenderNearby(nearby)) return null;
   const schools = nearby as NearbySchool[];
-
-  // One card matched on phase alone, so the section may not claim the set
-  // shares an intake with this school.
   const metricKey = schools[0].metric_key;
-  const noun = nearbyNoun(phase);
 
   return (
     <Section id="nearby">
@@ -91,12 +62,9 @@ export function NearbySchoolsSection({
         count={schools.length}
         labelledBy="nearby-schools-heading"
         header={
-          <div>
           <h2 id="nearby-schools-heading" className={styles.heading}>
             Other schools nearby
           </h2>
-            <p className={styles.lede}>{`Other ${noun} near ${schoolName}.`}</p>
-          </div>
         }
       >
         {schools.map((school) => (

nextjs-app/components/school/PrimarySchoolSections.tsx

@@ -154,8 +154,6 @@ export function PrimarySchoolSections({
       {/* Last: it is where the reader goes next, not part of this school. */}
       <NearbySchoolsSection
         urn={schoolInfo.urn}
-        schoolName={schoolInfo.school_name}
-        phase={schoolInfo.phase}
         thisMetricValue={flags.latestResults?.rwm_expected_pct ?? null}
         nearby={nearbySchools}
       />

nextjs-app/components/school/SecondarySchoolSections.tsx

@@ -148,8 +148,6 @@ export function SecondarySchoolSections({
       {/* Last: it is where the reader goes next, not part of this school. */}
       <NearbySchoolsSection
         urn={schoolInfo.urn}
-        schoolName={schoolInfo.school_name}
-        phase={schoolInfo.phase}
         thisMetricValue={flags.latestResults?.attainment_8_score ?? null}
         nearby={nearbySchools}
       />

pipeline/dags/school_data_pipeline.py

@@ -106,9 +106,12 @@ print(f'Validation passed: {{count}} GIAS rows')
         """,
     )
 
+    # Marts fed by annual EES staging models are rebuilt by the EES DAG, even
+    # when they join dim_school. Selecting them here fails in any database
+    # where that DAG hasn't run (pipeline/tests/test_dag_selectors.py).
     dbt_build = BashOperator(
         task_id="dbt_build",
-        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_gias_establishments+ stg_gias_links+ gias_code_names+ --exclude int_ks2_with_lineage+ int_ks4_with_lineage+",
+        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_gias_establishments+ stg_gias_links+ gias_code_names+ --exclude int_ks2_with_lineage+ int_ks4_with_lineage+ stg_ees_ks4_destinations+ stg_ees_ks5_destinations+",
     )
 
     sync_typesense = BashOperator(
@@ -143,7 +146,7 @@ with DAG(
 
     dbt_build_ofsted = BashOperator(
         task_id="dbt_build",
-        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_ofsted_inspections+ int_ofsted_latest+ fact_ofsted_inspection+ dim_school+",
+        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_ofsted_inspections+ int_ofsted_latest+ fact_ofsted_inspection+ dim_school+ --exclude stg_ees_ks4_destinations+ stg_ees_ks5_destinations+",
     )
 
     sync_typesense_ofsted = BashOperator(

pipeline/tests/test_dag_selectors.py

@@ -0,0 +1,98 @@
+"""Every scheduled dbt build must only build models whose parents exist.
+
+The daily GIAS build selects `stg_gias_establishments+`, so any mart that joins
+dim_school joins the daily build too. When such a mart also reads a staging
+model that only the manually triggered EES DAG builds, the daily build fails in
+any database where that DAG has not run since. Sync and cache invalidation then
+never run either. The destinations marts did this from late August 2026.
+
+The graph is read from the model SQL, because CI has no dbt.
+"""
+import re
+from collections import defaultdict
+from pathlib import Path
+
+import pytest
+
+PIPELINE = Path(__file__).resolve().parents[1]
+MODELS = PIPELINE / 'transform' / 'models'
+DAG_FILE = PIPELINE / 'dags' / 'school_data_pipeline.py'
+
+REF = re.compile(r"ref\(\s*'([a-z0-9_]+)'\s*\)")
+DBT_BUILD = re.compile(r'dbt_build\w*\s*=\s*BashOperator\(.*?build --profiles-dir \. --target production ([^"]+)"', re.S)
+DAG_ID = re.compile(r'dag_id="([a-z0-9_]+)"')
+
+DAILY = 'school_data_daily'
+
+# dim_school reads int_ofsted_latest only when the relation exists
+# (adapter.get_relation), so a missing table is not a failure.
+OPTIONAL_PARENTS = {'int_ofsted_latest'}
+
+
+def model_parents():
+    """{model: models it refs}. Seeds are left out: they are loaded once and always exist."""
+    sql = {p.stem: p.read_text() for p in MODELS.rglob('*.sql')}
+    return {name: set(REF.findall(text)) & set(sql) for name, text in sql.items()}
+
+
+def downstream(node, children):
+    seen, stack = {node}, [node]
+    while stack:
+        for child in children[stack.pop()]:
+            if child not in seen:
+                seen.add(child)
+                stack.append(child)
+    return seen
+
+
+def expand(tokens, children):
+    out = set()
+    for token in tokens:
+        out |= downstream(token[:-1], children) if token.endswith('+') else {token}
+    return out
+
+
+def scheduled_builds():
+    """{dag_id: dbt selection arguments} for every dbt build in the DAG file."""
+    text = DAG_FILE.read_text()
+    starts = [(m.start(), m.group(1)) for m in DAG_ID.finditer(text)]
+    builds = {}
+    for i, (start, dag_id) in enumerate(starts):
+        end = starts[i + 1][0] if i + 1 < len(starts) else len(text)
+        found = DBT_BUILD.search(text, start, end)
+        if found:
+            builds[dag_id] = found.group(1)
+    return builds
+
+
+def selected_models(args, parents):
+    children = defaultdict(set)
+    for model, ps in parents.items():
+        for p in ps:
+            children[p].add(model)
+    select = re.search(r'--select (.+?)(?= --exclude|$)', args).group(1).split()
+    excluded = re.search(r'--exclude (.+)$', args)
+    exclude = excluded.group(1).split() if excluded else []
+    return (expand(select, children) - expand(exclude, children)) & set(parents)
+
+
+PARENTS = model_parents()
+BUILDS = scheduled_builds()
+DAILY_MODELS = selected_models(BUILDS[DAILY], PARENTS)
+
+
+def test_every_dag_with_a_dbt_build_is_parsed():
+    assert set(BUILDS) == {
+        'school_data_daily', 'school_data_monthly_ofsted', 'school_data_annual_ees',
+        'school_data_annual_idaci', 'school_data_annual_distance',
+    }
+
+
+@pytest.mark.parametrize('dag_id', sorted(BUILDS))
+def test_selected_models_only_read_models_that_exist(dag_id):
+    selected = selected_models(BUILDS[dag_id], PARENTS)
+    # The daily build is the base layer: other DAGs may rely on what it builds.
+    available = selected | OPTIONAL_PARENTS | (DAILY_MODELS if dag_id != DAILY else set())
+    missing = {model: sorted(PARENTS[model] - available) for model in sorted(selected)
+               if PARENTS[model] - available}
+    assert missing == {}, f'{dag_id} builds models whose parents it never builds: {missing}'

pipeline/transform/models/intermediate/int_ofsted_latest.sql

@@ -1,18 +1,91 @@
--- Intermediate model: Latest Ofsted inspection per URN
+-- Intermediate model: the current Ofsted status per URN
--- Picks the most recent inspection for each school
+-- One row per school: its latest visit (report card, graded or ungraded
+-- inspection) and the overall grade still in force, if any. A grade is dated
+-- by the inspection that awarded or confirmed it, never by a later visit.
+-- Rule and examples: docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md
 
-with ranked as (
+with inspections as (
     select
         *,
+        -- The newest of the three inspections, read from the dates. (Report
+        -- cards began in Nov 2025, after the last legacy inspections, so today
+        -- a report card is always the latest; nothing below relies on that.)
+        greatest(rc_inspection_date, graded_inspection_date, ungraded_inspection_date)
+            as latest_visit_date
+    from {{ ref('stg_ofsted_inspections') }}
+),
+
+ranked as (
+    select
+        *,
+        -- Monthly loads can leave several rows per school. The newest visit
+        -- wins; the tie-breaks keep the choice deterministic.
         row_number() over (
             partition by urn
-            order by inspection_date desc
+            order by latest_visit_date desc,
+                     rc_inspection_date desc nulls last,
+                     graded_inspection_date desc nulls last,
+                     ungraded_inspection_date desc nulls last
         ) as rn
-    from {{ ref('stg_ofsted_inspections') }}
+    from inspections
+),
+
+latest as (
+    select
+        *,
+        -- Same-day ties resolve report card, then graded, then ungraded.
+        case
+            when rc_inspection_date = latest_visit_date then 'report_card'
+            when graded_inspection_date = latest_visit_date then 'graded'
+            else 'ungraded'
+        end as latest_visit_kind
+    from ranked
+    where rn = 1
+),
+
+graded as (
+    select
+        *,
+        -- The grade still in force. A report card replaced overall grades, so
+        -- none survives it. Otherwise: a graded inspection's own overall grade
+        -- (1-4; "Not judged" and the sentinel 9 are no grade); else an
+        -- ungraded visit's "School remains X"; else, after an ungraded visit
+        -- that names no grade, the graded inspection's grade.
+        case
+            when rc_inspection_date is not null
+                then null
+            when latest_visit_kind = 'graded' and overall_effectiveness between 1 and 4
+                then 'graded_latest'
+            when latest_visit_kind = 'ungraded' and ungraded_grade is not null
+                then 'confirmed'
+            when latest_visit_kind = 'ungraded' and overall_effectiveness between 1 and 4
+                then 'graded_earlier'
+        end as grade_case
+    from latest
 )
 
 select
     urn,
+    latest_visit_date,
+    latest_visit_kind,
+    case when latest_visit_kind = 'ungraded' then ungraded_outcome end as latest_visit_outcome,
+    case grade_case
+        when 'confirmed' then ungraded_grade
+        when 'graded_latest' then overall_effectiveness
+        when 'graded_earlier' then overall_effectiveness
+    end as current_grade,
+    case grade_case
+        when 'confirmed' then ungraded_inspection_date
+        when 'graded_latest' then graded_inspection_date
+        when 'graded_earlier' then graded_inspection_date
+    end as current_grade_date,
+    case grade_case
+        when 'confirmed' then 'confirmed'
+        when 'graded_latest' then 'graded'
+        when 'graded_earlier' then 'graded'
+    end as current_grade_basis,
+    graded_inspection_date,
+    ungraded_inspection_date,
     inspection_date,
     inspection_type,
     framework,
@@ -36,5 +109,4 @@ select
     rc_sixth_form,
     rc_inspection_date,
     report_url
-from ranked
+from graded
-where rn = 1

pipeline/transform/models/intermediate/int_ofsted_latest.yml

@@ -0,0 +1,113 @@
+version: 2
+
+unit_tests:
+  - name: graded_not_judged_has_no_grade
+    description: Rabbsfarm (102408). The 2025 inspection gave no overall grade, so the 2020 "remains Good" is not carried forward (audit C1).
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 102408, graded_inspection_date: '2025-06-17', ungraded_inspection_date: '2020-02-06', overall_effectiveness: null, ungraded_grade: 2, ungraded_outcome: 'School remains Good'}
+    expect:
+      rows:
+        - {urn: 102408, latest_visit_date: '2025-06-17', latest_visit_kind: graded, latest_visit_outcome: null, current_grade: null, current_grade_date: null, current_grade_basis: null}
+
+  - name: graded_with_overall_grade
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 1, graded_inspection_date: '2019-06-01', overall_effectiveness: 2}
+    expect:
+      rows:
+        - {urn: 1, latest_visit_date: '2019-06-01', latest_visit_kind: graded, current_grade: 2, current_grade_date: '2019-06-01', current_grade_basis: graded}
+
+  - name: ungraded_remains_good_confirms_the_grade
+    description: Robins Lane (104762). Graded Good 2020, "School remains Good" July 2024 — Good, dated by the confirming visit.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 104762, graded_inspection_date: '2020-01-07', ungraded_inspection_date: '2024-07-18', overall_effectiveness: 2, ungraded_grade: 2, ungraded_outcome: 'School remains Good'}
+    expect:
+      rows:
+        - {urn: 104762, latest_visit_date: '2024-07-18', latest_visit_kind: ungraded, latest_visit_outcome: 'School remains Good', current_grade: 2, current_grade_date: '2024-07-18', current_grade_basis: confirmed}
+
+  - name: post_2024_ungraded_keeps_graded_grade_with_its_own_date
+    description: Washwood Heath (139888). Graded Good 2020, "Standards maintained" May 2025 — Good, dated 2020; latest visit May 2025 (audit M1).
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 139888, graded_inspection_date: '2020-03-03', ungraded_inspection_date: '2025-05-21', overall_effectiveness: 2, ungraded_grade: null, ungraded_outcome: 'Standards maintained'}
+    expect:
+      rows:
+        - {urn: 139888, latest_visit_date: '2025-05-21', latest_visit_kind: ungraded, latest_visit_outcome: 'Standards maintained', current_grade: 2, current_grade_date: '2020-03-03', current_grade_basis: graded}
+
+  - name: ungraded_only_standards_maintained
+    description: Oakgrove (136454). Only an ungraded visit, outcome names no grade.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 136454, ungraded_inspection_date: '2024-11-13', ungraded_grade: null, ungraded_outcome: 'Standards maintained'}
+    expect:
+      rows:
+        - {urn: 136454, latest_visit_date: '2024-11-13', latest_visit_kind: ungraded, latest_visit_outcome: 'Standards maintained', current_grade: null, current_grade_date: null, current_grade_basis: null}
+
+  - name: report_card_wins
+    description: The Willink School (110048). A report card is the latest visit and no legacy grade stays in force.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 110048, ungraded_inspection_date: '2023-10-05', ungraded_grade: 2, ungraded_outcome: 'School remains Good', rc_inspection_date: '2026-05-06', rc_inclusion: 3}
+    expect:
+      rows:
+        - {urn: 110048, latest_visit_date: '2026-05-06', latest_visit_kind: report_card, latest_visit_outcome: null, current_grade: null, current_grade_date: null, current_grade_basis: null}
+
+  - name: duplicate_rows_newer_report_card_wins
+    description: Monthly loads can leave an older row beside a newer one for the same graded date; the row with the report card must win (audit H3).
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 138186, graded_inspection_date: '2023-06-13', overall_effectiveness: 3}
+          - {urn: 138186, graded_inspection_date: '2023-06-13', overall_effectiveness: 3, rc_inspection_date: '2026-06-02', rc_inclusion: 3}
+    expect:
+      rows:
+        - {urn: 138186, latest_visit_date: '2026-06-02', latest_visit_kind: report_card, current_grade: null}
+
+  - name: same_day_graded_wins
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 2, graded_inspection_date: '2024-03-01', ungraded_inspection_date: '2024-03-01', overall_effectiveness: 1, ungraded_grade: 2, ungraded_outcome: 'School remains Good'}
+    expect:
+      rows:
+        - {urn: 2, latest_visit_kind: graded, current_grade: 1, current_grade_basis: graded}
+
+  - name: overall_sentinel_is_not_a_grade
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 3, graded_inspection_date: '2018-05-01', overall_effectiveness: 9}
+    expect:
+      rows:
+        - {urn: 3, latest_visit_kind: graded, current_grade: null}
+
+  - name: newer_legacy_visit_after_a_report_card
+    description: >
+      Not in Ofsted's data today (0 of 2,451 report-card schools in the 31 Aug
+      2026 MI), but the latest visit is read from the dates, not assumed. The
+      report card still leaves no legacy grade in force.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 4, ungraded_inspection_date: '2026-03-02', ungraded_grade: 2, ungraded_outcome: 'School remains Good', rc_inspection_date: '2025-12-01', rc_inclusion: 3}
+    expect:
+      rows:
+        - {urn: 4, latest_visit_date: '2026-03-02', latest_visit_kind: ungraded, latest_visit_outcome: 'School remains Good', current_grade: null, current_grade_date: null, current_grade_basis: null}

pipeline/transform/models/marts/_marts_schema.yml

@@ -125,6 +125,30 @@ models:
       - name: inspection_date
         tests: [not_null]
 
+  - name: fact_ofsted_latest
+    description: >
+      Current Ofsted status, one row per URN: the latest visit and the overall
+      grade still in force. See int_ofsted_latest for the rule.
+    columns:
+      - name: urn
+        tests: [not_null, unique]
+      - name: latest_visit_date
+        tests: [not_null]
+      - name: latest_visit_kind
+        tests:
+          - not_null
+          - accepted_values:
+              values: ['report_card', 'graded', 'ungraded']
+      - name: current_grade
+        tests:
+          - accepted_values:
+              values: [1, 2, 3, 4]
+              quote: false
+      - name: current_grade_basis
+        tests:
+          - accepted_values:
+              values: ['graded', 'confirmed']
+
   - name: fact_pupil_characteristics
     description: Pupil demographics — one row per URN per year
     columns:

pipeline/transform/models/marts/fact_ofsted_latest.sql

@@ -0,0 +1,37 @@
+-- Mart: current Ofsted status — one row per URN
+-- The backend reads this instead of choosing the latest row of
+-- fact_ofsted_inspection itself. The rule lives in int_ofsted_latest.
+
+select
+    urn,
+    latest_visit_date,
+    latest_visit_kind,
+    latest_visit_outcome,
+    current_grade,
+    current_grade_date,
+    current_grade_basis,
+    graded_inspection_date,
+    ungraded_inspection_date,
+    rc_inspection_date,
+    inspection_type,
+    framework,
+    overall_effectiveness,
+    quality_of_education,
+    behaviour_attitudes,
+    personal_development,
+    leadership_management,
+    early_years_provision,
+    sixth_form_provision,
+    ungraded_outcome,
+    ungraded_grade,
+    rc_safeguarding_met,
+    rc_inclusion,
+    rc_curriculum_teaching,
+    rc_achievement,
+    rc_attendance_behaviour,
+    rc_personal_development,
+    rc_leadership_governance,
+    rc_early_years,
+    rc_sixth_form,
+    report_url
+from {{ ref('int_ofsted_latest') }}

pipeline/transform/models/staging/stg_ofsted_inspections.sql

@@ -1,5 +1,10 @@
 -- Staging model: Ofsted inspection records
--- Handles both OEIF (pre-Nov 2025) and Report Card (post-Nov 2025) frameworks
+-- Handles both OEIF (pre-Nov 2025) and Report Card (post-Nov 2025) frameworks.
+--
+-- Ofsted's MI carries up to three inspections per school: the latest graded
+-- one, the latest ungraded one and the latest report card. Their dates stay
+-- separate here so int_ofsted_latest can tell which came last and which one a
+-- grade belongs to (docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md).
 
 with source as (
     select * from {{ source('raw', 'ofsted_inspections') }}
@@ -8,17 +13,12 @@ with source as (
 renamed as (
     select
         cast(urn as integer)                                                    as urn,
-        -- Inspection event date: the graded inspection when present, otherwise the
+        to_date(nullif(trim(inspection_date), 'NULL'), 'DD/MM/YYYY')            as graded_inspection_date,
-        -- ungraded (Section 8) inspection so schools with only an ungraded
+        to_date(nullif(trim(ungraded_inspection_date), 'NULL'), 'DD/MM/YYYY')   as ungraded_inspection_date,
-        -- inspection are still retained.
-        coalesce(
-            to_date(nullif(trim(inspection_date), 'NULL'), 'DD/MM/YYYY'),
-            to_date(nullif(trim(ungraded_inspection_date), 'NULL'), 'DD/MM/YYYY')
-        )                                                                       as inspection_date,
         inspection_type,
         event_type_grouping                                                     as framework,
 
-        -- OEIF grades (1-4 scale)
+        -- OEIF grades (1-4 scale; 9 = not applicable)
         {{ safe_numeric('overall_effectiveness') }}::integer                    as overall_effectiveness,
         {{ safe_numeric('quality_of_education') }}::integer                     as quality_of_education,
         {{ safe_numeric('behaviour_and_attitudes') }}::integer                  as behaviour_attitudes,
@@ -27,9 +27,9 @@ renamed as (
         {{ safe_numeric('early_years_provision') }}::integer                    as early_years_provision,
         {{ safe_numeric('sixth_form_provision') }}::integer                     as sixth_form_provision,
 
-        -- Ungraded (Section 8) inspection outcome — free text, plus a grade
+        -- Ungraded (Section 8) inspection outcome — free text, plus the grade
-        -- parsed from it (1/2/null) used as a last-resort fallback for schools
+        -- it confirms ("School remains Good" → 2); null for outcomes that
-        -- with no graded overall effectiveness.
+        -- name no grade.
         nullif(trim(ungraded_outcome), 'NULL')                                  as ungraded_outcome,
         {{ parse_ungraded_outcome('ungraded_outcome') }}::integer               as ungraded_grade,
 
@@ -50,20 +50,17 @@ renamed as (
         {{ parse_report_card_grade('rc_sixth_form') }}::integer           as rc_sixth_form,
 
         -- Start date of the latest FULL inspection (the report-card
-        -- inspection in the renewed framework). Guarded in the final select:
+        -- inspection in the renewed framework). Only kept when the row
-        -- only kept when the row actually carries report-card grades, because
+        -- carries report-card grades, because in legacy-format files this
-        -- in legacy-format files this column is the legacy inspection date.
+        -- column is the legacy inspection date.
         to_date(nullif(trim(rc_inspection_date), 'NULL'), 'DD/MM/YYYY')   as rc_inspection_date_raw,
 
         nullif(trim(report_url), 'NULL')                                  as report_url
     from source
     where urn is not null
-      and (
+),
-          nullif(trim(inspection_date), 'NULL') is not null
-          or nullif(trim(ungraded_inspection_date), 'NULL') is not null
-      )
-)
 
+dated as (
     select
         *,
         case
@@ -77,4 +74,12 @@ select
             then rc_inspection_date_raw
         end as rc_inspection_date
     from renamed
-where inspection_date is not null
+)
+
+select
+    *,
+    -- For readers that predate the separate dates (fact_ofsted_inspection and
+    -- the backend until it reads fact_ofsted_latest). Never null below.
+    coalesce(graded_inspection_date, ungraded_inspection_date, rc_inspection_date) as inspection_date
+from dated
+where coalesce(graded_inspection_date, ungraded_inspection_date, rc_inspection_date) is not null

pipeline/transform/models/staging/stg_ofsted_inspections.yml

@@ -0,0 +1,25 @@
+version: 2
+
+unit_tests:
+  - name: stg_ofsted_keeps_the_three_dates_apart
+    description: The graded and ungraded dates must stay separate so the latest visit can be found.
+    model: stg_ofsted_inspections
+    given:
+      - input: source('raw', 'ofsted_inspections')
+        rows:
+          - {urn: 102408, inspection_date: '17/06/2025', ungraded_inspection_date: '06/02/2020', overall_effectiveness: 'Not judged', ungraded_outcome: 'School remains Good', rc_inspection_date: '17/06/2025', rc_inclusion: 'NULL'}
+    expect:
+      rows:
+        - {urn: 102408, graded_inspection_date: '2025-06-17', ungraded_inspection_date: '2020-02-06', rc_inspection_date: null, inspection_date: '2025-06-17', ungraded_grade: 2}
+
+  - name: stg_ofsted_keeps_report_card_only_rows
+    description: A school whose only inspection is a report card used to be dropped (audit H3).
+    model: stg_ofsted_inspections
+    given:
+      - input: source('raw', 'ofsted_inspections')
+        rows:
+          - {urn: 149612, inspection_date: 'NULL', ungraded_inspection_date: 'NULL', rc_inspection_date: '10/02/2026', rc_inclusion: 'Expected standard', rc_safeguarding_met: 'Met'}
+          - {urn: 1, inspection_date: 'NULL', ungraded_inspection_date: 'NULL', rc_inspection_date: 'NULL'}
+    expect:
+      rows:
+        - {urn: 149612, graded_inspection_date: null, ungraded_inspection_date: null, rc_inspection_date: '2026-02-10', inspection_date: '2026-02-10', rc_inclusion: 3}

pipeline/transform/tests/assert_ofsted_current_grade_consistent.sql

@@ -0,0 +1,10 @@
+-- A grade is dated by the inspection that awarded or confirmed it, never
+-- after the latest visit, and has a date and a basis exactly when it exists.
+-- A report card leaves no legacy grade in force.
+
+select urn
+from {{ ref('fact_ofsted_latest') }}
+where current_grade_date > latest_visit_date
+   or (current_grade is null) <> (current_grade_date is null)
+   or (current_grade is null) <> (current_grade_basis is null)
+   or (rc_inspection_date is not null and current_grade is not null)