apiVersion: v3.1.0
kind: DataContract
id: education.certified_personnel_composition
name: certified_personnel_composition
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Georgia certified-personnel staff composition, from the Governor''s Office of Student Achievement
    (GOSA) Certified Personnel report (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 staff headcounts along five mutually exclusive staff-attribute axes — certificate
    level (Bachelor''s through Doctoral, plus Other), certification status (Professional / Provisional),
    gender, employment status (Full/Part-time), and race/ethnicity — for each of three employee groups
    (Administrators, PK-12 Teachers, Support Personnel), school years 2010-11 through 2023-24. This topic
    covers WHO the staff are; its sibling topics from the same GOSA source cover how experienced they
    are (`certified_personnel_experience`: years-of-experience band headcounts and the mean) and their
    positions and pay (`certified_personnel_compensation`: FTE position counts and average salary/contract
    days).'
  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); zero counts
    are published as real `0` values. num_staff is NULL only on the two masked source-defect windows described
    below. The five staff_attribute_category axes each partition the SAME certified-personnel population
    a different way — sum num_staff only across rows within a single axis, never across axes (the total
    would multiply-count every person). staff_attribute_category is functionally determined by staff_attribute
    (17 valid pairs). Gender and race/ethnicity values reuse the canonical demographic vocabulary but
    describe STAFF, not the student demographic axis — there is no `demographic` column and these rows
    do not join the demographics dimension. The race_ethnicity axis uses the pre-1997 OMB combined `asian_pacific_islander`
    bucket (no separate Pacific Islander label exists in the source). Two known source-defect windows
    on the certification_status axis are MASKED to NULL rather than served (§4b known-source-defect rule;
    both fail the internal certification-vs-gender axis-sum test that every other year passes within ~0.3%):
    the entire axis in the 2016-17 file (captured ~3% of staff at every level) and the provisional bucket
    in the 2019-20 file (~19% of adjacent years; 102 of 214 districts at zero; professional passes the
    test and is served). The published defect values remain in bronze and in the transform manifest''s
    mask ledger. School rows must not be summed to rebuild district or state headcounts: staff serving
    multiple schools are counted once per school assignment but deduplicated at higher levels, so a district
    count can even be smaller than a single school''s count (observed only in employment_status/part_time;
    1,811 cells). State rows have NULL district_code and school_code; district rows have NULL school_code.
    Experience and compensation/salary measures from the same GOSA source live in the sibling topics `certified_personnel_experience`
    and `certified_personnel_compensation`.'
tags:
- education
- governor's office of student achievement
- certified_personnel_composition
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/certified_personnel_composition/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/certified_personnel_composition/**/*.parquet
  format: parquet
schema:
- name: certified_personnel_composition
  logicalType: object
  physicalType: parquet
  physicalName: certified_personnel_composition
  description: certified_personnel_composition fact table, year-partitioned Parquet (split by detail level
    where present).
  dataGranularityDescription: One row per year, district_code, school_code, employee_type, staff_attribute_category,
    staff_attribute (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
    - staff_attribute_category
    - staff_attribute
  - property: key_metric
    value: num_staff
  - property: null_semantics
    value:
      suppressed_to_null: false
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/certified_personnel_composition/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_composition WHERE year = 2024 LIMIT 100
    - description: District 644 in 2024
      query: SELECT * FROM certified_personnel_composition WHERE district_code = '644' AND year = 2024
        LIMIT 100
    - description: Filter by employee_type = pk_12_teachers
      query: SELECT * FROM certified_personnel_composition WHERE employee_type = 'pk_12_teachers' AND
        year = 2024 LIMIT 100
  - property: dependent_filter
    value:
    - primary: staff_attribute_category
      dependent: staff_attribute
      note: Filter staff_attribute together with staff_attribute_category — the valid attribute set depends
        on the chosen axis (5 certificate levels, 2 certification statuses, 2 genders, 2 employment statuses,
        6 race/ethnicity buckets).
  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 headcount covers. 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 headcount 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: 667092
      - value: 'pk_12_teachers'
        first_year: 2011
        last_year: 2024
        rows: 621328
      - value: 'support_personnel'
        first_year: 2011
        last_year: 2024
        rows: 651676
    primaryKey: true
    primaryKeyPosition: 4
  - name: staff_attribute_category
    logicalType: string
    physicalType: string
    required: true
    description: 'The staff-attribute axis this row belongs to. Each of the five axes partitions the SAME
      certified-personnel population a different way, so rows from different axes must never be summed
      together. Renamed from the confusing bronze DATA_CATEGORY family names: bronze `Certified Personnel`
      -> `certification_status`, bronze `Personnel` -> `employment_status`; the other three are direct
      snake_case recodes. The axis is functionally determined by `staff_attribute` (17 valid pairs, enforced
      by a quality check).'
    examples:
    - 'race_ethnicity'
    enum:
    - 'certificate_level'
    - 'certification_status'
    - 'employment_status'
    - 'gender'
    - 'race_ethnicity'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Staff Attribute Category
    - property: short_description
      value: 'Which staff-attribute axis the row slices by: certificate level, certification status, gender,
        employment status, or race/ethnicity.'
    - property: has_total
      value: false
    - property: key_metric_grain_contributor
      value: true
    - property: value_coverage
      value:
      - value: 'certificate_level'
        first_year: 2011
        last_year: 2024
        rows: 577895
      - value: 'certification_status'
        first_year: 2011
        last_year: 2024
        rows: 206411
      - value: 'employment_status'
        first_year: 2011
        last_year: 2024
        rows: 231158
      - value: 'gender'
        first_year: 2011
        last_year: 2024
        rows: 231158
      - value: 'race_ethnicity'
        first_year: 2011
        last_year: 2024
        rows: 693474
    primaryKey: true
    primaryKeyPosition: 5
  - name: staff_attribute
    logicalType: string
    physicalType: string
    required: true
    description: 'Specific attribute value within the axis. Snake_case recode of bronze DATA_SUB_CATEGORY
      — e.g. `5_yr_masters` (axis `certificate_level`), `professional` (axis `certification_status`),
      `full_time` (axis `employment_status`). `other` is the fifth CERTIFICATE LEVEL bucket (bronze `Other
      *`; the asterisk flags a source footnote), not a race value. Gender and race values reuse the canonical
      demographic vocabulary but describe STAFF — there is no `demographic` column and no join to the
      demographics dimension. `asian_pacific_islander` is the bronze staff-race label `Asian`: the source
      publishes only 6 race buckets with no separate Pacific Islander label in any year, and state-level
      race-bucket sums equal the gender-bucket sums (same population, residuals under 0.04% in both directions)
      — the pre-1997 OMB combined Asian + Pacific Islander bucket per data-cleaning-standards §5b. Certification
      status caveat: two source-defect windows on this axis are masked to NULL (see num_staff) — the whole
      axis in 2017 and the provisional bucket in 2020.'
    examples:
    - '5_yr_masters'
    enum:
    - '4_yr_bachelors'
    - '5_yr_masters'
    - '6_yr_specialists'
    - '7_yr_doctoral'
    - 'asian_pacific_islander'
    - 'black'
    - 'female'
    - 'full_time'
    - 'hispanic'
    - 'male'
    - 'multiracial'
    - 'native_american'
    - 'other'
    - 'part_time'
    - 'professional'
    - 'provisional'
    - 'white'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Staff Attribute
    - property: short_description
      value: The specific attribute value within the chosen axis (e.g. 5_yr_masters, professional, female,
        full_time, black).
    - property: has_total
      value: false
    - property: key_metric_grain_contributor
      value: true
    - property: value_coverage
      value:
      - value: '4_yr_bachelors'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: '5_yr_masters'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: '6_yr_specialists'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: '7_yr_doctoral'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'asian_pacific_islander'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'black'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'female'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'full_time'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'hispanic'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'male'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'multiracial'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'native_american'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'other'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'part_time'
        first_year: 2011
        last_year: 2024
        rows: 115579
      - value: 'professional'
        first_year: 2011
        last_year: 2024
        rows: 107356
      - value: 'provisional'
        first_year: 2011
        last_year: 2024
        rows: 99055
      - value: 'white'
        first_year: 2011
        last_year: 2024
        rows: 115579
    primaryKey: true
    primaryKeyPosition: 6
  - name: num_staff
    logicalType: integer
    physicalType: bigint
    required: false
    description: 'Whole-person headcount of certified personnel in the employee group carrying the row''s
      staff-attribute value. All 17 labels are verified-integral counts; the source has no suppression
      (zeros are real). Each staff_attribute_category axis partitions the same population, so only sum
      num_staff across rows WITHIN one axis (one staff_attribute_category value) — never across axes.
      Do not sum school rows to rebuild district or state headcounts (staff serving multiple schools are
      counted once per school assignment but deduplicated at higher levels). Two known source-defect windows
      on the certification_status axis are masked to NULL per §4b (the certification-axis staff sums otherwise
      match the gender-axis sums within ~0.3% — the internal test both windows fail): the ENTIRE axis
      in the 2016-17 file (captured ~3% of staff at every level, e.g. state professional PK-12 teachers
      2,689 vs ~110,000 in adjacent years) and the provisional bucket in the 2019-20 file (state teachers
      900 vs 4,745/4,716 neighbors; 102 of 214 districts at zero; professional passes the axis-sum test
      and is served). The bad values are recorded in the transform manifest and remain in bronze; they
      are not served.'
    examples:
    - 42
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Staff
    - property: short_description
      value: Whole-person headcount of staff in the employee group with the given attribute value.
    - property: unit
      value: count
    - property: null_meaning
      value: 'Known-source-defect mask (§4b), NOT suppression: the 2016-17 certification_status axis (all
        values) and the 2019-20 provisional counts are demonstrably wrong at the source and are NULLed
        rather than served. All other cells are never NULL.'
    - 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: staff_attribute_category_in_allowed_values
    description: staff_attribute_category is one of the 5 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE staff_attribute_category IS NOT NULL AND staff_attribute_category
      NOT IN ('certificate_level', 'certification_status', 'employment_status', 'gender', 'race_ethnicity')
    mustBe: 0
  - type: sql
    name: staff_attribute_in_allowed_values
    description: staff_attribute is one of the 17 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE staff_attribute IS NOT NULL AND staff_attribute NOT IN
      ('4_yr_bachelors', '5_yr_masters', '6_yr_specialists', '7_yr_doctoral', 'asian_pacific_islander',
      'black', 'female', 'full_time', 'hispanic', 'male', 'multiracial', 'native_american', 'other', 'part_time',
      'professional', 'provisional', 'white')
    mustBe: 0
  - type: sql
    name: num_staff_non_negative
    description: num_staff is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_staff IS NOT NULL AND num_staff < 0
    mustBe: 0
  - type: sql
    name: staff_attribute_category_attribute_valid_pair
    description: 'staff_attribute_category is functionally determined by staff_attribute: every (staff_attribute_category,
      staff_attribute) combination is one of the 17 valid pairs published by GOSA (verified identical
      in all 14 bronze years).'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE (staff_attribute_category, staff_attribute) NOT IN (('certificate_level',
      '4_yr_bachelors'), ('certificate_level', '5_yr_masters'), ('certificate_level', '6_yr_specialists'),
      ('certificate_level', '7_yr_doctoral'), ('certificate_level', 'other'), ('certification_status',
      'professional'), ('certification_status', 'provisional'), ('gender', 'female'), ('gender', 'male'),
      ('employment_status', 'full_time'), ('employment_status', 'part_time'), ('race_ethnicity', 'asian_pacific_islander'),
      ('race_ethnicity', 'black'), ('race_ethnicity', 'hispanic'), ('race_ethnicity', 'multiracial'),
      ('race_ethnicity', 'native_american'), ('race_ethnicity', 'white'))
    mustBe: 0
  - type: sql
    name: state_rows_exactly_51_per_year
    description: 'Structural fact: every year carries exactly 51 state-level rows (17 staff-attribute
      labels x 3 employee types). 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(*) <> 51) AS bad
    mustBe: 0
  - type: sql
    name: complete_17_label_grid_per_cell
    description: 'Structural fact: every (year, geography, employee_type) cell carries the complete grid
      of exactly 17 rows with 17 distinct staff_attribute labels — GOSA publishes the full measurement
      grid for every entity (zeros included, nothing suppressed), so the family filter inherits a complete
      17-label sub-grid. Verified in all 14 bronze years. GROUP BY treats NULL geography keys as equal,
      so the check self-scopes to each detail level.'
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, employee_type FROM {object}
      GROUP BY year, district_code, school_code, employee_type HAVING COUNT(*) <> 17 OR COUNT(DISTINCT
      staff_attribute) <> 17) AS bad
    mustBe: 0
  - type: sql
    name: per_axis_attribute_count_per_cell
    description: 'Structural fact: within every (year, geography, employee_type) cell, each staff-attribute
      axis carries exactly its own full label set — 5 certificate levels, 2 certification statuses, 2
      genders, 2 employment statuses, 6 race/ethnicity buckets. Verified in all 14 bronze years. GROUP
      BY treats NULL geography keys as equal, so the check self-scopes to each detail level.'
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, employee_type, staff_attribute_category
      FROM {object} GROUP BY year, district_code, school_code, employee_type, staff_attribute_category
      HAVING COUNT(*) <> (CASE staff_attribute_category WHEN 'certificate_level' THEN 5 WHEN 'race_ethnicity'
      THEN 6 ELSE 2 END) OR COUNT(DISTINCT staff_attribute) <> (CASE staff_attribute_category WHEN 'certificate_level'
      THEN 5 WHEN 'race_ethnicity' THEN 6 ELSE 2 END)) AS bad
    mustBe: 0
  - type: sql
    name: state_race_sums_match_gender_sums
    description: '§5b consistency fact behind the combined asian_pacific_islander bucket: at state level,
      the 6 race-bucket headcounts and the 2 gender-bucket headcounts sum over the same certified-personnel
      population, so per (year, employee_type) the two sums differ by at most 0.1% relative (observed
      max residual across all 14 bronze years: 0.038%, in both directions). Written as one conditional-aggregation
      scan — never a self-join (§15b).'
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, employee_type, SUM(CASE WHEN staff_attribute_category =
      'race_ethnicity' THEN num_staff END) AS race_total, SUM(CASE WHEN staff_attribute_category = 'gender'
      THEN num_staff END) AS gender_total FROM {object} WHERE district_code IS NULL AND staff_attribute_category
      IN ('race_ethnicity', 'gender') GROUP BY year, employee_type) AS sums WHERE race_total IS NOT NULL
      AND gender_total IS NOT NULL AND ABS(race_total - gender_total) > 0.001 * GREATEST(race_total, gender_total)
    mustBe: 0
  - type: sql
    name: num_staff_null_only_in_defect_mask_windows
    description: 'num_staff is NULL exactly on the two §4b known-source-defect mask windows and nowhere
      else: the entire certification_status axis in 2017, and the provisional bucket in 2020. Everywhere
      else the source has no suppression and zero NULLs (verified across all 14 bronze years). The biconditional
      guards both a mask regression (a defect cell served) and a new unexplained NULL.'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE (num_staff IS NULL) <> ((year = 2017 AND staff_attribute_category
      = 'certification_status') OR (year = 2020 AND staff_attribute = 'provisional'))
    mustBe: 0
  - type: sql
    name: state_certification_sums_match_gender_sums
    description: 'The axis-partition fact behind the §4b defect masks: at state level the certification_status
      axis and the gender axis sum over the same certified population, so per (year, employee_type) the
      two sums differ by at most 0.5% relative (observed max residual outside the masked windows: 0.28%).
      Groups whose certification cells are masked (2017; partially 2020) are excluded via the NULL-count
      filter, so the check self-scopes to served data. Conditional aggregation — never a self-join (§15b).'
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, employee_type, SUM(CASE WHEN staff_attribute_category =
      'certification_status' THEN num_staff END) AS cert_total, SUM(CASE WHEN staff_attribute_category
      = 'gender' THEN num_staff END) AS gender_total, COUNT(CASE WHEN staff_attribute_category = 'certification_status'
      AND num_staff IS NULL THEN 1 END) AS masked_cells FROM {object} WHERE district_code IS NULL AND
      staff_attribute_category IN ('certification_status', 'gender') GROUP BY year, employee_type) AS
      sums WHERE masked_cells = 0 AND cert_total IS NOT NULL AND gender_total IS NOT NULL AND ABS(cert_total
      - gender_total) > 0.005 * GREATEST(cert_total, gender_total)
    mustBe: 0
customProperties:
- property: title
  value: Certified Personnel Composition (Staff Characteristics)
- property: summary
  value: Staff headcounts by certificate level, certification status, gender, employment status, and race/ethnicity
    for Georgia schools and districts, 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: 67f87ed56ccdb8c85a96fa881bb5572287a273f2d73b9d43c15d3fee280ab9aa
- property: available_years
  value:
  - 2011
  - 2012
  - 2013
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
