apiVersion: v3.1.0
kind: DataContract
id: education.sat_scores_highest
name: sat_scores_highest
version: 1.2.0
status: active
domain: education
tenant: georgia-public-data
description:
  purpose: Average SAT scores by test section for Georgia public high schools, with official district
    and state rollups, using each student's HIGHEST section score across SAT administrations (the companion
    sat_scores_recent topic reports the most-recent administration instead). Spans the 2004-2024 GOSA
    publications across the old SAT (Verbal/Math, later +Writing) and the 2016 redesigned SAT (Evidence-Based
    Reading and Writing, Math, optional Essay).
  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. avg_score scales differ by test_component — never compare or
    aggregate across components, and never sum num_tested across components (it double-counts students).
    Old-SAT (pre-2016) and redesigned-SAT (2016+) results are not comparable. HIGHEST-basis caveat for
    the old-SAT writing component (2011-2016): GOSA''s published averages are systematically far below
    the recent-basis values (state 361-406 vs ~470) and 10 values fall below the section''s 200 floor
    (NULLed); in-range writing values — and the combined composite that embeds them — appear deflated
    on the highest basis and should be used with caution. The 2010 Elberta Open Campus verbal_math value
    (1522) is preserved although its reading component (822) was impossible and NULLed.'
tags:
- education
- gosa
- sat_scores_highest
servers:
- server: s3_gold
  type: s3
  location: s3://georgia-data-gold/education/sat_scores_highest/**/*.parquet
  format: parquet
- server: local_gold
  type: local
  path: data/gold/education/sat_scores_highest/**/*.parquet
  format: parquet
