apiVersion: v3.1.0
kind: DataContract
id: education.certified_personnel_experience
name: certified_personnel_experience
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Georgia Office of Student Achievement (GOSA) certified-personnel years-of-experience measures,
    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 the experience
    distribution of the certified-personnel roster for each of three employee groups (Administrators,
    PK-12 Teachers, Support Personnel): headcounts in five mutually exclusive experience bands (less than
    1 year, 1-10, 11-20, 21-30, over 30 years) plus the mean years of experience. One row per school,
    district, or state per employee group per year; coverage runs from the 2010-11 school year through
    2023-24. This is one of three topics derived from the same GOSA Certified Personnel source: `certified_personnel_composition`
    carries the staff headcount axes (certificate level, certification status, gender, employment status,
    race/ethnicity — including the total staff counts), and `certified_personnel_compensation` carries
    the FTE position counts and salary/contract-day measures.'
  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; a 0 is a real reported value. 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_experience
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/certified_personnel_experience/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/certified_personnel_experience/**/*.parquet
  format: parquet
schema:
- name: certified_personnel_experience
  logicalType: object
  physicalType: parquet
  physicalName: certified_personnel_experience
  description: certified_personnel_experience 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_years_experience
  - property: null_semantics
    value:
      suppressed_to_null: false
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/certified_personnel_experience/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_experience WHERE year = 2024 LIMIT 100
    - description: District 644 in 2024
      query: SELECT * FROM certified_personnel_experience WHERE district_code = '644' AND year = 2024
        LIMIT 100
    - description: Filter by employee_type = pk_12_teachers
      query: SELECT * FROM certified_personnel_experience 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: 'Employee group this row''s experience measures 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 measurements cover: 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: num_staff_less_than_1_yr
    logicalType: integer
    physicalType: bigint
    required: true
    description: Whole-person headcount of staff in this employee group with less than 1 year of experience.
      One of five mutually exclusive experience bands (<1, 1-10, 11-20, 21-30, >30 years) that partition
      the entity's certified-personnel roster — the bands may be summed WITHIN a row to rebuild the entity's
      staff total, but school rows must never be summed to rebuild district or state counts (staff serving
      multiple schools are counted once per school assignment but deduplicated at higher levels). Zero
      counts are real (no suppression in this source).
    examples:
    - 12
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Staff Less Than 1 Yr
    - property: short_description
      value: Headcount of staff with less than 1 year of experience.
    - property: unit
      value: count
  - name: num_staff_1_to_10_yr
    logicalType: integer
    physicalType: bigint
    required: true
    description: Whole-person headcount of staff in this employee group with 1 to 10 years of experience.
      One of five mutually exclusive experience bands (<1, 1-10, 11-20, 21-30, >30 years) that partition
      the entity's certified-personnel roster — the bands may be summed WITHIN a row to rebuild the entity's
      staff total, but school rows must never be summed to rebuild district or state counts (staff serving
      multiple schools are counted once per school assignment but deduplicated at higher levels). Zero
      counts are real (no suppression in this source).
    examples:
    - 45
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Staff 1 to 10 Yr
    - property: short_description
      value: Headcount of staff with 1-10 years of experience.
    - property: unit
      value: count
  - name: num_staff_11_to_20_yr
    logicalType: integer
    physicalType: bigint
    required: true
    description: Whole-person headcount of staff in this employee group with 11 to 20 years of experience.
      One of five mutually exclusive experience bands (<1, 1-10, 11-20, 21-30, >30 years) that partition
      the entity's certified-personnel roster — the bands may be summed WITHIN a row to rebuild the entity's
      staff total, but school rows must never be summed to rebuild district or state counts (staff serving
      multiple schools are counted once per school assignment but deduplicated at higher levels). Zero
      counts are real (no suppression in this source).
    examples:
    - 30
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Staff 11 to 20 Yr
    - property: short_description
      value: Headcount of staff with 11-20 years of experience.
    - property: unit
      value: count
  - name: num_staff_21_to_30_yr
    logicalType: integer
    physicalType: bigint
    required: true
    description: Whole-person headcount of staff in this employee group with 21 to 30 years of experience.
      One of five mutually exclusive experience bands (<1, 1-10, 11-20, 21-30, >30 years) that partition
      the entity's certified-personnel roster — the bands may be summed WITHIN a row to rebuild the entity's
      staff total, but school rows must never be summed to rebuild district or state counts (staff serving
      multiple schools are counted once per school assignment but deduplicated at higher levels). Zero
      counts are real (no suppression in this source).
    examples:
    - 15
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Staff 21 to 30 Yr
    - property: short_description
      value: Headcount of staff with 21-30 years of experience.
    - property: unit
      value: count
  - name: num_staff_over_30_yr
    logicalType: integer
    physicalType: bigint
    required: true
    description: Whole-person headcount of staff in this employee group with more than 30 years of experience.
      One of five mutually exclusive experience bands (<1, 1-10, 11-20, 21-30, >30 years) that partition
      the entity's certified-personnel roster — the bands may be summed WITHIN a row to rebuild the entity's
      staff total, but school rows must never be summed to rebuild district or state counts (staff serving
      multiple schools are counted once per school assignment but deduplicated at higher levels). Zero
      counts are real (no suppression in this source).
    examples:
    - 4
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Staff Over 30 Yr
    - property: short_description
      value: Headcount of staff with more than 30 years of experience.
    - property: unit
      value: count
  - name: avg_years_experience
    logicalType: number
    physicalType: double
    required: true
    description: 'Mean years of experience across this entity''s certified-personnel roster for the employee
      group, in years (0.01 precision; observed range [0, 50] across all 14 source years, enforced to
      [0, 60] by a quality check). An ENTITY-LEVEL mean: never sum it across entities, and a state mean
      is not the sum (or simple average) of district means. Carries no `unit` marker — a mean in years
      fits none of the unit vocabulary values — and no `metric_component` counts: the N it averages over
      (the total staff headcount) is published in the sibling `certified_personnel_composition` topic,
      not here.'
    examples:
    - 13.28
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Average Years Experience
    - property: short_description
      value: Mean years of experience across the employee group's certified-personnel roster.
    - property: key_metric
      value: true
  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: num_staff_less_than_1_yr_non_negative
    description: num_staff_less_than_1_yr is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_staff_less_than_1_yr IS NOT NULL AND num_staff_less_than_1_yr
      < 0
    mustBe: 0
  - type: sql
    name: num_staff_1_to_10_yr_non_negative
    description: num_staff_1_to_10_yr is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_staff_1_to_10_yr IS NOT NULL AND num_staff_1_to_10_yr
      < 0
    mustBe: 0
  - type: sql
    name: num_staff_11_to_20_yr_non_negative
    description: num_staff_11_to_20_yr is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_staff_11_to_20_yr IS NOT NULL AND num_staff_11_to_20_yr
      < 0
    mustBe: 0
  - type: sql
    name: num_staff_21_to_30_yr_non_negative
    description: num_staff_21_to_30_yr is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_staff_21_to_30_yr IS NOT NULL AND num_staff_21_to_30_yr
      < 0
    mustBe: 0
  - type: sql
    name: num_staff_over_30_yr_non_negative
    description: num_staff_over_30_yr is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_staff_over_30_yr IS NOT NULL AND num_staff_over_30_yr
      < 0
    mustBe: 0
  - type: sql
    name: avg_years_experience_within_range
    description: 'Mean years of experience lies within [0, 60] — a 60-year career is the conceivable extreme.
      Observed bronze range across all 14 years: [0, 50]. Authored with BOTH bounds because the column
      carries no `unit` marker (a mean in years fits no unit vocabulary value), so no range check auto-derives.'
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE avg_years_experience IS NOT NULL AND (avg_years_experience
      < 0 OR avg_years_experience > 60)
    mustBe: 0
  - type: sql
    name: state_rows_exactly_3_per_year
    description: 'Structural fact: every year carries exactly 3 state-level rows — one wide row per employee
      type (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: experience_measures_never_null
    description: 'Completeness: GOSA publishes the complete Years Experience measurement grid for every
      (year, geography, employee_type) cell — all five band headcounts and the mean are always present
      (zeros are real; nothing is suppressed). Verified: zero NULLs across all 14 bronze years in all
      six columns.'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE num_staff_less_than_1_yr IS NULL OR num_staff_1_to_10_yr
      IS NULL OR num_staff_11_to_20_yr IS NULL OR num_staff_21_to_30_yr IS NULL OR num_staff_over_30_yr
      IS NULL OR avg_years_experience IS NULL
    mustBe: 0
customProperties:
- property: title
  value: Certified Personnel Years of Experience
- property: summary
  value: Experience distribution of Georgia school staff -- headcounts in five experience bands plus mean
    years of experience for administrators, PK-12 teachers, and support personnel 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: 167aeba533a2e79b1a38b4cd634e78b1061d882e30468b2bbdca88851e14dbae
- property: available_years
  value:
  - 2011
  - 2012
  - 2013
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
