apiVersion: v3.1.0
kind: DataContract
id: education.per_pupil_expenditure
name: per_pupil_expenditure
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Annual per-pupil expenditure panel for Georgia public school districts and schools under the
    GOSA Financial Efficiency Star Rating (FESR) methodology: total, federal, and state/local expenditures,
    full-time-equivalent K-12 enrollment, and the resulting per-pupil expenditure (PPE) for every fiscal
    year the FESR downloads cover — district rows FY2014-FY2024 (the 180 traditional systems), school
    rows FY2017-FY2024 (all schools, including charter authorizers). District rows additionally carry
    the federal vs state/local split of total expenditures; school rows additionally carry the pre-allocation
    included/excluded expenditure split and the school''s share of district enrollment. This is the FESR
    included-cost per-pupil series, whereas the k12_expenditures topic is the full function-level expenditure
    ledger — their totals intentionally differ (FESR excludes capital, debt, and other non-included costs).
    It is also the reliable school-level PPE series (the k12_expenditures school per-pupil column is degenerate
    in 2023-24). The FESR star ratings built from this panel live in the sibling financial_efficiency_star_rating
    topic.'
  usage: Star-schema fact table. Join the districts dimension on district_code; the schools dimension
    on district_code + school_code. Read directly with DuckDB over Parquet.
  limitations: 'Two detail levels (district, school) in one fact table, split by parquet filename; school_code
    is NULL on district rows. Detail level is read from the geography-key NULL pattern. The district level
    covers FY2014-FY2024 (180 traditional systems, codes 601-793, no charters/RESAs/state agencies); the
    school level covers FY2017-FY2024 (all schools, including charter authorizers). School-level and district-level
    rows come from two independent GOSA downloads whose K-12 bases need not reconcile, so school rows
    are NOT guaranteed to sum to their district row. Level-specific columns are NULL on the other level:
    federal_expenditures / state_local_expenditures are district-only; included_* / excluded_expenditures
    / total_federal_expenditures / total_state_local_expenditures / pct_of_district_enrollment are school-only.
    The district federal_expenditures and the school total_federal_expenditures are the same federal share
    of total_expenditures but are kept as separate columns (different source naming; schools also publish
    the distinct pre-allocation included_* split). District federal/state-local breakdowns are NULL for
    FY2014 (the only source covering FY14 published totals only). FY2022 methodology change: from FY22
    school total_expenditures (and per_pupil_expenditure) additionally include Allocated CCA and Alternative
    Program costs, so FY22+ school totals are not strictly comparable to FY17-FY21. This is the FESR included-cost
    per-pupil series — totals intentionally differ from the k12_expenditures function-level expenditure
    ledger. Placeholder $0 school expenditure amounts on unreported/non-compliant FY17-FY18 rows are NULLed
    per §4b (see _transform_manifest.json masked_values). A small number of district 660 (Atlanta) school
    rows carry negative expenditure / per-pupil values — faithful GOSA restatements preserved as-is. Currency
    is nominal dollars (not inflation-adjusted). No cell-level small-cell suppression exists in this source;
    NULLs are structural or §4b masks, never suppression.'
tags:
- education
- governor's office of student achievement
- per_pupil_expenditure
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/per_pupil_expenditure/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/per_pupil_expenditure/**/*.parquet
  format: parquet
