Every Answer Cites Its Source

Most “AI over your data” demos point a language model at a vector database, get a plausible paragraph back, and call it done. I’ve built the small version of the honest alternative — a proof-of-concept where the model drafts and a deterministic layer decides. This is that idea taken end to end, at scale, on real data: a vehicle-safety evidence system in Microsoft Fabric, over the U.S. National Highway Traffic Safety Administration’s public complaints, recalls, and investigations.

The rule was one sentence: use SQL and explicit relationships where the answer is deterministic, use embeddings only where language similarity is actually needed, and make every result trace back to a named source. And one more, load-bearing for this domain: a consumer complaint is a report, not a finding. The system never presents one as an established defect, and keeps complaint evidence physically separate from official recalls and investigations.

Here’s the whole shape before we open up the parts that matter:

Bronze to Silver to Gold, then a fork: only the language lane gets embedded, and both lanes converge on one cited console.

Most of it isn’t retrieval

There are eight supported questions across four routes, and the router picks one from the question code. The useful surprise is how few of them need embeddings at all:

Five of the eight never compute a cosine similarity; only three do. And the route labels mislead if you trust them: narrative doesn’t mean semantic — N002 answers by recall identifier, returning the official defect, consequence, and remedy in a fixed order — and hybrid doesn’t mean everything is retrieved by similarity, since in H001 the trend is SQL and the investigation is an exact lookup, and only the complaint patterns are semantic. Done responsibly, most of a “RAG” system is SQL and identifiers.

The router splits on the question code. Only the two language routes reach the index at all.

And this isn’t just the design on paper — it’s what the results table holds. Every route lands a structured, deterministic row:

Even the narrative and hybrid routes emit structured output. N001 returns a candidate_row_count of 169,483 — the set the funnel narrows to further down — H001 a monthly TIME_SERIES, N002 an OFFICIAL_ACTION recall number, R001 explicit investigation → recall links. The language routes add semantic evidence on top of this; they never replace it.

Grain before transformation

The first notebook wasn’t ingestion; it was reconnaissance, because the raw NHTSA files don’t sit at one row per business thing. The complaint file is component-oriented — one complaint appears once per component it names — and the source hands you both identifiers: ODINO for the complaint, CMPLID for the complaint-component record. Treat the physical row as the complaint and your counts inflate the instant one names two components. That’s why Silver holds 766,726 complaints but 1,072,447 complaint-components, and why recalls and investigations became entity tables plus bridges rather than flattened “one row per action” tables.

The cheapest capacity, and a million embeddings

I ran the whole thing on an F2 capacity — the smallest Fabric sells — because it’s a portfolio build and I wanted it cheap. That’s fine for everything except turning nearly a million chunks into vectors. The early passes crawled, and pushing the batch size up to compensate didn’t speed things up — it crashed, through Fabric’s Native Execution Engine:

GlutenException
VeloxRuntimeError

A scale problem I’d bought by under-provisioning on purpose. Rather than pay for more capacity to brute-force it, I generated the vectors off the metered clock with EmbeddingGemma and imported them. The lesson wasn’t that Fabric can’t embed at scale — it was that I’d picked the cheapest capacity, and a million rows is exactly where that shows.

Filter first, rank second

Once retrieval worked, it was wrong in an instructive way. Ask for a recall’s defect, consequence, and remedy, and there are exactly four official documents; semantic ranking put the generic notes first, because their wording sat closest to the question. Valid similarity, wrong answer. So the known official sections stopped being ranked and got assembled in a fixed order instead. Cosine similarity is for discovering fuzzy evidence, not for ordering documents whose authority you already know.

That’s also why retrieval runs after structured filtering, never over the whole index. The first version scored the entire index per query and ran for over an hour. Scoped to the already-filtered candidate set — the largest case still 169,483 rows — and joining the narrative text back only after reducing to the top 20, it finished in about 67 seconds.

SQL narrows the population first; cosine similarity only ranks what’s left.

A hyphen, and one canonicalization contract

The first serving validation passed six of eight routes. Two failed identically:

Vehicle resolution expected one row for 2024 MAZDA CX-90; found 0

The vehicle was in the data — it had resolved fine during retrieval evaluation. The two notebooks had each invented their own matching rules, and one held CX-90 where the Gold dimension held CX 90. A canonical alphanumeric key — CX-90, CX 90, and CX90 all collapse to CX90 — fixed it. The lesson wasn’t “strip punctuation.” It was that every path resolving the same business entity has to share one canonicalization contract, or they will disagree on a hyphen and hand you a confident zero.

Spark and the SQL endpoint don’t share a schema

One more disagreement, this time inside the platform. The results tables carried complex types — arrays of citations, structs of scored evidence — which Spark writes and reads without complaint. The Fabric SQL analytics endpoint, the thing a BI tool actually connects to, drops columns it can’t represent as scalars — no error, they’re just gone. So the arrays stay internal to the Spark layer, and everything meant to be read from SQL is flattened to scalars plus a JSON manifest string. What’s convenient to compute in and what’s queryable from are two different schemas, and the serving layer has to speak the second one.

It’s a console, not a chatbot

The interface is a Fabric notebook: you set a question code and parameters, you don’t type prose at it. That refusal buys real properties. The executor is idempotent — run it with nothing pending and it returns a clean no-op, not a duplicated result, the same discipline as keeping the runner dumb. Because the three semantic questions are templates with fixed intent, each has one validated default query vector stored alongside it, so the console runs without a live embedder and the parameters — not the vector — choose which vehicle’s evidence comes back. And every response carries its evidence with provenance — source, identifier, section, citation label, lookup URL — under a standing disclaimer that consumer complaints are unverified reports, with official recall and investigation material labeled separately. A column, is_official_source, keeps that material distinct from complaints. The system won’t dress a complaint as a verdict, which is the evidence-vs-authority line enforced in schema, not tone.

A smoke test runs all eight questions across the four routes end to end — twenty quality checks, zero failures.

Where it lands

Sixty-seven seconds for the largest retrieval is notebook latency, not application latency; a real external app would need managed vector search, and that’s a different project. (I did later put a thin front-end on it — the same engine with a face, not the production rebuild.) This one was narrower and more useful: a deterministic question gets a deterministic answer, a language question gets the passages that actually match, every result names a source you can go read, and a complaint is never promoted to a defect. The retrieval is the part everyone demos. The parts that decided whether I’d trust it — the grain, the canonicalization contract, the line between a report and a finding — are the ones no demo shows.