apiVersion: v3.1.0
kind: DataContract
id: education.k12_revenues
name: k12_revenues
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'K-12 revenues for every Georgia public school district and the state as a whole, by funding
    source, published by the Governor''s Office of Student Achievement (GOSA). Each row reports the total
    dollar amount and the per-FTE (full-time-equivalent student) dollar amount for one of 6 revenue sources
    — local, federal, state QBE (Quality Basic Education formula), state lottery, state other, and other
    — covering school years 2010-11 through 2023-24. District and state detail only: the source never
    publishes school-level revenues. The spending side of the same GOSA report lives in the sibling topic
    `k12_expenditures` (K-12 expenditures by spending function, school detail included); the two topics
    split the retired combined `revenues_and_expenditures` topic (one measure family per fact table) and
    share one bronze source, so they join cleanly on year and district_code.'
  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: 'District and state detail only — the source never publishes school-level revenues, so
    school_code is always NULL (school-level finance exists only on the expenditure side; see the sibling
    topic k12_expenditures). State rows have NULL district_code and school_code. District detail includes
    the 16 RESA service-agency rollups (district codes 850-888) — exclude them when analyzing traditional
    districts only. State rows are published independently of district rows and do not always equal the
    sum of district values within a source — treat each detail level as its own published series. revenue_per_pupil
    is not a true per-pupil amount everywhere: a few dozen district rows per year publish it equal to
    revenue_amount verbatim (entities with no FTE denominator, mostly RESA rollups). No suppression exists
    in this topic — NULL never means suppressed; the only NULL metric values are 192 revenue_per_pupil
    district rows in 2013-2014 with a missing FTE denominator. Negative dollar values are legitimate budget
    adjustments and are preserved.'
tags:
- education
- governor's office of student achievement
- k12_revenues
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/k12_revenues/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/k12_revenues/**/*.parquet
  format: parquet
