Case study · Analytics engineering
632 queries,zero guesswork.
Turning a university's scattered report SQL into a governed, CI-tested analytics platform: schema checks, guards for SQL that runs clean but is wrong, and an AI-assisted workflow where a person verifies every query.
- Role
- Data Analyst & BI Developer, then analytics consultant
- Organization
- Southwestern Adventist University, Institutional Research
- Timeline
- Apr – Sep 2026
- Stack
- PostgreSQL · SQL Server · Power BI (DAX, M, TMDL) · Python · GitHub Actions · Claude Code
The problem
What neededto change.
The university's reporting SQL lived in legacy report tools and one-off files, with no naming, no documentation, no dialect labels and no checks.
Report SQL fails quietly: a wrong join doubles a count, a stale term code keeps running, and a column typo only surfaces when someone runs the report. That risk was about to grow, with a move to SQL Server and Power BI and a new student information system on the way.
The approach
How it was built,step by step.
- 01
One home, one catalog
Organized every query into 6 functional areas and 20 domains, with an auto-generated query index and per-domain notes on key tables, join keys and known gotchas.
- 02
Schema as the source of truth
Column catalogs for PostgreSQL, SQL Server and the new SIS live in the repo, and CI checks every table and column reference against them. If a column isn't in the catalog, it doesn't exist.
- 03
Guards for SQL that runs clean but is wrong
25 automated checks catch fan-out joins, hard-coded term codes nobody rolled forward, unreliable load flags, numeric overflow, personal data, dialect drift and DAX hygiene.
- 04
Debt that only goes down
Existing violations were frozen in baselines. New ones fail CI, and fixed files must leave the baseline, so quality only moves one way.
- 05
AI-assisted, human-verified
Specialist AI agents write, review and translate queries in a branch-and-pull-request flow. A person smoke-tests each query against the database; the AI never touches data.
The result
What it delivered.In numbers.
The first run of the new SQL parse gate caught two guaranteed runtime errors before anyone ran them, and a column-resolution check caught two Power BI queries joining on a column that didn't exist. A join diagnostic showed one common join silently multiplying rows 13 times, which set the order of fixes.
- grandfathered column-reference errors
- 839 → 0
- automated guards running in CI
- 25
- hidden row fan-out found and prioritized
- 13×