apiVersion: v3.1.0
kind: DataContract
id: education.financial_efficiency_star_rating
name: financial_efficiency_star_rating
version: 2.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Georgia Office of Student Achievement (GOSA) Financial Efficiency Star Rating (FESR) — the
    program''s efficiency evaluation of each rolling window, rating how much a district spends per student
    against its CCRPI academic results on a 0.5-5.0 star scale (0.5 increments; more stars = better academic
    outcomes per dollar). Rows exist ONLY for the fiscal years an FESR publication evaluated: district
    rows FY2016-FY2019 and FY2024 (one per traditional Georgia system, with the three-year rolling per-pupil-expenditure
    average, the three-year CCRPI average, and the PPE percentile the rating is built from), and school
    rows FY2017-FY2019 and FY2024 (rating only — schools publish no rolling metrics). GOSA paused the
    program FY2020-FY2023, so those years have no rows. The FY2024 report revived the rating with a non-contiguous
    FY19+FY23+FY24 window. The underlying annual spending panel (total/federal/state-local expenditures,
    FTE enrollment, per-pupil expenditure for every fiscal year) now lives in the sibling per_pupil_expenditure
    topic, from which each rating''s rolling window is built.'
  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: 'Rows exist only for FESR publication years — district rows FY2016-FY2019 and FY2024, school
    rows FY2017-FY2019 and FY2024. GOSA paused the program FY2020-FY2023: those years have NO rows (they
    are not NULL-padded), and year-over-year comparisons across the pause span a five-year gap and a methodology
    revision (the FY2024 rating uses a non-contiguous FY19+FY23+FY24 window). Two detail levels (district,
    school) in one fact table, split by parquet filename; school_code is NULL on district rows. The rolling-window
    metrics (per_pupil_expenditure_three_year_avg, ccrpi_three_year_avg, per_pupil_expenditure_percentile)
    are district-only — school rows carry the rating and compliance flag alone. Report-year school rows
    with no rating outcome at all are omitted (their spending facts live in the per_pupil_expenditure
    topic). The single FY2021 pause-year non-compliance flag (school 7830629/0629) is not carried — it
    falls outside the publication-year row structure. The annual spending panel behind the ratings (expenditures,
    FTE enrollment, per-pupil expenditure, FY2014-FY2024) lives in the sibling per_pupil_expenditure topic;
    the former merged topic''s ccrpi_single_score column was dropped from both derived topics in the v2
    split (annual CCRPI scores are served by the ccrpi_* topics). FESR methodology evolved across publications;
    cross-year comparisons should account for GOSA-documented methodology drift. No cell-level small-cell
    suppression exists in this source; NULLs are structural, never suppression.'
tags:
- education
- governor's office of student achievement
- financial_efficiency_star_rating
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/financial_efficiency_star_rating/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/financial_efficiency_star_rating/**/*.parquet
  format: parquet