schema:
- name: k12_revenues
  logicalType: object
  physicalType: parquet
  physicalName: k12_revenues
  description: k12_revenues fact table, year-partitioned Parquet (split by detail level where present).
  dataGranularityDescription: One row per year, district_code, school_code, revenue_source (geography
    columns are NULL at higher aggregation levels).
  customProperties:
  - property: sub_topic
    value: gosa
  - property: detail_levels
    value:
    - districts
    - states
  - property: default_detail
    value: districts
  - property: grain
    value:
    - year
    - district_code
    - school_code
    - revenue_source
  - property: key_metric
    value: revenue_amount
  - property: null_semantics
    value:
      suppressed_to_null: false
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/k12_revenues/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), districts detail
      query: SELECT * FROM k12_revenues WHERE year = 2024 LIMIT 100
    - description: District 644 in 2024
      query: SELECT * FROM k12_revenues WHERE district_code = '644' AND year = 2024 LIMIT 100
    - description: Filter by revenue_source = state_qbe
      query: SELECT * FROM k12_revenues WHERE revenue_source = 'state_qbe' AND year = 2024 LIMIT 100
  properties:
  - name: year
    logicalType: integer
    physicalType: int
    required: true
    description: Ending (spring) calendar year of the school year (e.g. 2024 for 2023-24), parsed from
      the source's SCHOOL_YEAR / LONG_SCHOOL_YEAR column and cross-checked against the filename 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: false
    description: 'GOSA district code (FK to districts dimension): 3-digit zero-padded county/city codes
      or 7-digit compound codes for commission / state charter schools. Codes 850-888 are RESA service
      agencies (typed ''resa'' in the dimension). NULL on state-level rows — the bronze sentinel ''ALL''
      is mapped to NULL.'
    examples:
    - '644'
    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: 'Always NULL: the source publishes revenues at district and state detail only — there
      are no school-level revenue rows in any year (enforced by a quality check and a transform-time assertion).
      The column exists so every education fact table shares the same key-column shape. For school-level
      finance, see the sibling topic k12_expenditures (expenditures only).'
    relationships:
    - to: schools.district_code
    - to: schools.school_code
    customProperties:
    - property: column_role
      value: fk_school
    - property: label
      value: School Code
    primaryKey: true
    primaryKeyPosition: 3
  - name: revenue_source
    logicalType: string
    physicalType: string
    required: true
    description: 'Funding source of the revenue (6 values): local (local taxes and other local funds),
      federal, state_qbe (Georgia''s Quality Basic Education formula allotment), state_lottery, state_other
      (state funds outside QBE and lottery), and other. NOTE: state_lottery revenue_amount is published
      as 0.0 on every row of every year in this source (lottery dollars evidently flow outside this ledger)
      — the category is kept for grid completeness, faithful to bronze. Every district and state row set
      carries all 6 sources in every year (enforced by quality checks), so summing revenue_amount over
      the 6 sources gives the entity''s total revenue.'
    examples:
    - 'state_qbe'
    enum:
    - 'federal'
    - 'local'
    - 'other'
    - 'state_lottery'
    - 'state_other'
    - 'state_qbe'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Revenue Source
    - property: short_description
      value: 'Funding source of the revenue: local, federal, state_qbe, state_lottery, state_other, or
        other.'
    - property: has_total
      value: false
    - property: key_metric_grain_contributor
      value: true
    - property: value_coverage
      value:
      - value: 'federal'
        first_year: 2011
        last_year: 2024
        rows: 3117
      - value: 'local'
        first_year: 2011
        last_year: 2024
        rows: 3117
      - value: 'other'
        first_year: 2011
        last_year: 2024
        rows: 3117
      - value: 'state_lottery'
        first_year: 2011
        last_year: 2024
        rows: 3117
      - value: 'state_other'
        first_year: 2011
        last_year: 2024
        rows: 3117
      - value: 'state_qbe'
        first_year: 2011
        last_year: 2024
        rows: 3117
    primaryKey: true
    primaryKeyPosition: 4
  - name: revenue_amount
    logicalType: number
    physicalType: double
    required: false
    description: Total dollar amount received from this revenue source at the row's detail level. Never
      NULL (enforced by a quality check — the source publishes no suppression). Negative values are legitimate
      budget adjustments / corrections and are preserved; no range check applies.
    examples:
    - 3960000.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Revenue Amount
    - property: short_description
      value: Total dollars received from this revenue source at the row's level; negatives are real budget
        adjustments.
    - property: unit
      value: currency
    - property: key_metric
      value: true
  - name: revenue_per_pupil
    logicalType: number
    physicalType: double
    required: false
    description: 'Per-FTE (full-time-equivalent student) dollar amount received from this revenue source.
      Can be negative (budget adjustments). NULL only in 2013-2014 where the bronze FTE denominator was
      missing (192 district rows; enforced by a quality check). Caution — not always a true per-pupil
      amount: the 16 RESA service-agency district rows (codes 850-888, no FTE denominator) publish revenue_per_pupil
      equal to revenue_amount verbatim in EVERY year including 2023-2024 (all 438 nonzero verbatim rows
      across all years are exactly those RESAs). Standard-district and state rows carry true per-FTE values.
      Verify revenue_per_pupil <> revenue_amount (or sanity-check the magnitude) before treating a value
      as per-pupil revenue.'
    examples:
    - 6696.37
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Revenue per Pupil
    - property: unit
      value: currency
    - property: null_meaning
      value: NULL = the entity has no FTE denominator in the 2013-2014 source files (the only revenue
        rows whose bronze FTE_COUNT was missing; 192 district rows), NOT suppression.
  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: revenue_source_in_allowed_values
    description: revenue_source is one of the 6 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE revenue_source IS NOT NULL AND revenue_source NOT IN ('federal',
      'local', 'other', 'state_lottery', 'state_other', 'state_qbe')
    mustBe: 0
  - type: sql
    name: state_rows_complete_every_year
    description: Every year publishes exactly 6 state rows — one per revenue_source value, covering all
      6 sources (verified across all 14 bronze files). State rows self-scope via NULL geography keys.
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year FROM {object} WHERE district_code IS NULL AND school_code
      IS NULL GROUP BY year HAVING COUNT(*) <> 6 OR COUNT(DISTINCT revenue_source) <> 6) AS bad_years
    mustBe: 0
  - type: sql
    name: district_entities_carry_all_6_sources
    description: Every district-detail entity (districts, RESA rollups, charters) carries exactly 6 rows
      covering all 6 revenue_source values in every year it appears (verified across all 14 bronze files;
      the retired combined topic's 17-pair check restricted to the revenue family). The check scopes to
      district rows via geography NULL-ness.
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year, district_code FROM {object} WHERE district_code IS NOT NULL
      AND school_code IS NULL GROUP BY year, district_code HAVING COUNT(*) <> 6 OR COUNT(DISTINCT revenue_source)
      <> 6) AS bad_entities
    mustBe: 0
  - type: sql
    name: revenue_amount_never_null
    description: 'The source publishes no suppression and no missing dollar amounts: the total-dollars
      column is populated on every row of all 14 bronze files (verified). A NULL means a parsing regression
      or a new suppression regime — either must be analyzed, not silently passed.'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE revenue_amount IS NULL
    mustBe: 0
  - type: sql
    name: revenue_per_pupil_null_only_in_2013_2014
    description: 'revenue_per_pupil NULLs are confined to 2013-2014 — the revenue rows whose Era 3 bronze
      FTE_COUNT denominator was NULL (192 district rows: 96 in 2013, 96 in 2014). Every other year is
      fully populated (verified; the retired combined topic''s 2012 NULLs were all expenditure rows, so
      this topic''s window is tighter). A NULL elsewhere means a parsing regression or a source change.'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE revenue_per_pupil IS NULL AND year NOT IN (2013, 2014)
    mustBe: 0
  - type: sql
    name: school_code_always_null
    description: 'Structural fact: this is a district/state-only topic — the source has never published
      school-level revenues (verified across all 14 bronze files; the transform also hard-asserts it before
      export). school_code exists only to keep the shared education key-column shape and is always NULL.'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL
    mustBe: 0
customProperties:
- property: title
  value: K-12 Revenues by Funding Source
- property: summary
  value: K-12 dollars taken in by Georgia public school districts, broken out by funding source (local,
    federal, state QBE, lottery, other), 2011-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: 2011-2024
- property: schema_hash
  value: 4907a5512e84783b27496f5662c5871fde2c355be3931669f43f0d5d7355371c
- property: available_years
  value:
  - 2011
  - 2012
  - 2013
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
