Higher-Ed Student Success & Retention Analytics data model
Kimball warehouse for higher-ed student success and retention reporting: a periodic-snapshot enrollment fact at student-term grain, a transaction-grain course-grade fact for the GPA build, and an intervention-event fact for advising and early-alert outcomes. SCD2 student preserves as-was cohort and demographic attributes for graduation-rate cohort tracking and equity-gap analysis. PII (student name, DOB, advisor/instructor name) is FERPA-classified and masked.
Higher-education student success and retention analytics sourced from the Student Information System (SIS) and advising / early-alert CRM. Models a term-grain periodic-snapshot enrollment fact (credits, term and cumulative GPA, persistence flag, financial aid) for first-year retention and term-to-term persistence, a transaction-grain course-grade fact for the GPA build, and an advising / intervention event fact for early-alert effectiveness, over conformed term, SCD2 student (cohort/demographics), academic program, course section, instructor, advisor, grade, enrollment status, intervention type, and risk-level dimensions. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 14
- tables
- 127
- columns
- 16
- relationships
- 4
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Term-grain periodic-snapshot enrollment fact
Retention and persistence are inherently term-to-term measures; a periodic snapshot at student-term grain captures credits attempted/earned, term/cumulative GPA, and a persistence-to-next-term flag in a single additive row, which is the natural grain for first-year retention and term-to-term persistence reporting.
Transaction-grain course-grade fact for the GPA build
A separate course-section-grain fact preserves every registration, grade letter, and grade-point value so cumulative and term GPA on the enrollment snapshot can be reproduced, and so course-level outcomes (DFW rates by instructor / modality) are queryable.
Dedicated intervention / early-alert event fact
Advising and early-alert events have their own grain (one row per event) and their own measures (resolution days, follow-up count, outcome); modelling them as a separate transaction fact lets us tie interventions back to the student-term snapshot to measure intervention effectiveness on persistence.
SCD2 on dim_student to preserve as-was demographics and cohort
Equity-gap and graduation-rate cohort reporting requires the student attributes (classification, residency, Pell eligibility, first-generation flag) that were true at the time of enrollment, not today; type-2 history on the student dimension is what makes comparable longitudinal cohort analysis possible.
FERPA-classified PII on student and staff dimensions
Student name, date of birth, and advisor/instructor names are FERPA-protected directory and education-record data; tagging them PII with confidential classification and partial masking gives the governance report immediate signal and drives downstream row/column-level access policies in Snowflake.
Tables in the Higher-Ed Student Success & Retention Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_term_enrollment
Grain: one row per student per academic term (periodic snapshot)
Periodic-snapshot enrollment fact at student-term grain for retention/persistence.
| Column | Type | Keys |
|---|---|---|
| term_enrollment_key | bigint | PK |
| term_key | bigint | |
| student_key | bigint | |
| program_key | bigint | |
| academic_advisor_key | bigint | |
| enrollment_status_key | bigint | |
| credits_attempted | decimal | |
| credits_earned | decimal | |
| credits_passed | decimal | |
| term_gpa | decimal | |
| cumulative_gpa | decimal | |
| financial_aid_amount | decimal | |
| is_full_time | smallint | |
| persistence_to_next_term | smallint | |
| currency | varchar |
fct_course_grade
Grain: one row per student per course-section per term (transaction grain)
Transaction-grain course-grade fact — the GPA build at registration level.
| Column | Type | Keys |
|---|---|---|
| course_grade_key | bigint | PK |
| term_key | bigint | |
| student_key | bigint | |
| course_section_key | bigint | |
| instructor_key | bigint | |
| grade_key | bigint | |
| credits_attempted | decimal | |
| credits_earned | decimal | |
| grade_points | decimal | |
| midterm_grade_points | decimal | |
| grade_letter | varchar | |
| withdrew_flag | smallint | |
| incomplete_flag | smallint | |
| passed_flag | smallint |
fct_intervention_event
Grain: one row per advising / intervention / early-alert event
Transaction-grain intervention/early-alert fact for advising effectiveness.
| Column | Type | Keys |
|---|---|---|
| intervention_event_key | bigint | PK |
| event_date_key | bigint | |
| term_key | bigint | |
| student_key | bigint | |
| academic_advisor_key | bigint | |
| intervention_type_key | bigint | |
| risk_level_key | bigint | |
| event_id | varchar | |
| alert_id | varchar | |
| resolution_days | decimal | |
| follow_up_count | integer | |
| days_to_resolution | decimal | |
| intervention_outcome | varchar | |
| is_early_alert | smallint |
Dimension tables (11)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| academic_week | smallint | |
| is_business_day | boolean |
dim_term
Grain: one row per academic term
| Column | Type | Keys |
|---|---|---|
| term_key | bigint | PK |
| term_code | varchar | BK |
| term_name | varchar | |
| academic_year | varchar | |
| term_type | varchar | |
| term_start_date | date | |
| term_end_date | date | |
| is_current | boolean |
dim_student
Grain: one row per student per SCD2 version (SIS person record)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| student_key | bigint | PK |
| student_id | varchar | BK |
| student_name | varchar | PII |
| date_of_birth | date | PII |
| gender | varchar | |
| ethnicity | varchar | |
| race | varchar | |
| first_generation_flag | boolean | |
| pell_eligible_flag | boolean | |
| residency_status | varchar | |
| classification | varchar | |
| expected_graduation_term | varchar | |
| cohort_year | smallint | |
| is_active | boolean |
dim_program
Grain: one row per academic program
| Column | Type | Keys |
|---|---|---|
| program_key | bigint | PK |
| program_code | varchar | BK |
| program_name | varchar | |
| degree_type | varchar | |
| college | varchar | |
| department | varchar | |
| cip_code | varchar | |
| is_stem | boolean |
dim_course_section
Grain: one row per offered course section
| Column | Type | Keys |
|---|---|---|
| course_section_key | bigint | PK |
| course_section_id | varchar | BK |
| course_code | varchar | |
| course_title | varchar | |
| course_subject | varchar | |
| section_number | varchar | |
| course_level | varchar | |
| modality | varchar | |
| credit_hours | decimal |
dim_instructor
Grain: one row per instructor of record
| Column | Type | Keys |
|---|---|---|
| instructor_key | bigint | PK |
| instructor_id | varchar | BK |
| instructor_name | varchar | PII |
| department | varchar | |
| faculty_rank | varchar | |
| tenure_status | varchar | |
| employment_type | varchar | |
| is_active | boolean |
dim_academic_advisor
Grain: one row per academic / career / financial advisor
| Column | Type | Keys |
|---|---|---|
| academic_advisor_key | bigint | PK |
| advisor_id | varchar | BK |
| advisor_name | varchar | PII |
| department | varchar | |
| caseload_size | integer | |
| advisor_type | varchar | |
| is_active | boolean |
dim_grade
Grain: one row per grade code on the institutional grading scale
| Column | Type | Keys |
|---|---|---|
| grade_key | bigint | PK |
| grade_code | varchar | BK |
| grade_points | decimal | |
| is_passing | boolean | |
| is_withdraw | boolean | |
| is_incomplete | boolean |
dim_enrollment_status
Grain: one row per enrollment status code
| Column | Type | Keys |
|---|---|---|
| enrollment_status_key | bigint | PK |
| status_code | varchar | BK |
| status_name | varchar | |
| is_active_enrollment | boolean |
dim_intervention_type
Grain: one row per advising / early-alert intervention type
| Column | Type | Keys |
|---|---|---|
| intervention_type_key | bigint | PK |
| intervention_type | varchar | BK |
| description | varchar | |
| intervention_category | varchar | |
| intervention_modality | varchar |
dim_risk_level
Grain: one row per at-risk level bucket from the early-alert model
| Column | Type | Keys |
|---|---|---|
| risk_level_key | bigint | PK |
| risk_level | varchar | BK |
| description | varchar | |
| score_min | decimal | |
| score_max | decimal | |
| requires_followup | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_term_enrollment.term_key → dim_term (many-to-one)
- fct_term_enrollment.student_key → dim_student (many-to-one)
- fct_term_enrollment.program_key → dim_program (many-to-one)
- fct_term_enrollment.academic_advisor_key → dim_academic_advisor (many-to-one)
- fct_term_enrollment.enrollment_status_key → dim_enrollment_status (many-to-one)
- fct_course_grade.term_key → dim_term (many-to-one)
- fct_course_grade.student_key → dim_student (many-to-one)
- fct_course_grade.course_section_key → dim_course_section (many-to-one)
- fct_course_grade.instructor_key → dim_instructor (many-to-one)
- fct_course_grade.grade_key → dim_grade (many-to-one)
- fct_intervention_event.event_date_key → dim_date (many-to-one)
- fct_intervention_event.term_key → dim_term (many-to-one)
- fct_intervention_event.student_key → dim_student (many-to-one)
- fct_intervention_event.academic_advisor_key → dim_academic_advisor (many-to-one)
- fct_intervention_event.intervention_type_key → dim_intervention_type (many-to-one)
- fct_intervention_event.risk_level_key → dim_risk_level (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.