apiVersion: v3.1.0
kind: DataContract
id: education.enrollment_by_grade_level
name: enrollment_by_grade_level
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Student enrollment counts by grade level for every Georgia public school, school district,
    and the state, published by GOSA for school years 2010-11 through 2023-24. Each row is the total number
    of students enrolled in one grade (K through 12) at one entity in one enrollment-snapshot period —
    GaDOE counts enrollment twice per school year (Fall and Spring collection cycles), and both snapshots
    are preserved as distinct rows. There is no demographic breakdown: every row is a total count for
    its (year, geography, period, grade) cell.'
  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: Suppressed cells are NULL (not zero). State rows have NULL district_code and school_code.
    District rows have NULL school_code. State and district rows are independently published GOSA aggregates
    and may differ from the sum of their component rows by up to ~0.02% (e.g., students enrolled in more
    than one district during a collection period); do not treat component sums as exact reconciliations.
tags:
- education
- governor's office of student achievement
- enrollment_by_grade_level
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/enrollment_by_grade_level/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/enrollment_by_grade_level/**/*.parquet
  format: parquet
schema:
- name: enrollment_by_grade_level
  logicalType: object
  physicalType: parquet
  physicalName: enrollment_by_grade_level
  description: enrollment_by_grade_level fact table, year-partitioned Parquet (split by detail level where
    present).
  dataGranularityDescription: One row per year, district_code, school_code, enrollment_period, grade_level
    (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
    - enrollment_period
    - grade_level
  - property: key_metric
    value: num_students
  - property: null_semantics
    value:
      suppressed_to_null: true
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/enrollment_by_grade_level/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 enrollment_by_grade_level WHERE year = 2024 LIMIT 100
    - description: District 601 in 2024
      query: SELECT * FROM enrollment_by_grade_level WHERE district_code = '601' AND year = 2024 LIMIT
        100
    - description: Filter by enrollment_period = fall
      query: SELECT * FROM enrollment_by_grade_level WHERE enrollment_period = 'fall' AND year = 2024
        LIMIT 100
  properties:
  - name: year
    logicalType: integer
    physicalType: int
    required: true
    description: Ending calendar year of the school year (2024 = 2023-24). Parsed from the bronze LONG_SCHOOL_YEAR
      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-character zero-padded code for standard
      county/city districts, or 7-character code for state-charter (782 prefix), commission-charter (783
      prefix), and state school (799 prefix) entities — 7-character codes appear in every year 2011-2024.
      NULL on state-level aggregate rows (bronze sentinel ''ALL'').'
    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-character zero-padded GOSA school code (composite FK to schools dimension with district_code;
      school codes are not globally unique on their own). NULL on district- and state-level aggregate
      rows (bronze sentinel 'ALL').
    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: enrollment_period
    logicalType: string
    physicalType: string
    required: true
    description: 'Enrollment-count snapshot within the school year: ''fall'' (October collection) or ''spring''
      (March collection). Both snapshots exist for nearly every entity — queries wanting one headline
      count per year must filter to a single period, never sum across them.'
    examples:
    - 'fall'
    enum:
    - 'fall'
    - 'spring'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Enrollment Period
    - property: short_description
      value: 'Enrollment snapshot within the school year: fall (October) or spring (March); never sum
        across them.'
    - property: has_total
      value: false
    - property: key_metric_grain_contributor
      value: true
    primaryKey: true
    primaryKeyPosition: 4
  - name: grade_level
    logicalType: string
    physicalType: string
    required: true
    description: 'Grade level of the students counted: ''k'' for kindergarten, ''01'' through ''12'' for
      grades 1-12 (canonical zero-padded codes per the shared grade vocabulary). This source publishes
      no pre-K row and no cross-grade aggregate row, so ''pk'' and ''all'' never appear. Bronze spellings
      were K and 1st..12th.'
    examples:
    - '03'
    enum:
    - '01'
    - '02'
    - '03'
    - '04'
    - '05'
    - '06'
    - '07'
    - '08'
    - '09'
    - '10'
    - '11'
    - '12'
    - 'k'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Grade Level
    - property: short_description
      value: 'Grade of the students counted: ''k'' for kindergarten, ''01''-''12'' for grades 1-12; no
        pre-K or all-grades row.'
    - property: has_total
      value: false
    - property: key_metric_grain_contributor
      value: true
    primaryKey: true
    primaryKeyPosition: 5
  - name: num_students
    logicalType: integer
    physicalType: bigint
    required: false
    description: 'Number of students enrolled in this grade and period at this entity. Three source regimes
      govern 0 vs NULL: (1) 2011-2020 — no suppression; an explicit 0 is a true zero (grade not served)
      and is preserved; (2) 2021-2022 — the source replaces both true zeros and counts 1-9 with ''TFS''
      (Too Few Students), stored as NULL here; (3) 2023-2024 — TFS suppression continues AND rows for
      non-applicable grade x entity combinations are no longer emitted, roughly halving the row count
      per year.'
    examples:
    - 80
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Students
    - property: short_description
      value: Number of students enrolled in this grade and period at this entity.
    - property: unit
      value: count
    - property: null_meaning
      value: 'Varies by year: never NULL in 2011-2020 (zeros are real and preserved); in 2021-2022 NULL
        is a TFS suppression that conflates true zeros with counts 1-9; in 2023-2024 NULL means a suppressed
        <10 cell (and non-applicable grade x entity cells are absent rows, not NULLs).'
    - 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: enrollment_period_in_allowed_values
    description: enrollment_period is one of the 2 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE enrollment_period IS NOT NULL AND enrollment_period NOT
      IN ('fall', 'spring')
    mustBe: 0
  - type: sql
    name: grade_level_in_allowed_values
    description: grade_level is one of the 13 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE grade_level IS NOT NULL AND grade_level NOT IN ('01', '02',
      '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', 'k')
    mustBe: 0
  - type: sql
    name: num_students_non_negative
    description: num_students is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_students IS NOT NULL AND num_students < 0
    mustBe: 0
  - type: sql
    name: state_rows_exactly_26_per_year
    description: Every year publishes exactly 26 state-level rows (2 enrollment periods x 13 grades K-12).
      Fewer means data loss; more means a dedup or sentinel-routing bug. State rows are those with NULL
      district_code AND NULL school_code. Verified to hold for all 14 years.
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year, COUNT(*) AS n FROM {object} WHERE district_code IS NULL
      AND school_code IS NULL GROUP BY year) t WHERE t.n <> 26
    mustBe: 0
  - type: sql
    name: state_rows_13_grades_per_period
    description: Each (year, enrollment_period) publishes exactly 13 state-level grade rows (k, 01-12)
      — the per-period refinement of the 26-per-year invariant, catching a missing/duplicated grade even
      when the yearly total still lands on 26.
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year, enrollment_period, COUNT(*) AS n FROM {object} WHERE district_code
      IS NULL AND school_code IS NULL GROUP BY year, enrollment_period) t WHERE t.n <> 13
    mustBe: 0
  - type: sql
    name: dense_years_full_grade_set
    description: 'Dense-era structural fact (2011-2022 files): every (geography, enrollment_period) group
      carries exactly 13 grade rows — the source emits all grades for every present entity-period, with
      0 or suppressed-NULL counts for grades not served. Verified to hold for all 12 dense years after
      the 2022 sentinel-twin drop. GROUP BY treats NULL geography keys as equal, so one scan covers state,
      district, and school levels. 2023-2024 are sparse by design and excluded.'
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, enrollment_period, COUNT(*)
      AS n FROM {object} WHERE year <= 2022 GROUP BY year, district_code, school_code, enrollment_period)
      t WHERE t.n <> 13
    mustBe: 0
  - type: sql
    name: num_students_suppression_floor_from_2021
    description: 'From 2021 onward the source suppresses any cell below 10 as TFS (stored as NULL), so
      no published num_students under 10 can exist in 2021-2024 — a value 0-9 in those years means a suppression-handling
      or cast regression. Verified: gold minimum is 10 in every suppression-era year.'
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE year >= 2021 AND num_students IS NOT NULL AND num_students
      < 10
    mustBe: 0
  - type: sql
    name: num_students_never_null_before_2021
    description: 2011-2020 bronze has no suppression and no blank cells (every value is a digit string,
      including true zeros), so a NULL num_students in those years means a cast or read regression. TFS
      suppression begins in 2021.
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE year <= 2020 AND num_students IS NULL
    mustBe: 0
  - type: sql
    name: district_code_length_3_or_7
    description: Non-NULL district_code is a 3-character standard code or a 7-character charter/state-school
      code — any other length means zfill or sentinel handling regressed.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE district_code IS NOT NULL AND LENGTH(district_code) NOT
      IN (3, 7)
    mustBe: 0
  - type: sql
    name: school_code_length_4
    description: Non-NULL school_code is always zero-padded to exactly 4 characters, matching the schools
      dimension key.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL AND LENGTH(school_code) <> 4
    mustBe: 0
customProperties:
- property: title
  value: Enrollment by Grade Level
- property: summary
  value: Student enrollment counts by grade (K-12) for each Georgia school, district, and the state, per
    Fall/Spring snapshot, 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: 0a3f9211501ce48472feaa33d39f66a4b455fa176383ce0fc966e4da86e19b6b
- property: available_years
  value:
  - 2011
  - 2012
  - 2013
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
