edu_wh v0.6.3

What's Changed

New features

• Update is_active_enrollment logic in fct_student_school_association to allow for extending active periods into Summer, if configured
• Update docs with better descriptions, column descriptions, etc. for several models
• Add constraints - primary_key and foreign_key declarations in dbt model configs for all core_warehouse models
• Add new columns to fct_course_transcripts, including external_ed_org_id and external_ed_org_name

Under the hood

• Move derive_school_year macro and logic upstream to edu_edfi_source
• Refine regex to allow apostrophes in bld_ef3__staff_emails

Fixes

• Fix non-databricks-compatible code from 0.6.2 release

Full Changelog: 0.6.2...0.6.3

Context-Specific Release Notes

For Engineers

• Use new var edu:enroll:year_end_active_buffer_days to extend student's active enrollments into Summer. Setting to 0 means students who exit on the last day of school or later remain active. Setting to 7 means students who exit during the last 7 days of the school year remain active.
• Vars edu:school_year:start_month and start_day now apply to fct_student_school_association, so you can fill in NULL school years using these vars
• dbt constraints primary_key and foreign_key are now available to use via dbt model config calls

For Data Consumers

• The logic for fct_student_school_associtaion.is_active_enrollment may change if your lead engineer configured the var listed above
• New fields are available in fct_course_transcripts, including external_ed_org_id and external_ed_org_name.

edu_wh v0.6.2

What's Changed

New features

• Add Cross-Tenant Assessment sharing functionality. OFF by default. New vars edu:assessment:cross_tenant_enabled and edu:assessments:removed_students_source
• Add configurable behavior for inclusivity/exclusivity of exit dates in enrollment windows. Default behavior (inclusivity) remains the same
• Add course_level_characteristics_array to dim_course_section (sourced from bld_ef3__course_char__combined_wide)
• Add two TPDM/EPDM models, dim_certification_exam and fct_certification_exam_result
• Make attendance_excusal_status logic configurable via new var edu:attendance:excusal_status_column_logic

Under the hood

Fixes

• Fix default fct_student_diploma.sort_index (from NULL -> 1) for databricks compatability
• Fix propagation of course level characteristics in dim_course_section by correcting the field pulled by bld_ef3__course_char__combined_long
• Fix unique_combination_of_columns tests for program association tables, to include program_enroll_begin_date

Full Changelog: 0.6.1...0.6.2

Context-Specific Release Notes

For Engineers:

• Despite the many updates, the only schema update that will occur by default is the addition of dim_course_section.course_level_characteristics_array. Everything else is optional, so you are safe to upgrade via dbt deps --upgrade, if you're already on the 0.6 line
• See here for how to configure the cross-tenant assessment feature (and below for link to blog article)
• See here for how to configure exit_withdraw_date_inclusive
• Some student-program-association dbt tests that previously failed erroneously should now pass

For Data Consumers:

• If your Stadium lead implements the cross-tenant assessment feature, more assessment records will flow to all assessment tables downstream of the core warehouse. See this EDU blog post on how it works
• If your Stadium lead implements the configurable exclusivity feature, some records may change on the margins to correctly reflect enrollment windows
• If your implementation has EPDM, dim_certification_exam and fct_certification_exam_result are now available

edu_wh v0.6.1

What's Changed

New features

• Add school_year column to fct_student_grades, fct_student_discipline_incident_behaviors, fct_student_discipline_incident_summary, fct_student_discipline_actions_summary, fct_student_school_attendance_event, fct_student_section_attendance_event, fct_student_daily_attendance

Under the hood

• Expand yml documentation for fct_student_section_attendance_event

Fixes

• Fix period_duration calculation in dim_class_period to account for different time formats.

Full Changelog: 0.6.0...0.6.1

Context-Specific Release Notes

For Engineers:

• Any downstream queries that implicitly reference school_year from fcts may be broken. All approved edu packages (including edu_ext_podium) were unaffected. Explicit column references should always be used.

For Data Consumers:

• Any downstream queries that implicitly reference school_year from fcts may be broken. All approved edu packages (including edu_ext_podium) were unaffected. Explicit column references should always be used.
• period_duration in dim_class_period will change in edge cases, e.g. for classes that begin prior to 6AM, or where begin_date is in military time, and end_date not in military time.

edu_wh v0.6.0

What's Changed

New features

• Add attendance_excusal_status and consecutive_days_by_excusal_status to fct_student_daily_attendance, where:
  • attendance_excusal_status classifies each attendance record as 'In Attendance', 'Not Enrolled', 'Excused Absence', or 'Unexcused Absence'
  • consecutive_days_by_excusal_status reports the number of consecutive days a student has had the same attendance_excusal_status
