apiVersion: v3.1.0
kind: DataContract
id: education.educator_qualifications_emergency_and_provisional_credentials
name: educator_qualifications_emergency_and_provisional_credentials
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: Georgia Office of Student Achievement (GOSA) Emergency-credential teacher FTE report. For every
    Georgia public school, school district, and the state as a whole, reports the total teacher full-time
    equivalent (`total_fte`), the FTE of teachers holding an Emergency teaching credential (`emergency_fte`),
    and the emergency-credentialed FTE as a percentage of total FTE (`emergency_fte_rate`, on a 0-1 decimal
    scale). Each entity has three rows — Total, High Poverty, and Low Poverty — reporting the same three
    metrics across poverty strata of schools. Coverage spans the 2017-2018 school year through 2023-2024.
    Despite the topic name referencing 'Provisional' credentials, no provisional rows appear in the source;
    only Emergency is reported.
  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: 'Source-lineage caveat: in the 2018-2021 bronze files (Era 3) the metric columns are NAMED
    `OUTOFFIELD_FTE` / `OUTOFFIELD_FTE_PCT`, but the VALUES they carry are Emergency-credential FTE counts,
    not out-of-field counts — a confirmed GOSA source mis-labeling (the 2021 state total here, 9,796.9,
    differs from the genuine out-of-field report''s 6,281.9). The transform reads these as emergency_fte
    / emergency_fte_rate; do not re-interpret those years as out-of-field data. Despite the topic name,
    no ''Provisional'' rows appear in the source — only Emergency is reported. Suppression is era-asymmetric:
    2018-2020 publish a real `0` (true zero emergency-credentialed teachers), while 2021 onward suppress
    any value below 10 with `TFS` (treat NULL as ''count < 10'' for 2021+ but as genuinely missing pre-2021);
    emergency_fte_rate may be non-null even when emergency_fte is suppressed. 2019 statewide FTE is ~37%
    above neighboring years (a broader teacher population GOSA reported that year), preserved faithfully
    per data-cleaning-standards §4b. The 2018 file mixes scopes ACROSS detail levels: school rows are
    on the broad teacher-population scope while district and state rows are on the narrow scope (statewide
    school-row total_fte sums to 157,557.3 vs the state row''s 118,009.1, a 1.335x ratio; e.g. Gwinnett''s
    district aggregate is 11,170.0 while its school rows sum to 22,190.6) — 2018 school rows do NOT sum
    to their district or state aggregates. The source publishes only district/school NAMES, so codes are
    resolved by name against the education dimensions via the shared educator-topic resolver (year-aware
    certified_personnel lookups, curated code pins, guarded matching); rows that cannot be resolved are
    dropped only under documented predicates — redundant 2023 truncated charter-container aggregates and
    cataloged source gaps — with per-year counts in the transform manifest. State rows have NULL district_code
    and school_code; district rows have NULL school_code.'
tags:
- education
- governor's office of student achievement
- educator_qualifications_emergency_and_provisional_credentials
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/educator_qualifications_emergency_and_provisional_credentials/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/educator_qualifications_emergency_and_provisional_credentials/**/*.parquet
  format: parquet
