Attendance Query Guide#

Getting started

New to EDU? Start with Getting Started for Users for guided modules on connecting to the warehouse, navigating tables, and writing queries. Return here for attendance-focused patterns and examples.

Introduction#

This is a query guide for using the EDU data warehouse to assemble datasets with attendance data for answering analytics questions.

This guide aims to:

Provide an overview of attendance-related tables (fct_student_school_attendance_event, fct_student_daily_attendance, msr_student_cumulative_attendance, and related dimensions)
Relay when to use the daily fact table vs. the pre-aggregated measure table
Demonstrate common use cases that should help you get started on:
- Exploring data
- Analyzing common attendance query patterns including:
  - Chronic absenteeism
  - Day-of-week analysis
  - Period-over-period trends
- Joining attendance data to student demographics, schools, and calendar dates
- Handling edge cases (partial days, excused vs. unexcused absences, dual-enrolled students)

Important notes

🔧 Implementation variations: This documentation is based on a standard implementation of EDU. Site-specific implementations may have additional data processing steps and features built out that this document does not cover. Please consult dbt Docs and your site’s hosted dbt documentation for more details.

📚 Prerequisites: This document assumes knowledge of SQL and common database concepts such as dimensional modeling.

💡 Learning approach: Examining the common use cases below should help you think critically about the factors to consider when combining attendance data with other types of student data available in EDU.

Which Tables Contain Attendance Data?#

EDU contains attendance data at multiple levels of granularity, including individual attendance events, daily attendance summaries, and pre-aggregated attendance measures. Attendance can be represented as a daily indicator of presence or absence, a detailed event with excused/unexcused status and reason, or a cumulative attendance rate for a student over a specific school year. These different levels are stored in different tables and have different use cases.

This section will walk you through all tables containing attendance data. It will also highlight some notable variables in each of these tables.

Note that this section includes links to a general EDU dbt Docs site. It is likely most helpful to consult the dbt Docs generated for your specific implementation.

When should each table be used?#

The table(s) you work with depends on your use case and the grain of data you are interested in seeing.

Quick reference#

Looking for...	Example question	Try querying from...
Daily trends or historic analysis at the student + school + daily grain	Which school had the lowest attendance rate yesterday? What was last year's October attendance rate?	`fct_student_daily_attendance`
Annual metrics at the student + school + annual grain	What was the final chronic absenteeism rate by school in 2025-2026?	`msr_student_cumulative_attendance`
Investigation/audit of data	What attendance events led to this student's 88% attendance rate?	`fct_student_school_attendance_event` (potentially also `fct_student_daily_attendance` and/or `msr_student_cumulative_attendance` as needed)

Attendance Tables#

Fact Tables#

fct_student_school_attendance_event
- This table contains individual student attendance events. Since attendance is generally recorded only in the negative, this is typically a table of absences only. There is one row per student + attendance event.
- Descriptions of some useful columns are provided below:
  - k_student — Surrogate key for the student in a given school year; foreign key to dim_student.
  - k_student_xyear — Surrogate key for a student across all school years.
  - k_school — Surrogate key for the school; foreign key to dim_school.
  - calendar_date — The date on which the attendance event occurred.
  - attendance_event_category — The category of the event, e.g. "Excused Absence," "Unexcused Absence". Configurable via xwalk_attendance_events.
  - attendance_event_reason — A manually-entered reason, such as appointment, illness, or parent pickup.
  - is_absent — Whether the event represents an absence (1 = absence, 0 = other).
  - attendance_excusal_status — Whether the absence was excused or unexcused.
fct_student_section_attendance_event
- This table defines student-section-level attendance events (period/class attendance). Since attendance is generally recorded only in the negative, this is typically a table of absences only. This data is more granular than school-level attendance, tracking attendance at the individual class/section level.
- Descriptions of some useful columns are provided below:
  - k_student — Surrogate key for the student in a given school year; foreign key to dim_student.
  - k_student_xyear — Surrogate key for a student across all school years.
  - k_school — Surrogate key for the school; foreign key to dim_school.
  - k_course_section — Surrogate key for the course section; foreign key to dim_course_section.
  - attendance_event_date — The date of the attendance event.
  - attendance_event_reason — The reason for the event (e.g., reason for absence).
  - is_absent — Indicator for absence, defined via descriptor mapping in xwalk_attendance_events.
  - event_duration — The duration of the event, as recognized by the school.
