Configuring dbt Project Variables in EDU#
This document provides brief descriptions and usage examples for the available dbt project variables in the EDU framework.
For an introduction to dbt project variables, see dbt's article. Variable configuration is located under vars: within dbt_project.yml in your dbt project directory.
Configuring Ed-Fi Descriptors#
Ed-Fi Descriptors are the data model elements that hold standardized enumerations in Ed-Fi — the "code sets" used to categorize data and power reporting and analytics. Throughout EDU's dbt models, a dbt macro - edu_edfi_source.extract_descriptor is used to extract data from Ed-Fi descriptors. This is most commonly done in base models of edu_edfi_source, like here.
By default (without any configuration), the EDU code replaces descriptor strings like "uri://ed-fi.org/AttendanceEventCategoryDescriptor#Tardy" with the code value ("Tardy"). However, Ed-Fi stores long and short descriptions attached to each code value, and implementations can configure EDU to replace the code values in-place with these descriptions. This configuration can be added per-descriptor by using the dbt project variable descriptors. We've found this is useful for implementations with custom descriptors whose code values are not human readable, e.g. uri://my-state.org/AttendanceEventCategoryDescriptor#01.
Example Config:
Configuring Ed-Fi Extensions#
Resources that have custom columns added via extensions can be added into any model. Ed-Fi extensions tend to come in two flavors: new columns on existing resources, or new resources. This section deals with new columns, while new/custom resources can be handled by adding base/stage models in the importing project.
Extension columns are stored in the API payloads under an _ext element in the JSON, which contains a dictionary of named extensions, each containing their extended columns.
To add these to your tables, you need a configuration block in dbt_project.yml specifying the name of the extended resource, the desired warehouse column name, a SQL snippet necessary to extract the element, and the data type you want it to have.
# general structure
extensions:
{stg_table_name}:
{desired_column_name}:
name: '{extension_name}:{element_name}'
dtype: '{SQL data type}'
# example:
extensions:
stg_ef3__student_special_education_program_associations:
bps_iep_exit_date:
name: 'myBPS:iepExitDate'
dtype: 'timestamp'
Attendance#
chronic_absence_min_days#
Minimum number of days a student needs to be enrolled to count towards Chronic Absenteeism calculations.
Example Config:
Found in these models:chronic_absence_threshold#
Sets the threshold for chronic absenteeism, determining the attendance rate at which students are labeled as chronically absent. e.g., if configured to 90, students with attendance rate less than or equal to 90 are considered chronically absent.
Example Config:
Found in these models:daily_attendance_source#
Optional custom data source for daily attendance. Should be used only when Ed-Fi ODS has been ruled out as the data source.
Example Config:
Found in these models:in_attendance_code#
Code to use as the default string value for Attendance Category when the implementation uses negative attendance. e.g. if no absence record found for student A today, mark as "In Attendance"
Example Config:
Found in these models:num_school_day_calendar_events#
Number of is_school_day calendar events required to mark a school day. Default value is 1, but can be overridden based on local context. In most implementations it is not necessary to change this value, as any of the is_school_day calendar events are sufficient for a day to count as a school day. In one implementation, the state requires a day to be marked both In-Session and Membership Day for it to count, and either of these on their own is insufficient.
Example Config:
Class Period#
custom_data_sources#
Configures the data source(s) and logic for class period columns that are sourced outside the standard set of models referenced by dim_class_period.
IMPORTANT: The custom data source must be unique on k_class_period. Otherwise, the join will incorrectly expand data in dim_class_period.
Example Config:
vars:
'edu:class_period:custom_data_sources':
int_class_period_extension:
period_name:
where:
'int_class_period_extension.period_name'
Course#
custom_data_sources#
Configures the data source(s) and logic for course columns that are sourced outside the standard set of models referenced by dim_course.
IMPORTANT: The custom data source must be unique on k_course. Otherwise, the join will incorrectly expand data in dim_course.
Example Config:
vars:
'edu:course:custom_data_sources':
int_course_extension:
department_name:
where:
'int_course_extension.department_name'
Enroll#
exclude_cross_year_enrollments#
If True, enrollments whose school_year doesn't match the api_year of the data source are filtered out from fct_student_school_association. This is useful if multi-year enrollments have been uploaded to a single-year ODS, and you'd like to filter out those years from the warehouse.
Example Config:
Found in these models:exclude_exit_before_first_day#
If True, enrollments whose exit date is prior to the first day of school (according to bld_school_calendar_windows.first_school_day) are filtered out of fct_student_school_association. This is useful if you'd like to remove "early exit" students, who may have never actually attended the school.
Example Config:
Found in these models:exclude_withdraw_codes#
Configures a list of exit_withdraw_codes to filter out of fct_student_school_association. This is useful if you want to avoid counting e.g. Invalid Enrollments toward warehouse enrollment counts.
Example Config:
Found in these models:Homeless#
agg_types#
Configures which of the default student homeless aggregate indicators to include in dim_student. annual refers to a boolean for whether the student was enrolled in a homeless program at any point during the school year. active refers to a boolean for whether the student is actively enrolled in a homeless program.
Example Config:
Found in these models:custom_program_agg_indicators#
Configures SQL for custom student aggregate homeless program indicators, to include in dim_student in addition to the default indicators from 'edu:homeless:agg_types'. For example, you could configure a new indicator called "is_homeless_on_census_day" based on an implementation-specific census date.
Example Config:
vars:
'edu:homeless:custom_program_agg_indicators':
is_homeless_on_census_day:
agg_sql: >
max(
and program_enroll_begin_date <= ext_census_day -- start date is before or equal to census day
and (program_enroll_exit_date is null -- no exit date
or program_enroll_exit_date > ext_census_day) -- exit date after census day
)
exclude_programs#
Configures program names to exclude from student default homeless indicator calculations.
Example Config:
Found in these models:exit_date_column#
Configures which column to use as exit date for student default homeless indicator calculations.
Example Config:
Found in these models:start_date_column#
Configures which column to use as start date for student default homeless indicator calculations.
Example Config:
Found in these models:Language Instruction#
agg_types#
Configures which of the default student language instruction aggregate indicators to include in dim_student. annual refers to a boolean for whether the student was enrolled in a language instruction program at any point during the school year. active refers to a boolean for whether the student is actively enrolled in a language instruction program.
Example Config:
Found in these models:custom_program_agg_indicators#
Configures SQL for custom student aggregate language instruction program indicators, to include in dim_student in addition to the default indicators from 'edu:language_instruction:agg_types'. For example, you could configure a new indicator called "is_language_instruction_on_census_day" based on an implementation-specific census date.
Example Config:
vars:
'edu:language_instruction:custom_program_agg_indicators':
is_language_instruction_on_census_day:
agg_sql: >
max(
and program_enroll_begin_date <= ext_census_day -- start date is before or equal to census day
and (program_enroll_exit_date is null -- no exit date
or program_enroll_exit_date > ext_census_day) -- exit date after census day
)
exclude_programs#
Configures program names to exclude from student default languge instruction indicator calculations.
Example Config:
Found in these models:exit_date_column#
Configures which column to use as exit date for student default language instruction indicator calculations.
Example Config:
Found in these models:start_date_column#
Configures which column to use as start date for student default language instruction indicator calculations.
Example Config:
Found in these models:School Year#
assessment_dates_xwalk_enabled#
If True, enables use of seed xwalk_assessment_school_year_dates for converting NULL school years (or overriding existing school years) in fct_student_assessment and fct_student_objective_assessment, using date windows. See [coming soon] doc on how to fill out this xwalk
Example Config:
start_month#
Configures the calendar month that each school year begins with, for populating or overriding school_year field in the warehouse. Must use in conjuction with edu:school_year:start_day. Must be 01-12 format.
Example Config:
start_day#
Configures the calendar day within the start month that each school year begins with, for populating or overriding school_year field in the warehouse. Must use in conjuction with edu:school_year:start_month. Must be 01-31 format.
Example Config:
Schools#
custom_indicators#
Configures SQL for custom indicators to add to dim_school.
Example Config:
vars:
'edu:schools:custom_indicators':
is_city_metro_area_school:
where: "city in ('City', 'Suburb 1', 'Suburb 2')"
Special Ed#
agg_types#
Configures which of the default student special education aggregate indicators to include in dim_student. annual refers to a boolean for whether the student was enrolled in a special education program at any point during the school year. active refers to a boolean for whether the student is actively enrolled in a special education program.
Example Config:
Found in these models:custom_program_agg_indicators#
Configures SQL for custom student aggregate special education program indicators, to include in dim_student in addition to the default indicators from 'edu:special_education:agg_types'. For example, you could configure a new indicator called "is_504_plan_active" based on an implementation-specific program descriptor for 504 Plans.
Example Config:
vars:
'edu:special_ed:custom_program_agg_indicators':
is_504_plan_active:
agg_sql: >
-- if the student has an active 504 program enrollment
max(
program_name = 'District 504 Plan'
and program_enroll_begin_date <= current_date() -- start date is today or in the past
and (program_enroll_exit_date is null -- no exit date
or program_enroll_exit_date > current_date()) -- exit date is in the future
)
exclude_programs#
Configures program names to exclude from student default special education indicator calculations.
Example Config:
Found in these models:exit_date_column#
Configures which column to use as exit date for student default special education indicator calculations.
Example Config:
Found in these models:start_date_column#
Configures which column to use as start date for student default special education indicator calculations.
Example Config:
Found in these models:Staff#
preferred_email#
Configures which Email Type to use as preferred Staff Email.
Example Config:
Found in these models:Staff Demos#
hispanic_latino_code#
Configures what text code to display in dim_staff for staff who have has_hispanic_latino_ethnicity = True.
Example Config:
Found in these models:multiple_races_code#
Configures what text code to display in dim_staff for staff who have >1 race.
Example Config:
Found in these models:race_unknown_code#
Configures what text code to display in dim_staff for staff who have no value populated for race.
Example Config:
Found in these models:Stu Demos#
custom_data_sources#
Configures the data source(s) and logic for student demographic columns that are sourced outside the standard set of models referenced by dim_student. Before using this variable, review which models are already referenced by dim_student.
For example, dim_student already has logic for pulling indicators from bld_ef3__student_characteristics. A common use of this variable is for pulling in data sourced outside of core Ed-Fi resources (e.g. a column from an Ed-Fi
extension resource for student economic status).
IMPORTANT: The custom data source must be unique on k_student. Otherwise, the join will incorrectly expand data in dim_student, and test custom_demo_sources_are_unique_on_k_student will fail.
Example Config:
vars:
'edu:stu_demos:custom_data_sources':
bld_ef3__stu_economic_status_extension:
economic_status:
where: >
bld_ef3__stu_economic_status_extension.economic_status in (
'Eligible For Free Meals',
'Eligible for Reduced-Price Meals',
'Other Economic Disadvantage'
)
In dim_student, this compiles to:
bld_ef3__stu_economic_status_extension.economic_status in (
'Eligible For Free Meals',
'Eligible for Reduced-Price Meals',
'Other Economic Disadvantage'
) as economic_status
Found in these models:
display_name_sql#
Configures custom SQL to use as override to student_display_name in dim_student. See example below, where "preferred_first_name" is used where it exists.
Example Config:
vars:
'edu:stu_demos:display_name_sql': >
concat(
stg_student.last_name, ', ',
coalesce(stg_student.preferred_first_name, stg_student.first_name)
coalesce(' ' || left(stg_student.middle_name, 1), ''),
coalesce(' ' || stg_student.generation_code_suffix, '')
)
grade_level_override#
Configures SQL for an override source of student grade level (default is logic in bld_ef3__stu_grade_level based on student enrolled grade).
IMPORTANT: The custom data source must be unique on k_student. Otherwise, the join will incorrectly expand data in dim_student, and the dbt test grade_level_override_unique_on_k_student will fail.
Example Config:
vars:
'edu:stu_demos:grade_level_override':
source: 'stg_ef3__stu_ed_org__indicators'
where: "case when indicator_name = 'TrueGrade' then indicator_value end"
hispanic_latino_code#
Configures the string value to display for students with has_hispanic_latino_ethnicity = True.
Example Config:
Found in these models:intersection_groups#
Configures SQL for custom student intersection groups. See example below for a way to create a "EL SWD" group.
Example Config:
vars:
'edu:stu_demos:intersection_groups':
is_swd_and_el:
where: "coalesce(is_special_education_active, false) and lep_code = 'Limited'"
make_demos_immutable#
If True, certain student columns are made "immutable", meaning they are held constant across years, and the most recent year's value is used. This is useful for columns like race, ethnicity, and name, where year-over-year changes are more often considered data corrections than year-based changes.
Example Config:
Found in these models:multiple_races_code#
Configures the string value to display for students with multiple races.
Example Config:
Found in these models:race_unknown_code#
Configures the string value to display for students with no race populated.
Example Config:
Found in these models:Title I#
agg_types#
Configures which of the default student Title I aggregate indicators to include in dim_student. annual refers to a boolean for whether the student was enrolled in a Title I program at any point during the school year. active refers to a boolean for whether the student is actively enrolled in a Title I program.
Example Config:
Found in these models:custom_program_agg_indicators#
Configures SQL for custom student aggregate Title I program indicators, to include in dim_student in addition to the default indicators from 'edu:title_i:agg_types'. For example, you could configure a new indicator called "is_title_i_on_census_day" based on an implementation-specific census date.
Example Config:
vars:
'edu:title_i:custom_program_agg_indicators':
is_title_i_on_census_day:
agg_sql: >
max(
and program_enroll_begin_date <= ext_census_day -- start date is before or equal to census day
and (program_enroll_exit_date is null -- no exit date
or program_enroll_exit_date > ext_census_day) -- exit date after census day
)
exclude_programs#
Configures program names to exclude from student default Title I indicator calculations.
Example Config:
Found in these models:exit_date_column#
Configures which column to use as exit date for student default Title I indicator calculations.
Example Config:
Found in these models:start_date_column#
Configures which column to use as start date for student default Title I indicator calculations.
Example Config:
Found in these models: