apiVersion: v3.1.0
kind: DataContract
id: education.certified_personnel_compensation
name: certified_personnel_compensation
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Georgia Office of Student Achievement (GOSA) certified-personnel compensation dataset, compiled
    from local school systems'' Certified/Classified Personnel Information (CPI) submissions. For every
    Georgia public school, school district, and the state as a whole, publishes four position measures
    per employee group (Administrators, PK-12 Teachers, Support Personnel): the full-time-equivalent (FTE)
    count of certified positions and the average annual salary, average daily salary, and average contract
    days of those positions. Coverage runs from the 2010-11 school year through 2023-24. This is one of
    three topics split from the retired certified_personnel dataset, all reading the same GOSA source:
    staff headcounts by certificate level, certification status, gender, employment status, and race/ethnicity
    live in certified_personnel_composition, and years-of-experience band headcounts live in certified_personnel_experience.'
  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: 'This source has no suppression markers in any year (2010-11 through 2023-24): every value
    is published, zeros are real, and all four metric columns are non-null on every row. avg_annual_salary,
    avg_daily_salary, and avg_contract_days are entity-level means — never sum them across entities, and
    a state average is not the sum (or mean) of district averages; use the published district/state rows
    for aggregate analysis. avg_annual_salary can be a real 0.0 at small cells where an entity reports
    positions without salary data. fte_positions is an FTE-style fractional position count (part-time
    positions count as fractions), and it is the N behind the three averages; do not sum school rows to
    rebuild district or state position totals. State rows have NULL district_code and school_code; district
    rows have NULL school_code.'
tags:
- education
- governor's office of student achievement
- certified_personnel_compensation
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/certified_personnel_compensation/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/certified_personnel_compensation/**/*.parquet
  format: parquet
