One Row Per Student, Per Term: What a Year-Long Migration Taught Me

What I actually wanted to build is simple to say and hard to build: pull up one student and watch their whole path through the university unfold in a single, continuous view. Not a transcript in one office's system, financial aid in another, co-op history somewhere a completely different office logs into. One view. Grades moving term over term. The programs they were part of, and when that changed. Every co-op placement, every study abroad semester, every tutoring session, every research fellowship they touched, in the order it actually happened to them. Day one to graduation, as one thread instead of six disconnected files that all happen to describe the same person.

That's the actual reason this project exists, and it's worth saying plainly before I get into how hard it was, because the difficulty is easy to remember and the point is easy to lose. An advisor sitting with a student should be able to see a grade dip line up against the term they picked up a heavy co-op load, instead of guessing from memory. Someone asking whether a specific program actually helps students stay enrolled should be able to see that in one place instead of stitching it together from five offices' worth of spreadsheets. The whole idea was to stop treating one student as six separate people who happen to share a name across six separate systems.

Mechanically, what makes that possible is unglamorous: a single table with one row per student, per academic term, built from a couple dozen source systems going back years. Enrollment data, financial aid, study abroad, co-op placements, a research fellowship program, a peer tutoring program, donor and outreach records, all resolved down onto that one row. If you've ever tried to answer a question like "how many first-generation students who studied abroad also completed a co-op" and had to hand-join six spreadsheets to get there, this table is the thing that turns that into a single query, and turns "show me this student's whole story" into a single, readable timeline instead of a research project.

But none of that is worth anything if the numbers underneath it are wrong, and for a while, in specific and avoidable ways, they were. I spent most of a year finding out how, and I want to walk through it the way it actually happened, because it wasn't three separate incidents so much as one long year of learning not to trust a number just because it looked plausible.

The first place trust broke down was quiet, and it stayed quiet for years before I found it. Somewhere in this pipeline, a staging model, the first layer that cleans up raw source data, correctly selected every student with an "active" registration status, in two different flavors: one for students who registered in person, one for students who registered through a self-service portal. A model built downstream of it, months later, by someone who didn't know both flavors existed, filtered down to just one of them.

-- the staging model: every genuinely active registration
select student_id, term, registration_status
from raw_program_enrollment
where registration_status in ('active', 'active_self_service')

-- the model built on top of it, months later, quietly narrower
select student_id, term
from stg_program_enrollment
where registration_status = 'active'   -- 'active_self_service' silently dropped

That one word missing from a where clause deleted whole course sections from the reporting. Not a percentage of rows, entire sections, because the students in them happened to register online instead of in person, which turned out to be the overwhelming majority of them. For years, the reported participation in that program was a small fraction of the real thing, and nobody caught it, because a small, quiet, plausible-looking number doesn't announce itself as wrong. It just looks like modest usage. I only found it because I went looking for a reason to distrust a number that had never given anyone a reason to distrust it.

The numbers below aren't the real ones, I'm not going to publish a university's actual enrollment figures, but they're reconstructed at the same order of magnitude, because that's the part worth showing:

When I shipped the fix, the reported number for one term went up by something close to eighty times in a single day. If you only saw that chart without the story behind it, you'd assume something had broken. It was the opposite. The break had been there the whole time, quietly, and the chart was the first moment it stopped being invisible.

That would have been enough to carry a whole quarter on its own, but a few weeks later I ran into something that scared me more, because this one didn't undercount anything, it deleted an entire term's worth of data with zero errors thrown anywhere. Every model that needed a clean, readable term label, like "2025 - Fall" instead of whatever the source system called it, ran essentially the same parsing logic, copy-pasted into four different files over a few years by four different people who didn't know about each other's copies.

import re

def parse_term(raw_label):
    m = re.match(r"(\d{4}) - (Fall|Spring|Summer 1|Summer 2|Summer Full)", raw_label)
    return m.group(0) if m else None

parse_term("2026 - Summer Semester A")   # -> None

When the university consolidated three separate summer sessions into one, the source system started emitting a label none of those four copies of that regular expression recognized. The function didn't error. It just returned nothing, quietly, for every single summer record, and downstream logic filtered out anything with no term label as a matter of routine data hygiene. An entire summer, tens of thousands of student-terms, vanished from the mart with a completely clean build log. Nothing was broken in a way any test would catch, because every individual model was doing exactly what it was told to do. The mistake wasn't in any one piece of logic. It was in four people making the same reasonable assumption independently, with no shared place to notice they'd made it.

By the time I found the third thing, I'd stopped being surprised and started expecting it, which is its own kind of lesson. This one wasn't even a bug, just a fact about the data that nobody had ever written down anywhere I could find. A term label like "2025 - Fall" and a term label like "2026 - Spring" belong to the same academic year, even though the number in the label is different for each. There's no academic-year column anywhere in the source data. You have to know the convention, or derive it, every single time.

I only found this by comparing enrollment freeze dates against each other until the pattern became obvious. It's the kind of institutional knowledge that lives in one or two people's heads and nowhere else, and it explains an entire category of off-by-one-year bugs that show up in reporting for reasons nobody can quite articulate.

None of that would mean much if I couldn't point at what it was all for, so here's the part I actually care about. Picture a student who declared one major, switched into a different program two years in, took a semester abroad in between, picked up a co-op the term after they got back, and had a rough stretch of grades somewhere in the middle of it that mostly lines up with that heavier schedule. Before this project, seeing that whole shape meant knowing which five or six offices to ask and hoping their records lined up cleanly enough to stitch together by hand. Now it's one row per term, read top to bottom, in order. The grade dip is sitting right next to the term the co-op started. The program change has a date on it. Nobody has to remember to ask the right office, because there's only one place to look.

That's the payoff, and it's the reason the eighty-times bug and the vanished summer and the calendar year confusion were worth chasing down instead of shipping around. A timeline like that is only useful if you can trust every row in it. Get one registration status code wrong and a student's participation quietly disappears from their own story. Get a term label wrong and a whole semester goes missing from a timeline that's supposed to be complete. The vision only works if the underlying rows are honest, which is most of why the year went the way it did.

The habit I actually walked away with isn't a tool or a query pattern. It's a specific kind of stubbornness: a green test suite proves the code does what the code says it should do. It proves nothing about whether what the code says it should do matches reality. The only thing that actually proves anything is comparing a number against a number you trust for an independent reason, and going and finding that second number is usually the slow, unglamorous part of the job that's easiest to skip.

I got burned by skipping it exactly once, on this same project. I expected a fix to roughly double a coverage number, because the bug it fixed looked, on paper, like it should. When I actually went and measured the two source tables instead of reasoning about them, the fix recovered a small fraction of what I'd predicted. The bug was real and worth fixing. My assumption about its size was not, and I would have written that assumption into a handoff doc as fact if I hadn't checked.

None of this makes for a flashy sentence on a resume. "Wrote a regex" and "fixed a where clause" don't sound like the hardest parts of a year of work, and on their own they aren't. The hard part was building the discipline to distrust every number, including my own, until I'd gone and found where it actually came from. That's not a skill anyone teaches you directly. You mostly learn it the way I did, by being wrong in front of a dashboard and having to explain why.