fct_student_daily_attendance
- This table contains one record per student, school, and calendar date with daily attendance status and cumulative metrics. The cumulative metrics in this table are calculated within the EDU warehouse. For an in-depth view into the calculations and business rules, see the fct_student_daily_attendance dbt Docs and EDU source code.
- Descriptions of some useful columns are provided below:
  - k_student — Surrogate key for the student in a given school year; foreign key to dim_student.
  - k_student_xyear — Surrogate key for a student across all school years.
  - k_school — Surrogate key for the school; foreign key to dim_school.
  - is_absent / is_present / is_enrolled — Binary indicators for daily attendance status.
  - attendance_excusal_status — Whether the absence was excused or unexcused. Configurable via xwalk_attendance_events.
  - cumulative_attendance_rate — The student's attendance rate calculated cumulatively up to the given date.
  - is_chronic_absentee — Whether the student meets the chronic absenteeism threshold (default: rate < 90% with ≥ 20 days enrolled).
  - absentee_category_rank / absentee_category_label — Risk-bucket ranking and label based on cumulative attendance rate. Configurable via absentee_categories.csv; see the dbt Variables page.

Measure Tables#

msr_student_cumulative_attendance
- This table contains cumulative student attendance measures for the school year. There is one row per student + school + school year. This is built on top of fct_student_daily_attendance, which has already aggregated to the student + school + day.
- Descriptions of some useful columns are provided below:
  - k_student — Surrogate key for the student in a given school year; foreign key to dim_student.
  - k_student_xyear — Surrogate key for a student across all school years.
  - k_school — Surrogate key for the school; foreign key to dim_school.
  - days_absent / days_attended / days_enrolled — Annual totals for the student at this school.
  - attendance_rate — days_attended / days_enrolled for the school year.
  - is_chronic_absentee — Whether the student meets the chronic absenteeism threshold (default: rate < 90% with ≥ 20 days enrolled).
  - absentee_category_rank / absentee_category_label — Risk-bucket ranking and label. Configurable; see the dbt Variables page.

Supporting Tables#

xwalk_attendance_events
- This supporting table gives attendanceEventDescriptors an analytic interpretation for whether a student is absent or not. For more information regarding the use of supporting tables in the EDU framework, see Configuring dbt Seed Crosswalks.

attendance_event_descriptor	is_absent	attendance_excusal_status
In Attendance	0	N/A
Excused Absence	1	Excused
Unexcused Absence	1	Unexcused
Tardy	0	N/A
Early departure	0	N/A
Partial	0	N/A

What processing has already been done?#

This section walks through the pre-processing done on the attendance data before it's made available in the tables mentioned above.

What thresholds have been set?#

The following default thresholds have been set, but the variables are configurable using the EDU configuration framework. For more information on these configurations, see the Attendance section of the dbt Variables page.

Chronic absenteeism threshold — What % cutoff defines chronic absenteeism? By default, attendance rate less than 90% qualifies a student as chronically absent. Configurable via edu:attendance:chronic_absence_threshold.
Enrollment threshold — How many days are required to count toward cumulative chronic absenteeism metrics? By default, 20 days of enrollment are required. Configurable via edu:attendance:chronic_absence_min_days.
Absentee category thresholds — How should attendance rates map to categories or 'risk buckets'? See this template for the default categories, set by absentee_categories.csv.

Business Rules & In-Warehouse Calculations of Student Daily Attendance#

Student–school attribution
- Daily attendance is tied to enrollment at a school. fct_student_daily_attendance is built from student–school membership in fct_student_school_association: a student gets a row for each instructional day in the window they were enrolled at that school (entry through exit for that association).
Building the Attendance Calendar
- Only instructional days (is_school_day = TRUE) are included, with the following date filters:
  - For the current school year: No future dates are included, and only dates prior to a school's most recent submitted attendance event are included.
  - For prior school years: All instructional days are included once the school calendar has ended for a school year.
Student-Specific Calendar
- Each student's attendance calendar begins on their entry date and continues through the end of the calendar.
Positive attendance fill
- If a student is enrolled and no attendance event exists for a day, they are presumed present. The model accounts for students who unenroll/withdraw by indicating them as neither absent nor present.