• Add attendance_excusal_statusand calendar_date to fct_student_school_attendance_event
• Add TPDM/EPDM domain warehouse models: dim_educator_preparation_program, fct_candidate_educator_preparation_program, fct_candidate_staff_association
• Add "bridge table" brg_course_section_program for convenient linking between course sections & programs

Under the hood

• Update syntax of dbt test argument declarations to avoid deprecated behavior. Requires dbt 1.10.5+

Full Changelog: 0.5.3...0.6.0

Context-Specific Release Notes

For Engineers - Migration Guide

example pr if not using tpdm: edanalytics/stadium_boston@main...bump/edu_060

if using tpdm: edanalytics/stadium_txexchange@main...bump/edu_060

TPDM breaking changes:

(these TPDM breaking changes are only relevant if you project has previously enabled TPDM)

As we've been developing additional models for TPDM, including dims&facts in edu_wh, we have made a couple breaking changes to the previously written edu_edfi_source models. We think now is an appropriate time to make these changes, before they are in wide use.

• **if you previously had edu:tpdm:enabled: True in your dbt_project.yml, **you will likely want to keep this var enabled (it enables new edu_wh tpdm models) but also add src:domain:tpdm:enabled: True
• **if you previously had edu:tpdmcommunity:enabled: True in your dbt_project.yml, **you will likely want to keep this var enabled, but also add src:domain:tpdmcommunity:enabled: True.
• This change reflects edu_wh additions and brings these vars in alignment with edu best practices for distinguishing var namespace by package
• If your stadium code had tpdm enabled and referenced the following columns, you will need to remove them, as they have been deleted
  • stg_tpdm__candidate_educator_preparation_program_associations.k_candidate_educator_preparation_program_association
  • stg_tpdm__survey_response_person_target_associations.k_survey_response_person_target_association
  • **Also, **k_educator_prep_program has been renamed k_educator_preparation_program in all cases

New DS5 Program tables:

These tables are needed to enable new base/stg models for program-related tables added by Ed-Fi in Data Standard 5.0. Because these tables are core to Ed-Fi, we require that you add them, even if not yet in use by your implementation. If you have run codegen since your implementation upgraded to 5.0, these tables should already exist in your project. But if you have not updated your tables since upgrading to 5.0, you will need to update the following, or else edu_edfi_source will break:

• In your stadium repo, add  to dbt/models/staging/src_edfi_3.yml :

  - name: program_evaluations
    description: >
      An evaluation instrument applied to evaluate a student in the context of a program. Student evaluations are typically applied by a staff member based upon a rubric.
    enabled: true
    columns: *column_defaults

  - name: student_program_evaluations
    description: >
      The evaluation results for a student as evaluated in the context of a program.
    enabled: true
    columns: *column_defaults

  - name: student_special_education_program_eligibility_associations
    description: >
      Captures details regarding the evaluation process for eligibility of students for special education services under IDEA Part C or Part B.
    enabled: true
    columns: *column_defaults

• Snowflake admin, run this DDL to create the new raw tables:

use role sysadmin;

-- FIRST, confirm ods_verson and data_model_version are included in your _template tables (should have been updated on previous upgrade migration)
-- alter table dev_raw.edfi3._template_table
-- add ods_version varchar, data_model_version varchar;

-- alter table raw.edfi3._template_table
-- add ods_version varchar, data_model_version varchar;

create or replace table dev_raw.edfi3.program_evaluations
like dev_raw.edfi3._template_table;

create or replace table dev_raw.edfi3.student_program_evaluations
like dev_raw.edfi3._template_table;

create or replace table dev_raw.edfi3.student_special_education_program_eligibility_associations
like dev_raw.edfi3._template_table;

create or replace table raw.edfi3.program_evaluations
like raw.edfi3._template_table;

create or replace table raw.edfi3.student_program_evaluations
like raw.edfi3._template_table;

create or replace table raw.edfi3.student_special_education_program_eligibility_associations
like raw.edfi3._template_table;

xwalk_attendance_events breaking change

We added a feature to track 'attendance excusal status' (excused, unexcused), and count consecutive days within an excusal status. This enables podium work to display students' consecutive days with an unexcused absence, which some states may use to determine policy outcomes like removing students from enrollment. It also helps educators/administrators to intervene before students reach those thresholds. We made a change to the xwalk that requires each implementation to add a field, like so:

https://github.com/edanalytics/stadium\_south\_carolina/commit/9f6fe75b73824b954246331384f22ef1f204f4ce

Note, if you leave this field blank for any given row, the logic is as follows

• if it's an is_absent = 0 row, the excusal status will be 'Not Absent'
• otherwise, the excusal status will be 'Unknown Excusal Status'

We recommend you work with your partner to determine the best status for each row in the xwalk, to make use of this new edu_wh feature.
Remember to run dbt seed --full-refresh after installing this change on your prod server.

dbt test arguments behavior change

In dbt 1.10.5, dbt introduce a new recommended syntax for declaring arguments to tests. There is a deprecation warning on all tests that do not follow this syntax. We have updated the edu_wh test declarations to resolve this warning, but you will need to ensure your dbt version is pinned to 1.10.5+ (also recommend pinning to < 2 to avoid surprise breaking changes when 2 is released).

For Data Consumers:

• You can now answer questions about excusal status in fct_student_daily_attendance, like 'Which students have had unexcused absence for 10 consecutive days this month?'
• If TPDM is enabled in your project, you can now answer questions about candidates and educator preparation programs, and potentially link to staffs data, to see e.g. 'Where were completers of certain educator preparation programs placed? When were they subsequently promoted?' More models in this domain to come soon.
• brg_course_section_program exposes a convenient bridge table between course sections and programs

edu_wh v0.5.3

What's Changed

New features

• Add dim_candidate model

Fixes

• Minor fixes for Databricks compatibility

Full Changelog: 0.5.2...0.5.3

Context-Specific Release Notes

For Engineers:

• If you have already enabled edu:tpdm:enabled: True, dim_candidate will automatically be created on your next dbt run post-upgrade.

For Data Consumers:

• A candidate is a person is enrolled in a educator preparation program and is a candidate to become an educator. From the Ed-Fi Educator Preparation Data Model, which was recently added to Ed-Fi Core.
• dim_candidate will be a central model to a set of EPDM warehouse models that are coming soon

edu_wh v0.5.2

What's Changed

New features

• Add safe_display_name to dim_staff, the logic for this column replicates that of bld_ef3__immutable_stu_demos.

Fixes

• Updated tests dbt_utils_unique_combination_of_columns_fct_student_language_instruction_program_association_k_student__k_program, dbt_utils_unique_combination_of_columns_fct_student_program_association_k_student__k_program, and dbt_utils_unique_combination_of_columns_fct_student_title_i_part_a_program_association_k_student__k_program to include two additional columns. Previous test only listed partial primary key.

Full Changelog: 0.5.1...0.5.2

Context-Specific Release Notes

For Engineers:

• dbt tests listed above should no longer fail on every run

For Data Consumers:

• new column safe_display_name now available in dim_staff

edu_wh v0.5.1

What's Changed

New features

• Add fct models fct_student_cte_program_associations, fct_student_migrant_education_program_associations, and fct_student_school_food_service_program_associations
• Add bld_ef3__student__other_names and conditional code in dim_student to pull into columns, if configured in dbt var 'edu:stu_demos:other_names'.
• Add tests sections_without_staff, sections_without_students, enrollments_without_overlapping_sections, and schools_with_enrollments_without_overlapping_sections to test for rostering data issues.
• Add QC model sections_per_enrollment to assist with identifying school enrollments potentially missing corresponding section enrollment data.

Under the hood

• Update join logic in bld_ef3__student_assessments_long_results and cfg_assessment_scores to join on the assessment_family and/or assessment_identifier fields in xwalk_assessment_scores, if they have been provided. This allows for score configuration by either assess ID or family

Fixes

• Fix model fct_student_daily_attendance to prevent incorrect 100% attendance rates in prior years. Includes school_year in school_max_submitted.max_date_by_school.

Migration

• (Optional) Configure xwalk_assessment_scores. Add in assessment_family field and remove redundant records.

New Contributors

• @mberrien-fitzsimons made their first contribution in #175
• @theokaufman made their first contribution in #185

Full Changelog: 0.5.0...0.5.1

Context-Specific Release Notes

For Engineers:

• If you would like to configure assessment scores or performance levels by assessment_family, you can now configure by adding that column to xwalk_assessment_scores (the code is backwards compatible, so this change is not required to upgrade)
• If you would like to configure any Other Names to show in dim_student, you can configure via new var 'edu:stu_demos:other_names' (example here).

For Data Consumers:

• If populated in Ed-Fi, there may now be new programs data available in fct_student_cte_program_associations, fct_student_migrant_education_program_associations, and fct_student_school_food_service_program_associations
• New data quality tests about rostering data to check out! sections_without_staff, sections_without_students, enrollments_without_overlapping_sections, and schools_with_enrollments_without_overlapping_sections

edu_wh v0.5.0

What's Changed

New features

• ⭐ Add Databricks platform compatibility ⭐
• Add optional gender_identity to dim_student as an immutable demographic. This field was introduced to Ed-Fi in Data Standard v5
• Add optional DS5 fields to fct_student_school_association: is_school_choice, school_choice_basis, enrollment_type, next_year_school_id, next_year_grade_level