schema:
- name: per_pupil_expenditure
  logicalType: object
  physicalType: parquet
  physicalName: per_pupil_expenditure
  description: per_pupil_expenditure fact table, year-partitioned Parquet (split by detail level where
    present).
  dataGranularityDescription: One row per year, district_code, school_code (geography columns are NULL
    at higher aggregation levels).
  customProperties:
  - property: sub_topic
    value: gosa
  - property: detail_levels
    value:
    - schools
    - districts
  - property: default_detail
    value: schools
  - property: grain
    value:
    - year
    - district_code
    - school_code
  - property: key_metric
    value: per_pupil_expenditure
  - property: null_semantics
    value:
      suppressed_to_null: false
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/per_pupil_expenditure/year={year}/{detail}.parquet
  - property: foreign_keys
    value:
    - column: district_code
      target_object: districts
      target_columns:
      - district_code
      attribute_columns:
      - district_name
      - district_census_id
      - district_type
      scope: domain
    - column: school_code
      target_object: schools
      target_columns:
      - district_code
      - school_code
      attribute_columns:
      - school_name
      scope: domain
  - property: example_queries
    value:
    - description: Latest year (2024), schools detail
      query: SELECT * FROM per_pupil_expenditure WHERE year = 2024 LIMIT 100
    - description: District 657 in 2024
      query: SELECT * FROM per_pupil_expenditure WHERE district_code = '657' AND year = 2024 LIMIT 100
  properties:
  - name: year
    logicalType: integer
    physicalType: int
    required: true
    description: 'Fiscal year the row''s expenditure and enrollment inputs refer to (e.g. FY2024 = 2023-24
      school year). Differs from the bronze filename year for back-window rows: each rolling FESR report
      covers up to 3 fiscal years, but gold keys every row by the actual fiscal year.'
    examples:
    - 2024
    customProperties:
    - property: column_role
      value: year
    - property: label
      value: Year
    primaryKey: true
    primaryKeyPosition: 1
  - name: district_code
    logicalType: string
    physicalType: string
    required: true
    description: GOSA district/system code (FK to districts dimension), zero-padded to 3 digits (7-digit
      codes are charter authorizers, school rows only). District-detail rows cover the 180 traditional
      school systems (codes 601-793); school-detail rows additionally include charter authorizer codes.
      Populated on every row.
    examples:
    - '657'
    relationships:
    - to: districts.district_code
    customProperties:
    - property: column_role
      value: fk_district
    - property: label
      value: District Code
    primaryKey: true
    primaryKeyPosition: 2
  - name: school_code
    logicalType: string
    physicalType: string
    required: false
    description: GOSA school code, zero-padded to 4 characters (composite FK to schools dimension with
      district_code; not globally unique on its own). Populated on school rows only — district aggregate
      rows carry NULL.
    examples:
    - '0103'
    relationships:
    - to: schools.district_code
    - to: schools.school_code
    customProperties:
    - property: column_role
      value: fk_school
    - property: label
      value: School Code
    - property: null_meaning
      value: Populated only on school-detail rows; NULL on every district-detail row (the district level
        has no school code). The geography-key NULL pattern is how detail level is read from the unioned
        table.
    primaryKey: true
    primaryKeyPosition: 3
  - name: total_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: Total expenditures for the fiscal year (nominal dollars, not inflation-adjusted). For
      DISTRICT rows this is the district's total included expenditures (= federal_expenditures + state_local_expenditures
      within $1, enforced by a quality check). For SCHOOL rows this is the per-school total — included
      expenditures plus the school's allocated share of district central costs (= total_federal_expenditures
      + total_state_local_expenditures within $10; from FY22 also folds in Allocated CCA and Alternative
      Program costs, so FY22+ school totals are not strictly comparable to FY17-FY21). per_pupil_expenditure
      = total_expenditures / fte_enrollment at both levels.
    examples:
    - 8125000.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Total Expenditures
    - property: unit
      value: currency
  - name: fte_enrollment
    logicalType: integer
    physicalType: bigint
    required: false
    description: Full-time-equivalent K-12 enrollment, the denominator of per_pupil_expenditure (per_pupil_expenditure
      = total_expenditures / fte_enrollment within rounding at both detail levels, enforced by a quality
      check).
    examples:
    - 984
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: FTE Enrollment
    - property: unit
      value: count
    - property: metric_component
      value: denominator
  - name: per_pupil_expenditure
    logicalType: number
    physicalType: double
    required: false
    description: Per-pupil expenditure for the fiscal year (nominal dollars) = total_expenditures / fte_enrollment,
      as published (rounded to cents by the source). A few Atlanta (district 660) school rows in FY21/FY22
      are negative — faithful GOSA restatements preserved per §4b, not errors.
    examples:
    - 9349.52
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Per-Pupil Expenditure
    - property: short_description
      value: Total dollars spent per student for the fiscal year (nominal, not inflation-adjusted).
    - property: unit
      value: currency
    - property: key_metric
      value: true
  - name: federal_per_pupil_expenditure
    logicalType: number
    physicalType: double
    required: false
    description: Federal portion of per_pupil_expenditure (nominal dollars). federal_per_pupil_expenditure
      + state_local_per_pupil_expenditure = per_pupil_expenditure within rounding (enforced by a quality
      check). NULL for district FY2014.
    examples:
    - 578.3
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Federal per Pupil Expenditure
    - property: unit
      value: currency
    - property: null_meaning
      value: NULL for district FY2014 (the 2016 workbook, the only source covering FY14, published no
        federal/state-local breakdown).
  - name: state_local_per_pupil_expenditure
    logicalType: number
    physicalType: double
    required: false
    description: State + local portion of per_pupil_expenditure (nominal dollars). NULL for district FY2014.
    examples:
    - 8771.22
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: State Local per Pupil Expenditure
    - property: unit
      value: currency
    - property: null_meaning
      value: NULL for district FY2014 (no breakdown published).
  - name: federal_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: Federal portion of the district's total_expenditures (nominal dollars). District-detail
      rows only. The school-level analogue of this quantity is total_federal_expenditures (both split
      the shared total_expenditures column); they are kept as separate columns because the school download
      names them differently and additionally publishes the pre-allocation included_* split, which districts
      do not.
    examples:
    - 5542000.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Federal Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on DISTRICT rows only (NULL on every school row — schools instead publish total_federal_expenditures,
        the same federal share of total_expenditures, plus the distinct included_federal_expenditures
        pre-allocation split). Also NULL for district FY2014, whose only source published totals without
        a breakdown.
  - name: state_local_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: State + local portion of the district's total_expenditures (nominal dollars). District-detail
      rows only; the school-level analogue is total_state_local_expenditures.
    examples:
    - 79501000.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: State Local Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on DISTRICT rows only (NULL on every school row — schools publish total_state_local_expenditures
        instead). Also NULL for district FY2014 (no breakdown).
  - name: included_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: Total included expenditures for the school (nominal dollars), BEFORE allocation of district
      central costs — the school's total_expenditures equals included_expenditures plus its allocated
      central-cost share. School-detail rows only. Placeholder $0 values on unreported/non-compliant FY17-FY18
      rows are NULLed per §4b (see limitations).
    examples:
    - 7413403.4
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Included Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on SCHOOL rows only (NULL on every district row — the district download has no
        pre-allocation included split; a district's total_expenditures IS its included total).
  - name: included_federal_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: Federal portion of the school's included_expenditures (nominal dollars). included_federal_expenditures
      + included_state_local_expenditures = included_expenditures (exact in the canonical bronze blocks;
      enforced by a quality check). School-detail rows only. FY17-FY18 placeholder $0 values NULLed per
      §4b.
    examples:
    - 456789.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Included Federal Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on SCHOOL rows only (NULL on district rows).
  - name: included_state_local_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: State + local portion of the school's included_expenditures (nominal dollars). School-detail
      rows only. FY17-FY18 placeholder $0 values NULLed per §4b.
    examples:
    - 6956614.4
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Included State Local Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on SCHOOL rows only (NULL on district rows).
  - name: excluded_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: Expenditures excluded from the PPE computation (nominal dollars) per the FESR methodology.
      NOT a component of total_expenditures (totals = included + allocated central costs). School-detail
      rows only. A few rows per year carry negative values — faithful GOSA restatements preserved per
      §4b. FY17-FY18 placeholder $0 values NULLed per §4b.
    examples:
    - 120000.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Excluded Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on SCHOOL rows only (NULL on district rows).
  - name: total_federal_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: Federal portion of the school's total_expenditures (post-allocation; nominal dollars).
      The school-level analogue of the district's federal_expenditures (both split the shared total_expenditures).
      School-detail rows only. FY17-FY18 placeholder $0 values NULLed per §4b.
    examples:
    - 500000.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Total Federal Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on SCHOOL rows only (NULL on district rows — the district analogue is federal_expenditures).
  - name: total_state_local_expenditures
    logicalType: number
    physicalType: double
    required: false
    description: State + local portion of the school's total_expenditures (post-allocation; nominal dollars).
      School-detail rows only; the district analogue is state_local_expenditures. FY17-FY18 placeholder
      $0 values NULLed per §4b.
    examples:
    - 7625000.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Total State Local Expenditures
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on SCHOOL rows only (NULL on district rows — the district analogue is state_local_expenditures).
  - name: pct_of_district_enrollment
    logicalType: number
    physicalType: double
    required: false
    description: 'The school''s share of its district''s total enrollment, 0-1 decimal (0.2912 = 29.12%),
      used by the FESR methodology to allocate district central costs. School-detail rows only. Caveat:
      within-district sums are not a strict partition — they exceed 1.0 in 552 district-years (55 of them
      by more than 0.005, up to 1.82), faithful to bronze.'
    examples:
    - 0.2912
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Percent of District Enrollment
    - property: unit
      value: proportion
    - property: null_meaning
      value: Populated on SCHOOL rows only (NULL on district rows). Also NULL for charter/excluded schools
        outside the district rollup.
  quality:
  - type: sql
    name: non_empty
    description: The dataset has at least one row.
    dimension: completeness
    query: SELECT COUNT(*) FROM {object}
    mustBeGreaterThan: 0
  - type: sql
    name: fte_enrollment_non_negative
    description: fte_enrollment is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE fte_enrollment IS NOT NULL AND fte_enrollment < 0
    mustBe: 0
  - type: sql
    name: pct_of_district_enrollment_within_unit_interval
    description: pct_of_district_enrollment is a bounded proportion on the [0, 1] scale.
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE pct_of_district_enrollment IS NOT NULL AND (pct_of_district_enrollment
      < 0 OR pct_of_district_enrollment > 1)
    mustBe: 0
  - type: sql
    name: ppe_consistent_with_total_over_fte
    description: per_pupil_expenditure = total_expenditures / fte_enrollment within $0.05 wherever all
      three are published and enrollment is positive, at BOTH detail levels (source rounds PPE to cents;
      max observed deviation ~$0.021).
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE per_pupil_expenditure IS NOT NULL AND total_expenditures
      IS NOT NULL AND fte_enrollment IS NOT NULL AND fte_enrollment > 0 AND ABS(per_pupil_expenditure
      - total_expenditures / fte_enrollment) > 0.05
    mustBe: 0
  - type: sql
    name: per_pupil_components_reconcile
    description: federal_per_pupil_expenditure + state_local_per_pupil_expenditure = per_pupil_expenditure
      within $0.20 wherever all three are published, at both detail levels (canonical blocks reconcile
      within ~$0.016; the loosest rows are two FY17 schools from the 2019 file's re-keyed back-window
      at $0.170).
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE per_pupil_expenditure IS NOT NULL AND federal_per_pupil_expenditure
      IS NOT NULL AND state_local_per_pupil_expenditure IS NOT NULL AND ABS(per_pupil_expenditure - (federal_per_pupil_expenditure
      + state_local_per_pupil_expenditure)) > 0.20
    mustBe: 0
  - type: sql
    name: district_expenditure_components_reconcile
    description: 'District rows (school_code IS NULL): federal_expenditures + state_local_expenditures
      = total_expenditures within $1 wherever the breakdown exists (exact to the cent in every gold-source
      block).'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NULL AND total_expenditures IS NOT NULL
      AND federal_expenditures IS NOT NULL AND state_local_expenditures IS NOT NULL AND ABS(federal_expenditures
      + state_local_expenditures - total_expenditures) > 1.0
    mustBe: 0
  - type: sql
    name: school_included_components_reconcile
    description: 'School rows (school_code IS NOT NULL): included_federal_expenditures + included_state_local_expenditures
      = included_expenditures within $1 wherever all three are published (exact in the canonical bronze
      blocks; gold max gap $0.00).'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL AND included_expenditures IS NOT
      NULL AND included_federal_expenditures IS NOT NULL AND included_state_local_expenditures IS NOT
      NULL AND ABS(included_expenditures - (included_federal_expenditures + included_state_local_expenditures))
      > 1.0
    mustBe: 0
  - type: sql
    name: school_total_components_reconcile
    description: 'School rows: total_federal_expenditures + total_state_local_expenditures = total_expenditures
      within $10 wherever all three are published (GOSA rounding noise; gold max gap $7.00, FY20).'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL AND total_expenditures IS NOT NULL
      AND total_federal_expenditures IS NOT NULL AND total_state_local_expenditures IS NOT NULL AND ABS(total_expenditures
      - (total_federal_expenditures + total_state_local_expenditures)) > 10.0
    mustBe: 0
  - type: sql
    name: school_only_columns_null_on_district_rows
    description: School-only columns (included_*, excluded_expenditures, total_federal/state_local_expenditures,
      pct_of_district_enrollment) must be NULL on every district row (school_code IS NULL) — they have
      no district analog.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NULL AND (included_expenditures IS NOT NULL
      OR included_federal_expenditures IS NOT NULL OR included_state_local_expenditures IS NOT NULL OR
      excluded_expenditures IS NOT NULL OR total_federal_expenditures IS NOT NULL OR total_state_local_expenditures
      IS NOT NULL OR pct_of_district_enrollment IS NOT NULL)
    mustBe: 0
  - type: sql
    name: district_only_columns_null_on_school_rows
    description: District-only columns (federal_expenditures, state_local_expenditures) must be NULL on
      every school row (school_code IS NOT NULL).
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL AND (federal_expenditures IS NOT
      NULL OR state_local_expenditures IS NOT NULL)
    mustBe: 0
  - type: sql
    name: district_code_never_null
    description: Every row (district or school) carries a populated district_code — this topic has no
      state-rollup rows.
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE district_code IS NULL
    mustBe: 0
  - type: sql
    name: district_code_length_3_or_7
    description: district_code is a 3-char county/city code or a 7-char charter-authorizer code — any
      other length means the ID formatting regressed.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE LENGTH(district_code) NOT IN (3, 7)
    mustBe: 0
  - type: sql
    name: school_code_length_4_when_present
    description: Where populated (school rows), school_code is zero-padded to exactly 4 characters, matching
      the schools dimension key.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL AND LENGTH(school_code) <> 4
    mustBe: 0
  - type: sql
    name: district_universe_complete_per_year
    description: Every fiscal year carries exactly one district row for each of the 180 traditional districts
      (identical code universe in all 9 district bronze files). A different count means rows were dropped
      or a new entity appeared.
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year FROM {object} WHERE school_code IS NULL GROUP BY year HAVING
      COUNT(*) <> 180 OR COUNT(DISTINCT district_code) <> 180) AS bad_years
    mustBe: 0
customProperties:
- property: title
  value: Per-Pupil Expenditure (FESR)
- property: summary
  value: Annual per-pupil spending — total, federal, and state/local — for Georgia school districts (2014-2024)
    and schools (2017-2024), from the FESR methodology.
- property: source
  value: Governor's Office of Student Achievement (GOSA)
- property: source_url
  value: https://gosa.georgia.gov/dashboards-data-report-card/downloadable-data
- property: update_frequency
  value: annual
- property: year_range
  value: 2014-2024
- property: schema_hash
  value: ea9016eba4caacdc00c5419c8999813a5aea53f943ac347a2478fe947519bb33
- property: available_years
  value:
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
