When Your AI Gets the Number Wrong: Building Enterprise Data Assistants That Can Be Trusted

Someone asked our assistant how many open projects belonged to one of the company’s operating entities. It came back with 370.

The real answer was 21,314.

Nothing about the response looked wrong. The SQL was valid, the table name matched the question, the result was a plausible-looking count rendered in a clean table. A project director with no reason to doubt it would have taken 370 into a meeting.

What had happened is that the engine picked a table whose name was a good semantic match for the question, and quietly dropped the company filter - because in that schema, company association lives in a joined dimension rather than as a column on the fact table. There was no way for the model to know that from the schema alone, and no mechanism for it to notice it didn’t know.

That is the failure mode that matters. Not the query that errors - the query that succeeds and lies.

We hit this building a company-wide AI data assistant with the data and automation engineering team at a national construction firm. The requirement was easy to state: let anyone ask a business question in plain English and get back a number they can act on, with a real query behind it. Satisfying it took two years and a fair amount of architecture we would rather not have needed.

What a model cannot infer from a schema

When a question fails to become correct SQL, the cause is almost always one of three knowledge gaps, and none of them are reasoning problems.

Which table is authoritative. Real warehouses accumulate near-duplicates - a raw table, a cleaned one, a certified one, and two abandoned experiments with similar names. Semantic similarity between a question and a table name is a weak guide to which one the business trusts.

What the table obliges you to do. Some tables are only correct when joined to a specific dimension, or when a status filter is applied. That knowledge lives in analysts’ heads and in a data catalog. It is not expressible in DDL.

What the words mean here. A fiscal year starting in April will silently corrupt every period-over-period comparison a model writes against it. So will an entity hierarchy where “the company” means a rollup of subsidiaries rather than a single ID.

A model given only the schema will produce syntactically valid SQL that answers a slightly different question than the one asked, and will present it with the same confidence it brings to a correct answer.

Splitting query generation in two

The fix is to stop treating query generation as a single step, and to make the first step mandatory.

Stage one takes the natural-language question and searches a curated metadata layer - table descriptions, certification status, join specifications, required filters, fiscal-year semantics. It returns candidate tables with their usage contract attached.

Stage two composes SQL using only what stage one returned, and executes it.

The model still writes the query. But by the time it does, the table choice and the join obligations have arrived as retrieved facts rather than inferences, and the space of things it can get wrong has collapsed.

If you have built retrieval-augmented generation over documents, this is the same instinct pointed at schemas. One difference is worth internalising: a RAG answer degrades gracefully when retrieval is mediocre - you get a vaguer answer. A SQL answer has no graceful degradation. It is right, or it is 370.

Reduced to its shape, it is two tools with the ordering enforced:

type TableSpec = {
  name: string;
  description: string;
  certified: boolean;
  requiredJoins: string[];
  requiredFilters: string[];
  notes: string; // fiscal year, entity semantics, known gotchas
};

// Stage one. Resolve a question to certified tables and their usage contract.
// This is a vector search over curated table documentation, not over
// information_schema. The whole point is the human-written context that the
// schema does not contain.
async function searchMetadata(question: string, limit = 5): Promise<TableSpec[]> {
  const embedding = await embed(question);
  const hits = await catalog.query({ embedding, limit });
  return hits.filter((h) => h.certified);
}

// Stage two. Execute, under the requesting user's own credentials.
async function runQuery(sql: string, user: UserContext): Promise<Row[]> {
  const conn = await warehouseConnection(user);
  return conn.execute(sql);
}

The code is the easy part. The contract around it is what does the work:

You do not have schema knowledge. You must call searchMetadata before runQuery,
in every case, with no exceptions.

Compose SQL using ONLY tables returned by searchMetadata. Apply every
requiredJoin and requiredFilter from the returned specification. If
searchMetadata returns nothing suitable, say so and stop. Do not infer a
table name.

Never compute arithmetic yourself. Use the calculator tool.

That last instruction is doing more work than it appears to.

Taking arithmetic away from the model

Ask a model for a growth rate and it will give you a growth rate. It will be approximately correct, which in a financial context is useless.

We routed every calculation through a deterministic expression parser. The model decides what to compute; something incapable of improvisation does the computing.

import { Parser } from 'expr-eval';

const parser = new Parser();

export function calculate(expression: string): number {
  return parser.evaluate(expression);
}

A few lines, one dependency, and an entire category of plausible-looking wrong numbers disappears. It is the cheapest reliability win available in an AI data product and it is routinely skipped.

Making the warehouse enforce permissions

