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

verify/METHODOLOGY.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.

Metric definitions & reconciliation notes

How each validated metric is defined as we ingest it, how that differs from NYC's official School Quality Reports (SQR) / Snapshot presentation, and what is (and isn't) reconcilable. Written from the validation work in verify/ (see docs/design/05_validation_spec.md, docs/design/06_mwg1378_packet.md). Sources for the SQR definitions: the per-school SQR PDFs (nycenet.edu/OA/SchoolReports/<year>/<DBN>_{SQR_Scoring_Guide|School_Quality_Guide}_<yy>_<EMS|HS>.pdf) and the per-year Educator Guide to the School Quality Reports (InfoHub).

Scope note: this records what is verified (a quoted definition, or a measured result) vs what is not established. Unexplained gaps are labeled as such, with ways to narrow them — not filled with a plausible mechanism.

The four validation checks (terms used throughout)

  • (i) Completeness — are years or schools missing?
  • (ii) Correctness — base case — does the DB match an independent re-read of the exact source file the loader ingested? (the binding correctness gate)
  • (iii) Correctness — spot check — does the DB match NYC's public-facing per-school report (the School Snapshot / SQR)? (a presentation cross-reference — see the caveat below; not an independent oracle)
  • (iv) Correctness — computed values — are derived metrics (composites, survey rollups) computed correctly from validated inputs? (not done yet)

Core distinction (applies to every metric)

We ingest the raw, all-tested / all-enrolled figures published in the NYCDOE InfoHub bulk files (and NYC Open Data for graduation). NYC's SQR/Snapshot publishes figures on an accountability cohort — students attributed to a school by its October-31 audited register ("N: the number of students contributing to the metric") — rounded to whole percents. So our values and the Snapshot's are different population cuts of the same underlying data, not competing measurements, and the two N's can differ in either direction (see Test proficiency). The operative cut is per-metric: for the test metrics it is the documented accountability cohort; for chronic absenteeism it turned out to be the grade band (K–8 / 9–12 — see that section). The base case (ii) is binding. The spot check (iii) is the Snapshot — NYC's own report (same data family), so it is not an independent ground-truth oracle; a truly independent publisher (NYSED) is a separate, deferred check.

Per-metric definitions

Test proficiency — ela_grade3/7_proficiency, math_grade3/7_proficiency, *_all_proficiency

  • Ours (InfoHub / NYS 3–8 tests): % Level 3+4 = (# scoring Level 3 or 4) ÷ (# tested), for all tested students at the grade. *_all_proficiency is the file's "All Grades" aggregate.
  • (ii) Base case — exact. Served % = the file's %L3+4 with 0 mismatches on every metric: g3 ELA 4,664 / Math 4,663; g7 2,765 / 2,759; school-wide ela_all 6,626 (6,565 + 61 suppressed) / math_all 6,626 (6,449 + 177). The file's "All Grades" Number Tested = sum of its grade 3–8 counts (no aggregation artifact). Reports: docs/qa_reports/*_proficiency__validation.md.
  • Snapshot/SQR figure. val_prof_pct_*_all (% Level 3+4), plus "Average Student Proficiency" (a 1–4 mean — the scored metric; %L3+4 is weighted 0%). The SQR PDF "% at Level 3 or 4" and the snapshot API val_prof_pct_ela_all are the same figure (verified 57/57 schools across 2022-23/2023-24/2024-25: identical N; the snapshot rounds the % to an integer).
  • Denominator (documented, stable 2021-22 → 2024-25) — per each year's EMS Educator Guide: students who (a) are on the school's October-31 audited register AND (b) took the exam; attributed by the Oct-31 register.
  • Verified N relationship (SQR-PDF N vs InfoHub "Number Tested"): 2022-23 SQR N > InfoHub (≤ in 6/19; mean ≈ +5); 2023-24 > (≤ in 5/19; e.g. 15K136 497 vs 469); 2024-25 mostly ≤ (≤ in 44/50). So the cohort is not a subset of test-takers and is not bounded by "Number Tested."
  • (iii) Spot check — agreement, stated honestly (two different checks; don't conflate):
    • Snapshot-API reconciliation (committed; all years; 300-school stratified sample; ±1.5pp): ela_all 73% agreement (216/295; 79 mismatch); math_all 62% (181/290; 109 mismatch). Central tendency is tight (median gap +0.4pp ELA / 0.0pp math), but the distribution has a wide tail: in-sample signed gaps run −3 to +11pp (ELA) and −13 to +19pp (math). Reports: docs/qa_reports/*_all_proficiency__snapshot.md.
    • SQR-PDF hand-check (recent-year-weighted subset): mean |SQR%−InfoHub%| ~0.6–1.3pp; 88% within 1pp in 2024-25. That "~1pp" is this small recent-year subset — not the overall agreement.
  • Documented explanation of the N difference + year-flip (attribution rules, not the formula):
    • Out-of-district testers — the main driver of the flip. Per the InfoHub test-file NOTES: "Starting in 2025, students enrolled in a NYC PS school outside of their zoned district are attributed to the school where the exam was administered. For prior years, these students were considered Out of District Placement testers and attributed separately." So pre-2025 InfoHub drops out-of-district testers from a school's count while the SQR (register) includes them → SQR > InfoHub; from 2024-25 InfoHub includes them at the exam school → SQR ≤ InfoHub. Matches the observed direction.
    • NYSED-style exclusions. Per the SQR Educator Guide, NYSED (and InfoHub, which reports NYS results) excludes IEP K-8 students whose home address is in a different community school district, and students not continuously enrolled BEDS-Day→exam; NYCPS/SQR includes them.
    • Math double-testing waiver (2024-25, grades 7–8 math). NYCPS imputes proficiency for Regents-takers and includes them; InfoHub counts only State-test-takers.
    • Residual — direction explained, tail size NOT established. The documented rules account for the direction of the N difference and the 2024-25 flip, but not the size of the largest individual gaps. The big disagreements are not just small-N noise: math 16K267 2023-24 is +18.9pp at N=89, math 14K318 2022-23 is −9.4pp at N=439, absenteeism 28Q157 2018-19 is −12pp at N=1693. A small N can amplify a gap (one student ≈3pp at N≈34 — e.g. ELA 13K054 2024-25 +7.8pp at N=34), but that does not explain the large-N cases. Way to narrow: per-school-year decomposition of snapshot N vs InfoHub N by attribution group — not done.
  • Corrections to earlier drafts: (1) the cohort is not a subset of test-takers (it exceeds InfoHub N in 2022-23/2023-24). (2) The max(tested, 95% of continuously enrolled) participation floor belongs to NYC's separate ESSA/StART Weighted Academic Achievement Index, not this Snapshot metric. (3) the "~1pp" headline was the SQR-PDF recent-year subset, not the overall snapshot agreement (73% / 62% within ±1.5pp). (4) "valid-score/vintage noise" is withdrawn.

Chronic absenteeism — chronic_absenteeism_rate

  • Ours (InfoHub attendance file): % Chronically Absent = # Chronically Absent ÷ # Contributing (10+ total days, 1+ present day). Chronically absent = absent ≥10% of enrolled days.
  • (ii) Base case — exact. Served = #CA ÷ #Contributing to 2 dp, recomputed from raw counts (10,639 cells, 0 mismatches).
  • (iii) Spot check (student_chronic_absent) — largely explained (2026-06-10 exhaustive census, docs/qa_reports/snapshot/; replaces the 300-school sample). All 6,570 comparable district school-year pairs, SY 2018-19 → 2022-23 (snapshot side: the committed bulk fetch data/quality/snapshot-values.json): school-wide agreement within ±1.5pp is 0.6534 — the sample's "~67%" confirmed, with the full tail −26.84 to +15.83pp. Basis established: the Snapshot displays the SAME-YEAR GRADE-BAND rate, not the school-wide rate — grades K–8 (K-inclusive) on EMS reports, grades 9–12 on HS reports. Re-compared on the matching band, agreement rises to 0.8639 pooled / 0.9223 EMS-report (≥0.94 for EMS report-years 2020–2023), and the entire worst-15 list is band-explained: the old "−12pp at large N" cases were middle schools served the HS report (worst case 28Q157 SY 2021-22, −26.84pp school-wide — the Snapshot's 54% is its grade-9 rate, 19/35, exactly) and K-8 schools whose school-wide rate differs by their PK rows. This supersedes the earlier "suspected accountability-cohort denominator" for district schools. Residual not established: HS reports in SY 2019-20/2021-22 (band agreement ~0.49 with a one-sided +1.5pp median shift, served above snapshot) and SY 2018-19 (0.80–0.83 both report types).
  • (iii) Report-years 2024/2025 — renamed varnames decoded (ledger A.7, verified). val_chronic_absent_{ems,hs}_all stores the complement: round(100 − same-report-year band rate) (EMS: K–8 K-inclusive; HS: 9–12) — i.e., the share NOT chronically absent. Decoded agreement at ±1.5pp: 0.9710/0.9740 (ry2024 EMS/HS) and 0.9665/0.9814 (ry2025); every raw-value candidate across 26 tested alignments maxes out at 0.042, which is why the earlier 4-school spot check "matched neither year". The series changed sign convention in 2024, not population or period. What label the rendered page attaches is not established (the page does not visibly display CA for 2023-24+).
  • (iii) Charters (Snapshot) — partially independent (ledger A.19, verified; docs/qa_reports/snapshot/PROVENANCE.md). The Snapshot's charter CA values are computed, not copied from NYSED (17.5–19.8% rounding-match vs ~100% expected for republication; values exist where NYSED suppresses; K-inclusive enrollment verified at scale) and track NYSED only loosely (median |diff| 1.79pp matched-band, r 0.73–0.83 vs district r 0.99 — why not established). Reporting outages co-move with the state chain 82/82 (DATA_REP_FLAG=N school-years 2021–2025, zero counterexamples): it can catch gross serving errors, cannot catch state-chain reporting failures, cannot arbitrate <2pp.
  • Harness follow-up (NOT done in this PR): verify/snapshot.yaml still compares student_chronic_absent against the school-wide served value; it should be re-pointed at the K–8 / 9–12 band aggregates (and taught the 2024/2025 complement varnames) so the recurring (iii) check runs on the established basis.
  • SQR scored attendance metric is different: "Percentage of Students with >90% Attendance" (documented in the SQR PDF) — a student-level distribution statistic, not chronic absenteeism, and not in the InfoHub bulk file (which has % Attendance, % Chronically Absent, day counts only). Not reproducible from what we ingest.
  • Coverage: the Snapshot page displays absenteeism only ~2018-19 → 2022-23; its data feed carries 2023-24/2024-25 under the renamed complement varnames above (decoded, usable as a reference); NYSED remains the independent recent-year reference (see Coverage).
  • Expansion (2026-06-09): the same file now also backs average_daily_attendance (% Attendance, N = # Total Days) and per-grade keys *_grade_{pk,k,1..12} for both metrics; (ii) components exist for all (subgroup ALL), and the ADA base case is exact (10,639 cells, 0 mismatches on the dev replica — regenerate against prod after ingest).
  • (iii-b) Cross-publisher (NYSED Report Card Database) — done. NYSED publishes school-level CA (grades 1–8 / 9–12, BEDS-keyed) independently of the DOE file; joined via the deterministic BEDS→DBN rule (zero collisions). Result (docs/qa_reports/nysed/, expanded 2026-06-10 to all comparable years — 2018-19 and 2020-21 → 2024-25): ten of twelve (year × grade-band) panels reconcile at the median (median per-school diff ≤0.4pp) — the strongest external validation of the ingested attendance data to date. The second failure besides 2024-25 EM is 2022-23 HS (median −0.60pp, only 47% within ±1pp, r 0.779) — the visible edge of a COVID-era HS tail (62/112/129 high schools >5pp below NYSED in 2020-21/2021-22/2022-23 on largely byte-identical denominators; publisher-level; hidden by median-based summaries; cause not established; ledger A.20, docs/qa_reports/nysed/RECONCILIATION.md §9). Exception: 2024-25 grades 1–8 diverges systematically (DOE falling vs NYSED rising on the same ~1,115 schools; numerator-side; weighted 29.0% vs 30.8%; no precursor — EM medians stay within ±0.4pp in every earlier panel). Document review rules out any documented 2024-25 definition change on either side; NYSED's standing suspension/extended-medical exclusion predicts the opposite sign; one documented candidate remains (SIRS defaults missing/unpaired attendance records to "absent, unexcused"); cause not established — NYSED inquiry drafted. Until resolved, treat 2024-25 grades-1–8 CA as publisher-dependent (carry source attribution). NYSED coverage limits: D75/D79 have zero rows in the CA tables; Success Academy stopped reporting attendance network-wide in 2023-24 (DATA_REP_FLAG=N).

Graduation — graduation_rate_4yr

  • Ours: 4-year August cohort grad rate, from NYC Open Data mjm3-8dw8 (report_category='School' AND cohort='4 year August'; grads_1). The prototype's stated source is Open Data here — the InfoHub grad file returns empty bodies.
  • (ii) Base case — exact vs the Open Data resource (3,789 cells, 0 mismatches).
  • (iii) Spot check: not yet wired (HS Snapshot varname unconfirmed).

Survey — survey_responses.positive_pct (and the survey_* rollups)

  • Ours (NYC School Survey "Pos & Neg %" sheet): top-2-box positive % per (school, year, question, respondent). Stored as percent (source is a 0–1 fraction → ×100). The positive column is the favorable bucket (Agree/Strongly agree side), sometimes the first of the two question columns.
  • (ii) Base case — exact: 0 mismatches across 826,354 served survey cells, given the column convention. Caveat: column selection mirrors the loader's interpretation.
  • (iii) Spot check: the Snapshot's "School Percent Positive" is a measure-group average on the same survey (same publisher); not run as an external check. The named survey_* DB metrics are derived rollups → check (iv).

What is NOT reconcilable from the bulk files

  • NYC's accountability cohort (Oct-31-register-attributed) — the register/attribution flag is derived inside NYC's accountability pipeline and isn't in the InfoHub downloads.
  • The SQR's % with >90% attendance — needs student-level attendance distributions not in the bulk file.
  • Grade-3/7 proficiency from the Snapshot — the Snapshot API is school-wide only.

Coverage & data gaps

Distinct from value-correctness (above): what's missing, and whether a source exists to fill it.

  • Years missing in the source itself. Test results have no 2019-20 / 2020-21 (NYSED cancelled 2020; 2021 ~21% participation — documented in the InfoHub test-file NOTES). Affects any trend/year analysis. Not fillable.
  • Site missing rows that exist in the source ("source-only"). ~215 test/grad/absenteeism school-years are in the source but not served (ela_g3 8, ela_g7 24, math_g3 8, math_g7 24, absenteeism 148, grad 3; school-wide ela_all/math_all 39 each); survey is much larger (student ~13k, teacher ~80–109k per file — partly the same cause, partly not yet explained). Cause: those DBNs aren't in the schools directory and every loader skips unknown DBNs (test-results.ts:132). Fix: refresh the directory loaders (all-school-directories.ts / nyc-od-schools.ts), then re-ingest.
  • Spot-check reference coverage limits. Snapshot student_chronic_absent ends ~2022-23 (the 2023-24/2024-25 feed values live under the renamed complement varnames — decoded per ledger A.7); NYC Open Data school-level absenteeism (gqq2-hgxd) ends 2020-21; Open Data test datasets (iebs-5yhr/74kb-55u9) end 2022-23; the Snapshot API exposes no grade-3/7 breakout.
  • Whole metrics not ingested. CCR, SSEC bullying incidents, course access — loaders are stubs (0 rows).
  • Survey scope. Parent/family surveys are not loaded (the loader skips them); the files do exist on the InfoHub survey archive. The latest published survey is 2023-24 (= the "2024" file we have) — there is no 2024-25 survey released yet, so we are current, not behind.

Sources that can fill recent-year gaps

  • Chronic absenteeism spot-check (recent years). Our absenteeism data covers through 2024-25 (base case exact); the gap is a reference. NYSED ESSA Report Card (data.nysed.gov/downloads.php) publishes per-school chronic absenteeism for 2023-24 — a different publisher (a true independent check). Caveats: BEDS→DBN crosswalk (already loaded), and NYSED's definition differs from NYC's. 2024-25 NYSED likely not released yet.
  • Parent/family survey. The K-12 Family / Pre-K Family files on the InfoHub survey archive (currently uningested).
  • CCR / SSEC incidents / course access. Sources listed in SPECS.md; needs the stub loaders built (write-path; mwg).

Practical guidance

  • Treat the base case (ii) (faithful to the ingested file) as the binding correctness gate.
  • Treat Snapshot/Open-Data agreement (spot check (iii)) as context — the central tendency matches (median gap ~0) but a real tail disagrees by up to ~10–19pp at both small and large N. For chronic absenteeism that tail is now largely explained (the Snapshot shows grade-band rates — compare against the band, residual pockets noted above); for the test metrics the tail's cause is not individually established. Do not "fix" our values toward the Snapshot — the base case (ii) is exact vs the file.
  • If matching NYC's published number becomes a requirement, ingest the SQR/Snapshot figure as a second source (the cohort %), rather than recomputing from the bulk files.