The degree audit rabbit hole: building a transfer equivalency tool students could actually use

I want to tell you what I thought a degree audit project would take, and what it actually took, because the gap between those two things taught me more about data work than most of the projects that went smoothly.

A degree audit system, uAchieve in our case, is the engine that checks a student's completed courses against their degree requirements and tells them what still counts, what's missing, and what a course from somewhere else would count as if they took it. Sounds like a lookup table. It is not a lookup table, and that's the part nobody warns you about.

Degree audit systems encode decades of institutional decisions into rule codes that were never meant to be read by a human outside the registrar's office. A single course equivalency might be wrapped in nested conditional logic, exceptions for specific catalog years, overrides for specific programs, and shorthand that made total sense to the person who wrote it in whatever year they wrote it. We weren't just extracting data. We were reverse engineering years of institutional memory that lived in code strings, one rule block at a time, cross-referencing with people who'd been at the registrar's office long enough to remember why a rule existed in the first place.

That part alone took far longer than the actual pipeline work. Making sense of the codes was the project, more than any dashboard we eventually built on top of them.

Here's a simplified, made-up version of what that decoding actually looked like, with fake rule codes standing in for the real ones:

-- an encoded equivalency rule, illustrative, not a real one
-- ELECTIVE-OVERRIDE::CATYR<2019::PROGRAM=BSBA|BSIB::PREREQ-WAIVE
--
-- what it actually meant, once we tracked down someone who remembered:
--   counts as a free elective
--   only for students admitted before catalog year 2019
--   only in the BSBA or BSIB program
--   and it waives the usual prerequisite check

select
  rule_code,
  case
    when rule_code like 'ELECTIVE-OVERRIDE%' then 'elective_override'
    when rule_code like 'MAJOR-REQ%'         then 'major_requirement'
    when rule_code like 'GENED-SUB%'         then 'gen_ed_substitution'
    else 'unclassified'
  end as rule_type,
  regexp_substr(rule_code, 'CATYR<([0-9]{4})', 1, 1, 'e')   as catalog_year_cutoff,
  regexp_substr(rule_code, 'PROGRAM=([A-Z\\|]+)', 1, 1, 'e') as eligible_programs
from raw_equivalency_rules

Most of the early weeks on this project looked like that: writing a translation, then finding someone at the registrar's office who'd actually run into that exact rule before, and asking them to confirm we'd read it right.

All of that decoding was in service of something specific. The goal was a student-facing tool. Prospective students, current students, and students planning study abroad could look up a course from another institution and see what it would actually count for here, before committing to anything. That "before committing" part mattered. Nobody should have to enroll first and find out later that a semester abroad didn't transfer the way they assumed. Self-serve transfer equivalency information turns a stressful unknown into something a student can actually plan around, which connects directly to something I've written about before: a lot of what makes the international and study abroad experience harder than it needs to be is just not having clear information early enough.

Once we knew what the rules actually meant, building the thing meant a fairly ordinary stack, except for one part that surprised me. We pulled the degree audit and course equivalency data through Fivetran, a managed pipeline tool that syncs data from source systems into a warehouse without us hand-rolling extraction jobs, into a dbt layer where we modeled and cleaned up those encoded rules into something a query could actually reason about. That part was straightforward, relatively speaking.

Power BI was where the real nuance showed up. We landed on Power BI for the front end, and Power BI's "Publish to Web" feature will happily make a report fully public with no login required, which is exactly what we needed for a student-facing tool. What it doesn't do is apply row-level security to that public version. Anything in the dataset behind a publicly published report is visible to anyone with the link, security rules or not. That meant we couldn't just point the public tool at our internal reporting models. We had to build a separate, purpose-built dataset specifically scoped for public consumption, aggregated and filtered down to exactly what a student needs to see and nothing else. It's an easy detail to miss if you've only ever used Power BI for internal dashboards behind organizational login.

The technical stack wasn't actually the hardest part, though, and I want to be honest about what was. None of this got built by one team. Registrar's office people who understood what the codes meant and why. Academic advising, who knew where students actually got stuck. The study abroad office, who understood transfer equivalency from the other direction, coming in rather than going out. Every one of those groups had a stake in the tool being right, because being wrong meant a real student making a real decision on bad information. Getting everyone in a room to actually agree on what a given equivalency meant, and keeping that agreement accurate as rules changed, took more coordination than any part of the technical build.

That's where this actually leaves me. The tool works now. Students use it to actually plan instead of guess. But what I remember most isn't the dbt models or the Power BI workaround, it's how much unglamorous groundwork sits underneath something that looks, to the end user, like a simple search box. Decoding institutional shorthand nobody wrote down properly. Getting five different offices to agree on ground truth. That's most of what real data work actually is, and it rarely shows up in the finished product.