Handling Common Attendance Edge Cases#

Like many forms of student data, attendance data may include nuanced edge cases that impact metric calculations. Some common edge cases include partial days, excused vs. unexcused absences, and dual-enrolled students. Be aware that these edge cases exist and adjust analysis according to your desired business rules.

Partial-Day Attendance
- Some attendance events represent only part of a day/class session. In fct_student_school_attendance_event, partial-day attendance can be identified using time/duration fields such as event_duration, school_attendance_duration, arrival_time, and departure_time.
- Example: WHERE event_duration < school_attendance_duration limits to partial absences.
Excused vs. unexcused absences
- You may want to only include unexcused absences in certain reports/queries. Both fct_student_school_attendance_event and fct_student_daily_attendance contain columns to distinguish excused vs. unexcused absences:
  - attendance_excusal_status
  - attendance_event_category
  - attendance_event_reason
- Example: WHERE attendance_excusal_status = 'Unexcused'
Dual-enrolled students
- A student may have more than one enrollment record within a single school year if they have transferred or were dual enrolled. This can explain "duplicate" student records within a school year. k_school can be used to limit to particular schools or count the number of schools a student has a record for.
Early Exits
- When a student exits a school before the end of the year, they continue to appear in fct_student_daily_attendance with is_enrolled = 0 for all dates after their exit. This supports cumulative attendance calculations.
  - For daily attendance (e.g. "what was the attendance rate on Jan 16th"), filter to is_enrolled = 1 to exclude exited students from that day's denominator.
  - For cumulative attendance, do not filter on is_enrolled — the trailing zero-enrollment records are what allow exited students to be included in year-to-date figures.
- Example: a student who exits on Jan 15th will have is_enrolled = 0 for every subsequent date through end of year.

Common use cases#

The queries listed in this section should help you get started with exploring attendance data available in EDU and assembling datasets for common analytic use cases.

Placeholder values

Note that in the queries provided in this section, values used for filtering using the WHERE clause may be placeholder values. Make sure to replace these with the actual values of the columns you wish to filter using.

Quick reference#

Use Case	Description	Complexity
How to Get Student Daily Attendance for a Specific Month/Year	Retrieve student-level attendance metrics for a given school and school year	Beginner
How to examine attendance events	Get event-level attendance data	Beginner
How to identify chronically absent students	Identify chronically absent students using cumulative metrics	Intermediate
How to perform day-of-week analysis	Analyze attendance rates for each day of the school week	Intermediate
How to find the lowest single-day attendance rate in a school year	Find the calendar date with the lowest aggregate daily attendance rate and return that date and rate	Intermediate
How to join attendance to other student data (Demographics)	Combine demographics with student attendance data	Intermediate
How to compare monthly attendance rates year-over-year	Roll up to months of the school year, compare two years per school, and show rate change (with calendar month ranges)	Advanced

How to Get Student Daily Attendance for a Specific Month/Year#

What you'll learn: How to retrieve student-level daily attendance metrics for a specific month within a school year.

This use case produces one row per student + school + day for a given month, showcasing student daily attendance for a window of time. This can be useful for reporting and examining daily attendance.

SELECT
    dim_calendar_date.school_year,
    dim_school.school_name,
    dim_student.student_unique_id,
    dim_student.first_name,
    dim_student.last_name,
    dim_calendar_date.calendar_date,
    dim_calendar_date.week_day,
    fct_student_daily_attendance.is_present,
    fct_student_daily_attendance.is_enrolled,
    fct_student_daily_attendance.attendance_event_category,
    fct_student_daily_attendance.cumulative_attendance_rate,
    fct_student_daily_attendance.is_chronic_absentee
FROM analytics.prod_wh.fct_student_daily_attendance
JOIN analytics.prod_wh.dim_student
  ON fct_student_daily_attendance.k_student = dim_student.k_student
JOIN analytics.prod_wh.dim_calendar_date
  ON fct_student_daily_attendance.k_calendar_date = dim_calendar_date.k_calendar_date
JOIN analytics.prod_wh.dim_school
  ON fct_student_daily_attendance.k_school = dim_school.k_school
WHERE dim_calendar_date.school_year = '2025'
  AND dim_school.school_name = 'Your School Name'
  AND dim_calendar_date.is_school_day = 1
  AND dim_calendar_date.calendar_date >= '2024-10-01'
  AND dim_calendar_date.calendar_date < '2024-11-01'
