Compare commits
1
Commits
| Author | SHA1 | Date | |
|---|---|---|---|
 TudorandClaude Opus 5.5
|
94bfac9caf |
No files matched your search
File diff suppressed because it is too large.
Load diff
@@ -1,257 +0,0 @@
|
||||
# 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.
|
||||
@@ -8,6 +8,7 @@
|
||||
import { render, screen } from '@testing-library/react';
|
||||
|
||||
import {
|
||||
nearbyNoun,
|
||||
NearbySchoolsSection,
|
||||
shouldRenderNearby,
|
||||
} from '@/components/school/NearbySchoolsSection';
|
||||
@@ -42,7 +43,13 @@ function school(overrides: Partial<NearbySchool> = {}): NearbySchool {
|
||||
|
||||
function renderSection(nearby: NearbySchool[]) {
|
||||
return render(
|
||||
<NearbySchoolsSection urn={100001} thisMetricValue={72} nearby={nearby} />,
|
||||
<NearbySchoolsSection
|
||||
urn={100001}
|
||||
schoolName="Meadowbrook Primary School"
|
||||
phase="Primary"
|
||||
thisMetricValue={72}
|
||||
nearby={nearby}
|
||||
/>,
|
||||
);
|
||||
}
|
||||
|
||||
@@ -83,10 +90,15 @@ describe('what the section claims', () => {
|
||||
});
|
||||
|
||||
it('shows no chips at all when nothing is shared, rather than inventing one', () => {
|
||||
const { container } = renderSection([
|
||||
school({ shared: [] }),
|
||||
school({ urn: 100003, shared: [] }),
|
||||
]);
|
||||
const { container } = render(
|
||||
<NearbySchoolsSection
|
||||
urn={100001}
|
||||
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);
|
||||
@@ -94,6 +106,37 @@ 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' })]);
|
||||
|
||||
@@ -1,7 +1,8 @@
|
||||
.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: center; justify-content: space-between; gap: 1rem; margin-bottom: 1.25rem; }
|
||||
.top { display: flex; align-items: flex-start; justify-content: space-between; gap: 1rem; }
|
||||
.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); }
|
||||
@@ -14,10 +15,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, for
|
||||
a control swiping already provides. They go, and the documented right-edge
|
||||
fade carries the affordance — lifting at the end of the travel, where there
|
||||
is nothing more to hint at. */
|
||||
/* Touch widths (MOBILE.md): the arrows would take 96px from a 328px card and
|
||||
crush the lede into four lines, for a control swiping already provides. They
|
||||
go, and the documented right-edge fade carries the affordance — lifting at
|
||||
the end of the travel, where there is nothing more to hint at. */
|
||||
@media (max-width: 640px) {
|
||||
.top { display: block; }
|
||||
.arrows { display: none; }
|
||||
|
||||
@@ -4,7 +4,7 @@
|
||||
* The scroller and its arrows.
|
||||
*
|
||||
* `children` are the server-rendered cards and `header` the server-rendered
|
||||
* heading: both stay server components, passed through, so this file
|
||||
* heading and lede: 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.
|
||||
|
||||
@@ -13,10 +13,8 @@
|
||||
* reached on their behalf.
|
||||
*
|
||||
* There is deliberately no "how these are chosen" panel: the method is already
|
||||
* visible in the chips and the distances. The single caption line is not a
|
||||
* 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.
|
||||
* visible in the lede, the chips and the distances. The single caption line is
|
||||
* not a method note — it is the one thing a card cannot self-correct.
|
||||
*/
|
||||
|
||||
import Link from 'next/link';
|
||||
@@ -34,6 +32,28 @@ 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';
|
||||
}
|
||||
@@ -45,16 +65,25 @@ 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">
|
||||
@@ -62,9 +91,12 @@ export function NearbySchoolsSection({
|
||||
count={schools.length}
|
||||
labelledBy="nearby-schools-heading"
|
||||
header={
|
||||
<h2 id="nearby-schools-heading" className={styles.heading}>
|
||||
Other schools nearby
|
||||
</h2>
|
||||
<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) => (
|
||||
|
||||
@@ -154,6 +154,8 @@ 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}
|
||||
/>
|
||||
|
||||
@@ -148,6 +148,8 @@ 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}
|
||||
/>
|
||||
|
||||
@@ -0,0 +1,26 @@
|
||||
"""Read a GIAS extract from the raw bytes of the download.
|
||||
|
||||
GIAS writes its CSVs in Windows-1252 and sends no charset, so `resp.text`
|
||||
leaves requests to guess the codec. On 3 Oct 2026 it guessed windows-1250 and
|
||||
"à" became "ŕ". Decode the bytes ourselves instead.
|
||||
"""
|
||||
|
||||
from __future__ import annotations
|
||||
|
||||
import io
|
||||
|
||||
import pandas as pd
|
||||
|
||||
GIAS_ENCODING = "cp1252"
|
||||
|
||||
|
||||
def read_gias_csv(content: bytes, logger=None) -> pd.DataFrame:
|
||||
"""Every column as a string; a blank cell stays ''."""
|
||||
# Windows-1252 leaves five bytes undefined. One stray byte must not stop
|
||||
# the daily refresh of every school, so it becomes U+FFFD and is logged.
|
||||
text = content.decode(GIAS_ENCODING, errors="replace")
|
||||
undecodable = text.count("�")
|
||||
if undecodable and logger is not None:
|
||||
logger.warning("%d byte(s) in the GIAS extract could not be decoded as %s",
|
||||
undecodable, GIAS_ENCODING)
|
||||
return pd.read_csv(io.StringIO(text), dtype=str, keep_default_na=False)
|
||||
@@ -7,6 +7,8 @@ from datetime import date, timedelta
|
||||
from singer_sdk import Stream, Tap
|
||||
from singer_sdk import typing as th
|
||||
|
||||
from tap_uk_gias.gias_csv import read_gias_csv
|
||||
|
||||
GIAS_URL_TEMPLATE = (
|
||||
"https://ea-edubase-api-prod.azurewebsites.net"
|
||||
"/edubase/downloads/public/edubasealldata{date}.csv"
|
||||
@@ -74,9 +76,6 @@ class GIASEstablishmentsStream(Stream):
|
||||
|
||||
def get_records(self, context):
|
||||
"""Download GIAS CSV and yield rows."""
|
||||
import io
|
||||
|
||||
import pandas as pd
|
||||
import requests
|
||||
|
||||
today = date.today()
|
||||
@@ -94,12 +93,7 @@ class GIASEstablishmentsStream(Stream):
|
||||
|
||||
resp.raise_for_status()
|
||||
|
||||
df = pd.read_csv(
|
||||
io.StringIO(resp.text),
|
||||
encoding="latin-1",
|
||||
dtype=str,
|
||||
keep_default_na=False,
|
||||
)
|
||||
df = read_gias_csv(resp.content, self.logger)
|
||||
|
||||
for _, row in df.iterrows():
|
||||
record = row.to_dict()
|
||||
@@ -126,9 +120,6 @@ class GIASLinksStream(Stream):
|
||||
|
||||
def get_records(self, context):
|
||||
"""Download GIAS links CSV and yield rows."""
|
||||
import io
|
||||
|
||||
import pandas as pd
|
||||
import requests
|
||||
|
||||
today = date.today()
|
||||
@@ -146,12 +137,7 @@ class GIASLinksStream(Stream):
|
||||
|
||||
resp.raise_for_status()
|
||||
|
||||
df = pd.read_csv(
|
||||
io.StringIO(resp.text),
|
||||
encoding="latin-1",
|
||||
dtype=str,
|
||||
keep_default_na=False,
|
||||
)
|
||||
df = read_gias_csv(resp.content, self.logger)
|
||||
|
||||
for _, row in df.iterrows():
|
||||
record = row.to_dict()
|
||||
|
||||
@@ -0,0 +1,66 @@
|
||||
"""GIAS publishes its extracts in Windows-1252 and declares no charset.
|
||||
|
||||
The tap used to hand pandas `resp.text`, so requests guessed the codec.
|
||||
On 3 Oct 2026 it guessed windows-1250, and "St Thomas à Becket" was stored
|
||||
as "St Thomas ŕ Becket". The `encoding=` passed to read_csv did nothing,
|
||||
because the text was already decoded.
|
||||
"""
|
||||
import importlib.util
|
||||
import logging
|
||||
from pathlib import Path
|
||||
|
||||
import pytest
|
||||
|
||||
MODULE = (Path(__file__).resolve().parents[1] / 'plugins' / 'extractors' / 'tap-uk-gias'
|
||||
/ 'tap_uk_gias' / 'gias_csv.py')
|
||||
|
||||
|
||||
@pytest.fixture
|
||||
def gias_csv():
|
||||
spec = importlib.util.spec_from_file_location('gias_csv', MODULE)
|
||||
module = importlib.util.module_from_spec(spec)
|
||||
spec.loader.exec_module(module)
|
||||
return module
|
||||
|
||||
|
||||
# Byte for byte as GIAS writes it: 0xE0 à, 0x92 ’, 0xE9 é, 0xB0 °, 0xE7 ç.
|
||||
EXTRACT = (
|
||||
b'"URN","EstablishmentName","HeadLastName"\r\n'
|
||||
b'"138950","St Thomas \xe0 Becket Catholic Secondary School","Smith"\r\n'
|
||||
b'"100000","The Dean and Chapter of St Paul\x92s Cathedral","Pr\xe9vert"\r\n'
|
||||
b'"140677","North Star 180\xb0","Fran\xe7ois"\r\n'
|
||||
b'"100001","No head recorded",""\r\n'
|
||||
)
|
||||
|
||||
|
||||
def test_names_decode_as_windows_1252(gias_csv):
|
||||
df = gias_csv.read_gias_csv(EXTRACT)
|
||||
assert list(df['EstablishmentName']) == [
|
||||
'St Thomas à Becket Catholic Secondary School',
|
||||
'The Dean and Chapter of St Paul’s Cathedral',
|
||||
'North Star 180°',
|
||||
'No head recorded',
|
||||
]
|
||||
assert list(df['HeadLastName']) == ['Smith', 'Prévert', 'François', '']
|
||||
|
||||
|
||||
def test_the_codec_requests_guessed_is_not_used(gias_csv):
|
||||
# What the tap stored on 3 Oct: the same bytes read as windows-1250.
|
||||
assert 'ŕ' in EXTRACT.decode('cp1250')
|
||||
names = ' '.join(gias_csv.read_gias_csv(EXTRACT)['EstablishmentName'])
|
||||
assert 'ŕ' not in names
|
||||
|
||||
|
||||
def test_values_stay_strings(gias_csv):
|
||||
df = gias_csv.read_gias_csv(EXTRACT)
|
||||
assert df.loc[0, 'URN'] == '138950'
|
||||
|
||||
|
||||
def test_a_byte_windows_1252_leaves_undefined_does_not_stop_the_load(gias_csv, caplog):
|
||||
# 0x81 has no Windows-1252 character. One odd name must not block the daily
|
||||
# refresh of every school, but it must be visible in the log.
|
||||
extract = b'"URN","EstablishmentName"\r\n"100002","Odd \x81 Name"\r\n'
|
||||
with caplog.at_level(logging.WARNING):
|
||||
df = gias_csv.read_gias_csv(extract, logger=logging.getLogger('gias'))
|
||||
assert df.loc[0, 'EstablishmentName'] == 'Odd � Name'
|
||||
assert 'could not be decoded' in caplog.text
|
||||
@@ -1,91 +1,18 @@
|
||||
-- Intermediate model: the current Ofsted status per URN
|
||||
-- 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
|
||||
-- Intermediate model: Latest Ofsted inspection per URN
|
||||
-- Picks the most recent inspection for each school
|
||||
|
||||
with inspections as (
|
||||
with ranked 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 latest_visit_date desc,
|
||||
rc_inspection_date desc nulls last,
|
||||
graded_inspection_date desc nulls last,
|
||||
ungraded_inspection_date desc nulls last
|
||||
order by inspection_date desc
|
||||
) as rn
|
||||
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
|
||||
from {{ ref('stg_ofsted_inspections') }}
|
||||
)
|
||||
|
||||
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,
|
||||
@@ -109,4 +36,5 @@ select
|
||||
rc_sixth_form,
|
||||
rc_inspection_date,
|
||||
report_url
|
||||
from graded
|
||||
from ranked
|
||||
where rn = 1
|
||||
@@ -1,113 +0,0 @@
|
||||
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}
|
||||
@@ -125,30 +125,6 @@ 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:
|
||||
|
||||
@@ -1,37 +0,0 @@
|
||||
-- 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') }}
|
||||
@@ -1,10 +1,5 @@
|
||||
-- Staging model: Ofsted inspection records
|
||||
-- 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).
|
||||
-- Handles both OEIF (pre-Nov 2025) and Report Card (post-Nov 2025) frameworks
|
||||
|
||||
with source as (
|
||||
select * from {{ source('raw', 'ofsted_inspections') }}
|
||||
@@ -13,12 +8,17 @@ with source as (
|
||||
renamed as (
|
||||
select
|
||||
cast(urn as integer) as urn,
|
||||
to_date(nullif(trim(inspection_date), 'NULL'), 'DD/MM/YYYY') as graded_inspection_date,
|
||||
to_date(nullif(trim(ungraded_inspection_date), 'NULL'), 'DD/MM/YYYY') as ungraded_inspection_date,
|
||||
-- Inspection event date: the graded inspection when present, otherwise the
|
||||
-- ungraded (Section 8) inspection so schools with only an ungraded
|
||||
-- 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; 9 = not applicable)
|
||||
-- OEIF grades (1-4 scale)
|
||||
{{ 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 the grade
|
||||
-- it confirms ("School remains Good" → 2); null for outcomes that
|
||||
-- name no grade.
|
||||
-- Ungraded (Section 8) inspection outcome — free text, plus a grade
|
||||
-- parsed from it (1/2/null) used as a last-resort fallback for schools
|
||||
-- with no graded overall effectiveness.
|
||||
nullif(trim(ungraded_outcome), 'NULL') as ungraded_outcome,
|
||||
{{ parse_ungraded_outcome('ungraded_outcome') }}::integer as ungraded_grade,
|
||||
|
||||
@@ -50,36 +50,31 @@ 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). Only kept when the row
|
||||
-- carries report-card grades, because in legacy-format files this
|
||||
-- column is the legacy inspection date.
|
||||
-- inspection in the renewed framework). Guarded in the final select:
|
||||
-- only kept when the row actually carries report-card grades, because
|
||||
-- in legacy-format files this 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
|
||||
),
|
||||
|
||||
dated as (
|
||||
select
|
||||
*,
|
||||
case
|
||||
when rc_safeguarding_met is not null
|
||||
or rc_inclusion is not null
|
||||
or rc_curriculum_teaching is not null
|
||||
or rc_achievement is not null
|
||||
or rc_attendance_behaviour is not null
|
||||
or rc_personal_development is not null
|
||||
or rc_leadership_governance is not null
|
||||
then rc_inspection_date_raw
|
||||
end as rc_inspection_date
|
||||
from renamed
|
||||
and (
|
||||
nullif(trim(inspection_date), 'NULL') is not null
|
||||
or nullif(trim(ungraded_inspection_date), 'NULL') 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
|
||||
case
|
||||
when rc_safeguarding_met is not null
|
||||
or rc_inclusion is not null
|
||||
or rc_curriculum_teaching is not null
|
||||
or rc_achievement is not null
|
||||
or rc_attendance_behaviour is not null
|
||||
or rc_personal_development is not null
|
||||
or rc_leadership_governance is not null
|
||||
then rc_inspection_date_raw
|
||||
end as rc_inspection_date
|
||||
from renamed
|
||||
where inspection_date is not null
|
||||
@@ -1,25 +0,0 @@
|
||||
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}
|
||||
@@ -1,10 +0,0 @@
|
||||
-- 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)
|
||||
Reference in new issue
Block a user