# Data-validation findings — review packet for mwg1378 ((ii) base case + (iii) spot check)

> **Note (2026-06-09):** this file is the point-in-time review packet for PR #2.
> The **living findings ledger** (validation + data + analysis, with statuses) is
> [docs/design/08_absenteeism_findings.md](../docs/design/08_absenteeism_findings.md);
> per-metric methodology stays in [METHODOLOGY.md](METHODOLOGY.md).

> What to review: a read-only validation harness on branch **`kx/data-validation`**
> (the `verify/` package, `docs/qa_reports/`, and `verify/METHODOLOGY.md`). Everything
> is **additive + read-only** — no existing code, data, or site behavior changes.
> Decisions were defaulted (per your "implement locally, don't block") and are flagged
> below for sign-off.
>
> Naming — standardized validation taxonomy: **(i) completeness**; **(ii) correctness,
> base case** (DB vs the *exact ingested file*); **(iii) correctness, spot check** (DB vs
> a public-facing per-school report); **(iv) correctness, computed values** (derived
> metrics). `verify/METHODOLOGY.md` has the full definitions + reconciliation and is
> authoritative.

## Verification status & coverage

Rows = data categories (spec outcomes #1–#8 + derived). ✅ done/clean · ⚠️ partial/caveat · ❌ not done/blocked · — n/a.

| Data category | Ingested + prov | (ii) base case (vs ingested file) | (iii) spot check (vs NYC Snapshot) | (i) coverage / data gaps | Def doc |
|---|---|---|---|---|---|
| 3rd-gr ELA+Math (#1) | ✅ | ✅ 0 mism (4,664 / 4,663) | ⚠️ snapshot has no grade grain | 2019-20 & 2020-21 absent (COVID, in source); 8 source-only¹ | ✅ |
| 7th-gr ELA+Math (#2) | ✅ | ✅ 0 mism (2,765 / 2,759) | ⚠️ no grade grain | COVID yrs absent; 24 source-only | ✅ |
| School-wide ELA / Math² | ✅ | ✅ 0 mism (6,626 each) | ⚠️ ELA 73% / Math 62% within ±1.5pp; median ~0, tail to ~19pp | COVID yrs absent; 39 source-only each | ✅ |
| Chronic absenteeism (#5) | ✅ | ✅ 0 mism (10,639) | ⚠️ 67% within ±1.5pp (141/212); gap −12..+7.7pp | 148 source-only; **snapshot ref ends ~2022-23** | ✅ |
| Graduation 4-yr (#3) | ✅ | ✅ 0 mism (3,789) | ❌ HS varname not wired | source = NYC Open Data (InfoHub grad file empty), ~1 cohort behind; 3 source-only | ✅ |
| College/Career Readiness (#3) | ❌ stub | — | — | not ingested (0 rows) | ⚠️ |
| Survey → bullying/trust/recommend (#4/#6/#7) | ✅ 6 files | ✅ 0 mism (826,354) | — same-publisher | parent/family skipped (files exist); large source-only³; latest published = 2023-24 (current) | ✅ |
| Bullying — SSEC incidents (#4) | ❌ stub | — | — | not ingested (0 rows) | ⚠️ |
| Course access (#8) | ❌ stub | — | — no official source | not ingested (0 rows) | ⚠️ |
| Derived (composites, rollups) | — | ❌ (iv) pending | — | n/a | ⚠️ (iv) |

¹ "source-only" = school-years in the source file but not served — DBNs absent from the `schools` directory (every loader skips unknown DBNs, `test-results.ts:132`). Fix: refresh the directory loaders + re-ingest. ² school-wide ELA/Math (the file's "All Grades" row) are **auxiliary** metrics added to enable the school-wide (iii) spot check (the snapshot exposes no grade breakout). ³ survey source-only is large esp. for teacher files; partly the DBN cause, partly not yet explained.

**Headlines.**
- **(ii) base case — clean.** 0 mismatches across **~29,000 core metric cells** (the 6 rows in Findings) + **13,252 school-wide aggregate cells** + **826,354 survey cells**; 0 phantom values. Where the site shows a number, it matches the ingested file exactly (within rounding).
- **(iii) spot check — context, not a gate.** Against NYC's School Snapshot (same-publisher *presentation* fidelity, 300-school sample), school-wide proficiency agrees within ±1.5pp for **73% (ELA) / 62% (Math)**; the median gap is ~0 but a real **tail reaches ~19pp at both small *and* large N** (e.g. Math `14K318` −9.4pp at N=439; absenteeism `28Q157` −12pp at N=1693). This is **not an ingest error** (the base case is exact); it reflects a different population basis — NYC's **accountability cohort** (Oct-31-register-attributed) vs InfoHub's all-tested count. The *direction* is explained by documented attribution rules (out-of-district testers attributed separately pre-2025; NYSED exclusions; the 2024-25 math waiver — see METHODOLOGY.md); the *size of the largest individual gaps is not established*. Absenteeism differs similarly by an (undocumented) denominator.
- **One real issue (coverage).** Every metric drops some school-years present in the source — **215 cells** across the 6 core metrics — because those DBNs aren't in the `schools` directory and loaders skip unknown DBNs. Recommend a directory refresh + re-ingest.

**Recent-year gap sources (checked).** Absenteeism cross-check: NYC Open Data school-level ends 2020-21 and the snapshot ends ~2022-23, but **NYSED's ESSA Report Card publishes per-school chronic absenteeism for 2023-24** (`data.nysed.gov`; needs BEDS crosswalk; different definition). Survey: **no 2024-25 survey is released yet** (latest = 2023-24, which we have), so we are current; parent/family files exist but are uningested.

**Open asks for you:** the 3 stub loaders (CCR, SSEC incidents, course access) need building (write-path / your domain) before those outcomes can be validated; confirm the defaulted decisions below.

## Method ((ii) base case — what "faithful" means here)
Per `(dbn, year)`, for each component, compare:
- `served_value` — what the site serves (read-only `SELECT` on `school_year_metrics`)
- `source_value` — an **independent re-read** of the stated source file (no loader code imported)

within ±1.0pp. Suppressed cells → reported, not a mismatch. Cells present on only one
side → enumerated as coverage gaps, never silently dropped. The base case compares
**every** cell (all-local). The **(iii) spot check** samples ~300 schools/metric against
the NYC School Snapshot. Design: `docs/design/05_validation_spec.md`.

## Decisions defaulted (please confirm)
1. **(ii) base case first** — validate against the *stated source file*. The **(iii) spot check** uses the **NYC School Snapshot** (same-publisher presentation fidelity). A different-publisher independent check (NYSED) is **deferred** — and NYC-vs-NYSED discrepancies are NY State's concern, not ours.
2. **Source per metric:** graduation's *stated source is already NYC Open Data* (`mjm3-8dw8` — the InfoHub grad file returns empty bodies, `graduation-results.ts:2`), so we re-read that; the InfoHub xlsx files are the stated source for tests/attendance.
3. **Tolerance:** ±1.0pp for the base case; ±1.5pp for the spot check (absorbs the snapshot's whole-percent rounding).
4. **Read-only access:** a dedicated `validator_ro` role (SELECT-only) + a forced read-only session; stored in `.env.local`, never committed.
5. **Toolchain:** Python sidecar (`verify/`) with its own `requirements.txt`; needs the repo's **first CI lane** eventually (none today).

## Findings — (ii) base case (live DB, read-only, exhaustive)

| Component | served cells | value matches | suppressed | mismatches | source-only (dropped) |
|---|--:|--:|--:|--:|--:|
| `ela_grade3_proficiency` | 4,664 | 4,646 | 18 | **0** | 8 |
| `ela_grade7_proficiency` | 2,765 | 2,757 | 8 | **0** | 24 |
| `math_grade3_proficiency` | 4,663 | 4,643 | 20 | **0** | 8 |
| `math_grade7_proficiency` | 2,759 | 2,748 | 11 | **0** | 24 |
| `chronic_absenteeism_rate` | 10,639 | 10,635 | 4 | **0** | 148 |
| `graduation_rate_4yr` | 3,789 | 3,736 | 53 | **0** | 3 |
| **total (6 core)** | **29,279** | — | — | **0** | **215** |

Auxiliary school-wide metrics (added for the spot check): `ela_all_proficiency` 6,626
(61 suppressed, 39 source-only) and `math_all_proficiency` 6,626 (177 suppressed, 39
source-only) — **0 mismatches** each. `served-only` (phantom values not in the source)
= **0** everywhere. Full per-cell reports: `docs/qa_reports/<metric>__validation.{md,json}`;
spot-check reports: `docs/qa_reports/<metric>__snapshot.{md,json}`.

### Interpretation
- ✅ **Ingest is faithful.** Where the site shows a number, it matches the source file exactly (within rounding) — no value corruption, unit errors, or made-up data, across all metrics.
- ⚠️ **Coverage gap (the one issue).** 215 school-years are in the source files but absent from the site. Confirmed root cause for the `ela_grade3_proficiency` set (4 schools — `07X642`, `19K938`, `20K939`, `26Q390` in 2023-24/2024-25): **those DBNs aren't in the `schools` table at all**, and every loader skips rows whose DBN isn't already in the directory (`scripts/loaders/test-results.ts:132`, `graduation-results.ts:96`, `attendance.ts:102`). They look like recently-opened schools the directory snapshot predates.
  - **Recommended fix (yours to decide):** refresh the school-directory loaders (`all-school-directories.ts` / `nyc-od-schools.ts`) to include these DBNs, then re-run the metric loaders — the dropped school-years will then appear. Worth confirming the larger absenteeism gap (148) is the same cause.

## The diff (`kx/data-validation` — additive + read-only)
```
verify/
  README.md  METHODOLOGY.md  requirements.txt  common.py  __init__.py
  sources.yaml  components.yaml  snapshot.yaml      # source registry + per-metric + spot-check config
  download.py                                       # full-file/Socrata fetch + provenance (url, retrieved_at, sha256)
  connectors/
    served_db.py  served_survey.py                 # read-only Postgres (SELECT only)
    source_file.py  source_socrata.py  source_survey.py   # independent re-reads of the ingested source
    reference_snapshot.py                           # NYC School Snapshot per-school API (the (iii) reference)
  reconcile.py                                      # (ii) base case: served vs source -> report
  reconcile_snapshot.py                             # (iii) spot check: served vs NYC Snapshot (sampled)
  reconcile_survey.py                               # (ii) base case at the survey grain
  tests/test_reconcile.py                           # unit tests for the compare core
data/reference/sources/<source>/<date>/provenance.json   # provenance per fetched file (bytes gitignored)
docs/qa_reports/<metric>__validation.{md,json}           # (ii) base-case findings
docs/qa_reports/<metric>__snapshot.{md,json}             # (iii) spot-check findings
.gitignore                                               # ignore large source snapshots + per-cell CSVs
```
Run it yourself: set `VALIDATE_READONLY_DATABASE_URL` in `.env.local`, then
`pip install -r verify/requirements.txt` and
`python -m verify.reconcile --metric ela_grade3_proficiency`
(spot check: `python -m verify.reconcile_snapshot --metric ela_all_proficiency`).

## What we'd like from you
1. **Sanity-check the decisions** above (esp. #1 scope + the (iii) reference choice, #4 access model).
2. **The coverage gap** — agree it's a directory-staleness issue worth a refresh + re-ingest? Anything you know about those 4 DBNs?
3. **Next steps** — OK to add the **different-publisher (NYSED) independent check** and the first **CI workflow** to run `verify/` under `--strict`? And to wire the HS graduation spot check?

## Caveats / limits
- The **(ii) base case** confirms fidelity to the **source file**, not that the source itself is correct (that's the deferred independent check).
- The **(iii) spot check** is **presentation fidelity** vs NYC's own Snapshot (same data family) — context, not an independent oracle, and not a pass/fail gate (documented population differences; see the tail above).
- `ALL` subgroup only for v1 (matches the prototype's outlier scope).
- Display-layer formatting and the on-the-fly views (movers delta, decile selection) aren't covered here — they're thin and can be unit-tested separately.
- Derived metrics (composites, survey rollups, course-access) are **(iv) computed values** (formula re-derivation), not in this packet.