ORDER BY
    dim_student.last_name,
    dim_student.first_name,
    dim_calendar_date.calendar_date;

Assumptions in this example

This example assumes 2025 is the current school year. For an alternative approach that uses the most recent year a student was enrolled (rather than a fixed year), see the Assessment Query Guide's approach to joining demographics from the most recent school year.

The school_name filter is a placeholder. If you want to limit to a specific school or set of schools, replace it with the corresponding value(s) found in dim_school. If you are not interested in limiting to schools, this WHERE condition can be removed.

How to examine attendance events#

What you'll learn: How to analyze detailed attendance events for students, including absence types, reasons, and timing.

This use case investigates attendance at the event/occurrence level, allowing users to examine the specific details behind attendance records. Attendance category, reason, excusal status, and times can allow users to understand when and why absence occurs.

-- Examine attendance events
SELECT
    fct_student_school_attendance_event.school_year,
    dim_school.k_school,
    dim_school.school_name,
    fct_student_school_attendance_event.calendar_date,
    dim_student.student_unique_id,
    dim_student.first_name,
    dim_student.last_name,
    fct_student_school_attendance_event.attendance_event_category,
    fct_student_school_attendance_event.attendance_event_reason,
    fct_student_school_attendance_event.attendance_excusal_status,
    fct_student_school_attendance_event.is_absent,
    fct_student_school_attendance_event.event_duration,
    fct_student_school_attendance_event.school_attendance_duration,
    fct_student_school_attendance_event.arrival_time,
    fct_student_school_attendance_event.departure_time,
    fct_student_school_attendance_event.educational_environment
FROM analytics.prod_wh.fct_student_school_attendance_event
JOIN analytics.prod_wh.dim_student
  ON fct_student_school_attendance_event.k_student = dim_student.k_student
JOIN analytics.prod_wh.dim_school
  ON fct_student_school_attendance_event.k_school = dim_school.k_school
WHERE fct_student_school_attendance_event.school_year = '2025'
  AND dim_school.school_name = 'Your School Name' -- replace with actual school name
ORDER BY
    fct_student_school_attendance_event.calendar_date,
    dim_student.last_name,
    dim_student.first_name;

How to identify chronically absent students#

What you'll learn: How to identify students who are chronically absent for a given school year using cumulative attendance metrics.

This use case identifies students whose cumulative attendance for the given school year meets the threshold for chronic absenteeism. The resulting table includes student-level attendance rate and absenteeism category, highlighting chronically absent students and the level of absenteeism.

-- Identify chronically absent students using cumulative metrics
SELECT
    msr_student_cumulative_attendance.school_year,
    dim_school.k_school,
    dim_school.school_name,
    dim_student.student_unique_id,
    dim_student.first_name,
    dim_student.last_name,
    msr_student_cumulative_attendance.days_absent,
    msr_student_cumulative_attendance.days_attended,
    msr_student_cumulative_attendance.days_enrolled,
    msr_student_cumulative_attendance.attendance_rate,
    msr_student_cumulative_attendance.absentee_category_rank,
    msr_student_cumulative_attendance.absentee_category_label,
    msr_student_cumulative_attendance.is_chronic_absentee
FROM analytics.prod_wh.msr_student_cumulative_attendance
JOIN analytics.prod_wh.dim_student
  ON msr_student_cumulative_attendance.k_student = dim_student.k_student
JOIN analytics.prod_wh.dim_school
  ON msr_student_cumulative_attendance.k_school = dim_school.k_school
WHERE msr_student_cumulative_attendance.school_year = '2025'
  AND dim_school.school_name = 'Your School Name' -- replace with actual school name
  AND msr_student_cumulative_attendance.is_chronic_absentee = 1
ORDER BY
    msr_student_cumulative_attendance.absentee_category_rank DESC,
    dim_student.last_name,
    dim_student.first_name;

How to perform day-of-week analysis#

What you'll learn: How to analyze attendance patterns by day of the week.

This use case examines how attendance varies by day of the school week, aggregating daily attendance for each day. Comparing attendance rates for each day allows users to identify daily patterns and trends in absence, informing attendance intervention programs.

