Case study · Data engineering
Freezing census day,twelve terms you can defend.
Enrollment drifts after census day. One census view now feeds an idempotent snapshot table, so each term's reported numbers can be rebuilt exactly, years later.
- Role
- Data Analyst & BI Developer, then analytics consultant
- Organization
- Southwestern Adventist University, Institutional Research
- Timeline
- Apr – Sep 2026
- Stack
- PostgreSQL views, DDL and DML · SQL Server · Power BI
The problem
What neededto change.
The student system is live, so enrollment keeps moving after census day: late adds, drops, relabelled codes. Any count re-run later no longer matches what was reported.
The Board's fact-sheet figures couldn't be rebuilt from history at all, because some of them come from live flags that have no as-of date.
The approach
How it was built,step by step.
- 01
One view, one definition
A single census view computes headcount, credit-based full- and part-time status, FTE and FFTE per student and term, reading tuition divisors by academic year instead of hard-coding them.
- 02
Census-day snapshots
An idempotent insert writes the view into a history table on each census date. Columns are matched by name, never position, so a change in column order can't shift values into the wrong field.
- 03
Schema changes without breaking history
New columns are appended and nullable, so older terms read blank instead of a false value. Run order, re-run safety and rollback steps are documented in the file itself.
- 04
Board flags, backfilled
Added the Board fact-sheet flags and backfilled every prior term, freezing live flags on census day so they can't drift.
- 05
One fix, carried everywhere
When a tuition mapping gap turned up for one graduate program, traced the fix through the snapshot and 16 downstream files in financial aid, federal reporting and retention.
The result
What it delivered.In numbers.
Year-over-year comparisons, FTE and FFTE reporting, federal reconciliation and the Board fact sheet now read frozen census data. The table became shared infrastructure that a colleague's executive dashboard reads too.
- terms backfilled, no missing or duplicate rows
- 12 / 12
- snapshot reconciled to census
- Row for row
- match to the printed Board fact sheet
- Exact