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; per-metric methodology stays in 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.

¹ "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)

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.