Find What Works: Schools
← All source documentsRaw file
Source document

verify/README.md

Served verbatim from the project repository. Internal working document conventions apply: documents may reference file paths, branch names, and findings-ledger anchors from the repo.

verify/ — read-only data validation harness

Confirms the data the site serves is correct, across four checks (definitions + findings in verify/METHODOLOGY.md; design rationale in docs/design/05_validation_spec.md; closes gap #9 in docs/design/02_gap_analysis.md):

  • (i) Completeness — are years/schools missing? (surfaced as "source-only" / coverage gaps)
  • (ii) Correctness — base case — DB vs an independent re-read of the exact source file the loader ingested. (the binding check)
  • (iii) Correctness — spot check — DB vs NYC's public-facing per-school report (the School Snapshot / SQR). (presentation cross-reference; the Snapshot is NYC's own report — same data family — so this is not an independent oracle. A truly independent publisher (NYSED) is identified but not yet wired.)
  • (iv) Correctness — computed values — derived metrics (composites, survey rollups). (not done yet)

This is a sidecar. Python (the app is TS); read-only — it never writes to the app DB or source data, only to data/reference/ (downloaded snapshots + provenance) and docs/qa_reports/ (its reports). It imports no scripts/loaders/ code — verification is independent of how the prototype computes.

Modules

  • download.py — fetch a source in full + write provenance.json (url, retrieved_at, sha256, bytes); handles xlsx/csv files and the Socrata API.
  • connectors/served_db.py — read-only Postgres read of what the site serves.
  • connectors/source_file.py, source_socrata.py — independent re-read of the ingested source (file / NYC Open Data).
  • connectors/served_survey.py, source_survey.py — survey grain (dbn, year, question, respondent).
  • connectors/reference_snapshot.py — NYC School Snapshot per-school JSON API (the spot-check reference).
  • reconcile.py — (ii) base case: served vs source file → docs/qa_reports/*__validation.{md,json}.
  • reconcile_snapshot.py — (iii) spot check: served vs Snapshot (sampled) → *__snapshot.{md,json}.
  • reconcile_survey.py — survey (ii) base case.
  • Config: sources.yaml (ingested sources), components.yaml (per-metric base-case config), snapshot.yaml (metric → Snapshot varname for the spot check).

Setup

python3 -m venv verify/.venv && source verify/.venv/bin/activate
pip install -r verify/requirements.txt

The served + spot-check runs read the DB. Set a read-only connection string (never commit it); the harness also auto-loads it from .env.local:

export VALIDATE_READONLY_DATABASE_URL="postgresql://<readonly_role>:...@host:5432/postgres?sslmode=require"

Provision a dedicated read-only role (no INSERT/UPDATE/DELETE grants), separate from the loaders' superuser DIRECT_URL; the connector issues SELECT only and forces a read-only session.

Run (from repo root)

# (ii) base case — fetch the source, then reconcile DB vs the exact file
python -m verify.download --source nycdoe_infohub_ela
python -m verify.reconcile --metric ela_grade3_proficiency        # -> docs/qa_reports/..._validation.{md,json}

# (iii) spot check — DB vs the NYC School Snapshot (sampled, ~300/metric)
python -m verify.reconcile_snapshot --metric ela_all_proficiency  # -> ..._snapshot.{md,json}

# survey base case
python -m verify.download --source nyc_survey_student_2023-24
python -m verify.reconcile_survey --source nyc_survey_student_2023-24

Status

  • ✅ (ii) base case — done for test proficiency (g3/g7 + school-wide), chronic absenteeism, graduation, survey responses: 0 mismatches across ~29k metric cells + ~826k survey cells (docs/qa_reports/).
  • ✅ (iii) spot check — school-wide ELA/Math + chronic absenteeism vs the NYC Snapshot (sampled). Differences are documented population/cohort effects, not errors (see METHODOLOGY.md). Graduation (HS) not yet wired.
  • ⬜ (i) completeness — coverage gaps reported (source-only rows; source-missing years); see METHODOLOGY "Coverage & data gaps."
  • ⬜ (iv) computed values — composite / survey-rollup formula validation (not started).
  • ⬜ Independent (different-publisher) reference — NYSED ESSA Report Card (per-school chronic absenteeism, 2023-24) is identified but not wired; the snapshot spot-check is same-publisher.