Higher Education EDW data model
Data Vault 2.0 raw vault for higher education: hubs (student/course/instructor/term/department), links (enrollment, course-instructor, course-department), and satellites for student, course, instructor, term, and department history plus an enrollment satellite carrying grades and outcomes.
Higher-education enterprise data warehouse as a Data Vault 2.0 raw vault: student, course, instructor, term, and department hubs; enrollment and teaching links; and descriptive satellites — built to integrate SIS, LMS, HR, and finance systems and retain full enrollment and outcome history across academic reorganizations, with FERPA-protected student data isolated in satellites. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 15
- tables
- 82
- columns
- 14
- relationships
- 6
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Enrollment as a three-hub link
An enrollment is the intersection of student, course, and term; a single link across the three hubs models it cleanly and supports term-over-term cohort analysis.
Grades in a satellite on the enrollment link
Grades and statuses change (drops, incompletes, final grades); a satellite on the enrollment link historizes those without losing the original enrollment event.
Department reorganizations absorbed by links
Courses move between departments over time; the course-department link plus satellites preserve history through reorganizations without restating prior terms.
Tables in the Higher Education EDW 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.
Hubs (5)
hub_student
Grain: one row per unique hub_student business key
| Column | Type | Keys |
|---|---|---|
| student_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_course
Grain: one row per unique hub_course business key
| Column | Type | Keys |
|---|---|---|
| course_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_instructor
Grain: one row per unique hub_instructor business key
| Column | Type | Keys |
|---|---|---|
| instructor_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_term
Grain: one row per unique hub_term business key
| Column | Type | Keys |
|---|---|---|
| term_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_department
Grain: one row per unique hub_department business key
| Column | Type | Keys |
|---|---|---|
| department_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (3)
lnk_enrollment
Grain: one row per lnk_enrollment relationship
A student enrolled in a course for a term.
| Column | Type | Keys |
|---|---|---|
| student_hk | binary | |
| course_hk | binary | |
| term_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_course_instructor
Grain: one row per lnk_course_instructor relationship
Who teaches a course in a term.
| Column | Type | Keys |
|---|---|---|
| course_hk | binary | |
| instructor_hk | binary | |
| term_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_course_department
Grain: one row per lnk_course_department relationship
Department that owns a course.
| Column | Type | Keys |
|---|---|---|
| course_hk | binary | |
| department_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_student_details
Grain: one row per student_hk per change
Student descriptive history (FERPA-protected).
| Column | Type | Keys |
|---|---|---|
| student_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| varchar | PII | |
| program | varchar | |
| class_standing | varchar | |
| enrollment_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_course_details
Grain: one row per course_hk per change
Course descriptive history.
| Column | Type | Keys |
|---|---|---|
| course_hk | binary | PK |
| load_date | timestamp | PK |
| course_title | varchar | |
| credit_hours | smallint | |
| level | varchar | |
| delivery_mode | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_instructor_details
Grain: one row per instructor_hk per change
Instructor descriptive history.
| Column | Type | Keys |
|---|---|---|
| instructor_hk | binary | PK |
| load_date | timestamp | PK |
| instructor_name | varchar | PII |
| rank | varchar | |
| tenure_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_term_details
Grain: one row per term_hk per change
Academic-term descriptive history.
| Column | Type | Keys |
|---|---|---|
| term_hk | binary | PK |
| load_date | timestamp | PK |
| term_name | varchar | |
| start_date | date | |
| end_date | date | |
| academic_year | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_department_details
Grain: one row per department_hk per change
Department descriptive history.
| Column | Type | Keys |
|---|---|---|
| department_hk | binary | PK |
| load_date | timestamp | PK |
| department_name | varchar | |
| college | varchar | |
| chair_name | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_enrollment_detail
Grain: one row per enrollment per change
Satellite on the enrollment link carrying grade/outcome history.
| Column | Type | Keys |
|---|---|---|
| enrollment_hk | binary | PK |
| load_date | timestamp | PK |
| enrollment_date | date | |
| enrollment_status | varchar | |
| grade | varchar | |
| grade_points | decimal | |
| credit_hours_earned | smallint | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_grade_scale
Grain: one row per grade
| Column | Type | Keys |
|---|---|---|
| grade_code | varchar | BK |
| grade_points | decimal | |
| is_passing | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_enrollment.student_hk → hub_student (many-to-one)
- lnk_enrollment.course_hk → hub_course (many-to-one)
- lnk_enrollment.term_hk → hub_term (many-to-one)
- lnk_course_instructor.course_hk → hub_course (many-to-one)
- lnk_course_instructor.instructor_hk → hub_instructor (many-to-one)
- lnk_course_instructor.term_hk → hub_term (many-to-one)
- lnk_course_department.course_hk → hub_course (many-to-one)
- lnk_course_department.department_hk → hub_department (many-to-one)
- sat_student_details.student_hk → hub_student (many-to-one)
- sat_course_details.course_hk → hub_course (many-to-one)
- sat_instructor_details.instructor_hk → hub_instructor (many-to-one)
- sat_term_details.term_hk → hub_term (many-to-one)
- sat_department_details.department_hk → hub_department (many-to-one)
- sat_enrollment_detail.enrollment_hk → lnk_enrollment (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.