Findings from first-fetch (2026-06-03)

Real surprises and validations from fetching the first two sources. Every finding triggers either a sources.yaml update or a planning-doc edit.

✓ Plan validated

• Philly school count: 302 (212 district + 81 charter + 6 CTC + 2 cyber + 1 special-ed by OrganizationTypeCode). The plan estimated ~300 with ~210 district + ~85 charter. On the money.
• Future Ready PA Index is a single bundle covering every PA school with school-level rows. The plan assumed this.
• Subgroup breakdowns are present for every metric × 12 subgroups: All / AmericanIndianAlaskaNative / Asian / HawaiianPacificIslander / Black / Hispanic / White / 2orMoreRaces / EconomicallyDisadvantaged / EnglishLearner / StudentsWithDisabilities / CombinedEthnicity. Richer than NYC, which doesn't carry HawaiianPacificIslander or AmericanIndianAlaskaNative subgroups separately.

⚠ Plan surprises (substantive)

F.1 Join key is composite (AUN + Schl), not single 9-digit code

The plan said:

PDE 9-digit Public School Code as primary key (covers both sectors)

The reality: every PA school file uses AUN (9-digit LEA Administrative Unit Number) + Schl (4-digit within-LEA school number). The 9-digit "school code" doesn't exist as a single column — what we want is the composite key (AUN, Schl).

Implication for 02_gap_analysis.md §B.1 and the future schema:

• schools PK should be a derived school_code = concat(AUN, '-', Schl) (13 chars). Carry both AUN and Schl as columns alongside.
• LEA-level joins (district info, SDP-only filtering) use AUN.
• SDP's AUN is 126515001. All SDP schools have AUN starting 1265….

F.2 Future Ready is richer than the spec assumed

The Performance file has three sheets totalling 514 columns of measures — not just the indicators we estimated. Specifically:

Two consequences:

1. PVAAS growth metric is available alongside proficiency. This is a value-added indicator that NYC doesn't have. It enables a real distinction between status-of-students and growth-of-students that NYC couldn't make. (Settles `00_overview.md §3 — O.1 in the methodology spec, not the repo decision: status vs. growth controls.)
2. outcomes.yaml should be expanded to include the College/Career indicators as future-v2 outcomes. Not in scope for v1 but worth tagging as available.

F.3 ESSA designations are a substantive analytical dimension

Every Philly school has an ESSASchoolDesignation:

53 Philly schools are CSI (the federally-designated bottom 5%), and 9 are ACSI (chronically lowest). This is a live analogue to NYC's Renewal Schools — except it's still in effect, federally-mandated, and updated annually. This belongs in the schools table as a first-class column, not as a story tag. The "CSI/ACSI school retrospective" deserves its own story stub.

F.4 Cyber-charter footprint in Philly is smaller than I framed

The plan called cyber-charters "out of scope for v1 because they serve students statewide; attribution to Philly is conceptually fuzzy." That framing still stands for the statewide cyber-charter universe, but the Future Ready file shows only 2 cyber-charter schools with a Philadelphia physical address — both very small footprints. The big cyber-charter question isn't "which Philly cyber-charters are good?" but "how many Philly students attend cyber-charters that are physically located elsewhere?" — which requires student-enrollment-origin data not in this file.

Net: keep v1 scope. The statewide-cyber-charter story is its own larger project; the 2 Philly-located cyber-charters can be tagged but won't move analytical narrative weight.

F.5 The regular / charter / compctc / cyber / specialed

   OrganizationTypeCode enum, not what the plan wrote

The plan proposed a 6-value school_type enum:

SDP_DISTRICT, RENAISSANCE_CHARTER, CHARTER, CYBER_CHARTER, ALTERNATIVE_ED, SPECIAL_ADMISSION

The Future Ready actual: 5 values (regular, charter, compctc, cyber, specialed). Renaissance charter is not distinguished from regular charter in PDE's classification. We'd have to derive Renaissance status from a different source (SDP's charter office list).

Net change to 02_gap_analysis.md §B.2:

• Adopt PDE's 5-value enum as the source-of-truth.
• Carry a derived is_renaissance boolean (from SDP's charter office list, not yet ingested).
• Drop SPECIAL_ADMISSION from school_type (it's an admission category, not a school type — fits cleanly under admission_category).

What this changes downstream

What's still TBD

• pde_pssa and pde_keystone URLs — first-fetch needed to confirm sheet shape (or use Future Ready as the substitute and skip these for v1).
• All pses_* (5 PSES respondent files) — URL pattern not known until fetch.
• sdp_spree — SPREE files at philasd.org/performance/programsservices/spree/ need URL discovery.
• sdp_school_profiles Socrata resource ID — discovery via OpenDataPhilly catalog.
• opendataphilly_pssa Socrata resource ID — same.

Each becomes its own first-fetch run.

Bottom line

The Future Ready bundle alone is enough to populate v1 for the 4 core outcomes (PSSA ELA, PSSA Math, Keystone, grad rate) plus chronic absenteeism. The PDE PSSA/Keystone bulk files can be skipped for v1; we add them in v2 if we need grade-by-grade breakdowns Future Ready doesn't expose at the school × grade × subgroup grain. The OpenDataPhilly + SDP files are still important for school directory enrichment (SDP Learning Network, City Council district, school-profile demographics) and for the PSES survey ingest.

Batch 2 findings (2026-06-03) — SDP CDN discovery

Updated after fetching 5 more sources: SDP PSSA+Keystone Zip, PSES Zip, SDP graduation CSV, SDP 90%-attendance CSV, SDP suspensions CSV.

✓ Plan validated

• PSES is one Zip per year, not five (as the plan originally enumerated). One source_id per year is sufficient; the Zip contains a codebook plus xlsx per respondent group.
• Stable CDN URLs at cdn.philasd.org, going back to 2009-10 for PSSA, 2014-15 for PSES, 2013-14 for graduation. Discovery problem solved for SDP data.
• PSSA + Keystone subgroup richness exceeds NYC's: each school × subject × grade × subgroup row has bel/bas/prof/adv/profadv counts AND percentages (5 levels × 2 measures). NYC publishes only "% Level 3+4." Philly gives full distribution.

⚠ Batch 2 surprises

F.6 SDP publishes two population cuts per metric, side by side

The SDP PSSA Zip contains two flavors of the same data:

This is exactly the population-cut distinction KX warned about in verify/METHODOLOGY.md for NYC — where the NYC InfoHub file is only the "Actual" cut and the accountability cohort had to be reverse-engineered. Philly gives us both. Implication:

• Canonical for SDP-district schools: Acct (matches SDP's own published reports + SPREE composite).
• (ii) base case validates against the file we ingested ("Acct" specifically).
• (iii) cross-cohort sanity check: load both, compare per-school differences, surface where Acct vs Actual diverges meaningfully — gives us a NYC-style population-cut report at ingest time, not at validation time.

F.7 Three distinct school-ID schemes need a crosswalk

The plan said composite PK = AUN + Schl. Reality:

Plan implication: the schools table needs three identifier columns plus a derived school_code (the canonical join key for site URLs / queries). A separate school_identifiers mapping table or a one-time discovery loader builds the crosswalk from SDP's directory + PDE's directory. This is a real infrastructure task that the plan didn't anticipate.

Operationally: most SDP CSVs use ULCS Code (the schoolid_ulcs field in graduation), while their PSSA CSV uses id_eos. These two SDP codes may or may not be 1:1 — the SDP school directory will clarify (next fetch).

F.8 Some SDP sources cover only district schools, not charters

The graduation CSV's sector column has two values: District and Alternative. No charter rows. Same pattern likely for attendance, suspensions, PSSA Zip (district + alternative only).

This is the inverse of Future Ready, which covers everyone but at a coarser grain. So the architecture is:

Loader does a per-school-type source routing rather than a single source per metric. Add this to the integration plan as a clarification to P1.

F.9 Subgroup taxonomy is richer than the plan said

The graduation CSV's subgroup distinct values include Non-Binary as a gender — NYC doesn't carry it. Also Multi Racial/Other, Native Hawaiian/Pacific Islander, Does Not Have IEP, Non-EL, etc. Subgroup enum needs to be wider than NYC's. Edit B.4 again.

F.10 SDP suspensions data is a distribution, not a rate

The OSS CSV doesn't publish a single "suspension rate" — it publishes the share of students with 0 / 1 / 2 / 3 / 4+ suspensions per year. That's richer than NYC's stub-only suspension data, and gives us a "high-tail suspensions" analytical dimension that NYC doesn't have. v1 should compute and store both:

• oss_pct_zero (% with 0 suspensions)
• oss_pct_multiple (% with 2 or more suspensions — the heavy-user share)
• oss_pct_chronic (% with 4+ suspensions)

These are derived metrics that ship cleanly via metric_definitions rather than as raw counts.

What this changes downstream

What's still TBD

• sdp_charter_office_list URL — needed to flag is_renaissance.

Batch 3 findings (2026-06-03) — SDP Master School List + Enrollment/Demographics

Fetched + empirically tested the school-identifier crosswalk. The "three ID schemes" of F.7 collapse to two, and the SDP Master School List contains the full crosswalk in one file.

✓ Major plan simplification: the ID space is smaller than F.7 said

Verified relationships (333-school Master List + PSSA 2024-25 sample + Future Ready Philly subset):

Updated schema (replaces F.7):

schools (primary key: school_code = "<ulcs_code>"):
  ulcs_code       (SDP canonical; ≡ id_eos in PSSA; SRC*10)
  src_school_id   (= ulcs_code / 10; carried for convenience)
  pa_code         (PDE Schl)
  aun_code        (PDE LEA)
  nces_code       (federal cross-state)
  ...

For SDP-only metrics, query by ulcs_code. For PDE-side joins, use (aun_code, pa_code). The Master School List is the single crosswalk source — no separate school_identifiers table needed.

✓ Master School List is a rich directory beyond crosswalk

56 columns. Carries everything we need to seed the schools table:

F.11 — Renaissance Charter distinction is published right here. The plan said "carry is_renaissance as a derived boolean from the SDP charter office list." Reality: School Reporting Category column already distinguishes Renaissance Charter from Charter and from district categories. No separate charter-office-list fetch required.

F.12 — Major Intervention column = NYC Renewal analogue. SDP records schools that were placed on an Academic Improvement Plan or similar, with the year. Bartram HS has Major Intervention: Academic Improvement Plan since 2017-2018. This is an ANALYTICAL DIMENSION the plan didn't anticipate — it lets us tell the Philly version of the "schools the district designated for turnaround" story directly from the directory.

F.13 — Community School Cohort column = NYC Community Schools analogue. Same pattern. SDP tracks which schools have joined the Community Schools program and in which cohort. Direct first-class data.

✓ Demographics file uses ULCS — clean join

The school-level enrollment+demographics CSV (35 cols, 2,629 rows for 2024-25) is grade × school grain (not just school). One row per (schoolyear, sector, ulcscode, gradelevel). For school-level demographic shares (the v1 use case), aggregate across grades on ULCS.

subsector column likely carries Renaissance distinction within charters (TBD, verify on next batch).

What this closes

Combined with the earlier finds, the v1 data layer is discovered:

• All v1 outcome metrics are in fetched files
• All universe-defining metadata (sector, type, admission, geography) is in the Master School List
• The two ID-space families (SDP's ULCS, PDE's AUN+PA Code) have a 99%+ join
• Renaissance Charter status is published, not derived
• ESSA designation is cross-published in two sources for validation

What's still genuinely TBD

• PDE PSSA + Keystone bulk files — only needed if we want grade-by-grade breakdowns for charter schools. Future Ready gives charters school-wide only. Decision: defer to v2 unless a story specifically needs it.
• The 3 Future Ready Philly schools missing from Master List — cyber-charters with Philly mailing addresses but not in SDP's directory. Tag as "out-of-network" and proceed.
• Major Intervention historical detail — Master List has current designation. For the "Philly Renewal-style retrospective" story, we'd need historical Master Lists (which the longitudinal Zip provides).