The first time I pointed an agent at the unified record, I asked it something I already knew the answer to. It came back fast, formatted nicely, with a confident sentence on top. It was also wrong, and not in a way you'd notice unless you already knew. That's the whole problem with putting AI on top of institutional data, in one moment. A wrong answer that looks wrong is fine, somebody catches it. A wrong answer that looks right goes into a slide deck.
If you read the post about the unified record mart, you know what that table is: one row per student, per term, with a student's whole path through the university resolved onto it. Enrollment, grades, programs, co-op, study abroad, all of it. It took a year to make those rows trustworthy. The natural next thought was, okay, now let people just ask it questions in plain English. That's what this post is about, and it turned out to be its own project rather than a weekend add-on.
The mart was built for people. Specifically, for people like me who know its conventions. I know a term label like "2024 - Fall" can't be sorted. I know about a fifth of the rows describe students who aren't actively enrolled that term, because they graduated or went inactive, and that you filter them out before you count anything. I know two of the yes/no indicator columns are stored as the letters Y and N while the other twenty are stored as 1 and 0, so adding one of them up throws an error instead of a number. I know the race column also has "International" as one of its values, which means any cross-tab by race silently drops every international student.
An agent knows none of that. Text-to-SQL, which is what these tools do, means the agent reads your question, writes a SQL query against the tables it can see, runs it, and summarizes the result. It writes the most reasonable query a stranger would write. And against the raw mart, the most reasonable query a stranger would write is usually wrong. Every one of those quirks I just listed was fine for the mart's original purpose and a trap for natural language. I counted nine of them before I stopped counting.
So instead of pointing the agent at the mart, we built a layer in between. A semantic layer is just a set of tables and definitions designed to be read by something that doesn't know the house rules. On our side that meant a handful of new dbt models, each one shaped around a kind of question: one row per student per term for headcounts, one row per admission for graduation and retention rates, one row per awarded degree, one row per admitted student, and a couple of tables for program participation. On top of those sits a Snowflake semantic view, which is the object the agent actually talks to. It carries descriptions, metrics, and plain-English rules for how to write SQL against each table.
The decision I'm happiest with is that every correctness fix lives in the tables themselves, not in the agent's instructions. The Y/N columns are real booleans now. Terms have a sortable date and both a calendar year and an academic year on every row. Inactive rows are filtered where they need to be. That means a second agent, one that has never seen our semantic view or read any of our instructions, still can't make the same mistakes, because the tables no longer allow them. Instructions are a convention. A table shape is a control.
I have a sentence I keep coming back to on this project, and it's the same one from the mart post, because I apparently needed to learn it twice. A passing test suite tells you the code does what the code says. It does not tell you the code says the right thing.
The retention rate is the best example. Retention here means: of the students who started in a given fall, what share came back the following fall. Every dbt test on the model was green. Uniqueness, not-null, accepted values, all of it. And the number it produced was somewhere in the sixties to high seventies, when the university publishes a figure in the high nineties. The logic was looking for a student's next enrollment inside the same admission record, so a student who re-enrolled under a changed program looked like they'd left. Nothing about that is a test failure. It's a wrong idea, correctly implemented. The only thing that caught it was comparing our number against a number somebody else computed for their own reasons.
That became the rule for every rate this layer produces: find an outside number and compare. Retention was the canary. Completion, meaning how many students eventually finish a degree, doesn't have a clean outside benchmark that matches our population, which is exactly why completion ships as a range with its caveats attached instead of a single tidy percentage.
The other mistake I keep seeing, and made myself three separate times on this project, is promoting something that changes over time into a key.
For graduation rates you need a cohort, meaning a group of students defined by when they started. I tried keying cohorts on student and degree level, and the start term was ambiguous for about one in six of them. I tried student and program, and the ambiguity dropped to almost nothing. That looked like the answer. It wasn't. A student's program changes. Most of what looked like "one student, two admissions" was one admission where the student switched majors. Keying on program would have counted every major-switcher twice and inflated every cohort denominator by a noticeable margin. The right key was the one boring enough to be obvious in hindsight: student and admit term.
The same shape of bug showed up again when the semantic view grew from two tables to five. Snowflake's own quality tooling flags a semantic view that doesn't declare relationships between its tables, the joins the agent is allowed to make. So the helpful thing to do is declare them. Except joining one-row-per-admission outcomes onto one-row-per-term enrollment quietly weights every student by how many terms they were enrolled. Students who finish stay enrolled longer, so they get counted more, so the completion rate goes up. The numbers below are made up, but the size of the gap is the real one:
| Query | Completion rate |
|---|---|
| Outcomes table on its own | ~80% |
| Same query, joined to term rows on student id | ~84% |
Four points. Close enough that nobody would ever question it, which is precisely what makes it dangerous. The rule we landed on is that tables sharing a grain get related on their full grain key, and tables with different grains get related to nothing at all, whatever the tooling suggests.
-- safe: same grain, joined on the whole key
student_terms <-> student_participation on (student_id, term)
-- never: different grains, looks joinable, silently inflates rates
cohort_outcomes <-> student_terms on (student_id)
The part that changed how I think about agents was realizing that some questions shouldn't be answered straight away.
Ask "how many international students were admitted in 2026" and there are two correct answers. In the mart post I wrote about how a term labeled "2025 - Fall" and one labeled "2026 - Spring" belong to the same academic year. So "2026" could mean the calendar year, or it could mean the 2025 to 2026 academic year, and the two numbers differ by a few hundred students. Neither is wrong. Picking one silently is. So the semantic view carries rules that tell the agent to ask "calendar year or academic year?" before it answers anything with a bare year in it. Once the year is settled, it just answers.
"What is the graduation rate" is worse. Graduation rate of whom? Undergraduates who started as first-year students? Transfers? Masters students, most of whom finish before a second fall even comes around? Over what window? A single number there is almost guaranteed to be misleading, so the agent is told to refuse and ask for a population and a cohort year.
I'll be honest that this is the least-tested behavior in the whole build. I've confirmed the rule is in the deployed object. I haven't yet sat and watched a live agent follow it under pressure.
One of the definitions that has to be pinned down before anyone, human or agent, quotes a graduation number. Years to graduate runs between two census freeze dates, and on time allows a small margin for freeze-date lag. Three illustrative students, real rule.
And then there's the one I think matters most. These agents usually have web search too, and the university publishes graduation and retention rates that are numerically close to ours but computed on a different population. An agent that tries to be helpful by corroborating its answer against the web will find a plausible number, blend it in, and be wrong in a way nobody catches. So web search is flatly off limits for any institutional figure. If the tables don't have it, it doesn't exist.
Before handing this to anyone, I validated it the only way I trust: compute every figure twice, by two genuinely different routes. The semantic view on one side, hand-written SQL straight against the tables on the other. For the questions about students in two programs at once, the wide one-flag-per-program table on one side and a self-join over the long table on the other, which is the exact clumsy query the wide table was built to replace. Comparing the view to itself proves nothing.
Every check agreed, down to the student. The one time something didn't, early on, I spent a while convinced I'd found a real discrepancy before realizing my own comparison query was the thing that was wrong. So now the note at the top of that document says: if a check disagrees, suspect the comparison before the data.
There's one more unglamorous thing worth mentioning. The agent's instructions, its description, its starter questions, none of that is deployed by a build. Somebody pastes it into a form in Snowflake. Which means the live configuration and the repo can drift apart with no way to tell which one is current. So all of it lives in version control as plain text files, next to the view definition, with a note saying when it was last re-pasted and why. It's a small thing. It's also the kind of small thing that makes an agent keep describing tables that were renamed two weeks ago.
Right now the whole thing lives in my personal development schema, which means the day a teammate points their own agent at it, they'll get a permissions error. Getting it into a proper production schema with real access grants is the obvious next step, and it's mostly paperwork at this point, which is the best kind of remaining work.
The bigger thing I'm taking away isn't about Snowflake or dbt. It's that building for an agent is mostly writing down the knowledge that used to live in one or two people's heads, the "oh, you have to filter that" and the "no, that year means something else", and putting it somewhere a stranger can't miss. For a year I was the stranger-proofing between the data and the people asking questions. The semantic layer is me trying to write that job down.