schema:
- name: educator_qualifications_emergency_and_provisional_credentials
  logicalType: object
  physicalType: parquet
  physicalName: educator_qualifications_emergency_and_provisional_credentials
  description: educator_qualifications_emergency_and_provisional_credentials fact table, year-partitioned
    Parquet (split by detail level where present).
  dataGranularityDescription: One row per year, district_code, school_code, poverty_subgroup (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
    - poverty_subgroup
  - property: key_metric
    value: emergency_fte_rate
  - property: null_semantics
    value:
      suppressed_to_null: true
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/educator_qualifications_emergency_and_provisional_credentials/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 educator_qualifications_emergency_and_provisional_credentials WHERE year =
        2024 LIMIT 100
    - description: District 601 in 2024
      query: SELECT * FROM educator_qualifications_emergency_and_provisional_credentials WHERE district_code
        = '601' AND year = 2024 LIMIT 100
    - description: Filter by poverty_subgroup = total
      query: SELECT * FROM educator_qualifications_emergency_and_provisional_credentials WHERE poverty_subgroup
        = 'total' 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: 3-digit GOSA district code (zero-padded) for standard districts; 7-digit charter code
      for charter / specialty-school districts; NULL for state-level aggregate rows. FK to the education
      districts dimension. Because the source publishes only district NAMES (no codes), codes are resolved
      against `data/gold/education/_dimensions/districts.parquet` via the shared educator-topic resolver
      (year-aware certified_personnel lookups, curated code pins, and guarded name matching — see src/etl/education/gosa/_educator_lookups.py).
      Rows whose names cannot be resolved are dropped only under documented predicates; the transform
      manifest records each dropped class and count per year.
    examples:
    - '601'
    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 GOSA school code (zero-padded); NULL for district-level and state-level aggregate
      rows. FK to the education schools dimension (composite key with district_code). Resolved by name
      via the shared educator-topic resolver; school-level rows whose name cannot be resolved to a dimension
      entry are dropped under documented source-gap predicates only (counts in the transform manifest).
    examples:
    - '0103'
    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: poverty_subgroup
    logicalType: string
    physicalType: string
    required: true
    description: Poverty stratum of the schools whose FTE this row aggregates. `total` covers all schools
      in the entity; `high_poverty` covers only the entity's schools in the state's highest-poverty quartile;
      `low_poverty` only those in the lowest-poverty quartile. Per GOSA's K-12 Teacher & Leader Workforce
      Reports, a school's poverty level is defined by its direct-certification rate (the share of students
      directly certified for free/reduced-price meals) — higher direct certification means higher poverty.
      For a school-level row the stratum describes the school itself, so `high_poverty`/`low_poverty`
      rows duplicate that school's `total` row (enforced by a quality check). This is a SCHOOL-poverty
      stratum, NOT a student demographic — it does not map to the global demographics dimension.
    examples:
    - 'total'
    enum:
    - 'high_poverty'
    - 'low_poverty'
    - 'total'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Poverty Subgroup
    - property: short_description
      value: 'School-poverty stratum the row covers: all schools (total), or only the highest- or lowest-poverty
        quartile; a school-poverty level, not a student demographic.'
    - property: has_total
      value: true
    - property: total_value
      value: 'total'
    - property: key_metric_grain_contributor
      value: true
    primaryKey: true
    primaryKeyPosition: 4
  - name: total_fte
    logicalType: number
    physicalType: double
    required: false
    description: 'Total teacher full-time equivalent count in the entity (or in the poverty subgroup).
      Fractional FTEs are real (e.g. 34.8). NULL when suppressed by the GOSA reporting floor (`TFS`, <
      10 teachers). Denominator of `emergency_fte_rate`. The 2019 statewide level (162,256.2) is ~37%
      above neighboring years (2018: 118,009.1; 2020: 110,800.8) — a broader teacher population GOSA reported
      that year at every detail level, preserved faithfully per data-cleaning-standards §4b (extreme-but-conceivable).'
    examples:
    - 42.5
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Total FTE
    - property: unit
      value: count
    - property: null_meaning
      value: Suppressed by the GOSA reporting floor (`TFS`, fewer than 10 teachers); suppression exists
        from the 2021 file onward.
    - property: metric_component
      value: denominator
  - name: emergency_fte
    logicalType: number
    physicalType: double
    required: false
    description: Teacher FTE holding an Emergency teaching credential in the entity (or poverty subgroup).
      NULL when suppressed (`TFS`, < 10 FTE) — observed from 2021 onward. True zeros (no emergency-credentialed
      teachers) are preserved as 0.0 in 2018-2020, before GOSA introduced suppression for this report.
      Numerator of `emergency_fte_rate`. Sourced from bronze columns CATEGORY_FTE (2023-24), Emergency_FTE
      (2022), and the MIS-LABELED OUTOFFIELD_FTE (2018-21) — see the limitations note.
    examples:
    - 1.5
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Emergency FTE
    - property: unit
      value: count
    - property: null_meaning
      value: Suppressed (`TFS`, < 10 FTE) — 2021 onward. Pre-2021 files have no suppression, so NULL does
        not occur and 0.0 is a true zero.
    - property: metric_component
      value: numerator
  - name: emergency_fte_rate
    logicalType: number
    physicalType: double
    required: false
    description: '`emergency_fte` / `total_fte` on a 0-1 decimal scale. Bronze publishes an integer 0-100
      percent (verified range 0-100 across all years); divided by 100 per data-cleaning-standards §4.
      Because GOSA rounds the percent to an integer computed from unrounded FTE values, the published
      rate can deviate from emergency_fte/total_fte by up to ~0.012 at small schools (worst observed:
      0.0121 at a 1.9-FTE program in 2020); a quality check enforces reconciliation within 0.015. The
      rate is preserved from bronze even when `emergency_fte` is suppressed — e.g. a row can have emergency_fte=NULL
      with a non-NULL rate.'
    examples:
    - 0.05
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Emergency FTE Rate
    - property: short_description
      value: Share of teacher FTE holding an emergency credential, on a 0-1 scale (emergency_fte / total_fte).
    - property: unit
      value: proportion
    - property: null_meaning
      value: The bronze percentage itself was suppressed (`TFS`). The rate can be non-NULL while `emergency_fte`
        is suppressed, and vice versa — GOSA suppresses each cell independently.
    - 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: poverty_subgroup_in_allowed_values
    description: poverty_subgroup is one of the 3 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE poverty_subgroup IS NOT NULL AND poverty_subgroup NOT IN
      ('high_poverty', 'low_poverty', 'total')
    mustBe: 0
  - type: sql
    name: total_fte_non_negative
    description: total_fte is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE total_fte IS NOT NULL AND total_fte < 0
    mustBe: 0
  - type: sql
    name: emergency_fte_non_negative
    description: emergency_fte is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE emergency_fte IS NOT NULL AND emergency_fte < 0
    mustBe: 0
  - type: sql
    name: emergency_fte_rate_within_unit_interval
    description: emergency_fte_rate is a bounded proportion on the [0, 1] scale.
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE emergency_fte_rate IS NOT NULL AND (emergency_fte_rate
      < 0 OR emergency_fte_rate > 1)
    mustBe: 0
  - type: sql
    name: emergency_fte_within_total_fte
    description: 'The emergency-credentialed FTE count never exceeds the total teacher FTE it is drawn
      from (verified across all bronze years: zero violations).'
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE emergency_fte IS NOT NULL AND total_fte IS NOT NULL AND
      emergency_fte > total_fte
    mustBe: 0
  - type: sql
    name: emergency_fte_rate_reconciles_with_components
    description: 'emergency_fte_rate reconciles with emergency_fte / total_fte within 0.015 on the 0-1
      scale. Tolerance derived from bronze: GOSA publishes an integer percent computed from unrounded
      FTE values, so the worst observed deviation is 0.0121 (2020, a 1.9-FTE program); the naive integer-rounding
      bound of 0.005 is too tight.'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE total_fte IS NOT NULL AND emergency_fte IS NOT NULL AND
      emergency_fte_rate IS NOT NULL AND total_fte > 0 AND ABS(emergency_fte / total_fte - emergency_fte_rate)
      > 0.015
    mustBe: 0
  - type: sql
    name: school_poverty_stratum_mirrors_total
    description: A school-level high_poverty or low_poverty row duplicates the same school's total row
      (a school IS its poverty stratum; the stratum rows are republications, not sub-populations). Checked
      on all three metrics (total_fte, emergency_fte, emergency_fte_rate) with a 0.001 float tolerance
      and null-safe guards — GOSA suppresses each cell independently, so a comparison is made only when
      both sides are non-NULL.
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, MAX(CASE WHEN poverty_subgroup
      = 'total' THEN total_fte END) AS t, MAX(CASE WHEN poverty_subgroup = 'high_poverty' THEN total_fte
      END) AS hp, MAX(CASE WHEN poverty_subgroup = 'low_poverty' THEN total_fte END) AS lp, MAX(CASE WHEN
      poverty_subgroup = 'total' THEN emergency_fte END) AS te, MAX(CASE WHEN poverty_subgroup = 'high_poverty'
      THEN emergency_fte END) AS hpe, MAX(CASE WHEN poverty_subgroup = 'low_poverty' THEN emergency_fte
      END) AS lpe, MAX(CASE WHEN poverty_subgroup = 'total' THEN emergency_fte_rate END) AS tr, MAX(CASE
      WHEN poverty_subgroup = 'high_poverty' THEN emergency_fte_rate END) AS hpr, MAX(CASE WHEN poverty_subgroup
      = 'low_poverty' THEN emergency_fte_rate END) AS lpr FROM {object} WHERE school_code IS NOT NULL
      GROUP BY year, district_code, school_code) WHERE (t IS NOT NULL AND hp IS NOT NULL AND ABS(hp -
      t) > 0.001) OR (t IS NOT NULL AND lp IS NOT NULL AND ABS(lp - t) > 0.001) OR (te IS NOT NULL AND
      hpe IS NOT NULL AND ABS(hpe - te) > 0.001) OR (te IS NOT NULL AND lpe IS NOT NULL AND ABS(lpe -
      te) > 0.001) OR (tr IS NOT NULL AND hpr IS NOT NULL AND ABS(hpr - tr) > 0.001) OR (tr IS NOT NULL
      AND lpr IS NOT NULL AND ABS(lpr - tr) > 0.001)
    mustBe: 0
  - type: sql
    name: school_never_in_both_poverty_strata
    description: The high/low poverty quartiles are disjoint, so no school carries both a high_poverty
      and a low_poverty row in the same year (verified across all bronze years).
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code FROM {object} WHERE school_code
      IS NOT NULL AND poverty_subgroup IN ('high_poverty', 'low_poverty') GROUP BY year, district_code,
      school_code HAVING COUNT(DISTINCT poverty_subgroup) > 1) AS bad
    mustBe: 0
  - type: sql
    name: aggregate_poverty_strata_within_total
    description: 'At district and state level the high-poverty and low-poverty strata are disjoint subsets
      of the entity, so high_poverty + low_poverty <= total + 0.25 when all three are reported — checked
      on both total_fte and emergency_fte. The 0.25 tolerance covers GOSA''s 0.1-FTE rounding (observed
      worst excess: 0.1 at Greene County 2021/2022 and Richmond County 2021).'
    dimension: accuracy
    query: SELECT COUNT(*) FROM (SELECT year, district_code, MAX(CASE WHEN poverty_subgroup = 'total'
      THEN total_fte END) AS t, MAX(CASE WHEN poverty_subgroup = 'high_poverty' THEN total_fte END) AS
      hp, MAX(CASE WHEN poverty_subgroup = 'low_poverty' THEN total_fte END) AS lp, MAX(CASE WHEN poverty_subgroup
      = 'total' THEN emergency_fte END) AS te, MAX(CASE WHEN poverty_subgroup = 'high_poverty' THEN emergency_fte
      END) AS hpe, MAX(CASE WHEN poverty_subgroup = 'low_poverty' THEN emergency_fte END) AS lpe FROM
      {object} WHERE school_code IS NULL GROUP BY year, district_code) WHERE (t IS NOT NULL AND hp IS
      NOT NULL AND lp IS NOT NULL AND hp + lp > t + 0.25) OR (te IS NOT NULL AND hpe IS NOT NULL AND lpe
      IS NOT NULL AND hpe + lpe > te + 0.25)
    mustBe: 0
  - type: sql
    name: state_rows_exactly_three_per_year
    description: 'Structural fact: every year carries exactly three state-level rows (district_code IS
      NULL) — one per poverty stratum.'
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year FROM {object} WHERE district_code IS NULL GROUP BY year HAVING
      COUNT(*) <> 3 OR COUNT(DISTINCT poverty_subgroup) <> 3) AS bad
    mustBe: 0
customProperties:
- property: title
  value: Emergency-Credentialed Teachers
- property: summary
  value: Share of Georgia teachers holding an emergency credential by school, district, and school-poverty
    stratum, 2018-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: 2018-2024
- property: schema_hash
  value: 2c38701537a70afc578be7768152b086b8ed0f90687c4d3ac63762856fac0ab8
- property: available_years
  value:
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
