# 05 — Data Validation Spec (correctness vs. reality) > Implements the "correctness vs reality" gap (`02_gap_analysis.md` #9), the > verification spine in `03_integration_plan.md §C3`, and the read-only Python > approach agreed in review. This is the original design; the **as-built** harness is > in PR B (`verify/`), with `verify/METHODOLOGY.md` authoritative wherever the two > differ (notably the independent reference — see below). > > **Validation taxonomy (standardized).** (i) **completeness** (missing years/schools); > (ii) **correctness — base case** (DB vs the *exact ingested source file*); (iii) > **correctness — spot check** (DB vs a public-facing per-school report); (iv) > **correctness — computed values** (derived metrics). This spec was originally framed as > "Phase 1a/1b/2", which map to: 1a → **(ii) base case** (ships first); 1b → an > **independent-publisher** correctness check (deferred); 2 → **(iv) computed** > (follow-up). The shipped **(iii) spot check** uses the NYC School Snapshot > (same-publisher presentation fidelity), not the NYSED check planned here. Pilot one > metric end-to-end, then templatize. > > Prototype = `mwg1378/nyc-edu-data` (cloned at `main @ a4baa1a`). Scaffold reused > = `reference-scaffold/` (`pipeline/verify/`). --- ## 0. Principles (load-bearing) 1. **Read-only.** The validator never writes to the prototype DB or to upstream sources. It connects to Postgres via a **read-only role**, issues **SELECT only**, imports **no loader code**, and writes **only** to its own output dirs (the reference store + QA reports). See §5.4. 2. **Independent.** We re-read the source files and the official figures **ourselves** and compare them to what the site serves. Verification does **not** depend on, or need to understand, the prototype's calculation code. (For (iv) computed metrics we independently re-derive the *documented formula* — still not their implementation.) 3. **Replicable + provenanced.** Every file we fetch is stored in full with `{url, retrieved_at, sha256, bytes, http_status, publisher}` so any result can be reproduced from the exact bytes, offline. See §2. 4. **Deterministic.** Same inputs → same report; content hashes recorded; fixed sampling seed. 5. **Suppression-aware.** Officially-suppressed cells are reported as `n/a`, never as a mismatch. --- ## 1. Phasing The correctness work is split so the first deliverable is self-contained and squarely *our* responsibility: the **(ii) base case ships first**; an **independent-publisher** correctness check is **deferred**; **(iv) computed values** is the final follow-up. | | (ii) Base case — internal fidelity (**ships first**) | Independent-publisher correctness (deferred) | (iv) Computed values (follow-up) | |---|---|---|---| | Question | "Does the site faithfully serve the **stated source file** it ingests?" | "Is that source file itself **right**, vs an independent publisher?" | "Are the **computed** metrics derived correctly from validated components?" | | Targets | Each directly-ingested component metric (per-grade proficiency, grad rate, chronic absenteeism, per-question survey %, SSEC counts) | Same components | Composites (ES/MS academic, teacher-voice), survey rollups, course-access index | | Layers compared | **served (DB) ↔ independent re-read of the ingested source file** | + **independent reference** (different publisher: NYSED) → independent truth | re-derive **documented formula** from validated components → formula fidelity (no external layer) | | Reference needed | **None beyond the file we already ingest** (downloaded + provenanced) | A different-publisher per-school file (NYSED); NYC Open Data shares the NYC publisher | n/a | | Catches | parse/ingest bugs: column mapping, units, year norm, suppression, dropped/dup rows, DBN mismatch | source-level errors faithfully copied from a wrong file | bad composite membership / rollup question set / index formula | | Does **not** catch | whether the source file itself is wrong (→ independent check) | — | — | | Depends on | a **read-only DB connection**; runs against the current DB today | base case green + (for NYSED) DBN↔BEDS crosswalk | base-case components passing | | Course access (#8) | n/a (derived) | n/a (NYCDOE publishes no index, `spec §9.7`) | Input-parse validation only | This spec details the **(ii) base case** fully (the shipping piece). **As built (PR B):** the shipped **(iii) spot check** compares the DB against the **NYC School Snapshot** (same-publisher *presentation* fidelity, §5.3) rather than the different-publisher NYSED check planned in the middle column — that independent check is **deferred**. **(iv) computed values** is outlined in §9. > Ownership note: the base case ("we faithfully use our stated source") is *our* > responsibility; discrepancies between NYC and NYSED are largely **NY State's** > data-quality concern, which is why the independent-publisher check is a separate, later step. --- ## 2. Source acquisition & provenance Two distinct source *roles*, both downloaded **in full** (never sampled) and provenanced: - **Ingested source** — the exact file the prototype pulls (e.g. NYCDOE InfoHub ELA/Math xlsx, `scripts/loaders/test-results.ts:12-23`). Used for **parse fidelity** (did their ingest faithfully reflect this file?). - **Independent reference** — an authoritative per-school figure from, where possible, a **different publisher** than the ingested source, so the check catches source-level problems, not just parse bugs (`pipeline/verify/references/base.py:9-16`). Used for **external truth**. ### 2.1 Reference store layout (read-only artifact, committed metadata) ``` data/reference/ sources/ // # one dir per fetch # the full file, verbatim provenance.json # see schema below manual/.csv # optional human-recorded spot values (manual_csv adapter) ``` `provenance.json` schema (one per fetched file): ```json { "source_id": "nysed_3to8_assessment", "role": "reference", // "ingested" | "reference" "publisher": "NYSED", "url": "https://data.nysed.gov/...", // exact download URL "retrieved_at": "2026-06-01T14:03:22Z", "http_status": 200, "sha256": "…", "bytes": 12345678, "format": "xlsx", "metrics_backed": ["g3_ela_proficiency", "g3_math_proficiency"], "notes": "Per-school 3-8 assessment DB; keyed by 12-digit BEDS." } ``` > Large source files are **git-ignored** (mirroring the prototype's `.gitignore:32-37`); > only `provenance.json` + the registry below are committed, so the *record* is in > version control while the bytes live in the (reproducible) reference store. A small > downloader records `provenance.json` and verifies the sha256 on re-fetch. ### 2.2 Source registry — `verify/sources.yaml` Human-and-machine-readable catalog driving replication. One entry per source file: ```yaml - source_id: nycdoe_infohub_ela role: ingested publisher: NYCDOE InfoHub url: https://infohub.nyced.org/.../school-ela-results-2018-2025-public....xlsx format: xlsx sheets: ["ELA - All", "ELA - SWD", ...] # which sheets carry per-school rows key_columns: {dbn: DBN, year: Year, grade: Grade, value: "% Level 3+4", n: "Number Tested"} join_key: DBN refresh: annual metrics_backed: [g3_ela_proficiency, g7_ela_proficiency, ...] - source_id: nysed_3to8_assessment role: reference publisher: NYSED url: https://data.nysed.gov/... # exact URL recorded at fetch time format: xlsx join_key: BEDS # -> DBN via crosswalk (§3.3) metrics_backed: [g3_ela_proficiency, g3_math_proficiency, ...] ``` (Exact reference URLs are filled in at first fetch and pinned in `provenance.json`; this spec names the publisher/role, not fabricated URLs.) --- ## 3. Validation method > **As built (PR B):** the shipped harness runs two checks — **(ii) base case** (served > vs an independent re-read of the ingested file) and **(iii) spot check** (served vs the > NYC School Snapshot). The three-layer reference model below (an independent-publisher > `reference_value` plus the `source_consistency` diagnostic) is the original design; the > different-publisher (NYSED) layer is **deferred**. `verify/METHODOLOGY.md` is > authoritative for the as-built design. Three independently-read layers, per `(dbn, year, metric_key, subgroup=ALL)`: | Layer | How obtained | Connector | |---|---|---| | `source_value` | re-read the **ingested source file** ourselves (independent parse) | `source` (§5.1) | | `served_value` | the value **the site serves** = read-only Postgres `school_year_metric.value` | `served` (§5.2) | | `reference_value` | the **official per-school figure** from the independent reference | `reference` adapter (§5.3) | Three comparisons (tolerance `tolerance_abs`, per metric): - **parse_fidelity** = `served_value` vs `source_value` → catches the prototype's ingest/parse bugs. - **external_truth** = `served_value` vs `reference_value` → **the headline correctness check.** - **source_consistency** = `source_value` vs `reference_value` → diagnostic: if `served == source` but both ≠ `reference`, the *source file* disagrees with the official reference (publisher discrepancy), not a parse bug. Status values: `match` | `MISMATCH` | `suppressed` | `unavailable` | `n/a`. We reuse the scaffold's `_status` + `summarize` + `write_report` verbatim (`reconcile.py:109-205`); only the layer-loaders are new (§5). ### 3.1 Suppression A cell suppressed by the publisher (`s`/`*`/`n<5`, per `_fetch.ts:111-115`) yields `reference_value.status = "suppressed"` and is excluded from agreement rates — never a mismatch. ### 3.2 Sampling & known cases Stratify by `borough` + `admission_bucket`, fixed `seed`, configurable `size` (`reconcile.py:82-105`). Always include a curated **known-case list** (a few schools whose official figures we read by hand) as a face-validity anchor. (The scaffold also always includes flagged outliers; in the base case there are no engine flags yet, so the sample = stratified + known-cases.) ### 3.3 Join keys / crosswalk NYC sources key on **DBN**; NYSED sources key on **12-digit BEDS**. Reference adapters reading NYSED data translate via the **DBN↔BEDS crosswalk** the prototype already loads (`scripts/loaders/dbn-beds-crosswalk.ts`); `cached_file` supports a crosswalk (`references/base.py:14-16`). Unmatched rows are **enumerated in the report, not dropped**. --- ## 4. Component → source → reference map Per-school externally-checkable components behind each of the spec's 8 outcomes. (`metric_key`s follow the prototype's convention, e.g. `test-results.ts:157`.) | Spec outcome | Component metric(s) | Prototype `metric_key` | Ingested source | Independent reference (publisher) | Join | Notes | |---|---|---|---|---|---|---| | 1. 3rd-gr ELA+Math | g3 ELA %L3+4; g3 Math %L3+4 | `ela_grade3_proficiency`, `math_grade3_proficiency` | InfoHub ELA/Math xlsx | **NYSED 3–8 Assessment DB** | DBN / BEDS | **pilot** | | 2. 7th-gr ELA+Math | g7 ELA/Math %L3+4 | `*_grade7_proficiency` | same xlsx | NYSED 3–8 Assessment DB | DBN/BEDS | reuses loader | | 3. Graduation (+CCR) | 4-yr grad rate | `graduation_rate_4yr` | **NYC Open Data `mjm3-8dw8`** | NYSED grad-rate DB | DBN/BEDS | InfoHub grad xlsx returns empty bodies → prototype falls back to Open Data (`graduation-results.ts:2,16`); cohort = 4-yr August | | 3. CCR | CCR rate | (school-quality key) | InfoHub School Quality | NYC Open Data / School Quality | DBN | confirm which CCR index (`spec §9.8`) | | 4. Bullying (incidents) | SSEC incident counts | (SSEC key) | NYSED SSEC | NYSED SSEC public file | BEDS | low-N, reporting-sensitive | | 5. Chronic absenteeism | % chronically absent | (attendance key) | InfoHub attendance | NYSED report-card / NYC Open Data | DBN | per-year reconcile (COVID regime irrelevant here) | | 4/6/7. Survey items | per-question positive % | `SurveyResponse.positive_pct` | NYC School Survey file | NYC School Survey published results | DBN | NYC-only publisher → independence limited; check at **question** grain | | 8. Course access | (directory inputs) | — | HS Directory xlsx | **none published** | DBN | parse-validation only | For the survey-derived rollups (#4 bullying, #6 trust, #7 recommend) the **externally-checkable unit is the underlying question's `positive_pct`**, not the rollup; the rollup itself is a **(iv) computed** check. --- ## 5. Architecture Lands in the prototype repo as a new additive Python tree (the scaffold's `pipeline/verify/` adapted), with its own `requirements.txt` + CI lane — the second-toolchain note in `04_contribution_plan.md §4` applies. Reuses the scaffold's reconcile **skeleton**; only the connectors are new. ``` verify/ sources.yaml # source registry (§2.2) components.yaml # per-component config (§5.5) download.py # full-file fetch + provenance.json + sha256 verify (read-only upstream) connectors/ served_db.py # read-only Postgres SELECT -> served_value source_file.py # independent xlsx/csv re-read -> source_value references/ # lifted from scaffold; each adapter records vintage base.py manual_csv.py cached_file.py socrata.py reconcile.py # scaffold skeleton: sample, compare(3 checks), summarize, report checks.py # optional internal gates (coverage, join-match rate) requirements.txt # numpy, pandas, pyarrow, PyYAML, pytest, psycopg[binary], openpyxl ``` ### 5.1 `source` connector — independent re-read Reads the ingested source file from the reference store (by `provenance.json`) using pandas (`read_excel`/`read_csv`), maps `key_columns` from `sources.yaml`, normalizes year/DBN, returns `{(dbn, year): value}`. Deliberately re-implements parsing **independently** of `test-results.ts` — divergence is the signal. ### 5.2 `served` connector — what the site serves (READ-ONLY) A read-only Postgres SELECT against `school_year_metrics` (+ `schools` for borough/admission strata). Returns `{(dbn, year, metric_key): {value, suppressed}}`. For the directly-ingested base metrics the served value **is** the displayed value (precomputed at ingest, read straight through — `lib/queries/schools.ts`), so this faithfully represents the site without scraping. (Display/formatting + the on-the-fly views — movers `delta`, decile selection — are out of scope here; covered cheaply by TS unit tests.) ### 5.3 `reference` adapters — official figure Scaffold interface (`references/base.py`): `get(dbn, year, metric_key) -> ReferenceValue{value,status,vintage}`. - **`cached_file`** (primary) — an independent **bulk official file** (e.g. NYSED assessment/grad DB) stored in the reference store; offline, deterministic, different publisher; crosswalk-aware. Matches the user's "download full data files." - **`manual_csv`** — human-recorded per-school values for known-cases / sources with no bulk file (survey item spot-checks). - **`socrata`** — NYC Open Data API (DBN-native, automatable) where a suitable dataset exists; note independence caveat if it mirrors the ingested publisher. ### 5.4 Read-only enforcement - Dedicated **read-only DB role**; connection via `VALIDATE_READONLY_DATABASE_URL` (separate from the loaders' `DIRECT_URL` superuser). - Connector issues **SELECT only**; no `INSERT/UPDATE/DELETE`; imports no module under `scripts/loaders/`. - All writes confined to `data/reference/**` and `docs/qa_reports/**`. ### 5.5 Per-component config — `verify/components.yaml` Mirrors the scaffold `reconciliation` block (`config/outcomes.yaml:39-50`): ```yaml - metric_key: ela_grade3_proficiency subgroup: ALL ingested_source: nycdoe_infohub_ela # -> sources.yaml reference_sources: [nysed_3to8_assessment] # cached_file reference_metric_key: g3_ela_pct_level34 tolerance_abs: 1.0 # pp; absorbs cross-publisher rounding sample: { size: 30, stratify_by: [borough, admission_bucket], seed: 12345 } known_cases: ["01M015 2023-24", ...] # hand-checked anchors ``` --- ## 6. Outputs - `data/reference/sources///provenance.json` — provenance for every fetched file. - `docs/qa_reports/__validation.{md,csv,json}` — per-cell table (3 statuses) + summary (agreement rate, parse breaks, unmatched, suppressed) + mismatch list. (Format = scaffold `write_report`, `reconcile.py:179-204`.) - `docs/validation_cards/.md` — the **replicable process card** (template §8.1): source URLs, sheets/columns, reference, join, tolerance, suppression, gotchas, sign-off. --- ## 7. Acceptance criteria (per metric; gate a release under `--strict`) 1. Provenance complete: `url` + `retrieved_at` + `sha256` + `bytes` recorded for every file used. 2. Join-match rate ≥ threshold; **all unmatched rows enumerated** (not silently dropped). 3. `external_truth` agreement rate reported; **0 external MISMATCH** and **0 parse_fidelity breaks** under `--strict` (exit nonzero otherwise — `reconcile.py:220-228`). 4. All known-case anchors checked and matching. 5. Suppressed cells reported as `suppressed`, not `MISMATCH`. 6. Validation card filled + signed off. --- ## 8. Pilot (one metric, end-to-end) → then replicate **Pilot = `ela_grade3_proficiency` (ALL subgroup).** Flagship academic input, clean per-school publication on both NYCDOE and NYSED, sets up the composite ((iv) computed) next. End-to-end: 1. `download.py` fetches the **full** InfoHub ELA xlsx (ingested) **and** the NYSED 3–8 assessment DB (reference); writes both + `provenance.json` (URL + date + sha256). 2. Implement `connectors/source_file.py` (ELA xlsx → per-school %L3+4) and `connectors/served_db.py` (read-only SELECT). 3. Wire `cached_file` reference adapter + DBN↔BEDS crosswalk; record `known_cases`. 4. Run `reconcile.py --metric ela_grade3_proficiency`; produce QA report + validation card. 5. Verify on a local branch (`kx/data-validation`), then **PR to mwg1378** for review. Replication to the rest = add a `sources.yaml` + `components.yaml` block + (if needed) a source reader per metric, reusing everything else. Order by source affinity: g7 (reuses ELA/Math reader) → graduation → chronic absenteeism → survey items → SSEC. #8 course access = parse-only. ### 8.1 Validation card template (`docs/validation_cards/.md`) ```markdown # Validation card: - Ingested source: - Independent reference: - Tolerance: ± () | Suppression: - Sample: size=<>, stratify=<>, seed=<> | Known cases: - Results: parse_fidelity=<>, external_agreement=<>, unmatched=<>, suppressed=<> - Mismatches investigated: - Sign-off: ``` --- ## 9. (iv) Computed values — derived-metric validation (follow-up) For composites / survey rollups: **re-derive the documented formula** in Python from the **base-case-validated components** and compare to the stored derived value (formula fidelity), within tolerance. No external layer. - `composite_es_academic` = mean(`ela_grade3_proficiency`, `math_grade3_proficiency`) — confirm against `compute-composites.ts:33` definition, recompute independently. - `survey_*` rollups = mean of matched-question `positive_pct` — confirm the question set + average against `compute-survey-metrics.ts:25-99`, recompute from validated question values. - Course access (#8): validate the directory inputs parse correctly; no external index exists. This phase **also** surfaces whether the prototype's derivation choices (which questions roll up, composite membership) are defensible — a methodology discussion for mwg1378, not just an arithmetic check. --- ## 10. Workflow Per `04_contribution_plan.md`: sync `main` → branch **`kx/data-validation`** → build the **(ii) base-case** pilot read-only → verify locally → **PR to mwg1378** (link this spec + gap #9) → merge → replicate per metric (small PRs) → **(iv) computed** as a separate branch. ## 11. To confirm with mwg1378 before coding - **Independent reference per metric** — NYSED bulk file (stronger, BEDS+crosswalk) vs NYC Open Data/Socrata (DBN-native, but may mirror the ingested publisher). Per the map in §4. - **Read-only DB role** provisioning (`VALIDATE_READONLY_DATABASE_URL`). - **Where the `verify/` tree lives** + adding the first **CI lane** for the Python test/gate. - **Tolerances** per metric family (rounding differences across publishers). - Survey independence: accept question-grain check against the NYC-published survey file (no second publisher exists).