TudorandClaude Opus 4.8
2332ee6347
Build and Push Docker Images / Build Backend (FastAPI) (push) Successful in 18s
Build and Push Docker Images / Build Frontend (Next.js) (push) Successful in 48s
Build and Push Docker Images / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m30s
Build and Push Docker Images / Trigger Portainer Update (push) Successful in 1s
Ungraded (Section 8) inspections don't assign a fresh grade — the export only gives free text like "School remains Good". Parse that text into a grade (remains Outstanding -> 1, remains Good -> 2, else null) and use it as a last-resort fallback when no graded overall effectiveness exists. Also retain schools that have only an ungraded inspection (no graded date) by coalescing the inspection date, so ~8.5k previously-dropped schools now carry a grade. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
21 lines
987 B
SQL
21 lines
987 B
SQL
-- Macro: Parse an Ofsted ungraded (Section 8) inspection outcome into a grade.
|
|
--
|
|
-- Ungraded inspections do not assign a fresh grade; the outcome is free text
|
|
-- describing whether the school kept its previous judgement, e.g.
|
|
-- "School remains Good", "School remains Good (Concerns) - S5 Next",
|
|
-- "School remains Outstanding", "School remains Outstanding (Concerns) - S5 Next".
|
|
-- These are the only Good/Outstanding signal we can recover, so we match on the
|
|
-- "remains <grade>" phrase (tolerant of any trailing qualifier).
|
|
--
|
|
-- Outcomes that don't establish a Good/Outstanding status — "Standards
|
|
-- maintained", "Improved significantly", "Some aspects not as strong" — map to
|
|
-- null. Grades align with parse_ofsted_grade (1 = Outstanding, 2 = Good).
|
|
|
|
{% macro parse_ungraded_outcome(column) %}
|
|
case
|
|
when {{ column }} ilike '%remains Outstanding%' then 1
|
|
when {{ column }} ilike '%remains Good%' then 2
|
|
else null
|
|
end
|
|
{% endmacro %}
|