PhiladelphiaMethodologyMethodology recipe

Computed-value (iv) validation: OSS + PSES rollups agree to the cell with the raw source

A reproducibility log: the data this analysis touched, the queries it ran, and how a third party would re-run it.

Summary

(iv) Computed-value validation: re-derives OSS distribution metrics, PSES topic rollups, and demographic shares from raw source files; compares to served DB.

Data sources

Database tablephilly_school_year_metrics
Long-format facts. PK (school_ulcs, year, metric_key, subgroup, population_cut). Carries comparison_group_percentile + citywide_percentile (direction-adjusted).
External datasetSDP School Performance (OpenDataPhilly CDN)
External datasetPhilly School Experience Survey
Loader scriptpipeline_philly/verify/reconcile_computed.py

Steps

Re-derive each computed metric family from the cached source files.
Compare per-cell to served DB, tolerance ±0.5pp (tighter than (iii) since checking math, not cross-publisher).

Caveats

OSS and PSES achieve 100% agreement. Demographics show 94% because pct_econ_disadv (CEP) is reported per-grade row but populated once per school in the loader; my re-derivation logic handles NaN slightly differently. Underlying data is correct.

Reproduce

All source files cached. Run reconcile_computed.py.

The recipe lives at data/cities/philly/stories/recipes.ts in the repo.