🎓 `edu`¶

Institutional education data for K-12 districts, schools, and higher education. K-12 tables cover NCES Common Core of Data (CCD), Civil Rights Data Collection (CRDC), and NAEP assessments. Higher-ed tables cover IPEDS directory, completions, financials, and College Scorecard outcomes. All sourced via the Urban Institute Education Data Portal API (free, unauthenticated) except College Scorecard (free API key required). Complements census.acs_education (population attainment) — this schema describes the institutions themselves.

25 datasets · 310 columns

ccd_districts · table¶

NCES Common Core of Data — Local Education Agency (district) directory. One row per LEA per school year (year = end year; 2022 = 2021-22). Primary join target for ccd_schools (leaid) and geo.school_districts (sd_lea = leaid). Staff FTE fields (teachers, counselors, psychologists) enable resource equity analysis. Several numeric fields (number_of_schools, enrollment, spec_ed_students, english_language_learners, teachers_total_fte, guidance_counselors_total_fte, librarian_specialists_fte, staff_total_fte — see each column's own comment) carry raw NCES CCD sentinel codes instead of a real count on some rows: -1 = Missing and -2 = Not Applicable are NCES's own official codes (numeric fields, all CCD surveys since 2002-03); -9 = does not meet NCES data quality standards is also an official code, observed here on english_language_learners. These are meaningful metadata, not corrupted data, so this raw table keeps them as-is — but any view aggregating these fields (SUM/AVG/ratio) MUST exclude negative values first (value < 0), or the sentinel codes get silently summed in as if they were real counts.

Column	Type	Null	Description
`leaid`	string	no	NCES LEA ID (7-digit string) — PK component
`year`	int	no	School year end year (e.g. 2022 = 2021-22) — PK component
`lea_name`	string	yes	District name
`fips`	int	yes	State FIPS code (integer 1-56) — FK to geo.states.state_fips
`state_abbr`	string	yes	2-letter state abbreviation from state_location field
`county_code`	string	yes	County FIPS, zero-padded to 5 chars by the transformer (e.g. '06037') — FK to geo.counties.county_fips
`county_name`	string	yes	County name
`urban_centric_locale`	int	yes	NCES locale code (11=city large, 12=city mid, 13=city small, 21=suburb large, 31=town fringe, 41=rural fringe)
`cbsa`	int	yes	Core-Based Statistical Area code
`latitude`	double	yes	LEA mailing address latitude
`longitude`	double	yes	LEA mailing address longitude
`agency_type`	int	yes	1=local, 2=supervisory union component, 3=state agency, 4=federal, 7=independent charter
`agency_level`	int	yes	1=elementary, 2=secondary, 3=K-12, 4=other
`agency_charter_indicator`	int	yes	1=charter LEA, 0=not charter
`lowest_grade_offered`	int	yes	Lowest grade offered (-1=PK, 0=K, 1-12)
`highest_grade_offered`	int	yes	Highest grade offered
`number_of_schools`	int	yes	Number of operational schools in LEA. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`enrollment`	int	yes	Total student enrollment (may be null for some administrative LEAs). May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`spec_ed_students`	int	yes	Students receiving special education services. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`english_language_learners`	int	yes	English language learner students. May carry a negative NCES CCD sentinel code (see table comment, incl. -9) instead of a real count.
`teachers_total_fte`	double	yes	Total FTE classroom teachers across all grade levels. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`guidance_counselors_total_fte`	double	yes	Total FTE guidance counselors. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`school_counselors_fte`	double	yes	FTE school counselors
`school_psychologists_fte`	double	yes	FTE school psychologists
`librarian_specialists_fte`	double	yes	FTE librarian/media specialists. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`staff_total_fte`	double	yes	Total FTE all staff categories. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`state_leg_district_lower`	string	yes	State lower legislative district
`state_leg_district_upper`	string	yes	State upper legislative district
`congress_district_id`	int	yes	Congressional district ID

ccd_schools · table¶

NCES Common Core of Data — individual school directory. One row per school per year. Joins to ccd_districts via leaid. free_or_reduced_price_lunch enables poverty proxy at the school level. Biennial CRDC data joins via ncessch = crdc_schools.ncessch. Numeric fields (enrollment, teachers_fte, free_lunch, reduced_price_lunch, free_or_reduced_price_lunch, direct_certification — see each column's own comment) carry raw NCES CCD sentinel codes instead of a real count on some rows: -1 = Missing and -2 = Not Applicable are NCES's own official codes (numeric fields, all CCD surveys since 2002-03); a further -3 is consistently present across all six fields above but its exact NCES definition could not be confirmed against NCES/Urban Institute documentation as of 2026-08-02 (checked live; do not assume it means the same thing as -3 on unrelated datasets, e.g. IMLS's Public Libraries Survey — that's a different codebook). These are meaningful metadata, not corrupted data, so this raw table keeps them as-is — but any view aggregating these fields (SUM/AVG/ratio) MUST exclude negative values first (value < 0), or the sentinel codes get silently summed in as if they were real counts.

Column	Type	Null	Description
`ncessch`	string	no	NCES school ID (12-digit string) — PK component
`leaid`	string	no	Parent LEA ID (7-digit) — FK to ccd_districts.leaid
`year`	int	no	School year end year — PK component
`school_name`	string	yes	School name
`fips`	int	yes	State FIPS (integer) — FK to geo.states.state_fips
`county_code`	string	yes	County FIPS, zero-padded to 5 chars by the transformer (e.g. '06037') — FK to geo.counties.county_fips
`latitude`	double	yes	School latitude
`longitude`	double	yes	School longitude
`urban_centric_locale`	int	yes	NCES locale code
`cbsa`	string	yes	CBSA code (string in API)
`school_level`	int	yes	1=elementary, 2=middle, 3=high, 4=other combined
`school_type`	int	yes	1=regular, 2=special ed, 3=vocational, 4=alternative
`school_status`	int	yes	1=open, 2=closed, 3=new, 4=added, 5=changed agency, 6=reopened, 8=future
`lowest_grade_offered`	int	yes	Lowest grade (-1=PK, 0=K, 1-12)
`highest_grade_offered`	int	yes	Highest grade offered
`charter`	int	yes	Charter school indicator (1=yes, 0=no)
`magnet`	int	yes	Magnet school indicator (1=yes, 0=no)
`virtual`	int	yes	Virtual school indicator (1=yes, 0=no, 2=supplemental)
`shared_time`	int	yes	Shared-time school (students also attend another school)
`title_i_status`	int	yes	Title I status code (5=schoolwide, 6=targeted assistance, 7=eligible not participating)
`title_i_eligible`	int	yes	Title I eligible (1=yes, 0=no)
`title_i_schoolwide`	int	yes	Schoolwide Title I program (1=yes, 0=no)
`teachers_fte`	double	yes	FTE classroom teachers. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`enrollment`	int	yes	Total student enrollment. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`free_lunch`	int	yes	Students approved for free lunch (NSLP). May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`reduced_price_lunch`	int	yes	Students approved for reduced-price lunch. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count.
`free_or_reduced_price_lunch`	int	yes	Combined free+reduced lunch count; divide by enrollment for poverty proxy. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count — exclude value < 0 before using in any ratio. SOURCE BEHAVIOR: states that have moved their schools to the Community Eligibility Provision stop collecting FRPL applications altogether, and CCD then publishes NOTHING in this field for every school in the state while still publishing direct_certification. Observed live against production: no such state before 2016, then a state set that changes each year — 2016 {DE,DC,MA,TN}, 2021 peaks at 10, 2022 {DE,DC,MA,MT,TN,WV}, 2024 {AZ,DE,DC,MA,TN,WV,GU}. A NULL here for a whole state-year is therefore the source's own answer, not a gap in ingestion, and it is not repairable by a reprocess. direct_certification is the only poverty signal CCD carries for those state-years — see that column for why the two are not interchangeable.
`direct_certification`	int	yes	Students directly certified for free meals via SNAP/TANF data matching, without a household application. May carry a negative NCES CCD sentinel code (see table comment) instead of a real count. NOT INTERCHANGEABLE WITH free_or_reduced_price_lunch: direct certification captures only the categorically eligible subset, so it runs well below FRPL wherever both are reported — measured live across production, the ratio of summed direct_certification to summed free_or_reduced_price_lunch on schools reporting both is 0.57-0.69 in every year 2016-2024. Substituting it for FRPL (a COALESCE, say) understates poverty by roughly a third and silently changes what the number means, so derive a separate rate from it rather than folding it into an FRPL rate.
`lunch_program`	int	yes	Lunch program type code
`bureau_indian_education`	int	yes	Bureau of Indian Education school indicator
`congress_district_id`	int	yes	Congressional district
`state_leg_district_lower`	string	yes	State lower legislative district
`state_leg_district_upper`	string	yes	State upper legislative district

naep_scores · table¶

NAEP (National Assessment of Educational Progress) state-level mean scale scores for grade 4 and grade 8 math/reading. One row per jurisdiction × year × subject × grade × variable_type × subgroup_name; jurisdiction=0 is the national aggregate, otherwise state FIPS (joins to ccd_districts.fips or geo.states.state_fips). Administered in odd years 2013-2019, then 2022 and 2024 (COVID-19 postponed the 2021 cycle) — not a strict biennial cadence. Proficiency-level (below_basic/basic/proficient/advanced) breakdowns are in the separate naep_achievement_levels table, joinable via the full key.

Column	Type	Null	Description
`jurisdiction`	int	no	State FIPS code (0 = national aggregate) — PK component
`jurisdiction_name`	string	yes	State name or "National"
`year`	int	no	Assessment year — PK component
`subject`	string	no	MAT=mathematics, RED=reading — PK component
`grade`	int	no	4 or 8 — PK component
`variable_type`	string	no	Disaggregation type (TOTAL, Race/Ethnicity, Gender, NAEP Eligibility) — PK component
`subgroup_name`	string	no	Subgroup label (All students, Male, White, Black, etc.) — PK component
`avg_score`	double	yes	Average NAEP scale score
`is_displayable`	int	yes	1 if statistically displayable (sufficient sample size)

naep_achievement_levels · table¶

NAEP proficiency level distributions sourced from GetAdhocData ALD stattype calls. One row per state × year × subject × grade × subgroup × level. level values: below_basic, basic, proficient, advanced. pct is the percentage of students at that level for the given key. Joins to naep_scores via (jurisdiction, year, subject, grade, variable_type, subgroup_name).

Column	Type	Null	Description
`jurisdiction`	int	no	State FIPS code (0 = national aggregate) — PK component
`jurisdiction_name`	string	yes	State name or "National"
`year`	int	no	Assessment year — PK component
`subject`	string	no	MAT=mathematics, RED=reading — PK component
`grade`	int	no	4 or 8 — PK component
`variable_type`	string	no	Disaggregation type (TOTAL, Race/Ethnicity, Gender, NAEP Eligibility) — PK component
`subgroup_name`	string	no	Subgroup label (All students, Male, White, Black, etc.) — PK component
`level`	string	no	Proficiency level — PK component; below_basic, basic, proficient, advanced
`pct`	double	yes	Percentage of students at this proficiency level
`is_displayable`	int	yes	1 if statistically displayable

crdc_schools · table¶

CRDC school-level civil-rights/equity data consolidated from four Urban Institute sub-endpoints, selected via the crdc_topic dimension: directory (charter/magnet/ alternative-school flags), chronic-absenteeism (chronically-absent student counts by race/sex/disability/LEP subgroup), offenses (firearm, homicide, rape, and assault incident counts), and teachers-staff (FTE teachers/certified-vs-uncertified/counselors/ psychologists/nurses/security/law-enforcement). Each row belongs to exactly one crdc_topic, so most columns are null outside their topic's rows. Joins to ccd_schools via ncessch (crdc_id equals ncessch for schools in the NCES universe; null ncessch means a non-NCES private/charter school with only a crdc_id). Collected by the Dept of Education Office for Civil Rights on an irregular, roughly-biennial schedule — not a strict 2-year interval, and not every topic shares the same loaded years: see observedCoverage below for the table-wide year set, but per-topic availability varies within it (e.g. offenses has no 2013 data even though teachers-staff does; year 2022 currently carries only chronic-absenteeism, not teachers-staff/offenses). CRDC's own known survey years are 2011, 2013, 2015, 2017, and the 2020-21 cycle (which lands here as two separate years, 2020 and 2021, not consolidated into one) — 2011 is a known CRDC survey year with no rows currently loaded in this table.

Column	Type	Null	Description
`crdc_id`	string	yes	Best available PK component for CRDC records; more properly an AK (alternate key). Equals ncessch for schools in the NCES universe. Chronic-absenteeism pre-2022 data may be null (transformer backfills from ncessch). For schools outside NCES (private/charter), crdc_id is the only identifier; ncessch will be null instead.
`ncessch`	string	yes	NCES school ID — FK to ccd_schools.ncessch; null for ~1,800 non-NCES schools
`leaid`	string	yes	LEA ID — FK to ccd_districts.leaid
`fips`	int	yes	State FIPS
`year`	int	no	Survey year (2011, 2013, 2015, 2017) — PK component
`crdc_topic`	string	no	Sub-endpoint dimension: directory, chronic-absenteeism, offenses, teachers-staff — PK component
`charter_crdc`	int	yes	Charter school per CRDC report (may differ from CCD value)
`magnet_crdc`	int	yes	Magnet school per CRDC
`alt_school`	int	yes	Alternative school indicator
`primarily_serve_students_w_dis`	int	yes	School primarily serves students with disabilities
`race`	string	yes	Race/ethnicity disaggregation (HI=Hispanic, BL=Black, WH=White, etc.; MF=all)
`sex`	string	yes	Sex disaggregation (M=male, F=female, MF=both)
`disability`	string	yes	Disability status disaggregation (WD=with disability, WOD=without, TOT=total)
`lep`	string	yes	Limited English proficiency disaggregation
`students_chronically_absent`	int	yes	Count of chronically absent students (>10% days missed) for this subgroup
`firearm_incident_ind`	int	yes	Any firearm incident reported (0/1)
`homicide_ind`	double	yes	Homicide incident indicator
`rape_incidents`	double	yes	Number of rape allegations
`sexual_battery_incidents`	double	yes	Number of sexual battery incidents
`attack_w_weapon_incidents`	double	yes	Attacks with a weapon (non-firearm)
`attack_no_weapon_incidents`	double	yes	Attacks without a weapon
`possession_firearm_incidents`	double	yes	Firearm possession incidents
`teachers_fte_crdc`	double	yes	Total FTE teachers per CRDC report
`teachers_certified_fte`	double	yes	FTE certified teachers
`teachers_uncertified_fte`	double	yes	FTE uncertified/emergency-certified teachers
`counselors_fte`	double	yes	FTE school counselors
`psychologists_fte`	double	yes	FTE school psychologists
`social_workers_fte`	double	yes	FTE school social workers
`nurses_fte`	double	yes	FTE nurses
`security_guard_fte`	double	yes	FTE security guards
`law_enforcement_fte`	double	yes	FTE sworn law enforcement officers

ipeds_institutions · table¶

NCES IPEDS Institutional Characteristics (HD survey) directory. One row per institution per year. Joins to geo.counties via county_fips (pad integer to 5-char string). Enrollment is NOT here (IPEDS EF survey). Tuition/fees are NOT here — see ipeds_tuition table (academic-year-tuition endpoint). college_scorecard joins via unitid = college_scorecard.id. ipeds_completions and ipeds_financials join via unitid. To reach an institution's cross-schema identity FROM another schema rather than by name, join ref.canonical_org_entity on ipeds_unitid = unitid — that table carries one foreign key per source, so the institution's LEI/SEC CIK and its unitid sit in the same row and the join is exact. Matching inst_name as text finds the wrong entities and misses institutions filing under a differently-worded name.

Column	Type	Null	Description
`unitid`	int	no	IPEDS unit ID (integer) — PK component
`opeid`	string	yes	OPE ID 8-digit — parent key for college_scorecard.ope8_id (that child declares the FK)
`year`	int	no	Survey year — PK component
`inst_name`	string	yes	Institution name (INSTNM in bulk CSV)
`inst_alias`	string	yes	Institution alias or common name
`address`	string	yes	Street address
`city`	string	yes	City
`state_abbr`	string	yes	State abbreviation — FK to geo.states.state_abbr
`zip`	string	yes	ZIP code
`fips`	int	yes	State FIPS (integer) — FK to geo.states.state_fips
`county_fips`	int	yes	County FIPS integer (COUNTYCD in bulk); source for county_fips_str
`county_fips_str`	string	yes	County FIPS zero-padded to 5 chars (e.g. '06037') — FK to geo.counties.county_fips
`county_name`	string	yes	County name
`longitude`	double	yes	Longitude (LONGITUD in bulk CSV)
`latitude`	double	yes	Latitude
`sector`	int	yes	0=admin, 1=public 4yr, 2=private nonprofit 4yr, 3=private for-profit 4yr, 4=public 2yr, 5=private nonprofit 2yr, 6=private for-profit 2yr, 7=public <2yr, 8=private nonprofit <2yr, 9=private for-profit <2yr
`inst_control`	int	yes	1=public, 2=private nonprofit, 3=private for-profit
`institution_level`	int	yes	1=4yr or higher, 2=2-4yr, 3=<2yr
`hbcu`	int	yes	Historically Black College or University (1=yes, 0=no)
`hospital`	int	yes	Institution has hospital (1=yes, 0=no)
`medical_degree`	int	yes	Medical degree offered (1=yes, 0=no)
`tribal_college`	int	yes	Tribal college or university (1=yes, 0=no)
`land_grant`	int	yes	Land-grant institution (1=yes, 0=no)
`inst_size`	int	yes	1=<1000 students, 2=1000-4999, 3=5000-9999, 4=10000-19999, 5=20000+
`degree_granting`	int	yes	Degree-granting institution (1=yes, 0=no)
`title_iv_indicator`	int	yes	Title IV federal financial aid eligible
`offering_undergrad`	int	yes	Undergraduate programs offered (1=yes)
`offering_grad`	int	yes	Graduate programs offered (1=yes)
`cc_basic_2021`	int	yes	Carnegie Classification basic 2021 (integer code; see carnegie.edu for legend)
`cbsa`	int	yes	CBSA code
`cbsa_type`	int	yes	1=metropolitan, 2=micropolitan
`url_school`	string	yes	Institution website URL

ipeds_completions · table¶

NCES IPEDS Completions survey (C_A bulk CSV) — degrees/certificates awarded. One row per (unitid, year, cipcode, majornum, award_level). Race/ethnicity and sex counts are wide columns (ctotalt, caiant, casiat, cbkaat, chispt, cnhpit, cwhitt, c2mort, cunknt, cnralt and per-sex _m/_w variants). cipcode is the full 6-digit dotted string (e.g. "52.0101"). Source: nces.ed.gov/ipeds/datacenter/data/C{effective_year}_A.zip. Configured from 2010; the source has a multi-year publication lag (final revised files only, provisional-only vintages are skipped) — see observedCoverage below for the actual latest loaded vintage rather than assuming a fixed lag length.

Column	Type	Null	Description
`unitid`	int	no	IPEDS unit ID — PK component; FK to ipeds_institutions.unitid
`year`	int	no	Award year — PK component; injected from URL dimension
`cipcode`	string	no	6-digit CIP field of study (e.g. "52.0101") — PK component
`majornum`	int	no	1=first major, 2=second major — PK component
`award_level`	int	no	IPEDS award level — PK component; 1=<1yr cert, 2=1-2yr cert, 3=Associate, 5=Bachelor's, 7=Master's, 17=Doctoral research, 18=Doctoral professional, 19=Doctoral other
`ctotalt`	int	yes	Total completions (all races, both sexes)
`ctotalm`	int	yes	Total completions — men
`ctotalw`	int	yes	Total completions — women
`caiant`	int	yes	American Indian/Alaska Native — total
`caianm`	int	yes	American Indian/Alaska Native — men
`caianw`	int	yes	American Indian/Alaska Native — women
`casiat`	int	yes	Asian — total
`casiam`	int	yes	Asian — men
`casiaw`	int	yes	Asian — women
`cbkaat`	int	yes	Black or African American — total
`cbkaam`	int	yes	Black or African American — men
`cbkaaw`	int	yes	Black or African American — women
`chispt`	int	yes	Hispanic or Latino — total
`chispm`	int	yes	Hispanic or Latino — men
`chispw`	int	yes	Hispanic or Latino — women
`cnhpit`	int	yes	Native Hawaiian/Other Pacific Islander — total
`cnhpim`	int	yes	Native Hawaiian/Other Pacific Islander — men
`cnhpiw`	int	yes	Native Hawaiian/Other Pacific Islander — women
`cwhitt`	int	yes	White — total
`cwhitm`	int	yes	White — men
`cwhitw`	int	yes	White — women
`c2mort`	int	yes	Two or more races — total
`c2morm`	int	yes	Two or more races — men
`c2morw`	int	yes	Two or more races — women
`cunknt`	int	yes	Race/ethnicity unknown — total
`cunknm`	int	yes	Race/ethnicity unknown — men
`cunknw`	int	yes	Race/ethnicity unknown — women
`cnralt`	int	yes	Nonresident alien — total
`cnralm`	int	yes	Nonresident alien — men
`cnralw`	int	yes	Nonresident alien — women

ipeds_financials · table¶

NCES IPEDS Finance survey (F survey). Revenues, expenditures, endowment, and net position per institution per fiscal year. Sourced from NCES bulk ZIP downloads (nces.ed.gov/ipeds/datacenter/data/). Three form_type variants cover all institutions: F1A=GASB public, F2=FASB larger private, F3=FASB smaller private non-profit. All monetary values in dollars. Transformer maps NCES coded column names to canonical names. FY2022-23 is provisional (flag with is_provisional=true); FY2021-22 is the most recent final release. Joins to ipeds_institutions via unitid.

Column	Type	Null	Description
`unitid`	int	no	IPEDS unit ID — PK component; FK to ipeds_institutions.unitid
`year`	int	no	Fiscal year end (end year of academic year, e.g. 2022 = FY2021-22) — PK component
`form_type`	string	no	NCES form variant — PK component; F1A=GASB public, F2=FASB larger private, F3=FASB smaller private
`is_provisional`	int	no	1 if this is a provisional release (FY2022-23 and later); 0 if final
`rev_tuition_fees_gross`	double	yes	Gross tuition and fees revenue before allowances
`rev_tuition_fees_net`	double	yes	Net tuition and fees revenue after discounts/allowances
`rev_appropriations_state`	double	yes	State appropriations revenue
`rev_appropriations_fed`	double	yes	Federal appropriations revenue
`rev_grants_contracts_federal`	double	yes	Federal grants and contracts revenue
`rev_grants_contracts_state`	double	yes	State grants and contracts revenue
`rev_fed_approps_grants`	double	yes	Combined federal appropriations + grants revenue
`rev_state_local_approps_grants`	double	yes	Combined state/local appropriations + grants revenue
`rev_gifts_grants_contracts`	double	yes	Private gifts, grants, and contracts revenue
`rev_investment_return`	double	yes	Return on investments and endowment
`rev_auxiliary_enterprises_net`	double	yes	Auxiliary enterprises net revenue (housing, dining, bookstore)
`rev_operating`	double	yes	Total operating revenues
`rev_nonoperating`	double	yes	Total nonoperating revenues
`rev_total_current`	double	yes	Total all revenues and additions
`sch_pell_grant`	double	yes	Pell grant scholarships/fellowships
`sch_grants_institutional`	double	yes	Unrestricted institutional grants awarded
`sch_total_student_aid`	double	yes	Total student aid awarded
`sch_allowances_tuition_fees`	double	yes	Discount/allowance applied to tuition and fees
`exp_instruc_total`	double	yes	Instruction expenditures total
`exp_research_total`	double	yes	Research expenditures total
`exp_pub_serv_total`	double	yes	Public service expenditures total
`exp_acad_supp_total`	double	yes	Academic support expenditures (libraries, computing, curriculum)
`exp_student_serv_total`	double	yes	Student services expenditures (admissions, registrar, athletics)
`exp_inst_supp_total`	double	yes	Institutional support expenditures (executive management, finance, HR)
`exp_aux_ent_total`	double	yes	Auxiliary enterprises expenditures
`exp_net_grant_aid_total`	double	yes	Net grant aid and fellowships expenditures
`exp_total_current`	double	yes	Total expenses and deductions
`exp_total_salaries`	double	yes	Total salary expenditures across all functions
`endowment_beg`	double	yes	Endowment assets beginning of year
`endowment_end`	double	yes	Endowment assets end of year
`assets`	double	yes	Total assets
`liabilities`	double	yes	Total liabilities
`net_position_end`	double	yes	Net position / net assets at end of year
`longterm_debt`	double	yes	Long-term debt outstanding

ipeds_tuition · table¶

NCES IPEDS IC_AY (Institutional Characteristics — Academic Year) survey. Published tuition and fees by institution, level of study, and tuition type. One row per (unitid, year, level_of_study, tuition_type). level_of_study: 1=undergraduate, 2=graduate. tuition_type: 1=in-district, 2=in-state, 3=out-of-state, 4=out-of-district. Use tuition_type=2 for in-state and tuition_type=3 for out-of-state comparisons. Data only available through year=2021 (the Urban Institute academic-year-tuition endpoint has no more recent years); use ipeds_institutions/college_scorecard for more current institution data.

Column	Type	Null	Description
`unitid`	int	no	IPEDS unit ID — PK component; FK to ipeds_institutions.unitid
`year`	int	no	Academic year — PK component
`fips`	int	yes	State FIPS (denormalized)
`level_of_study`	int	no	1=undergraduate, 2=graduate — PK component
`tuition_type`	int	no	1=in-district, 2=in-state, 3=out-of-state, 4=out-of-district — PK component
`tuition_fees_ft`	int	yes	Full-time tuition and fees (combined)
`tuition_ft`	int	yes	Full-time tuition only
`fees_ft`	int	yes	Full-time required fees only
`credit_hour_charge_pt`	int	yes	Per-credit-hour charge for part-time students
`tuition_published`	int	yes	Published (sticker) tuition
`fees_published`	int	yes	Published required fees
`tuition_fees_published`	int	yes	Published total tuition and fees

college_scorecard · table¶

Dept of Ed College Scorecard institution-level outcomes. Earnings, debt, completion rates, and enrollment demographics. One row per institution per vintage year. Joins to ipeds_institutions via id=unitid or ope8_id=opeid. API key required (API_DATA_GOV); PrivacySuppressed values are null. Earnings field is mean (not median) at 10yr — median available at 8yr. For program-level (CIP code / credential) earnings, see college_scorecard_programs (finer grain, same source).

Column	Type	Null	Description
`id`	int	no	IPEDS unit ID — PK component; FK to ipeds_institutions.unitid
`ope8_id`	string	yes	OPE ID 8-digit — FK to ipeds_institutions.opeid
`ope6_id`	string	yes	OPE ID 6-digit
`year`	int	no	Data vintage year — PK component
`name`	string	yes	Institution name (school.name)
`city`	string	yes	City
`state`	string	yes	State abbreviation — FK to geo.states.state_abbr
`ownership`	int	yes	1=public, 2=private nonprofit, 3=private for-profit
`carnegie_basic`	int	yes	Carnegie basic classification (integer code)
`hbcu`	int	yes	HBCU flag (1=yes)
`tribal`	int	yes	Tribal college flag (1=yes)
`hispanic_serving`	int	yes	Hispanic-serving institution (1=yes)
`online_only`	int	yes	Online-only institution (1=yes)
`locale`	int	yes	NCES locale code
`size`	int	yes	Total enrollment (latest.student.size)
`pell_grant_rate`	double	yes	Share of students receiving Pell grants (latest.aid.pell_grant_rate)
`completion_rate_4yr`	double	yes	6-year completion rate at 150% time for 4yr institutions
`completion_rate_2yr`	double	yes	Completion rate for <4yr institutions
`retention_rate_fulltime`	double	yes	First-time full-time student retention rate (4yr)
`median_debt_completers`	double	yes	Median federal loan debt at completion (aid.median_debt.completers.overall)
`median_debt_noncompleters`	double	yes	Median federal loan debt for non-completers (aid.median_debt.noncompleters)
`mean_earnings_10yr`	int	yes	Mean earnings 10yr after entry for working non-enrolled students (note: mean, not median)
`median_earnings_8yr`	double	yes	Median earnings 8yr after entry (earnings.8_yrs_after_entry.median_earnings)
`median_family_income`	int	yes	Median family income of entering students
`default_rate_3yr`	double	yes	3-year student loan cohort default rate
`avg_net_price_public`	int	yes	Average net price for public institutions after aid
`avg_net_price_private`	int	yes	Average net price for private institutions after aid
`tuition_in_state`	int	yes	In-state tuition (cost.tuition.in_state)
`tuition_out_state`	int	yes	Out-of-state tuition (cost.tuition.out_of_state)

college_scorecard_programs · table¶

Dept of Ed College Scorecard program-level outcomes by CIP 4-digit code. One row per institution × program × credential level per year. Earnings by field of study — the most granular post-graduation outcome data. Joins to ipeds_completions via (unitid=unit_id, cipcode LIKE cip_code+'%'). PrivacySuppressed values are null. Institution-level scorecard outcomes (overall earnings, debt, completion rate) are in college_scorecard; this table breaks those out by field of study instead.

Column	Type	Null	Description
`unit_id`	int	no	IPEDS unit ID — PK component; FK to ipeds_institutions.unitid
`ope6_id`	string	yes	OPE ID 6-digit
`year`	int	no	Data vintage year — PK component
`cip_code`	string	no	4-digit CIP code (e.g. "0199") — PK component
`cip_title`	string	yes	CIP field of study title
`credential_level`	int	no	Credential level code — PK component; 1=certificate <1yr, 2=certificate 1-2yr, 3=associate, 5=bachelor's, 7=master's, 17=doctoral
`credential_title`	string	yes	Credential level description
`ipeds_awards`	int	yes	IPEDS completions count for this program (counts.ipeds_awards1)
`median_earnings_1yr`	int	yes	Median earnings 1yr after completion (earnings.1_yr.overall_median_earnings)
`median_earnings_4yr`	int	yes	Median earnings 4yr after completion
`median_earnings_5yr`	int	yes	Median earnings 5yr after completion
`pell_median_earnings_1yr`	int	yes	Pell grant recipient median earnings 1yr after completion
`nonpell_median_earnings_1yr`	int	yes	Non-Pell recipient median earnings 1yr after completion

library_outlets · table¶

IMLS Public Libraries Survey (PLS), outlet-level file — one row per physical library outlet (central, branch, or bookmobile stop) x fiscal year, the only PLS grain that carries square footage. IMLS lists every fiscal year's ZIP on one static landing page (not a per-year URL), so LibraryOutletsTransformer discovers the year's link from that page, then the "outlet" CSV inside the ZIP (which also holds a separate administrative-entity-level CSV with no square-footage column). Outlet identity is (state, fscs_key, fscs_seq) — NOT the source's own library_id field, which is not reliably unique/self-consistent in the raw data (confirmed: over a thousand library_id values collide across two different library systems in the FY2024 file). outlet_type: CE=Central, BR=Branch, BS=Bookmobile Stop, BM=Bookmobile. square_feet is null wherever the source carries an NCES missing/not-applicable/not-reported sentinel rather than a real count. county_fips is derived from the first 5 digits of the source's 11-digit Census tract FIPS (no county FIPS column is provided directly). Source: imls.gov/research-evaluation/surveys/public-libraries-survey-pls.

Column	Type	Null	Description
`year`	int	no	PLS fiscal year — PK component
`state`	string	no	STABR, 2-letter USPS state code — PK component (FK to geo.state_ref)
`fscs_key`	string	no	FSCSKEY, the outlet's parent library system ID — PK component
`fscs_seq`	string	no	FSCS_SEQ, sequence number of this outlet within its library system — PK component
`library_id`	string	yes	LIBID as reported (nominally FSCSKEY + '-' + FSCS_SEQ). NOT used as the key — see the table comment on source data-quality collisions.
`library_name`	string	yes	LIBNAME
`outlet_type`	string	yes	C_OUT_TY (CE=Central, BR=Branch, BS=Bookmobile Stop, BM=Bookmobile). One known source-data anomaly: library_id='MO0004-037' ("Library By Mail") carries outlet_type='MO' in year=2019 only (every other year for this outlet reads 'BM') -- confirmed present verbatim in IMLS's own FY2019 raw file (C_OUT_TY column, not a positional/mapping error on our side), not corrected here since there is no better field to derive the true value from.
`address`	string	yes	Street address
`city`	string	yes	CITY
`zip_code`	string	yes	ZIP
`county_name`	string	yes	CNTY (name, not FIPS)
`county_fips`	string	yes	Derived from CENTRACT[0:5] (FK to geo.counties)
`square_feet`	long	yes	SQ_FEET; null where the source carries a missing/not-applicable/not-reported sentinel
`latitude`	double	yes	LATITUDE
`longitude`	double	yes	LONGITUD

f33_district_finance · table¶

Census Bureau Annual Survey of School System Finances ("F-33"), individual-unit file — one row per public school district (LEA) per fiscal year. Source: the Census Bureau's published elsec{yy}t.txt comma-delimited file, one per fiscal year (14,088 LEAs in the verified FY2023 file). leaid (source column NCESID) is the Census Bureau's own copy of the NCES LEA ID and joins directly to ccd_districts.leaid and geo.school_districts.sd_lea. Unlike ccd_districts (a staffing/enrollment directory with no dollar figures at all), this table carries the actual survey dollar aggregates: total_revenue_thousand, total_expenditure_thousand, and current_expenditure_thousand are reported in thousands of dollars (the source's own convention, not converted here — see each column's comment); per_pupil_current_expenditure, per_pupil_instruction_expenditure, and per_pupil_support_services_expenditure are the source's own per-pupil dollar figures (PPCSTOT/PPITOTAL/PPSTOTAL), computed by the Census Bureau from fall membership, not derived here. A two-year data lag applies (dataLag: 2 below) — the Census Bureau's most recently published fiscal year is real time minus roughly two years (verified live 2026-08-21: the FY2024 file was the latest published, FY2025 was not yet available). Verified live against the FY2023 file: NCESID is unique per row (0 duplicates) and always 7 digits; none of TOTALREV/TOTALEXP/TCURSPND/ PPCSTOT/ENROLL carried a negative value in that file, so (unlike CCD) this table does not null out negative sentinel codes — none were observed, but this has only been checked for FY2023/FY2024.

Column	Type	Null	Description
`leaid`	string	no	NCES LEA ID (7-digit string), from source column NCESID — PK component. FK to ccd_districts.leaid and geo.school_districts.sd_lea.
`year`	int	no	Fiscal year of the survey (the yearRange dimension's effective_year, after dataLag=2 is applied) — PK component. Matches ccd_districts.year.
`pid6`	string	yes	Census Bureau 6-digit processing identifier for the school system, source column PID6 (as of FY2023 this replaced the older IDCENSUS identifier as the Census Bureau's primary key for the unit).
`unit_type`	string	yes	Census government unit type code, source column UNIT_TYPE (raw code)
`state_fips`	string	yes	2-digit state FIPS code — FK to geo.states.state_fips. From source column FIPST for FY2022 and later; earlier files publish a narrower header with no FIPST at all, so it is taken from the leading two digits of CONUM (a county FIPS is the state FIPS plus a 3-digit county code). Null for the handful of multi-county educational cooperatives whose CONUM is the "M" not-applicable marker, and for those years PID6, UNIT_TYPE and SCHLEV are null too — those columns do not exist in the pre-FY2022 file.
`county_fips`	string	yes	5-digit county FIPS (state+county), source column CONUM — FK to geo.counties.county_fips
`district_name`	string	yes	School system name, source column NAME
`cbsa`	string	yes	Core-Based Statistical Area code, source column CBSA
`school_level`	string	yes	School level code, source column SCHLEV (raw code)
`enrollment`	long	yes	Fall membership (student enrollment) used to compute the per-pupil columns, source column ENROLL
`total_revenue_thousand`	double	yes	Total elementary-secondary revenue, in thousands of dollars, source column TOTALREV
`federal_revenue_thousand`	double	yes	Total revenue from federal sources, in thousands of dollars, source column TFEDREV
`state_revenue_thousand`	double	yes	Total revenue from state sources, in thousands of dollars, source column TSTREV
`local_revenue_thousand`	double	yes	Total revenue from local sources, in thousands of dollars, source column TLOCREV
`total_expenditure_thousand`	double	yes	Total elementary-secondary expenditure, in thousands of dollars, source column TOTALEXP
`current_expenditure_thousand`	double	yes	Total current spending (operating expenditure — excludes capital outlay and debt service), in thousands of dollars, source column TCURSPND
`instruction_expenditure_thousand`	double	yes	Current spending for instruction, in thousands of dollars, source column TCURINST
`support_services_expenditure_thousand`	double	yes	Current spending for support services, in thousands of dollars, source column TCURSSVC
`capital_outlay_expenditure_thousand`	double	yes	Total capital outlay expenditure, in thousands of dollars, source column TCAPOUT
`interest_on_debt_thousand`	double	yes	Interest on school system indebtedness, in thousands of dollars, source column TINTRST
`long_term_debt_outstanding_thousand`	double	yes	Long-term debt outstanding at end of the fiscal year, in thousands of dollars, source column DEBTOUT
`per_pupil_current_expenditure`	double	yes	Per-pupil total current spending, in whole dollars — the source's own derived per-pupil figure (source column PPCSTOT), not computed here.
`per_pupil_instruction_expenditure`	double	yes	Per-pupil current spending for instruction, in whole dollars, source column PPITOTAL
`per_pupil_support_services_expenditure`	double	yes	Per-pupil current spending for support services, in whole dollars, source column PPSTOTAL

district_resources · view¶

District-level pupil_teacher_ratio (enrollment / teachers_total_fte) and district_frpl_rate, a free/reduced-price-lunch poverty proxy computed by summing free_or_reduced_price_lunch and enrollment from ccd_schools per (leaid, year) and joining to ccd_districts. One row per district per year. ccd_districts.enrollment and teachers_total_fte and ccd_schools.free_or_reduced_price_lunch/enrollment all carry negative NCES CCD sentinel codes on some rows (see those tables' comments) — this view excludes them (value < 0 treated as null) before computing pupil_teacher_ratio and before summing into district_frpl_count/frpl_rate, so a sentinel code is never silently counted as a real value. frpl_rate is NULL for a whole district in any year its state runs the Community Eligibility Provision: CEP schools take no FRPL applications, so CCD publishes nothing in free_or_reduced_price_lunch statewide and carries direct_certification instead. That state set grows and shifts from 2016 on (2022: DE, DC, MA, MT, TN, WV; 2024: AZ, DE, DC, MA, TN, WV, GU). direct_cert_rate is provided for those districts but is a DIFFERENT measure, not a fallback — it counts only the categorically eligible subset and runs at 0.57-0.69 of FRPL wherever both are reported, so the two must not be pooled into one poverty control. Use one or the other and say which.