apiVersion: v3.1.0
kind: DataContract
id: education.student_mobility_rate
name: student_mobility_rate
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Annual student mobility (churn) rate for Georgia public schools and districts, published by
    GOSA for school years 2011-12 through 2023-24 (filename year = ending calendar year, e.g. 2024 = 2023-24).
    Mobility is the share of students enrolled at any point during the school year but not for the full
    year; GOSA computes it as a churn rate — student entries plus withdrawals between the fall count date
    and May 1, over the fall-count enrollment — so it counts moves rather than movers and can legitimately
    exceed 100%. One fact table at two detail levels: school (one row per school) and district (one row
    per district / system). No demographic breakdowns. Merges the formerly separate student_mobility_rates_school
    and student_mobility_rates_district topics.'
  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: 'Two detail levels (school, district) in one fact table, split by parquet filename; school_code
    is NULL on district rows. No statewide rollup is published in either family, so state figures cannot
    be read from this table. The district level has a documented 2014 gap: GOSA published the 2014 district
    file at the school level only (a file byte-identical to the school family''s 2014 file), with no enrollment
    counts from which to reconstruct the district rate, so the district 2014 rows are omitted; 2014 school-level
    rows are present (from the school family). The school-level and district-level rows come from two
    independent GOSA downloads — the district rate rolls up entry/withdrawal counts rather than averaging
    the school rates — so school rows are NOT guaranteed to reconcile with their district row. Mobility
    is a churn ratio, not a bounded proportion: it counts moves rather than movers, so school-level values
    above 1.0 are legitimate and preserved (one suspected 2020 source defect, Eagle''s Landing Academy
    at 115.0, is retained per §4b). No suppression exists in this topic — the only NULLs are four genuinely
    blank school cells (2019, 2020, 2021, 2024); NULL never means suppressed.'
tags:
- education
- gosa
- student_mobility_rate
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/student_mobility_rate/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/student_mobility_rate/**/*.parquet
  format: parquet