Under the hood

• The following 'breaking' under the hood changes were introduced for databricks compatibility:
  • All columns which are part of the primary key of a table are set explicitly as not null
  • The primary key of fct_student_gpa has been changed to remove is_cumulative, but the logic was adjusted so that the effective grain is the same
  • Added fct_staff_school_association.k_staff_school_association and updated the primary key
  • Changed column order in fct_student_assessment and fct_student_objective_assessment
  • Changed dim_class_period.start_time and end_time from time data types to strings

Fixes

• Potentially breaking for queryers: bld_ef3__combine_gpas (and downstream fct_student_gpa) gpa_type 'Unknown' values have been made more specific: 'Cumulative, unknown weighting' and 'Non-cumulative, unknown weighting' to respect the grain of the table.

Full Changelog: 0.4.4...0.5.0

Context-Specific Release Notes

For Engineers:

• Make sure to test your Stadium project locally, as this is a minor version bump with potential breaking changes. Things to check include:
  • full dbt project compiles and runs successfully
  • no new dbt test failures are produced
  • impact of changes listed above, e.g. how many records changed in fct_student_gpa? Do you have users that should be alerted of this?
• Going forward, contributions to edu_wh will need to follow these guidelines to ensure databricks compatibiliity

For Data Consumers:

• bld_ef3__combine_gpas (and downstream fct_student_gpa) gpa_type 'Unknown' values have been made more specific: 'Cumulative, unknown weighting' and 'Non-cumulative, unknown weighting'. If you are referencing these values, connect with your project's lead DE to hear when you can expect this change to take effect.
• In fct_student_assessment and fct_student_objective_assessment, column order was rearranged to move v_other_results to the end of the table, but their grain and values were not changed
• dim_class_period.start_time and end_time were changed from time data types to strings

edu_wh v0.4.4

What's Changed

• Add student's birth country to dim_student by @jabortell in #178
• dbt docs: clarify tenant part of student surrogate keys by @rlittle08 in #177
• Staff Email Handling by @ejoranlienea in #179
• Bump/044 by @rlittle08 in #180

New Contributors

• @jabortell made their first contribution in #178

Changelog

Full Changelog: 0.4.3...0.4.4

New features

• Add birth_country to bld_ef3__immutable_stu_demos and upstream in dim_student
• Add bld_ef3__staff_emails, a build model that combines staff emails from stg_ef3__staffs__emails and stg_ef3__staff_education_organization_contact_associations
• Add bld_ef3__staff_official_emails, a build model that filters staff emails to official/work emails (can be used for RLS)

Under the hood

• Change dim_staff email sourcing to this new combined model ^ bld_ef3__staff_emails
• Update dim_student.yaml (dbt documentation) to be more precise on surrogate key defs for commonly referenced k_student and k_student_xyear

Context-Specific Release Notes

For Engineers:

• If you want to replace birth_country from country codes to full country names, you can use the "replace descriptor" functionality -- read more here
• The staff emails changes are a precursor for upcoming changes to edu_ext_heimdall, where emails may be filtered down to "official valid emails" only

For Data Consumers:

• Check out dim_student.birth_country
• More staffs may have email addresses now in dim_staff
• dbt docs for dim_student have been improved

edu_wh v0.4.3

Overview

What's Changed

• update key to include begin_date, ds 5.0 by @ejoranlienea in #169
• Fix/Support multiple subjects per course in DS 5.0 by @alchenist in #172
• add incident_id_array to discipline actions summary by @ejoranlienea in #171
• Bump/043 by @rlittle08 in #173
• Add school_year to dim_discipline_incident by @rlittle08 in #174

New Contributors

• @alchenist made their first contribution in #172

Copied Changelog

Full Changelog: 0.4.2...0.4.3

Under the hood

• Add incident_id_array to fct_student_discipline_actions_summary
• Add begin_date to primary key of fct_staff_section_associations to align with DS 5.0 and later
• Add school_year to dim_discipline_incident

Fixes

• Fix model dim_course to handle multiple academic subjects per course (Ed-Fi Data Standard v5.0 breaking change)
  • Add array column subject_array to dim_course, containing array of academic subjects if these exist
  • Add logic to populate academic_subject column with single-valued subjects in both cases where data source is <5.0 or >5.0
  • Add upstream bld_ef3__course_subject

Context-Specific Release Notes

For Engineers:

• Check that incident_id_array is correctly populating
• Check for downstream queries that reference school_year from the same query as dim_discipline_incident in case of errant column alias issues

For Data Consumers:

• If your project is on Ed-Fi Data Standard 5.0+, you may have more available course subjects data in dim_course.
• You can now query the list of related incident identifiers via fct_student_discipline_actions_summary.incident_id_array