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.

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/
    <source_id>/<YYYY-MM-DD>/        # one dir per fetch
      <original_filename>            # the full file, verbatim
      provenance.json                # see schema below
  manual/<metric>.csv                # optional human-recorded spot values (manual_csv adapter)

provenance.json schema (one per fetched file):

{
  "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:

- 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):

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_keys follow the prototype's convention, e.g. test-results.ts:157.)

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):

- 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/<source>/<date>/provenance.json — provenance for every fetched file.
• docs/qa_reports/<metric>__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/<metric>.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/<metric>.md)

# Validation card: <metric_key>
- Ingested source: <source_id, url, retrieved_at, sha256, sheets/columns>
- Independent reference: <publisher, source_id, url, retrieved_at, sha256, join key>
- Tolerance: ±<tolerance_abs> (<unit>) | Suppression: <handling>
- Sample: size=<>, stratify=<>, seed=<> | Known cases: <list + official values>
- Results: parse_fidelity=<>, external_agreement=<>, unmatched=<>, suppressed=<>
- Mismatches investigated: <notes>
- Sign-off: <name, date, commit>

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).