-- Attendance rates for each day of the school week
SELECT
    dim_calendar_date.school_year,
    dim_calendar_date.week_day,
    dim_school.k_school,
    ANY_VALUE(dim_school.school_name) as school_name,
    SUM(fct_student_daily_attendance.is_present) AS present_days,
    SUM(fct_student_daily_attendance.is_enrolled) AS enrolled_days,
    present_days/enrolled_days as attendance_rate
FROM analytics.prod_wh.fct_student_daily_attendance
JOIN analytics.prod_wh.dim_calendar_date
  ON fct_student_daily_attendance.k_calendar_date = dim_calendar_date.k_calendar_date
JOIN analytics.prod_wh.dim_school
  ON fct_student_daily_attendance.k_school = dim_school.k_school
WHERE dim_school.school_name = 'Your School Name' -- replace with actual school name
  AND dim_calendar_date.school_year = '2025'
  AND dim_calendar_date.is_school_day = 1
GROUP BY
    dim_calendar_date.school_year,
    dim_calendar_date.week_day,
    dim_school.k_school
ORDER BY
  dim_calendar_date.school_year,
  attendance_rate desc;

Attendance rate calculation and student-school attribution

The rules for calculating average attendance rates and attributing students to schools should be reviewed carefully depending on your use case.

Attendance rate calculation: The query above calculates attendance rate by summing days attended and days enrolled across all students, then dividing. This gives equal weight to all days enrolled. Alternative approaches might include averaging individual student attendance rates (which gives equal weight to each student) or using weighted averages based on enrollment duration.

Student-school attribution: Remember that students with multiple enrollments will have multiple rows in fct_student_daily_attendance. This query groups by school, which means students who transferred schools will be counted in multiple schools' aggregates, for the amount of days they were enrolled at each respective school. Depending on your analysis needs, you may want to use only the school where the student was enrolled longest, use only the student's primary school for the year, or aggregate at a higher level (e.g., district) to avoid attribution issues.

Note on ANY_VALUE: The query uses ANY_VALUE(dim_school.school_name) because school_name is not in the GROUP BY clause. Since we're grouping by k_school, all rows in each group will have the same school name, so ANY_VALUE safely returns that value. This is a common pattern when you need a non-aggregated column that is functionally determined by the grouped key.

Why not include school_name in the GROUP BY? While including both k_school and school_name in the GROUP BY would be functionally correct (and safe), including just school_name without k_school would be problematic—two different schools could have the same name (e.g., two 'Lincoln Elementary' schools in different districts). Additionally, grouping by the surrogate key alone can be computationally more efficient and makes the code's intent clearer to readers: we're grouping by school identity, not by school name.

How to find the lowest single-day attendance rate in a school year#

What you'll learn: How to find the calendar day with the lowest overall attendance rate in a school year, returning that date and the rate for that day.

-- lowest single-day attendance rate in the school year (district-wide)
SELECT
    dim_calendar_date.calendar_date,
    SUM(fct_student_daily_attendance.is_present) AS total_present,
    SUM(fct_student_daily_attendance.is_enrolled) AS total_enrolled,
    total_present/nullif(total_enrolled, 0) as attendance_rate
FROM analytics.prod_wh.fct_student_daily_attendance
INNER JOIN analytics.prod_wh.dim_calendar_date
    ON fct_student_daily_attendance.k_calendar_date = dim_calendar_date.k_calendar_date
WHERE dim_calendar_date.school_year = '2025'
  AND dim_calendar_date.is_school_day = 1
  AND fct_student_daily_attendance.is_enrolled = 1
GROUP BY
    dim_calendar_date.calendar_date
    ORDER BY attendance_rate ASC;

Aggregate attendance rate and scope

As with day-of-week analysis, this rate is total present divided by total enrolled student-days for that calendar date, so it weights each enrolled student–school–day equally. Students with enrollments at multiple schools contribute to each school’s rows; at district scope those days still appear as separate rows in fct_student_daily_attendance. If you need the lowest day for a single school, filter by that school in the FROM/WHERE clause. For other definitions of “attendance rate” (e.g., average of student-level rates), adjust the aggregation accordingly.

How to join attendance to other student data (Demographics)#

What you'll learn: How to combine attendance data with student demographic information.

