apiVersion: v3.1.0
kind: DataContract
id: education.sat_scores_recent
name: sat_scores_recent
version: 1.0.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: 'Average SAT scores (most recent administration per student) and number of students tested
    for Georgia public high schools, with official district and state rollups, by SAT test component.
    2004-2010 additionally break out demographic subgroups; 2011 onward is all-students only. Spans the
    2016 SAT redesign: pre-redesign components (verbal_math, reading, mathematics, writing, combined,
    verbal_math_writing) and redesigned components (combined_test_score, math_section_score, evidence_based_reading_and_writing,
    reading_test_score, writlang_test_score, essay components) are distinct categorical values because
    they measure on different scales. Best-attempt scores live in the sibling topic sat_scores_highest.'
  usage: Star-schema fact table. Join the districts dimension on district_code; the schools dimension
    on district_code + school_code; the demographics dimension on demographic. 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. Scores are NOT comparable across the 2016 SAT redesign boundary
    — use the distinct pre-redesign vs redesigned test_component values, never mix them in one time series.
    Demographic subgroups exist only for 2004-2010. num_tested can be fractional (see column description).
    reading_test_score / writlang_test_score are on a GOSA-rescaled axis, not the College Board 10-40
    scale and not the 200-800 section scale.
tags:
- education
- gosa
- sat_scores_recent
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/sat_scores_recent/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/sat_scores_recent/**/*.parquet
  format: parquet
schema:
- name: sat_scores_recent
  logicalType: object
  physicalType: parquet
  physicalName: sat_scores_recent
  description: sat_scores_recent fact table, year-partitioned Parquet (split by detail level where present).
  dataGranularityDescription: One row per year, district_code, school_code, demographic, test_component
    (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
    - demographic
    - test_component
  - property: key_metric
    value: avg_score
  - property: null_semantics
    value:
      suppressed_to_null: true
      zero_is_real: true
  - property: partition_columns
    value:
    - year
  - property: path_template
    value: education/sat_scores_recent/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
    - column: demographic
      target_object: demographics
      target_columns:
      - demographic
      attribute_columns:
      - demographic_label
      - demographic_category
      scope: global
  - property: example_queries
    value:
    - description: Latest year (2024), schools detail
      query: SELECT * FROM sat_scores_recent WHERE year = 2024 LIMIT 100
    - description: District 601 in 2024
      query: SELECT * FROM sat_scores_recent WHERE district_code = '601' AND year = 2024 LIMIT 100
    - description: Filter by test_component = combined_test_score
      query: SELECT * FROM sat_scores_recent WHERE test_component = 'combined_test_score' AND year = 2024
        LIMIT 100
  properties:
  - name: year
    logicalType: integer
    physicalType: int
    required: true
    description: Reporting year. For 2011-2024 this is the spring (ending) calendar year of the school
      year in the source's LONG_SCHOOL_YEAR; for 2004-2010 the source carries no year column and the filename
      publication year is used.
    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-digit zero-padded county/city codes
      or 7-digit charter codes. NULL on state-level rows.'
    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: GOSA school code, zero-padded to 4 characters (composite FK to schools dimension with
      district_code; not globally unique on its own). NULL on district- and state-level rows.
    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: demographic
    logicalType: string
    physicalType: string
    required: true
    description: Demographic subgroup (FK to the global demographics dimension). Subgroup breakdowns exist
      only for 2004-2010; from 2011 onward the source publishes all-students rows only, so every 2011+
      row is 'all'. The asian_pacific_islander key reflects the source's pre-1997 OMB combined Asian +
      Pacific Islander bucket (the bronze label is bare 'Asian', but no era publishes a separate Pacific
      Islander row and sibling GOSA reports use the explicit combined label) — it is NOT an Asian-only
      count, per data-cleaning-standards section 5b. race_unknown (no-response) carries data only in 2004;
      2005-2007 publish the slot empty and 2008+ drop it.
    examples:
    - 'all'
    relationships:
    - to: demographics.demographic
    enum:
    - 'all'
    - 'asian_pacific_islander'
    - 'black'
    - 'female'
    - 'hispanic'
    - 'male'
    - 'native_american'
    - 'other'
    - 'race_unknown'
    - 'white'
    customProperties:
    - property: column_role
      value: fk_demographic
    - property: label
      value: Demographic
    - property: short_description
      value: Student race or gender subgroup; breakdowns exist only for 2004-2010, every later row is
        'all'.
    - property: has_total
      value: true
    - property: total_value
      value: 'all'
    - property: key_metric_grain_contributor
      value: true
    primaryKey: true
    primaryKeyPosition: 4
  - name: test_component
    logicalType: string
    physicalType: string
    required: true
    description: 'SAT test component. Pre-redesign (scales in parentheses): reading (200-800; published
      as ''Verbal'' until 2011, the same Critical Reading section, 2004-2016), mathematics (200-800, 2004-2016),
      writing (200-800, 2008-2016), verbal_math (V+M composite, 400-1600, 2004-2010), verbal_math_writing
      (V+M+W composite, 600-2400, 2008-2010, all-students only), combined (V+M+W composite, 600-2400,
      2011-2016). Redesigned SAT (2016 onward): combined_test_score (Math+EBRW, 400-1600), math_section_score
      (200-800), evidence_based_reading_and_writing (200-800, 2016-2019 only), reading_test_score and
      writlang_test_score (GOSA-rescaled test scores, observed 180-338), essay_reading_score / essay_analysis_score
      / essay_writing_score (2-8) and essay_total (6-24, a GOSA-derived sum of the three essay dimensions,
      not an official College Board score). Old and redesigned components are distinct values because
      they measure on different scales.'
    examples:
    - 'combined_test_score'
    enum:
    - 'combined'
    - 'combined_test_score'
    - 'essay_analysis_score'
    - 'essay_reading_score'
    - 'essay_total'
    - 'essay_writing_score'
    - 'evidence_based_reading_and_writing'
    - 'math_section_score'
    - 'mathematics'
    - 'reading'
    - 'reading_test_score'
    - 'verbal_math'
    - 'verbal_math_writing'
    - 'writing'
    - 'writlang_test_score'
    customProperties:
    - property: column_role
      value: categorical
    - property: label
      value: Test Component
    - property: short_description
      value: Which SAT section or composite the score is for; scales differ, so never compare across components.
    - property: has_total
      value: true
    - property: total_value
      value: 'combined'
    - property: key_metric_grain_contributor
      value: true
    primaryKey: true
    primaryKeyPosition: 5
  - name: num_tested
    logicalType: number
    physicalType: double
    required: false
    description: 'Number of students tested. Float64, NOT Int64 — a documented exception to the integer-count
      convention (data-cleaning-standards section 16): 2016-2024 sources publish fractional counts on
      the combined_test_score component because GOSA reports it as the equal-weight mean of the three
      SAT section test-taker counts (Math, Reading, Writing & Language), not a headcount (e.g. (47+37+37)/3
      -> 40.3 — exact thirds where published full-precision, rounded to one decimal from ~2020), and 2023-24
      district counts are fractional throughout (e.g. 4159.3). Rounding to an integer would silently lose
      precision, so values are carried as published. 2004-2010 sources publish one count per (entity,
      demographic), repeated on each of that demographic''s component rows; 2011-2024 publish true per-component
      counts. Either way, summing num_tested across test_component double-counts students — filter to
      one component for headcounts. A 0 means the source reported zero test-takers for that demographic
      (2004-2010 only).'
    examples:
    - 156.0
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Tested
    - property: unit
      value: count
    - property: null_meaning
      value: Suppressed by GOSA (too few students; TFS marker in 2011-2024 sources).
    - property: metric_component
      value: denominator
  - name: avg_score
    logicalType: number
    physicalType: double
    required: false
    description: 'Average SAT score. The scale varies by test_component (sections 200-800; verbal_math
      / combined_test_score 400-1600; combined / verbal_math_writing 600-2400; essay dimensions 2-8; essay_total
      6-24), so this column carries no single value_min/value_max — the avg_score_within_component_scale
      quality check enforces the per-component ranges instead. reading_test_score and writlang_test_score
      are excluded from that check: GOSA publishes them on a rescaled axis (observed 180-338, consistent
      with 10x the College Board 10-40 test-score scale) with no clean published ceiling. Known source
      defects NULLed by the transform per data-cleaning-standards section 4b (rows and num_tested preserved):
      seven values above their component ceilings — the 2009 Carroll County district verbal_math_writing
      of 2751 (> 2400), and in 2010 Rockdale County High School (722:3052) verbal_math_writing=3114, verbal_math=2133,
      reading=1039, mathematics=1094, writing=981 plus Elberta Open Campus (676:3050) reading=819 (> 800)
      — and nine 2011-2015 writing averages below the 200 section floor (as low as 92, all small schools/districts).
      This revises the preserve-bronze default for those values; the per-component range check remains
      enforceable because of it. Extreme-but-conceivable values are preserved: Elberta Open Campus''s
      2010 verbal_math_writing of 2287 is within the 600-2400 scale and is carried as published (suspect
      — the same row''s reading section is impossible — but not provably wrong itself).'
    examples:
    - 982.3
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Average Score
    - property: short_description
      value: Average most-recent-attempt SAT score; on the scale of the section named by test_component.
    - property: unit
      value: score
    - property: null_meaning
      value: Suppressed by GOSA (too few test-takers), a zero-test-taker demographic cell (2004-2010),
        or one of 16 impossible source values NULLed by the transform.
    - 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: demographic_in_allowed_values
    description: demographic is one of the 10 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE demographic IS NOT NULL AND demographic NOT IN ('all',
      'asian_pacific_islander', 'black', 'female', 'hispanic', 'male', 'native_american', 'other', 'race_unknown',
      'white')
    mustBe: 0
  - type: sql
    name: test_component_in_allowed_values
    description: test_component is one of the 15 canonical codes.
    dimension: conformity
    query: SELECT COUNT(*) FROM {object} WHERE test_component IS NOT NULL AND test_component NOT IN ('combined',
      'combined_test_score', 'essay_analysis_score', 'essay_reading_score', 'essay_total', 'essay_writing_score',
      'evidence_based_reading_and_writing', 'math_section_score', 'mathematics', 'reading', 'reading_test_score',
      'verbal_math', 'verbal_math_writing', 'writing', 'writlang_test_score')
    mustBe: 0
  - type: sql
    name: num_tested_non_negative
    description: num_tested is a count (>= 0).
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE num_tested IS NOT NULL AND num_tested < 0
    mustBe: 0
  - type: sql
    name: avg_score_within_component_scale
    description: Each test_component's avg_score stays within its physical SAT scale (sections 200-800;
      verbal_math / combined_test_score 400-1600; combined / verbal_math_writing 600-2400; essay dimensions
      2-8; essay_total 6-24). The GOSA-rescaled reading_test_score / writlang_test_score components have
      no published ceiling and are intentionally excluded.
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE avg_score IS NOT NULL AND ((test_component = 'reading'
      AND (avg_score < 200 OR avg_score > 800)) OR (test_component = 'mathematics' AND (avg_score < 200
      OR avg_score > 800)) OR (test_component = 'writing' AND (avg_score < 200 OR avg_score > 800)) OR
      (test_component = 'math_section_score' AND (avg_score < 200 OR avg_score > 800)) OR (test_component
      = 'evidence_based_reading_and_writing' AND (avg_score < 200 OR avg_score > 800)) OR (test_component
      = 'verbal_math' AND (avg_score < 400 OR avg_score > 1600)) OR (test_component = 'combined_test_score'
      AND (avg_score < 400 OR avg_score > 1600)) OR (test_component = 'verbal_math_writing' AND (avg_score
      < 600 OR avg_score > 2400)) OR (test_component = 'combined' AND (avg_score < 600 OR avg_score >
      2400)) OR (test_component = 'essay_reading_score' AND (avg_score < 2 OR avg_score > 8)) OR (test_component
      = 'essay_analysis_score' AND (avg_score < 2 OR avg_score > 8)) OR (test_component = 'essay_writing_score'
      AND (avg_score < 2 OR avg_score > 8)) OR (test_component = 'essay_total' AND (avg_score < 6 OR avg_score
      > 24)))
    mustBe: 0
  - type: sql
    name: avg_score_requires_positive_num_tested
    description: 'A published average implies at least one reported test-taker: avg_score must not be
      non-NULL while num_tested is NULL or < 1. Holds in every era (counts are published even for suppressed-score
      rows; verified across all 22 bronze files).'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE avg_score IS NOT NULL AND (num_tested IS NULL OR num_tested
      < 1)
    mustBe: 0
  - type: sql
    name: demographic_breakdowns_only_2004_2010
    description: Demographic subgroup rows exist only in the 2004-2010 wide-format sources; every 2011+
      bronze row is 'All Students'.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE demographic != 'all' AND year > 2010
    mustBe: 0
  - type: sql
    name: pre_redesign_components_within_year_windows
    description: 'Pre-redesign components only appear in their source year windows: verbal_math 2004-2010,
      verbal_math_writing 2008-2010, writing 2008-2016, combined 2011-2016, reading/mathematics 2004-2016.'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE (test_component = 'verbal_math' AND (year < 2004 OR year
      > 2010)) OR (test_component = 'verbal_math_writing' AND (year < 2008 OR year > 2010)) OR (test_component
      = 'writing' AND (year < 2008 OR year > 2016)) OR (test_component = 'combined' AND (year < 2011 OR
      year > 2016)) OR (test_component IN ('reading', 'mathematics') AND (year < 2004 OR year > 2016))
    mustBe: 0
  - type: sql
    name: redesigned_components_within_year_windows
    description: Redesigned-SAT components first appear in 2016; evidence_based_reading_and_writing exists
      2016-2019 only (dropped from the source from 2020 onward).
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE (test_component IN ('combined_test_score', 'math_section_score',
      'reading_test_score', 'writlang_test_score', 'essay_reading_score', 'essay_analysis_score', 'essay_writing_score',
      'essay_total') AND year < 2016) OR (test_component = 'evidence_based_reading_and_writing' AND (year
      < 2016 OR year > 2019))
    mustBe: 0
  - type: sql
    name: verbal_math_reconciles_with_sections
    description: The 2004-2010 verbal_math composite equals reading + mathematics within rounding tolerance
      (means are linear; each published average is rounded independently, so the composite can deviate
      by up to ~1 point per section; 23,442 complete groups, max observed deviation within 1.5).
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, demographic, MAX(CASE WHEN test_component
      = 'verbal_math' THEN avg_score END) AS composite, MAX(CASE WHEN test_component = 'reading' THEN
      avg_score END) AS reading, MAX(CASE WHEN test_component = 'mathematics' THEN avg_score END) AS mathematics
      FROM {object} WHERE test_component IN ('verbal_math', 'reading', 'mathematics') AND year BETWEEN
      2004 AND 2010 GROUP BY year, district_code, school_code, demographic) WHERE composite IS NOT NULL
      AND reading IS NOT NULL AND mathematics IS NOT NULL AND ABS(composite - (reading + mathematics))
      > 1.5
    mustBe: 0
  - type: sql
    name: verbal_math_writing_reconciles_with_sections
    description: The 2008-2010 standalone verbal_math_writing composite equals reading + mathematics +
      writing within rounding tolerance (three independently rounded section means; 1,632 complete groups,
      max observed deviation within 2.0).
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, demographic, MAX(CASE WHEN test_component
      = 'verbal_math_writing' THEN avg_score END) AS composite, MAX(CASE WHEN test_component = 'reading'
      THEN avg_score END) AS reading, MAX(CASE WHEN test_component = 'mathematics' THEN avg_score END)
      AS mathematics, MAX(CASE WHEN test_component = 'writing' THEN avg_score END) AS writing FROM {object}
      WHERE test_component IN ('verbal_math_writing', 'reading', 'mathematics', 'writing') AND year BETWEEN
      2008 AND 2010 GROUP BY year, district_code, school_code, demographic) WHERE composite IS NOT NULL
      AND reading IS NOT NULL AND mathematics IS NOT NULL AND writing IS NOT NULL AND ABS(composite -
      (reading + mathematics + writing)) > 2.0
    mustBe: 0
  - type: sql
    name: combined_reconciles_with_sections
    description: The 2011-2016 combined composite (V+M+W, 600-2400) equals reading + mathematics + writing
      within rounding tolerance (three independently rounded section means; 3,474 complete groups, max
      observed deviation within 2.0).
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, demographic, MAX(CASE WHEN test_component
      = 'combined' THEN avg_score END) AS composite, MAX(CASE WHEN test_component = 'reading' THEN avg_score
      END) AS reading, MAX(CASE WHEN test_component = 'mathematics' THEN avg_score END) AS mathematics,
      MAX(CASE WHEN test_component = 'writing' THEN avg_score END) AS writing FROM {object} WHERE test_component
      IN ('combined', 'reading', 'mathematics', 'writing') AND year BETWEEN 2011 AND 2016 GROUP BY year,
      district_code, school_code, demographic) WHERE composite IS NOT NULL AND reading IS NOT NULL AND
      mathematics IS NOT NULL AND writing IS NOT NULL AND ABS(composite - (reading + mathematics + writing))
      > 2.0
    mustBe: 0
  - type: sql
    name: combined_test_score_reconciles_with_sections
    description: The 2016-2019 redesigned-SAT combined_test_score equals math_section_score + evidence_based_reading_and_writing
      within rounding tolerance (EBRW is published 2016-2019 only, so the window ends there; 2,243 complete
      groups, max observed deviation within 1.5).
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, demographic, MAX(CASE WHEN test_component
      = 'combined_test_score' THEN avg_score END) AS composite, MAX(CASE WHEN test_component = 'math_section_score'
      THEN avg_score END) AS math_section, MAX(CASE WHEN test_component = 'evidence_based_reading_and_writing'
      THEN avg_score END) AS ebrw FROM {object} WHERE test_component IN ('combined_test_score', 'math_section_score',
      'evidence_based_reading_and_writing') AND year BETWEEN 2016 AND 2019 GROUP BY year, district_code,
      school_code, demographic) WHERE composite IS NOT NULL AND math_section IS NOT NULL AND ebrw IS NOT
      NULL AND ABS(composite - (math_section + ebrw)) > 1.5
    mustBe: 0
  - type: sql
    name: essay_total_reconciles_with_dimensions
    description: The published essay_total equals the sum of the three essay dimension averages within
      rounding tolerance (means are linear; each published average is rounded, observed max deviation
      1.0).
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, demographic, MAX(CASE WHEN test_component
      = 'essay_total' THEN avg_score END) AS total, MAX(CASE WHEN test_component = 'essay_reading_score'
      THEN avg_score END) AS reading, MAX(CASE WHEN test_component = 'essay_analysis_score' THEN avg_score
      END) AS analysis, MAX(CASE WHEN test_component = 'essay_writing_score' THEN avg_score END) AS writing
      FROM {object} WHERE test_component IN ('essay_total', 'essay_reading_score', 'essay_analysis_score',
      'essay_writing_score') GROUP BY year, district_code, school_code, demographic) WHERE total IS NOT
      NULL AND reading IS NOT NULL AND analysis IS NOT NULL AND writing IS NOT NULL AND ABS(total - (reading
      + analysis + writing)) > 1.5
    mustBe: 0
  - type: sql
    name: state_all_students_rows_never_suppressed
    description: 'GOSA publishes unsuppressed statewide all-students aggregates in every era: state-level
      rows (district_code and school_code both NULL) with demographic = ''all'' must carry non-NULL num_tested
      and avg_score.'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE district_code IS NULL AND school_code IS NULL AND demographic
      = 'all' AND (num_tested IS NULL OR avg_score IS NULL)
    mustBe: 0
customProperties:
- property: title
  value: SAT Scores (Most Recent Administration)
- property: summary
  value: Average SAT scores by section for Georgia public high schools, using each student's most recent
    test date, 2004-2024.
- 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: 2004-2024
- property: schema_hash
  value: 675005c92aa47a74bca9b4271362bb5d6105914ed5d308994fe51dd31d69efba
- property: available_years
  value:
  - 2004
  - 2005
  - 2006
  - 2007
  - 2008
  - 2009
  - 2010
  - 2011
  - 2012
  - 2013
  - 2014
  - 2015
  - 2016
  - 2017
  - 2018
  - 2019
  - 2020
  - 2021
  - 2022
  - 2023
  - 2024
- property: year_gaps
  value: []