schema:
- name: student_mobility_rate
  logicalType: object
  physicalType: parquet
  physicalName: student_mobility_rate
  description: student_mobility_rate fact table, year-partitioned Parquet (split by detail level where
    present).
  dataGranularityDescription: One row per year, district_code, school_code (geography columns are NULL
    at higher aggregation levels).
  customProperties:
  - property: sub_topic
    value: gosa
  - property: detail_levels
    value:
    - schools
    - districts
  - property: default_detail
    value: schools
  - property: grain
    value:
    - year
    - district_code
    - school_code
  - property: key_metric
    value: mobility_rate
  - property: null_semantics
    value:
      suppressed_to_null: false
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/student_mobility_rate/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 student_mobility_rate WHERE year = 2024 LIMIT 100
    - description: District 601 in 2024
      query: SELECT * FROM student_mobility_rate WHERE district_code = '601' AND year = 2024 LIMIT 100
  properties:
  - name: year
    logicalType: integer
    physicalType: int
    required: true
    description: Ending (spring) calendar year of the school year (e.g. 2024 for 2023-24), taken from
      the bronze filename — no in-file year column exists in either family.
    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), zero-padded to 3 digits for standard
      county/city districts (codes 601-793) and the 799 state-schools authorizer; 7-digit state/commission-charter
      authorizer codes (782xxxx/783xxxx, school rows only) are preserved unchanged. On district-detail
      rows it is the bronze school_district_cd; on school-detail rows it is the first 3 (or 7) characters
      of the compound sys_sch code. Never NULL.
    examples:
    - '601'
    relationships:
    - to: districts.district_code
    customProperties:
    - property: column_role
      value: fk_district
    - property: label
      value: District Code
    - property: null_meaning
      value: Never NULL — neither family publishes a statewide aggregate, so every school and district
        row carries a populated district_code.
    primaryKey: true
    primaryKeyPosition: 2
  - name: school_code
    logicalType: string
    physicalType: string
    required: false
    description: 'GOSA school code, zero-padded to 4 characters (composite FK to schools dimension with
      district_code; not globally unique on its own). Populated on school rows only — district rows carry
      NULL. Extracted from the compound bronze sys_sch code: chars after the 3-char district prefix for
      regular districts (''601103'' -> ''0103''), chars 4-7 for 799 state-school codes (both the 11-char
      2012-2019 form ''79918931893'' and the 7-char 2020-2024 form ''7991893'' -> ''1893''), or the last
      3 chars for 10-char charter codes (''7820108108'' -> ''0108'').'
    examples:
    - '0103'
    relationships:
    - to: schools.district_code
    - to: schools.school_code
    customProperties:
    - property: column_role
      value: fk_school
    - property: label
      value: School Code
    - property: null_meaning
      value: Populated only on school-detail rows; NULL on every district-detail row (the district level
        has no school code). The column is shared across the education key shape.
    primaryKey: true
    primaryKeyPosition: 3
  - name: mobility_rate
    logicalType: number
    physicalType: double
    required: false
    description: 'Student mobility (churn) rate on the 0-1 scale (0.214 = 21.4%): student entries plus
      withdrawals between the fall count date and May 1, divided by fall-count enrollment. Bronze publishes
      0-100; divided by 100 per data-cleaning-standards §4. A ratio, not a bounded proportion: the metric
      counts student moves rather than distinct students, so values above 1.0 are legitimate and preserved
      — at the school level 33-59 rows per year exceed 1.0 at alternative schools, DJJ facilities, and
      residential treatment centers (e.g. 5.333 = 533.3% at Lighthouse Care Center of Augusta, 2024).
      District-level values stay below 1.0 in all published years (max 0.59 in 2016). One school value
      is a suspected source defect preserved per §4b (extreme-but-conceivable): 2020 district_code=701
      / school_code=0298 (Eagle''s Landing Academy) publishes 115.0 (raw 11500), ~17x that year''s next
      extreme (657.9); it is retained, not capped, and flagged by the transform''s sanity warning. Bronze
      column name: mobility_rate (2012-2020), renamed to mobility (2021-2024) in both families, semantically
      identical.'
    examples:
    - 0.214
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Mobility Rate
    - property: short_description
      value: 'Student churn rate (0-1 scale): student moves per fall enrollment; counts moves, so it can
        exceed 1.0.'
    - property: unit
      value: ratio
    - property: null_meaning
      value: 'The source cell was genuinely blank (no rate published for that school that year) — NOT
        small-cell suppression. Exactly four such blanks exist, all school-detail: one each in 2019, 2020,
        2021, and 2024. District rows 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: mobility_rate_non_negative
    description: mobility_rate is a ratio (>= 0; may legitimately exceed 1.0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE mobility_rate IS NOT NULL AND mobility_rate < 0
    mustBe: 0
  - type: sql
    name: no_state_rows
    description: 'Structural fact: neither family publishes a statewide rollup, so every gold row (school
      or district detail) has a populated district_code. A NULL district_code means a state row appeared
      or an ID parse failed — either must be analyzed.'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE district_code IS NULL
    mustBe: 0
  - type: sql
    name: district_code_length_3_or_7
    description: district_code is a 3-character standard county/city / state-schools code or a 7-character
      charter-authorizer code (school rows only) — any other length means the ID formatting or the sys_sch
      split regressed.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE LENGTH(district_code) NOT IN (3, 7)
    mustBe: 0
  - type: sql
    name: school_code_length_4
    description: Where populated (school-detail rows), school_code is always zero-padded to exactly 4
      characters, matching the schools dimension key format.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NOT NULL AND LENGTH(school_code) <> 4
    mustBe: 0
  - type: sql
    name: district_detail_180_per_year
    description: 'Every published year carries exactly one district-detail row for each of the same 180
      standard districts (verified: the identical 3-digit code set in all 12 district-level bronze files;
      2014 is omitted). District-detail rows are the school_code IS NULL rows. A deviation means a dropped/added
      entity or a mis-routed file and must be analyzed.'
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year FROM {object} WHERE school_code IS NULL GROUP BY year HAVING
      COUNT(*) <> 180 OR COUNT(DISTINCT district_code) <> 180) AS bad_years
    mustBe: 0
  - type: sql
    name: no_district_rows_2014
    description: 'Structural fact: the district family''s 2014 file is school-level and is dropped, so
      there must be zero district-detail rows (school_code IS NULL) in 2014. A district row in 2014 means
      the school-level anomaly file was mis-routed as district data.'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE year = 2014 AND school_code IS NULL
    mustBe: 0
  - type: sql
    name: district_mobility_rate_never_null
    description: 'The district family has no suppression and no blank cells: every district-detail row
      (school_code IS NULL) in every published year carries a numeric mobility_rate. A NULL there means
      a parsing/era-routing regression.'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE school_code IS NULL AND mobility_rate IS NULL
    mustBe: 0
  - type: sql
    name: school_mobility_nulls_only_in_known_years
    description: The source has no suppression; the only NULL mobility_rate values are the four genuinely
      blank school-level bronze cells, one each in 2019, 2020, 2021, and 2024. A NULL in any other year
      (or on a district row) means a cast or era-routing regression.
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE mobility_rate IS NULL AND year NOT IN (2019, 2020, 2021,
      2024)
    mustBe: 0
  - type: sql
    name: school_mobility_at_most_one_null_per_year
    description: Each of the four known-blank years carries exactly one blank school cell, so no year
      may have more than one NULL mobility_rate (pins the no-suppression fact; a larger count would mean
      a column-rename or cast regression).
    dimension: completeness
    query: SELECT COUNT(*) FROM (SELECT year FROM {object} WHERE mobility_rate IS NULL GROUP BY year HAVING
      COUNT(*) > 1) AS bad_years
    mustBe: 0
customProperties:
- property: title
  value: Student Mobility Rates
- property: summary
  value: How often students change schools in each Georgia public school and district, as an annual churn
    rate, 2012-2024 (no 2014 district rows).
- property: source
  value: GOSA
- property: source_url
  value: https://gosa.georgia.gov/dashboards-data-report-card/downloadable-data
- property: update_frequency
  value: annual
- property: year_range
  value: 2012-2024
- property: schema_hash
  value: 1881f49ef6adc087096645d77f71733135080b53052ab453e0d0c21d3ff4c07b
- property: available_years
  value:
  - 2012
  - 2013
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