schema:
- name: certified_personnel_compensation
  logicalType: object
  physicalType: parquet
  physicalName: certified_personnel_compensation
  description: certified_personnel_compensation fact table, year-partitioned Parquet (split by detail
    level where present).
  dataGranularityDescription: One row per year, district_code, school_code, employee_type (geography columns
    are NULL at higher aggregation levels).
  customProperties:
  - property: sub_topic
    value: gosa
  - property: detail_levels
    value:
    - schools
    - districts
    - states
  - property: default_detail
    value: schools
  - property: grain
    value:
    - year
    - district_code
    - school_code
    - employee_type
  - property: key_metric
    value: avg_annual_salary
  - property: null_semantics
    value:
      suppressed_to_null: false
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/certified_personnel_compensation/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 certified_personnel_compensation WHERE year = 2024 LIMIT 100
    - description: District 644 in 2024
      query: SELECT * FROM certified_personnel_compensation WHERE district_code = '644' AND year = 2024
        LIMIT 100
    - description: Filter by employee_type = pk_12_teachers
      query: SELECT * FROM certified_personnel_compensation WHERE employee_type = 'pk_12_teachers' AND
        year = 2024 LIMIT 100
  properties:
  - name: year
    logicalType: integer
    physicalType: int
    required: true
    description: Ending calendar year of the school year. Year 2024 = 2023-2024 school year. Derived from
      the bronze `LONG_SCHOOL_YEAR` column's ending 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 the education districts dimension): 3-digit zero-padded for
      standard districts, 7-digit for state/commission charter schools (present from 2012 onward). NULL
      for state-level aggregate rows (the bronze sentinel `ALL`).'
    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: 4-digit zero-padded GOSA school code (FK to the education schools dimension, composite
      key with district_code). NULL for district-level and state-level aggregate rows (the bronze sentinel
      `ALL`).
    examples:
    - '0189'
    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: employee_type
    logicalType: string
    physicalType: string
    required: true
    description: 'Certified employee group this row''s position and compensation figures cover. Snake_case
      recode of bronze EMPLOYEE_TYPE: `administrators`, `pk_12_teachers`, `support_personnel`.'
    examples:
    - 'pk_12_teachers'
    enum:
    - 'administrators'
    - 'pk_12_teachers'
    - 'support_personnel'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Employee Type
    - property: short_description
      value: 'Which staff group the row covers: administrators, PK-12 teachers, or support personnel.'
    - property: has_total
      value: false
    - property: key_metric_grain_contributor
      value: true
    - property: value_coverage
      value:
      - value: 'administrators'
        first_year: 2011
        last_year: 2024
        rows: 39742
      - value: 'pk_12_teachers'
        first_year: 2011
        last_year: 2024
        rows: 37014
      - value: 'support_personnel'
        first_year: 2011
        last_year: 2024
        rows: 38823
    primaryKey: true
    primaryKeyPosition: 4
  - name: fte_positions
    logicalType: number
    physicalType: double
    required: true
    description: Full-time-equivalent (FTE) count of certified positions for this employee group (bronze
      Positions/Number). An FTE-style count, so legitimately fractional — part-time positions contribute
      fractions of a position. This is the N behind the three salary/contract-day averages (their denominator).
      Do not sum school rows to rebuild district or state totals — use the published aggregate rows.
    examples:
    - 52.5
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: FTE Positions
    - property: short_description
      value: Full-time-equivalent count of certified positions in this staff group — fractional because
        part-time positions count as fractions.
    - property: unit
      value: count
    - property: metric_component
      value: denominator
  - name: avg_annual_salary
    logicalType: number
    physicalType: double
    required: true
    description: 'Average annual salary in US dollars across this entity''s certified positions in the
      employee group (bronze Positions/Average Annual Salary). An entity-level mean: never sum across
      entities, and a state average is not the sum (or mean) of district averages. Reconciles with avg_daily_salary
      x avg_contract_days to within $5 (enforced by a quality check). A value of 0.0 is a real published
      figure, occurring at small cells where an entity reports positions without salary data (observed
      max: 250,831.94).'
    examples:
    - 62108.55
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Average Annual Salary
    - property: short_description
      value: Average annual salary (US dollars) of this staff group's certified positions.
    - property: unit
      value: currency
    - property: key_metric
      value: true
  - name: avg_daily_salary
    logicalType: number
    physicalType: double
    required: true
    description: 'Average daily salary in US dollars across this entity''s certified positions in the
      employee group (bronze Positions/Average Daily Salary). An entity-level mean: never sum across entities.
      Multiplied by avg_contract_days it reproduces avg_annual_salary to within $5 (observed max: 2,764.56).'
    examples:
    - 328.62
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Average Daily Salary
    - property: short_description
      value: Average daily salary (US dollars) of this staff group's certified positions.
    - property: unit
      value: currency
  - name: avg_contract_days
    logicalType: number
    physicalType: double
    required: true
    description: 'Average number of contract days per year across this entity''s certified positions in
      the employee group (bronze Positions/Average Contract Days). An entity-level mean: never sum across
      entities. Declares no `unit` (days are neither a rescaled percentage nor a currency); the [0, 366]
      range is enforced by an authored quality check instead (observed bronze range: [30, 260]).'
    examples:
    - 190.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Average Contract Days
    - property: short_description
      value: Average number of contract days per year for this staff group's certified positions.
  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: employee_type_in_allowed_values
    description: employee_type is one of the 3 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE employee_type IS NOT NULL AND employee_type NOT IN ('administrators',
      'pk_12_teachers', 'support_personnel')
    mustBe: 0
  - type: sql
    name: fte_positions_non_negative
    description: fte_positions is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE fte_positions IS NOT NULL AND fte_positions < 0
    mustBe: 0
  - type: sql
    name: avg_contract_days_within_range
    description: 'Average contract days lies within [0, 366] — a contract cannot exceed a year. Authored
      because avg_contract_days declares no unit (days are not a rescaled percentage, so no range check
      auto-derives). Observed bronze range across all 14 years: [30, 260].'
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE avg_contract_days IS NOT NULL AND (avg_contract_days <
      0 OR avg_contract_days > 366)
    mustBe: 0
  - type: sql
    name: annual_salary_reconciles_daily_times_days
    description: 'The three compensation figures describe the same positions: avg_annual_salary equals
      avg_daily_salary x avg_contract_days to within $5. Verified across all 115,579 cells in the 14 bronze
      years (max absolute error 4.98).'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE avg_annual_salary IS NOT NULL AND avg_daily_salary IS NOT
      NULL AND avg_contract_days IS NOT NULL AND ABS(avg_annual_salary - (avg_daily_salary * avg_contract_days))
      > 5.0
    mustBe: 0
  - type: sql
    name: state_rows_exactly_3_per_year
    description: 'Structural fact: every year carries exactly 3 state-level rows — one per employee group
      (administrators, PK-12 teachers, support personnel). Verified in all 14 bronze years.'
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year FROM {object} WHERE district_code IS NULL GROUP BY year HAVING
      COUNT(*) <> 3) AS bad
    mustBe: 0
  - type: sql
    name: compensation_metrics_never_null
    description: GOSA publishes the complete Positions grid for every (year, geography, employee_type)
      cell — nothing is suppressed and zeros are real — so all four metric columns are non-null on every
      row. Verified in all 14 bronze years.
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE fte_positions IS NULL OR avg_annual_salary IS NULL OR avg_daily_salary
      IS NULL OR avg_contract_days IS NULL
    mustBe: 0
customProperties:
- property: title
  value: Certified Personnel Compensation (Salaries and Positions)
- property: summary
  value: Average salaries, contract days, and FTE position counts for Georgia school staff by school,
    district, and state, 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: e00ffea94b1d4b03140629f26a99507e7706e7891fb5a99220f7cbe8a6e2a654
- property: available_years
  value:
  - 2011
  - 2012
  - 2013
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