schema:
- name: financial_efficiency_star_rating
  logicalType: object
  physicalType: parquet
  physicalName: financial_efficiency_star_rating
  description: financial_efficiency_star_rating 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: fesr_star_rating
  - property: null_semantics
    value:
      suppressed_to_null: false
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/financial_efficiency_star_rating/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 financial_efficiency_star_rating WHERE year = 2024 LIMIT 100
    - description: District 657 in 2024
      query: SELECT * FROM financial_efficiency_star_rating WHERE district_code = '657' AND year = 2024
        LIMIT 100
    - description: Filter by is_non_compliant = False
      query: SELECT * FROM financial_efficiency_star_rating WHERE is_non_compliant = 'False' AND year
        = 2024 LIMIT 100
  properties:
  - name: year
    logicalType: integer
    physicalType: int
    required: true
    description: 'Fiscal year the FESR publication treats as its newest (rated) year. Only publication
      years exist in this topic: 2016-2019 and 2024 for districts, 2017-2019 and 2024 for schools (the
      program paused FY2020-FY2023 — those years have no rows).'
    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: per_pupil_expenditure_three_year_avg
    logicalType: number
    physicalType: double
    required: false
    description: Three-year rolling average of the district's per-pupil expenditure (nominal dollars)
      over the publication's window — a rating input. FY2024's window is non-contiguous (FY19+FY23+FY24).
      District-detail rows only. The yearly per-pupil values live in the sibling per_pupil_expenditure
      topic.
    examples:
    - 9024.04
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Per Pupil Expenditure Three Year Average
    - property: unit
      value: currency
    - property: null_meaning
      value: Populated on DISTRICT rows only; NULL on every school row (schools do not publish rolling
        averages — derivable from the per_pupil_expenditure topic's yearly values).
  - name: ccrpi_three_year_avg
    logicalType: number
    physicalType: double
    required: false
    description: Three-year rolling average of the district's CCRPI Single Score (0-100 scale) over the
      publication's window — the academic-outcome input of the rating. District-detail rows only.
    examples:
    - 71.2
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: CCRPI Three Year Average
    - property: unit
      value: score
    - property: value_min
      value: 0
    - property: value_max
      value: 100
    - property: null_meaning
      value: Populated on DISTRICT rows only; NULL on every school row.
  - name: per_pupil_expenditure_percentile
    logicalType: integer
    physicalType: bigint
    required: false
    description: Percentile rank (1-100 integer) of the district's per_pupil_expenditure_three_year_avg
      among all 180 Georgia districts in the same publication — population-based, not a national percentile.
      Higher = more spending per pupil. District-detail rows only.
    examples:
    - 76
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Per Pupil Expenditure Percentile
    - property: unit
      value: percentile
    - property: null_meaning
      value: Populated on DISTRICT rows only; NULL on every school row.
  - name: fesr_star_rating
    logicalType: number
    physicalType: double
    required: false
    description: 'Financial Efficiency Star Rating in 0.5 increments. Districts: observed 1.0-5.0; schools:
      0.5-5.0 (the value range spans both levels). Half-steps enforced by a quality check. Bronze stores
      it as Float64 in early files and as strings in later files; non-numeric publisher statuses (district
      ''Non-Compliant''; school ''.'', ''Non-Compliant[ in 20YY]'') are NULLed here, with non-compliance
      preserved via is_non_compliant.'
    examples:
    - 3.5
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: FESR Star Rating
    - property: short_description
      value: Financial efficiency star rating, 0.5-5.0 in half-star steps — more stars mean better academic
        outcomes per dollar spent.
    - property: unit
      value: rating
    - property: value_min
      value: 0.5
    - property: value_max
      value: 5
    - property: null_meaning
      value: NULL where the publisher issued a 'Non-Compliant' status (is_non_compliant=true) or no rating
        (the school-file '.' sentinel) instead of a numeric rating.
    - property: key_metric
      value: true
  - name: is_non_compliant
    logicalType: boolean
    physicalType: boolean
    required: false
    description: 'FESR publication outcome: true when the source flags the entity non-compliant (no star
      rating exists, so fesr_star_rating is NULL — enforced by a quality check); false when the publisher
      processed the entity normally; NULL when no outcome was published. The false/NULL boundary is ERA-DEPENDENT,
      faithful to bronze (same behavior as the pre-split approved topic): the 2017/2018 school files publish
      an explicit status column, so rated rows AND the ''.'' listed-without-rating rows (103 in 2017,
      127 in 2018) carry false; the 2019/2024 numeric school files publish no status, so rated rows carry
      NULL. A school''s 2018-file ''Non-Compliant in 2017'' sentinel is served on both the 2017 and 2018
      rows (Cirrus Charter 7830611/0611), as the publisher restated it. Districts: the 2018 report''s
      ''Non-Compliant'' status (Talbot County 730). The FY2021 pause-year PPE-sentinel flag (7830629/0629)
      falls outside the publication years and is not carried (see limitations).'
    examples:
    - false
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Is Non Compliant
    - property: short_description
      value: True when the district or school was flagged non-compliant with financial reporting (no star
        rating issued); false otherwise — including rows the publisher listed without issuing a rating,
        so false does not guarantee a published rating.
    - property: null_meaning
      value: NULL where no compliance outcome was published — the numeric-FESR school files (2019/2024)
        publish ratings without a flag, so unrated schools in those years carry no outcome.
  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: ccrpi_three_year_avg_within_range
    description: ccrpi_three_year_avg is within its declared [0, 100] range.
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE ccrpi_three_year_avg IS NOT NULL AND (ccrpi_three_year_avg
      < 0 OR ccrpi_three_year_avg > 100)
    mustBe: 0
  - type: sql
    name: per_pupil_expenditure_percentile_within_range
    description: per_pupil_expenditure_percentile is within its declared [0, 100] range.
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE per_pupil_expenditure_percentile IS NOT NULL AND (per_pupil_expenditure_percentile
      < 0 OR per_pupil_expenditure_percentile > 100)
    mustBe: 0
  - type: sql
    name: fesr_star_rating_within_range
    description: fesr_star_rating is within its declared [0.5, 5] range.
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE fesr_star_rating IS NOT NULL AND (fesr_star_rating < 0.5
      OR fesr_star_rating > 5)
    mustBe: 0
  - type: sql
    name: fesr_star_rating_half_step
    description: 'fesr_star_rating is a half-step rating: 2x the value is a whole number (0.5 increments).'
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE fesr_star_rating IS NOT NULL AND (fesr_star_rating * 2)
      != ROUND(fesr_star_rating * 2)
    mustBe: 0
  - type: sql
    name: non_compliant_implies_no_rating
    description: 'Co-null rule: is_non_compliant = TRUE implies fesr_star_rating IS NULL (a non-compliant
      outcome replaces the star rating) at both detail levels.'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE is_non_compliant = TRUE AND fesr_star_rating IS NOT NULL
    mustBe: 0
  - type: sql
    name: rows_only_in_publication_years
    description: 'Structural fact of the v2 shape: rows exist for exactly the five FESR publication years
      {2016, 2017, 2018, 2019, 2024} — no row outside them (program paused FY2020-FY2023) and none of
      the five missing.'
    dimension: consistency
    query: SELECT (SELECT COUNT(*) FROM {object} WHERE year NOT IN (2016, 2017, 2018, 2019, 2024)) + ABS(5
      - (SELECT COUNT(DISTINCT year) FROM {object}))
    mustBe: 0
  - type: sql
    name: school_rows_only_in_school_publication_years
    description: 'The school level starts FY2017: school rows exist only in {2017, 2018, 2019, 2024} (FY2016
      is district-only).'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL AND year NOT IN (2017, 2018, 2019,
      2024)
    mustBe: 0
  - type: sql
    name: rolling_metrics_district_only
    description: The rolling-window metrics (per_pupil_expenditure_three_year_avg, ccrpi_three_year_avg,
      per_pupil_expenditure_percentile) are district-only and 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 (per_pupil_expenditure_three_year_avg
      IS NOT NULL OR ccrpi_three_year_avg IS NOT NULL OR per_pupil_expenditure_percentile IS NOT NULL)
    mustBe: 0
  - type: sql
    name: district_rating_implies_window_metrics_present
    description: On district rows, a published star rating is computed from the rolling window, so a non-NULL
      fesr_star_rating implies per_pupil_expenditure_three_year_avg, ccrpi_three_year_avg, and per_pupil_expenditure_percentile
      are all populated on the same row.
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NULL AND fesr_star_rating IS NOT NULL AND
      (per_pupil_expenditure_three_year_avg IS NULL OR ccrpi_three_year_avg IS NULL OR per_pupil_expenditure_percentile
      IS NULL)
    mustBe: 0
  - type: sql
    name: district_rating_or_flag_present
    description: 'Every district row carries an FESR outcome: a numeric star rating or is_non_compliant
      = TRUE (verified: all 180 districts in every publication year).'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NULL AND fesr_star_rating IS NULL AND (is_non_compliant
      IS NULL OR is_non_compliant = FALSE)
    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
customProperties:
- property: title
  value: Financial Efficiency Star Rating (FESR)
- property: summary
  value: 0.5-5 star financial efficiency ratings — spending versus academic outcomes — for Georgia school
    districts and schools in FESR publication years (2016-2019, 2024).
- 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: 2016-2024
- property: schema_hash
  value: 388fc5498ebc79a423c540b8a32ea8ff4fd6351d222cf2ef695e8f9f99d89fa1
- property: available_years
  value:
  - 2016
  - 2017
  - 2018
  - 2019
  - 2024
- property: year_gaps
  value:
  - 2020
  - 2021
  - 2022
  - 2023