This use case joins student attendance and student demographics to allow for attendance analysis by student subgroup. Combining attendance with demographics such as grade level, gender, and race/ethnicity, patterns in attendance by subgroup can be identified, informing intervention programs.

-- Join attendance and student demographics
SELECT
    dim_calendar_date.school_year,
    dim_calendar_date.calendar_date,
    dim_calendar_date.week_of_school_year,
    dim_calendar_date.week_day,
    dim_school.school_name,
    dim_school.k_school,
    dim_student.student_unique_id,
    dim_student.first_name,
    dim_student.last_name,
    dim_student.grade_level,
    dim_student.gender,
    dim_student.race_ethnicity,
    fct_student_daily_attendance.is_enrolled,
    fct_student_daily_attendance.is_present,
    fct_student_daily_attendance.is_absent,
    fct_student_daily_attendance.cumulative_attendance_rate,
    fct_student_daily_attendance.is_chronic_absentee
FROM analytics.prod_wh.fct_student_daily_attendance
JOIN analytics.prod_wh.dim_student
  ON fct_student_daily_attendance.k_student = dim_student.k_student
JOIN analytics.prod_wh.dim_school
  ON fct_student_daily_attendance.k_school = dim_school.k_school
JOIN analytics.prod_wh.dim_calendar_date
  ON fct_student_daily_attendance.k_calendar_date = dim_calendar_date.k_calendar_date
WHERE dim_calendar_date.school_year = '2025'
  AND dim_school.school_name = 'Your School Name' -- replace with actual school name
  AND dim_calendar_date.is_school_day = 1
ORDER BY
    dim_calendar_date.calendar_date,
    dim_student.last_name,
    dim_student.first_name;

How to compare monthly attendance rates year-over-year#

What you’ll learn: How to aggregate daily attendance to months of the school year, place two school years side by side for each school and month, and compute the change in monthly attendance rate. This might be useful to identify trends in schools with higher or lower attendance rate versus the same time last year.

-- Monthly attendance by school, two years side by side, with YoY rate difference
WITH monthly_attendance as (
  SELECT 
    fct_student_daily_attendance.k_school,
    ANY_VALUE(dim_school.school_name) as school_name,
    fct_student_daily_attendance.school_year,
    MONTHNAME(dim_calendar_date.calendar_date) as month,
    COUNT(DISTINCT k_student) as n_students,
    SUM(fct_student_daily_attendance.is_present) as n_present,
    SUM(fct_student_daily_attendance.is_enrolled) as n_enrolled,
    n_present/NULLIF(n_enrolled, 0) as monthly_attendance_rate
  FROM analytics.prod_wh.fct_student_daily_attendance
  JOIN analytics.prod_wh.dim_calendar_date
    ON fct_student_daily_attendance.k_calendar_date = dim_calendar_date.k_calendar_date
  JOIN analytics.prod_wh.dim_school
    ON fct_student_daily_attendance.k_school = dim_school.k_school
  GROUP BY fct_student_daily_attendance.k_school,fct_student_daily_attendance.school_year, month
),
monthly_24 as (
    SELECT * FROM monthly_attendance WHERE school_year = 2024
),
monthly_25 as (
    SELECT * FROM monthly_attendance WHERE school_year = 2025
)
SELECT
  monthly_24.k_school,
  monthly_24.school_name,
  monthly_24.month,
  monthly_24.monthly_attendance_rate as rate_24,
  monthly_25.monthly_attendance_rate as rate_25,
  monthly_24.n_students as n_students_24,
  monthly_25.n_students as n_students_25,
  rate_25 - rate_24 as yoy_diff
FROM monthly_24
JOIN monthly_25
  ON monthly_24.k_school = monthly_25.k_school
  AND monthly_24.month = monthly_25.month
ORDER BY yoy_diff DESC;

Filters, years, and rate definition

Adjust school_year IN (‘2024’, ‘2025’) to whatever pair you need, and keep the CASE expressions in yearly_comparison in sync with those literals. To scope to one school or a list, add to the WHERE in monthly_attendance (for example AND dim_school.school_name = ‘Your School Name’) or filter on k_school. As in day-of-week analysis, the monthly attendance rate here is an aggregate of student-days, not the average of each student’s individual rate; multi-school students contribute separately to each school’s totals.

Haven't found what you're looking for?

Looking for additional use cases? Want similar guides for other domains? Contact us with your thoughts!