schema:
- name: sat_scores_highest
  logicalType: object
  physicalType: parquet
  physicalName: sat_scores_highest
  description: sat_scores_highest 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_highest/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_highest WHERE year = 2024 LIMIT 100
    - description: District 601 in 2024
      query: SELECT * FROM sat_scores_highest WHERE district_code = '601' AND year = 2024 LIMIT 100
    - description: Filter by test_component = combined_test_score
      query: SELECT * FROM sat_scores_highest 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 state-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: 'Student demographic (FK to demographics dimension). Breakdowns exist only for 2004-2010:
      race buckets (asian_pacific_islander, black, hispanic, native_american, white, other, race_unknown)
      and gender (female, male); every 2011-2024 row is ''all''. The source''s bare ''Asian'' label is
      the pre-1997 OMB COMBINED Asian/Pacific Islander bucket (no era of this source publishes a separate
      Pacific Islander row), so it maps to asian_pacific_islander — race buckets are mutually exclusive
      within this topic. 2007-2010 subgroup rows carry counts only (avg_score is NULL); race_unknown (''No
      Response'') carries data in 2004 only (the source''s R columns exist through 2007 but are blank
      in 2005-2006 and literal-''NULL'' in 2007).'
    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 section/composite, era-aware: 2004-2010 wide files publish reading (source ''High
      Verbal''), mathematics (''High Math''), verbal_math (their 400-1600 two-section total, equal to
      reading + mathematics) and writing (2008-2010); 2011-2016 old-SAT files publish reading, mathematics,
      writing and combined (the 600-2400 three-section total); 2016-2024 redesigned-SAT files publish
      combined_test_score (400-1600), math_section_score (200-800), reading_test_score / writlang_test_score
      (GOSA-rescaled 10-40 section tests, observed ~190-340), evidence_based_reading_and_writing (200-800,
      2016-2019 only), and the essay components (essay_reading_score / essay_analysis_score / essay_writing_score
      on 2-8, essay_total on 6-24). Old-SAT and redesigned-SAT composites are NOT comparable across 2016.'
    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'
    - '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: false
    - property: key_metric_grain_contributor
      value: true
    - property: value_coverage
      value:
      - value: 'combined'
        first_year: 2011
        last_year: 2016
        rows: 3483
      - value: 'combined_test_score'
        first_year: 2016
        last_year: 2024
        rows: 4970
      - value: 'essay_analysis_score'
        first_year: 2016
        last_year: 2024
        rows: 2827
      - value: 'essay_reading_score'
        first_year: 2016
        last_year: 2024
        rows: 2828
      - value: 'essay_total'
        first_year: 2016
        last_year: 2024
        rows: 2827
      - value: 'essay_writing_score'
        first_year: 2016
        last_year: 2024
        rows: 2825
      - value: 'evidence_based_reading_and_writing'
        first_year: 2016
        last_year: 2019
        rows: 2243
      - value: 'math_section_score'
        first_year: 2016
        last_year: 2024
        rows: 4979
      - value: 'mathematics'
        first_year: 2004
        last_year: 2016
        rows: 9575
      - value: 'reading'
        first_year: 2004
        last_year: 2016
        rows: 9576
      - value: 'reading_test_score'
        first_year: 2016
        last_year: 2024
        rows: 4961
      - value: 'verbal_math'
        first_year: 2004
        last_year: 2010
        rows: 6090
      - value: 'writing'
        first_year: 2008
        last_year: 2016
        rows: 5105
      - value: 'writlang_test_score'
        first_year: 2016
        last_year: 2024
        rows: 4961
    primaryKey: true
    primaryKeyPosition: 5
  - name: num_tested
    logicalType: number
    physicalType: double
    required: false
    description: 'Number of students tested. Float64 by documented exception (data-cleaning-standards
      §16): 2016-2024 bronze publishes 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 in 2016-2019, rounded
      to one decimal from ~2020); rounding to an integer would lose precision, and every other component''s
      count is integral (enforced by a quality check). 2004-2010 sources publish one count per (entity,
      demographic) administration, repeated on each of that demographic''s component rows; 2011-2024 sources
      publish true per-component counts. Either way, summing num_tested across test_component double-counts
      students — filter to one component for headcounts. A count of 0 is a real observation (zero students
      in that demographic took the SAT).'
    examples:
    - 104
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Number Tested
    - property: short_description
      value: Number of students tested; on the combined test score the source reports the average of the
        three section counts, so it can be fractional, not a headcount.
    - property: unit
      value: count
    - property: null_meaning
      value: Suppressed by GOSA (TFS marker, 2011-2024) or not published for that rollup.
    - property: metric_component
      value: denominator
  - name: avg_score
    logicalType: number
    physicalType: double
    required: false
    description: 'Average SAT score on the component''s own scale (see test_component; scales range from
      essay 2-8 up to combined 600-2400, so never aggregate avg_score across components). Known source
      defects NULLed per data-cleaning-standards §4b (rows and num_tested preserved; 20 values): (a) 2009
      Heritage High School (722:176) published reading=1022, mathematics=995, writing=976, verbal_math=2017
      and South Atlanta Leadership (761:308) writing=811 — all above the 200-800 / 400-1600 scales; (b)
      2010 Rockdale County High (722:3052) published reading=1055, mathematics=1113, writing=1009, verbal_math=2168
      and Elberta Open Campus (676:3050) reading=822; (c) 2011-2015 published 10 old-SAT writing averages
      below the 200 section floor (7 school + 3 district rollup values, as low as 112). An average cannot
      leave the per-student scale, so these are publication errors; NULLing them revises the preserve-bronze
      default for this column and keeps the per-component range quality check enforceable. Preserved-but-flagged
      instead: 2010 Elberta verbal_math=1522 (in range, but its masked reading component makes it suspect)
      and the systematically low 2011-2016 writing averages (state 361-406 vs ~470 on the recent-SAT basis)
      — see limitations.'
    examples:
    - 1043.6
    customProperties:
    - property: column_role
      value: metric
    - property: label
      value: Average Score
    - property: short_description
      value: Average highest-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 count-only observation (all 2007-2010 demographic
        subgroup rows), or one of 20 impossible source values NULLed by the transform (2009-2015).
    - 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 14 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', '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 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. Enforces the transform's known-bad mask.
    dimension: accuracy
    query: SELECT COUNT(*) FROM {object} WHERE avg_score IS NOT NULL AND ((test_component = 'reading'
      AND (avg_score < 200.0 OR avg_score > 800.0)) OR (test_component = 'mathematics' AND (avg_score
      < 200.0 OR avg_score > 800.0)) OR (test_component = 'writing' AND (avg_score < 200.0 OR avg_score
      > 800.0)) OR (test_component = 'evidence_based_reading_and_writing' AND (avg_score < 200.0 OR avg_score
      > 800.0)) OR (test_component = 'math_section_score' AND (avg_score < 200.0 OR avg_score > 800.0))
      OR (test_component = 'verbal_math' AND (avg_score < 400.0 OR avg_score > 1600.0)) OR (test_component
      = 'combined_test_score' AND (avg_score < 400.0 OR avg_score > 1600.0)) OR (test_component = 'verbal_math_writing'
      AND (avg_score < 600.0 OR avg_score > 2400.0)) OR (test_component = 'combined' AND (avg_score <
      600.0 OR avg_score > 2400.0)) OR (test_component = 'essay_reading_score' AND (avg_score < 2.0 OR
      avg_score > 8.0)) OR (test_component = 'essay_analysis_score' AND (avg_score < 2.0 OR avg_score
      > 8.0)) OR (test_component = 'essay_writing_score' AND (avg_score < 2.0 OR avg_score > 8.0)) OR
      (test_component = 'essay_total' AND (avg_score < 6.0 OR avg_score > 24.0)))
    mustBe: 0
  - type: sql
    name: verbal_math_equals_reading_plus_mathematics
    description: 2004-2010 wide bronze satisfies High Total = High Verbal + High Math exactly (max abs
      diff 0.0 in 2007-2010, <= 1 in 2005), so every gold verbal_math row must equal its matching reading
      + mathematics rows within 1 point when all three are non-NULL (masked/suppressed rows drop out via
      the NULL guard).
    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 total, 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 year <= 2010 AND test_component IN ('verbal_math', 'reading', 'mathematics')
      GROUP BY year, district_code, school_code, demographic) WHERE total IS NOT NULL AND reading IS NOT
      NULL AND mathematics IS NOT NULL AND ABS(total - reading - mathematics) > 1.0
    mustBe: 0
  - type: sql
    name: combined_equals_reading_plus_mathematics_plus_writing
    description: 2011-2016 old-SAT files publish combined as the three-section V+M+W total (600-2400),
      so every gold combined row must equal its matching reading + mathematics + writing rows within 1
      point when all four are non-NULL (empirically holds on all 3,473 complete groups, max abs diff 1.0;
      masked/suppressed rows drop out via the NULL guard).
    dimension: consistency
    query: SELECT COUNT(*) FROM (SELECT year, district_code, school_code, demographic, MAX(CASE WHEN test_component
      = 'combined' THEN avg_score END) AS total, 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 year
      BETWEEN 2011 AND 2016 AND test_component IN ('combined', 'reading', 'mathematics', 'writing') GROUP
      BY year, district_code, school_code, demographic) WHERE total IS NOT NULL AND reading IS NOT NULL
      AND mathematics IS NOT NULL AND writing IS NOT NULL AND ABS(total - reading - mathematics - writing)
      > 1.0
    mustBe: 0
  - type: sql
    name: test_component_year_coverage
    description: 'Each test_component appears only in the years its source era publishes it: verbal_math
      2004-2010; reading/mathematics 2004-2016 (old SAT); writing 2008-2016; combined 2011-2016; redesigned-SAT
      components 2016+; evidence_based_reading_and_writing 2016-2019 only.'
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE (test_component = 'verbal_math' AND year > 2010) OR (test_component
      IN ('reading','mathematics') AND year > 2016) OR (test_component = 'writing' AND (year < 2008 OR
      year > 2016)) OR (test_component = 'combined' AND (year < 2011 OR year > 2016)) OR (test_component
      = 'evidence_based_reading_and_writing' AND (year < 2016 OR year > 2019)) OR (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)
    mustBe: 0
  - type: sql
    name: demographic_breakdowns_only_pre_2011
    description: Only the 2004-2010 wide bronze publishes demographic breakdowns; every 2011+ row is demographic
      = 'all'.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE year >= 2011 AND demographic != 'all'
    mustBe: 0
  - type: sql
    name: subgroup_scores_only_2004_2006
    description: Demographic-split HIGHEST scores exist only in 2004-2006 bronze; 2007-2010 subgroup rows
      are count-only (the 2007 demographic High columns are the literal 'NULL' string and 2008-2010 publish
      no subgroup High columns), so any non-'all' row outside 2004-2006 must have NULL avg_score.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE demographic != 'all' AND avg_score IS NOT NULL AND (year
      < 2004 OR year > 2006)
    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. Verified to hold in every bronze era (counts are published
      even where scores are suppressed).'
    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: state_rows_always_have_num_tested
    description: 'GOSA publishes unsuppressed statewide counts in every era: state-level rows (district_code
      and school_code both NULL) must carry non-NULL num_tested.'
    dimension: completeness
    query: SELECT COUNT(*) FROM {object} WHERE district_code IS NULL AND school_code IS NULL AND num_tested
      IS NULL
    mustBe: 0
  - type: sql
    name: fractional_num_tested_only_on_combined_test_score
    description: GOSA publishes fractional test-taker counts ONLY on the redesigned-SAT Combined Test
      Score component (the equal-weight mean of the three section counts — the reason num_tested is Float64);
      every other component's count must be integral.
    dimension: consistency
    query: SELECT COUNT(*) FROM {object} WHERE num_tested IS NOT NULL AND test_component != 'combined_test_score'
      AND num_tested != ROUND(num_tested)
    mustBe: 0
customProperties:
- property: title
  value: SAT Scores (Highest Section Score)
- property: summary
  value: Average SAT scores by section for Georgia public high schools, using each student's highest score
    across test dates, 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: 54310a68e15a2192a2cc29d30867e593e87667bc641b53c7e54127633abfa3e7
- 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: []