Retrieval-first is what made the answers correct. This next decision is what made it possible to ship the assistant to an entire company rather than a vetted pilot group.

The standard pattern is to connect through one privileged service account and filter afterwards — usually by telling the model which rows the user is allowed to see. That is a request addressed to a probabilistic system. One prompt injection, one confused-deputy path, one unusual phrasing, and the filter is gone. You are also permanently one bug away from an unfiltered answer, because the privileged connection can always see everything and the only thing standing between it and the user is instruction-following.

We inverted it. Every user is provisioned their own warehouse service principal, with OAuth token refresh, and catalog grants derived from their assigned data domain — project delivery, competitive intelligence, or finance. Custom agents are constrained to a single domain by construction.

// Connect as the user, not as the application. Authorization is resolved by
// the warehouse: if the user lacks the grant, the query fails at the engine.
// There is no filtered-answer path and no prompt that talks its way around it.
async function warehouseConnection(user: UserContext) {
  const principal = await principalStore.get(user.id);
  const token = await principal.accessToken(); // refreshed out of band

  return warehouseClient.connect({
    credentials: token,
    catalog: user.domainCatalog, // grant-derived, never user-supplied
  });
}

The behavioural difference shows up at the moment of failure. Under a service account, a user without finance access asks a finance question and receives a filtered answer that looks like a real one. Under per-user principals, the query does not execute and they get an error naming the missing grant.

Be clear-eyed about what this costs. You are no longer managing one credential — you are managing one per employee, which means provisioning on join, revocation on leave, and grant changes when someone moves teams. We ended up writing a reconciliation job that syncs grants for existing users against the current domain assignments, because the drift between “who should have access” and “who has a working principal” is real and silent. Identity provisioning became an ongoing operational surface rather than a one-time setup task.

It was still the right trade. Governance stopped being a feature layered onto the AI and became the thing that let the AI exist at organisational scale.

What the benchmark showed, including the part we lost

We ran 21 real business questions — the kind people were already filing BI tickets for — through both our pipeline and the warehouse’s native natural-language engine, and had the results judged on whether the returned data actually answered the question.

Ours won 13. The native engine won 1. Seven were comparable.

Two caveats, because a benchmark without them is marketing. This is an internal evaluation, LLM-judged with human spot-checking rather than fully hand-scored. And 21 questions gives you a direction, not a precise margin.

The failure analysis is the useful part, and it splits cleanly.

The seven comparable results were all single-table aggregations. Month-to-date earned revenue over a five-month window came back identical from both systems. So did a top-five ranking of late-stage pipeline opportunities, down to the same total number. When a question maps to one certified table with no join obligation, direct generation works fine, and the extra retrieval stage buys nothing.

The gap opened exactly where you would predict: multi-table joins, entity filters, and fiscal-year windows. The 370-versus-21,314 case was a missing join to a company dimension. A question about the largest monthly underbilling returned a different project entirely from each system, because the native engine applied no entity filter and surfaced a figure from outside the requested scope. A four-year comparison of projects above a cost threshold returned results from our pipeline and a tool error from the native engine.

The one we lost is worth stating plainly: on a question involving a month-boundary filter, the native engine chose a more appropriate date predicate than we did. It was a better answer and our metadata layer did not encode the convention it needed. That is a curation gap, not an architectural one, which is a useful thing to learn from a benchmark — it tells you where to spend the next week.

Three things worth knowing before you build this

The metadata layer is the product. We spent more effort curating table documentation than on prompt engineering, and it was not close. Retrieval-first only works if there is something worth retrieving. If your catalog is stale, fix that before writing a line of agent code, or you will have built a sophisticated way to be confidently wrong.

Enforce tool ordering in code. “Always call searchMetadata first” in a system prompt is a suggestion, and under load, in a long conversation, with a distracting question, it will occasionally be ignored. If correctness depends on the ordering, make the second tool refuse to run without evidence of the first.

Prefer loud failures. Every place we allowed a degraded answer instead of an error, we later regretted it. Users cannot reliably distinguish a degraded answer from a good one. They have no trouble at all distinguishing an error message.

Conclusion

The instinct with a capable model is to hand it the schema and let it work. It is the shortest path to a demo, and it is why so many AI-on-data projects look brilliant in a pilot and get quietly shelved after someone in finance checks a number.

Retrieval-first generation is slower to build. It means curating metadata nobody wanted to curate, splitting one clean tool into two awkward ones, and taking arithmetic away from a model perfectly capable of doing it. What you get back is a system whose wrong answers are bugs you can find and fix, rather than an emergent property you can only apologise for.

For enterprise data, that trade is not close.