docs/cities/philly/sources.yaml
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.
# Source registry for the Philly build — follows the schema of verify/sources.yaml.
# `role: ingested` = the exact file the prototype pulls (base case (ii)).
# `role: reference` = a different-publisher official per-school figure (independent check).
#
# All sheet names, column names, and stable URLs are marked TBD until first fetch (P1).
# After first fetch, populate the TBDs and snapshot the file (sha256 + retrieved_at + bytes
# via verify-philly/download.py, mirroring verify/download.py).
#
# Year encoding conventions:
# year_format: end_year_int -> "2025" means 2024-25 school year
# year_format: passthrough -> already YYYY-YY in source
# year_format: cohort_plus3 -> cohort start year + 3 yields grad school year
# ─────────────────────────────────────────────────────────────────────────────
# PSSA — Pennsylvania System of School Assessment (grades 3–8 ELA + Math)
# ─────────────────────────────────────────────────────────────────────────────
- source_id: pde_pssa
role: ingested
publisher: Pennsylvania Department of Education
url: TBD # https://www.pa.gov/agencies/education/data-and-reporting/assessment-reporting
format: xlsx
sheet: TBD # multiple sheets, one per (subject, grade); enumerate after first fetch
year_format: end_year_int
columns:
school_code: TBD # PDE 9-digit Public School Code
grade: TBD
year: TBD
subgroup: TBD
value: "Percent Proficient and Advanced" # provisional
n: "Number Tested" # provisional
join_key: school_code
refresh: annual
metrics_backed:
- pssa_grade3_ela_proficiency
- pssa_grade3_math_proficiency
- pssa_grade4_ela_proficiency
- pssa_grade4_math_proficiency
- pssa_grade5_ela_proficiency
- pssa_grade5_math_proficiency
- pssa_grade6_ela_proficiency
- pssa_grade6_math_proficiency
- pssa_grade7_ela_proficiency
- pssa_grade7_math_proficiency
- pssa_grade8_ela_proficiency
- pssa_grade8_math_proficiency
- pssa_all_ela_proficiency
- pssa_all_math_proficiency
# ─────────────────────────────────────────────────────────────────────────────
# Keystone Exams — HS end-of-course (Algebra I, Literature, Biology)
# ─────────────────────────────────────────────────────────────────────────────
- source_id: pde_keystone
role: ingested
publisher: Pennsylvania Department of Education
url: TBD
format: xlsx
sheet: TBD # one per (subject); Algebra, Literature, Biology
year_format: end_year_int
columns:
school_code: TBD
subject: TBD # Algebra I | Literature | Biology
year: TBD
subgroup: TBD
value: "Percent Proficient and Advanced" # provisional
n: "Number Tested" # provisional
join_key: school_code
refresh: annual
metrics_backed:
- keystone_algebra_proficiency
- keystone_literature_proficiency
- keystone_biology_proficiency
# ─────────────────────────────────────────────────────────────────────────────
# Graduation rate (4-year cohort) — via Future Ready PA Index bundle
# ─────────────────────────────────────────────────────────────────────────────
- source_id: futurereadypa_performance_2024-25
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=60
format: xlsx
sheets:
- "State Assessment Measures" # 228 cols; PSSA + Keystone × subgroup × measure
- "School On Track Measures" # 93 cols; ELL growth, chronic absenteeism, suspensions
- "College Career Measures" # 193 cols; AP, dual enrollment, post-secondary
year_format: end_year_int
year: "2024-25"
columns:
aun: "AUN" # 9-digit Administrative Unit Number (LEA)
schl: "Schl" # 4-digit within-LEA school number
name: "Name"
# Wide-format: one column per (Measure × Subgroup), e.g.
# PercentProficientorAdvancedonMathematicsAlgebra1_AllStudent
# PercentProficientorAdvancedonELALiterature_Black
# See findings.md F.2 for the full shape. Loader unpivots
# <Measure>_<Subgroup> wide columns into long (school, year, metric_key, subgroup)
# facts in school_year_metrics.
join_key: ["AUN", "Schl"] # composite primary key
refresh: annual
# Sha256 / bytes / retrieved_at recorded in
# data/cities/philly/sources/<source_id>/<date>/provenance.json
notes: |
Single-file source of v1 outcomes. Prior years: id=49 (2023-24),
id=44 (2022-23), id=39 (2021-22), id=32 (2020-21), id=26 (2019-20),
id=20 (2018-19), id=17 (2017-18). Loader walks the year axis.
Subgroup taxonomy (from column suffixes): AllStudent, AmericanIndianAlaskaNative,
Asian, HawaiianPacificIslander, Black, Hispanic, White, 2orMoreRaces,
EconomicallyDisadvantaged, EnglishLearner, StudentswithDisabilities,
CombinedEthnicity. PVAAS growth ("MeetingAnnualAcademicGrowthExpectations_PVAAS*")
is a separate first-class indicator not present in NYC's data.
metrics_backed:
- pssa_grade3_ela_proficiency # provisional naming
- pssa_grade3_math_proficiency
- pssa_all_ela_proficiency
- pssa_all_math_proficiency
- keystone_algebra_proficiency
- keystone_literature_proficiency
- keystone_biology_proficiency
- graduation_rate_4yr
- chronic_absenteeism_rate
- pvaas_growth_math_algebra1
- pvaas_growth_ela_literature
- pvaas_growth_science_biology
# NB: 2024-25 Future Ready exposes Keystone HS subjects at school grain,
# not grade-by-grade PSSA. To get school × grade × subgroup, we still need
# PDE's PSSA bulk file separately (pde_pssa, TBD).
# ─────────────────────────────────────────────────────────────────────────────
# Chronic absenteeism — via Future Ready PA Index bundle
# ─────────────────────────────────────────────────────────────────────────────
# (chronic absenteeism rolled into futurereadypa_performance_2024-25 above —
# the Future Ready bundle is one xlsx covering all indicators per school year.)
- source_id: futurereadypa_performance_2023-24
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=49
format: xlsx
sheet: ["State Assessment Measures", "School On Track Measures", "College Career Measures"]
year_format: end_year_int
year: "2023-24"
columns:
aun: "AUN"
school_code: "Schl"
value: "<Measure>_<Subgroup> wide columns (see futurereadypa_performance_2024-25)"
join_key: ["AUN", "Schl"]
refresh: annual (prior-year archive; ids discovered by probing getdatafile?id=N, 2026-09-06)
notes: |
Prior-year Future Ready datafile (PDE filename Datafile_20232024.xlsx; saved as futurereadypa_performance_2023-24.xlsx). Same wide
schema as the 2024-25 file as far as first-fetch findings show; the
multi-year loader (scripts/loaders/philly/futurereadypa_performance.ts)
takes the source id + year as arguments. Loaded for the cross-sector
attendance series (attendance_persistence_rate incl. charters); the
cross-publisher check (docs/qa_reports/philly/__cross_publisher_attendance.md)
found this measure NOT spliceable with SDP's 90%+ metric.
metrics_backed: [attendance_persistence_rate, keystone_algebra_proficiency, keystone_literature_proficiency, keystone_biology_proficiency, pvaas_growth_math_algebra1, pvaas_growth_ela_literature, pvaas_growth_science_biology, graduation_rate_4yr]
- source_id: futurereadypa_performance_2022-23
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=44
format: xlsx
sheet: ["State Assessment Measures", "School On Track Measures", "College Career Measures"]
year_format: end_year_int
year: "2022-23"
columns:
aun: "AUN"
school_code: "Schl"
value: "<Measure>_<Subgroup> wide columns (see futurereadypa_performance_2024-25)"
join_key: ["AUN", "Schl"]
refresh: annual (prior-year archive; ids discovered by probing getdatafile?id=N, 2026-09-06)
notes: |
Prior-year Future Ready datafile (PDE filename Datafile_20222023.xlsx; saved as futurereadypa_performance_2022-23.xlsx). Same wide
schema as the 2024-25 file as far as first-fetch findings show; the
multi-year loader (scripts/loaders/philly/futurereadypa_performance.ts)
takes the source id + year as arguments. Loaded for the cross-sector
attendance series (attendance_persistence_rate incl. charters); the
cross-publisher check (docs/qa_reports/philly/__cross_publisher_attendance.md)
found this measure NOT spliceable with SDP's 90%+ metric.
metrics_backed: [attendance_persistence_rate, keystone_algebra_proficiency, keystone_literature_proficiency, keystone_biology_proficiency, pvaas_growth_math_algebra1, pvaas_growth_ela_literature, pvaas_growth_science_biology, graduation_rate_4yr]
- source_id: futurereadypa_performance_2021-22
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=39
format: xlsx
sheet: ["State Assessment Measures", "School On Track Measures", "College Career Measures"]
year_format: end_year_int
year: "2021-22"
columns:
aun: "AUN"
school_code: "Schl"
value: "<Measure>_<Subgroup> wide columns (see futurereadypa_performance_2024-25)"
join_key: ["AUN", "Schl"]
refresh: annual (prior-year archive; ids discovered by probing getdatafile?id=N, 2026-09-06)
notes: |
Prior-year Future Ready datafile (PDE filename Datafile_20212022.xlsx; saved as futurereadypa_performance_2021-22.xlsx). Same wide
schema as the 2024-25 file as far as first-fetch findings show; the
multi-year loader (scripts/loaders/philly/futurereadypa_performance.ts)
takes the source id + year as arguments. Loaded for the cross-sector
attendance series (attendance_persistence_rate incl. charters); the
cross-publisher check (docs/qa_reports/philly/__cross_publisher_attendance.md)
found this measure NOT spliceable with SDP's 90%+ metric.
metrics_backed: [attendance_persistence_rate, keystone_algebra_proficiency, keystone_literature_proficiency, keystone_biology_proficiency, pvaas_growth_math_algebra1, pvaas_growth_ela_literature, pvaas_growth_science_biology, graduation_rate_4yr]
- source_id: futurereadypa_performance_2020-21
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=32
format: xlsx
sheet: ["Schools A - M", "Schools M - Z"] # LONG format: one row per school x DataElement
year_format: end_year_int
year: "2020-21"
columns:
aun: "AUN"
school_code: "Schl"
element: "DataElement" # e.g. "Percent Regular Attendance (All Student)"
value: "DisplayValue" # e.g. "94.1%", or "IS" (suppressed)
join_key: ["AUN", "Schl"]
refresh: annual (prior-year archive)
notes: |
Prior-year Future Ready datafile (PDE filename Datafile_20202021.xlsx; saved as
futurereadypa_performance_2020-21.xlsx). The attendance element in this workbook is a LAGGING indicator (PDE glossary): it describes 2019-20 attendance and is stored under year 2019-20 — a year cut short by the March 2020 building closures.
Long format — the first-fetch findings show only the six columns; the attendance
element was confirmed by scanning DataElement values (2026-09-07). Only that element
is loaded (futurereadypa_longformat_attendance.ts).
metrics_backed: [attendance_persistence_rate]
- source_id: futurereadypa_performance_2019-20
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=26
format: xlsx
sheet: ["Schools A - M", "Schools M - Z"] # LONG format: one row per school x DataElement
year_format: end_year_int
year: "2019-20" # REPORT year; the attendance element describes 2018-19 (see notes)
columns:
aun: "AUN"
school_code: "Schl"
element: "DataElement" # e.g. "Percent Regular Attendance (All Student)"
value: "DisplayValue" # e.g. "94.1%", or "IS" (suppressed)
join_key: ["AUN", "Schl"]
refresh: annual (prior-year archive)
notes: |
Prior-year Future Ready datafile (PDE filename Datafile_20192020.xlsx; saved as
futurereadypa_performance_2019-20.xlsx). Registered 2026-09-07 after an external
methodology audit showed the file exists and carries "Percent Regular Attendance"
values for Philadelphia schools; an earlier version of the site wrongly stated that
PDE never published a 2019-20 datafile. PDE waived assessment-based accountability
for 2019-20, so assessment elements are largely absent, but the attendance element
is populated.
YEAR SEMANTICS (applies to every futurereadypa_performance_* file): PDE's Future
Ready glossary defines Regular Attendance as "a lagging indicator indicating data is
from the year prior to the reporting year". The attendance element in the 2019-20
workbook therefore describes 2018-19 attendance, and the loader stores it under
year 2018-19 (scripts/loaders/philly/_lib.ts STATE_METRIC_YEAR_OFFSET). Other
indicators keep the workbook's report year.
Long format; only the attendance element is loaded (futurereadypa_longformat_attendance.ts).
metrics_backed: [attendance_persistence_rate]
- source_id: futurereadypa_performance_2018-19
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=20
format: xlsx
sheet: ["Schools A to L", "Schools L to Z"] # LONG format: one row per school x DataElement
year_format: end_year_int
year: "2018-19"
columns:
aun: "AUN"
school_code: "Schl"
element: "DataElement" # e.g. "Percent Regular Attendance (All Student)"
value: "DisplayValue" # e.g. "94.1%", or "IS" (suppressed)
join_key: ["AUN", "Schl"]
refresh: annual (prior-year archive)
notes: |
Prior-year Future Ready datafile (PDE filename Datafile_20182019.xlsx; saved as
futurereadypa_performance_2018-19.xlsx). The attendance element in this workbook is a LAGGING indicator (PDE glossary): it describes 2017-18 attendance and is stored under year 2017-18 (a pre-pandemic year).
Long format — the first-fetch findings show only the six columns; the attendance
element was confirmed by scanning DataElement values (2026-09-07). Only that element
is loaded (futurereadypa_longformat_attendance.ts).
metrics_backed: [attendance_persistence_rate]
- source_id: futurereadypa_school_fast_facts_2024-25
role: ingested
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: https://futurereadypa.org/home/getdatafile?id=58
format: xlsx
sheet: "School Fast Facts" # only sheet; 37 columns
year_format: end_year_int
year: "2024-25"
columns:
aun: "AUN"
schl: "Schl"
name: "Name"
district_name: "DistrictName"
organization_type_id: "OrganizationTypeId"
organization_type_code: "OrganizationTypeCode" # regular | charter | compctc | cyber | specialed
address: "StreetAddress"
city: "City"
state: "State"
zip: "ZipCode"
website: "WebSite"
telephone: "Telephone"
grades_offered: "GradesOffered"
title_i: "TitleISchool"
enrollment: "Enrollment"
percent_gifted: "PercentGiftedStudents"
intermediate_unit_name: "IUName"
# Demographic shares — directly usable as percentages
pct_amer_indian: "AmericanIndian_AlaskanNative"
pct_asian: "Asian"
pct_native_hawaiian: "NativeHawaiianOtherPacificIslander"
pct_black: "Black"
pct_hispanic: "Hispanic"
pct_white: "White"
pct_two_or_more_races: "TwoOrMoreRaces"
pct_econ_disadv: "EconomicallyDisadvantaged"
pct_english_learner: "EnglishLearner"
pct_special_ed: "SpecialEducation"
pct_foster_care: "FosterCare"
pct_homeless: "Homeless"
pct_military_connected: "MilitaryConnected"
pct_female: "Female"
pct_male: "Male"
has_career_tech: "CareerTechnicalPrograms"
latitude: "Latitude"
longitude: "Longitude"
essa_designation: "ESSASchoolDesignation" # DFLT | TSI | CSI | ATSI | ACSI — see findings.md F.3
join_key: ["AUN", "Schl"]
refresh: annual
notes: |
37 cols, one row per school × year. Statewide; filter to City='Philadelphia'
for the v1 universe (302 schools). DistrictName='Philadelphia City School
District' identifies SDP district schools (212 of 302); the remaining 90
are 81 charter + 6 CTC + 2 cyber + 1 specialed by OrganizationTypeCode.
Renaissance Charter status is NOT distinguished here — needs the SDP
charter office list to flag.
# ─────────────────────────────────────────────────────────────────────────────
# SDP outcomes — graduation, attendance, suspensions, serious incidents.
# Multi-year single CSV files; loader doesn't need year-walking.
# ─────────────────────────────────────────────────────────────────────────────
- source_id: sdp_graduation_school_2014-2025
role: ingested
publisher: School District of Philadelphia (Performance Office)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/Graduation_Rates/OD_SDP_Graduation_Rates_School_S_2026-02-12.csv
format: csv
year_format: passthrough # `cohort` col is 9th-grade start year, e.g. "2010-2011"
year: "2013-14..2024-25"
columns:
cohort: "cohort" # "YYYY-YYYY" — 9th-grade start year
school_code: "schoolid_ulcs" # SDP ULCS code; crosswalk via school_identifiers
school_name: "schoolname"
sector: "sector" # District | Alternative — no charters here
rate_type: "rate_type" # 4-Year | 5-Year | 6-Year
subgroup_category: "group"
subgroup_value: "subgroup"
denom: "denom"
num: "num"
value: "score"
join_key: ["schoolid_ulcs"]
refresh: annual
notes: |
35,661 rows covering cohorts 2010-2011 through 2021-2022, 4yr/5yr/6yr.
Sectors: District + Alternative (NO charters — those via Future Ready).
Subgroups include Non-Binary (see findings.md F.9).
metrics_backed: [graduation_rate_4yr, graduation_rate_5yr, graduation_rate_6yr]
- source_id: sdp_attendance_90_school
role: ingested
publisher: School District of Philadelphia (Performance Office)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/Attendance/Student_Attendance_90_School_20260203.csv
format: csv
year_format: passthrough
year: "2020-21..2024-25"
columns:
year: "School Year"
sector: "Sector"
school_code: "ULCS Code"
school_name: "School Name"
subgroup_category: "Category"
subgroup_value: "Group"
denom: "Total Students (Yearly)"
num: "# with 90%+ Attendance (Yearly)"
value: "% with 90%+ Attendance (Yearly)"
join_key: ["ULCS Code"]
refresh: annual per SDP's research/data page — 'Last Refreshed Feb 2026 | Next Update Spring 2027' as read 2026-09-08
notes: |
16,926 rows. The complement (1 − this rate) approximates chronic absenteeism
but isn't identical — chronic absenteeism in NYC uses ≥10% absent of
*enrolled days that include at least 10 contributing days*. Reconcile against
Future Ready's chronic_absenteeism_rate at validation (iii-b).
metrics_backed: [attendance_rate_above_90]
- source_id: sdp_attendance_90_district_monthly
role: analysis # offline analysis input (absenteeism working packet); not loader-ingested
publisher: School District of Philadelphia (Performance Office)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/Attendance/Student_Attendance_90_District_20260508.csv
format: csv
year_format: passthrough
year: "2020-21..2025-26" # 2025-26 partial (Sep–Mar as of the 2026-05-08 vintage)
columns:
year: "School Year"
month: "Month"
sector: "Sector"
subgroup_category: "Category" # All Students / Gender / Grade Level / Race/Ethnicity
subgroup_value: "Group"
denom: "Total Students (This Month)"
num: "# with 90%+ Attendance (This Month)"
value: "% with 90%+ Attendance (This Month)"
join_key: [] # district-level; no school join
refresh: quarterly per SDP's research/data page (philasd.org/research, Attendance section) — 'Last Refreshed May 2026 | Next Update Aug 2026' as read 2026-09-08; 8 May 2026 vintage is the latest retrieved (probe of June–Sept 2026 date stamps found nothing newer on 2026-09-07)
notes: |
District-wide within-month 90%+-attendance shares — the only published
2025-26 attendance on the 90% threshold among the public files used in this
analysis (SDP also publishes 2025-26 district-level monthly ADA and 95%-
threshold files, not used here; school-level yearly files lag ~1 school year).
Within-month shares are NOT comparable to the yearly cumulative rate in
sdp_attendance_90_school; use for month-shape and year-over-year month
comparisons only. The public daily dashboard (dashboards.philasd.org)
exposed only the current school year when inspected (Qlik engine field
values, 2026-08-31); we have not established whether the district retains
prior-year daily records internally.
metrics_backed: []
- source_id: sdp_attendance_ada_school
role: ingested
publisher: School District of Philadelphia (Performance Office)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/Attendance/Student_ADA_Yearly_School_20260211.csv
format: csv
year_format: passthrough
year: "2020-21..2024-25"
refresh: annual
notes: |
Average Daily Attendance per school per year.
metrics_backed: [average_daily_attendance]
- source_id: sdp_suspensions_school
role: ingested
publisher: School District of Philadelphia (Performance Office)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/Suspensions/SDP_OSS_School_S_20260213.csv
format: csv
year_format: passthrough
year: "2019-20..2024-25"
columns:
year: "School Year"
sector: "Sector"
school_code: "ULCS Code"
school_name: "School Name"
subgroup_category: "Category"
subgroup_value: "Group"
denom: "Total Students (Yearly)"
# Distribution columns (each pair of count + %):
n_zero: "# with Zero OS Suspensions (Yearly)"
pct_zero: "% with Zero OS Suspensions (Yearly)"
n_one: "# with 1 OS Suspension (Yearly)"
pct_one: "% with 1 OS Suspension (Yearly)"
n_two: "# with 2 OS Suspensions (Yearly)"
pct_two: "% with 2 OS Suspensions (Yearly)"
n_three: "# with 3 OS Suspensions (Yearly)"
pct_three: "% with 3 OS Suspensions (Yearly)"
n_four_plus: "# with 4+ OS Suspensions (Yearly)"
join_key: ["ULCS Code"]
refresh: annual
notes: |
20,225 rows. Publishes the FULL DISTRIBUTION of suspensions per school per
year (not a single rate). Per findings.md §F.10, v1 derives at least
`oss_pct_any` (= 100 − pct_zero), `oss_pct_multiple` (≥2 suspensions),
`oss_pct_chronic` (≥4 suspensions). Analytically richer than NYC.
metrics_backed: [oss_pct_any, oss_pct_multiple, oss_pct_chronic]
# ─────────────────────────────────────────────────────────────────────────────
# OpenDataPhilly — SDP's curated Socrata copy of district-school outcomes.
# Use for development convenience; PDE is authoritative.
# ─────────────────────────────────────────────────────────────────────────────
# SDP-curated PSSA + Keystone (SDP-district schools only; charters covered by
# futurereadypa_performance above). Source has grade × subgroup × subject
# detail including gender, which Future Ready doesn't. Use both: SDP for
# district-school analytic depth, Future Ready for charter-side coverage.
# Year encoded in URL: one Zip per year going back to 2009-10. Loader walks
# the year axis; the 2024-25 entry is representative.
- source_id: sdp_pssa_keystone_2023-24
role: ingested
publisher: School District of Philadelphia (Performance Office; via OpenDataPhilly CDN)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/PSSA_Keystone/2023_2024_PSSA_Keystone_All_Data.zip
format: zip
year_format: end_year_int
year: "2023-24"
notes: |
Prior-year SDP PSSA + Keystone, fetched so the SPREE (iii-a) spot check
(which is only published for 2023-24 currently) has year-aligned values
to compare against. Same schema as the 2024-25 entry.
- source_id: sdp_pssa_keystone_2024-25
role: ingested
publisher: School District of Philadelphia (Performance Office; via OpenDataPhilly CDN)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/PSSA_Keystone/2024_2025_PSSA_Keystone_All_Data.zip
format: zip
year_format: end_year_int
year: "2024-25"
zip_contents:
- "PSSA_Keystone_Schools_Acct_Suppressed.csv" # canonical for SDP-district per O.8
- "PSSA_Keystone_Schools_Actual_Suppressed.csv" # ingest both for the per-school diff report
- "PSSA_Keystone_District_Acct_Suppressed.csv"
- "PSSA_Keystone_District_Actual_Suppressed.csv"
columns: # PSSA_Keystone_Schools_Acct/Actual share schema
school_code: "id_eos" # SDP's EOS identifier; crosswalk to AUN+Schl via SDP directory
school_name: "publicationname"
learning_network: "learningnetwork"
test_name: "testname" # PSSA | Keystone
subject: "subject" # ELA | Math | Science | Algebra I | Literature | Biology
grade: "grade" # 3..8 + "Grades 3-8" aggregate
subgroup_category: "category" # All | Gender | Ethnicity | IEP | ELL | EconDis
subgroup_value: "group" # All Students | Female | Male | Non-Binary | Black/African American | ...
denom: "denom"
pct_below_basic: "bel_score"
pct_basic: "bas_score"
pct_proficient: "prof_score"
pct_advanced: "adv_score"
pct_proficient_or_advanced: "profadv_score"
n_below_basic: "bel_num"
n_basic: "bas_num"
n_proficient: "prof_num"
n_advanced: "adv_num"
n_proficient_or_advanced: "profadv_num"
join_key: ["id_eos"] # within SDP; crosswalk via school_identifiers
refresh: annual
notes: |
Covers SDP district + Alternative schools only (charters covered by
Future Ready). The Zip ships BOTH the Actual (all tested) and Acct
(Oct-31 register-attributed) cuts — per `findings.md §F.6` and O.8,
Acct is the canonical SDP figure. Subgroup richness exceeds NYC:
full distribution across 5 levels (Below Basic / Basic / Proficient /
Advanced / Prof+Adv), with both counts and percents.
Prior years stable at the same URL pattern:
.../2023_2024_PSSA_Keystone_All_Data.zip
.../2022_2023_PSSA_Keystone_All_Data.zip
... back to 2009_2010
COVID gaps: 2019-2020 and 2020-2021 not administered.
metrics_backed:
- pssa_grade3_ela_proficiency
- pssa_grade3_math_proficiency
- pssa_grade4_ela_proficiency
- pssa_grade4_math_proficiency
- pssa_grade5_ela_proficiency
- pssa_grade5_math_proficiency
- pssa_grade5_science_proficiency
- pssa_grade6_ela_proficiency
- pssa_grade6_math_proficiency
- pssa_grade7_ela_proficiency
- pssa_grade7_math_proficiency
- pssa_grade8_ela_proficiency
- pssa_grade8_math_proficiency
- pssa_grade8_science_proficiency
- keystone_algebra_proficiency
- keystone_literature_proficiency
- keystone_biology_proficiency
# ─────────────────────────────────────────────────────────────────────────────
# School directory — PDE 9-digit code, school name, sector, location
# ─────────────────────────────────────────────────────────────────────────────
- source_id: sdp_master_school_list_2024-25
role: ingested
publisher: School District of Philadelphia (via OpenDataPhilly CDN)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Information/School_List/2024-2025%20Master%20School%20List%20(20250401).csv
format: csv
year: "2024-25"
year_format: passthrough
columns:
# Identifier columns — empirically verified per findings.md F.7 update:
# ulcs_code == id_eos (in PSSA) == src_school_id × 10
# (aun_code, pa_code) is the PDE composite key; 99% match to Future Ready
ulcs_code: "ULCS Code"
src_school_id: "SRC School ID"
pa_code: "PA Code"
aun_code: "AUN Code"
nsc_code: "NSC Code"
nces_code: "NCES Code"
# Names + display
name_ulcs: "School Name (ULCS)"
publication_name: "Publication Name"
abbreviated_name: "Abbreviated Name"
# Universe + governance
governance: "Governance" # District | Charter | Contracted
school_reporting_category: "School Reporting Category" # Renaissance distinction lives here (F.11)
management_organization: "Management Organization"
admission_type: "Admission Type" # Catchment | lottery | special admission | ...
school_level: "School Level"
current_grade_span: "Current Grade Span Served"
year_opened: "Year Opened"
# Geography
learning_network: "Learning Network"
city_council_district: "City Council District"
gps_location: "GPS Location" # "<lat>, <lng>"
street_address: "Street Address"
city: "City"
state: "State"
zip_code: "Zip Code"
# Federal + program designations (analogues of NYC Renewal/Community Schools)
federal_accountability: "Federal Accountability Designation" # ESSA: DFLT/TSI/CSI/ATSI/ACSI
title_i: "Title I Designation"
major_intervention: "Major Intervention" # F.12 — NYC Renewal analogue
major_intervention_year: "Major Intervention Year"
community_school_cohort: "Community School Cohort" # F.13 — NYC Community Schools analogue
alternate_education_type: "Alternate Education Type"
cte_status: "CTE Status"
# Leadership + grade flags
school_leader_name: "School Leader Name"
# Grade-served flags are columns "Grade K", "Grade 1"..."Grade 12" — loader pivots into a grades_served array.
refresh: annual
notes: |
333 rows of all District + Charter + Contracted schools in SDP's
directory. This is THE crosswalk source — 99% join coverage with Future
Ready Philly subset. Plan-level changes per findings.md F.7/F.11-F.13:
- school_code primary key is `ulcs_code` (canonical SDP form)
- `is_renaissance` is read from `School Reporting Category`, not derived
- `major_intervention` IS the Philly Renewal-equivalent column; we don't
need to build it
- `community_school_cohort` IS the Philly Community-Schools tag; same
Prior years at the same URL pattern; longitudinal:
.../Longitudinal%20School%20List%20(20171128).xlsx (2001-02..2016-17).
metrics_backed: [] # directory, not metrics
- source_id: sdp_enrollment_demographics_school_2024-25
role: ingested
publisher: School District of Philadelphia (via OpenDataPhilly CDN)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Information/Enrollment_Demographics_School/2024-2025%20Enrollment%20&%20Demographics.csv
format: csv
year: "2024-25"
year_format: passthrough
columns:
year: "schoolyear"
sector: "sector"
subsector: "subsector" # likely carries Renaissance distinction within charters (verify next batch)
school_code: "ulcscode"
school_name: "schoolname"
learning_network: "learningnetwork"
grade_level: "gradelevel" # NB: row grain is school × grade, not school alone
enrollment: "allstudents"
n_ell: "ell"
pct_ell: "ellpct"
n_iep: "iep"
pct_iep: "ieppct"
n_female: "female"
pct_female: "femalepct"
n_male: "male"
pct_male: "malepct"
n_amer_indian: "indian"
pct_amer_indian: "indianpct"
n_asian: "asian"
pct_asian: "asianpct"
n_black: "black"
pct_black: "blackpct"
n_hispanic: "hispanic"
pct_hispanic: "hispanicpct"
n_two_or_more: "mult"
pct_two_or_more: "multpct"
n_native_hawaiian: "hawaiian"
pct_native_hawaiian: "hawaiianpct"
n_white: "white"
pct_white: "whitepct"
pct_econ_disadv: "ceppct" # Community Eligibility Provision proxy for FRL/Econ Disadv
join_key: ["ulcscode"]
refresh: annual
notes: |
2,629 rows × 35 cols. Grain = school × grade-level. For school-level
demographic shares (v1 default), aggregate across grades on ulcscode.
Subsector likely distinguishes Renaissance within charters — verify.
IMPORTANT: school years prior to 2019-20 DO NOT INCLUDE CHARTER SCHOOLS.
For pre-2019 charter demographics, fall back to Future Ready's School
Fast Facts.
All-years roll-up:
.../Enrollment_Demographics_School_All_Years.zip
metrics_backed:
- enrollment
- pct_econ_disadv
- pct_english_learner
- pct_special_ed
- pct_female
- pct_male
- pct_amer_indian
- pct_asian
- pct_native_hawaiian
- pct_black
- pct_hispanic
- pct_white
- pct_two_or_more_races
# ─────────────────────────────────────────────────────────────────────────────
# Philly School Experience Survey (PSES) — climate, by respondent group
# ─────────────────────────────────────────────────────────────────────────────
# PSES — the SDP CDN bundles ALL FIVE respondent groups into ONE Zip per
# year (not one Zip per respondent as the NYC build assumed). Significantly
# cleaner than the NYCSS pattern.
- source_id: pses_2024-25
role: ingested
publisher: School District of Philadelphia (Research, Evaluation & Accountability)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Information/District_Wide_Survey/2024_2025_All_Respondent_Data.zip
format: zip
year: "2024-25"
year_format: passthrough
notes: |
Single Zip per year containing all five respondent groups
(student / parent / teacher / principal / support_staff) as
separate CSVs inside. Cross-sectional grain: response counts +
percentages per (school, question, response_option).
Prior years stable at:
.../2023_2024_All_Respondent_Data.zip
.../2022_2023_All_Respondent_Data.zip
... back to 2014_2015
All-years combined: .../DWS_All_Respondent_Data_All_Years.zip
metrics_backed: [survey_responses]
- source_id: pses_topic_subtopic_scores_all_years
role: ingested
publisher: School District of Philadelphia (Research, Evaluation & Accountability)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Information/District_Wide_Survey/Topic_Subtopic_Scores_2019-2020_to_2024-2025.zip
format: zip
year: "2019-20..2024-25"
year_format: passthrough
notes: |
Pre-rolled-up topic and subtopic scores per school per year, all years
that are currently methodologically comparable. The other PSES Zips
have raw response distributions; this one is SDP's official rollup.
Useful for v1 to avoid re-deriving the rollups; (iv) computed-value
checks will reconcile our rollups against these.
metrics_backed:
- survey_climate_score
- survey_instruction_score
- survey_leadership_score
- survey_professional_capacity_score
- survey_family_engagement_score
- survey_dei_score
# ─────────────────────────────────────────────────────────────────────────────
# Reference layer — SPREE, the (iii-a) presentation spot-check oracle
# ─────────────────────────────────────────────────────────────────────────────
- source_id: sdp_spree_2023-24
role: reference
publisher: School District of Philadelphia (Performance Office)
url: https://cdn.philasd.org/offices/performance/Open_Data/School_Performance/SPREE/SPREE_SY2324_School_Metric_Scores.csv
format: csv
year_format: passthrough
year: "2023-24"
notes: |
SPREE = School Progress Report on Education and Equity, SDP's family-facing
accountability composite. Used as the (iii-a) spot-check reference (same
publisher as the (ii) ingest source — presentation cross-reference, not an
independent oracle).
Prior years stable at:
.../SPREE_SY2324_School_Metric_Scores.csv
.../SPREE_SY2223_School_Metric_Scores.csv
.../SPREE_SY2122_School_Metric_Scores.csv
.../SPREE_SY2021_School_Metric_Scores.csv
Pre-2021 used the predecessor SPR format (.xlsx); not loaded for v1.
All-years zip: .../SPR_Data_All_Years.zip (legacy SPR through 2018-19).
columns: {} # populated after first fetch
# ─────────────────────────────────────────────────────────────────────────────
# Reference layer — Future Ready PA Index, the (iii-b) independent oracle
# ─────────────────────────────────────────────────────────────────────────────
- source_id: futurereadypa_reference
role: reference
publisher: Pennsylvania Department of Education (Future Ready PA Index)
url: TBD
format: xlsx
sheet: TBD
year_format: end_year_int
columns:
school_code: TBD
year: TBD
metric: TBD
value: TBD
join_key: school_code
refresh: annual
notes: |
Different publisher from SDP-curated copies. Where Future Ready PA Index and
SPREE both report a comparable metric, both reconciliations should run; large
SPREE vs. Future Ready gaps are themselves a finding worth reporting.