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

Ask an LLM to write SQL against an enterprise warehouse and it will write SQL. That's the problem.

It will produce something syntactically perfect, semantically plausible, and quietly wrong - a query against a table that looks right but isn't certified, missing the entity filter that every analyst in the company knows to apply, using a calendar year when the business runs on a fiscal one. The result is a number. Nobody can tell it's the wrong number by looking at it.

We hit this building a company-wide AI data assistant with a client's data and automation engineering team. The requirement was simple to state and hard to satisfy: let anyone in the company ask a business question in plain English and get back a number they can act on. Not a summary. Not an approximation. A number with a real query behind it.

What follows is the architecture we landed on, why the obvious approach fails, and the benchmark that convinced us the extra complexity was worth it.

The problem with direct NL2SQL

The warehouse we were working against ships its own natural-language-to-SQL feature. We benchmarked against it, because if it had been good enough, the correct engineering decision would have been to use it and go home.

Here's the query that settled the question. A user asks for open projects associated with a particular company. The native engine returned 370 records. Our pipeline returned 21,314.

The native engine hadn't malfunctioned. It had picked a table whose name matched the question well, and dropped the company filter because it had no way to know that in this schema, company association lives in a joined dimension rather than a column on the fact table. It answered confidently. A user with no reason to doubt it would have taken 370 and made a decision.

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

Three things a model can't guess

When a question fails to become correct SQL, it's almost always one of these:

Table selection. 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 poor guide to which one the business considers authoritative.

Join and filter obligations. Some tables are only correct when joined to a specific dimension, or when a status filter is applied. This knowledge lives in analysts' heads and in a data catalog. It does not live in the schema.

Domain semantics. A fiscal year that starts in a month other than January will silently corrupt every period-over-period comparison a model writes. So will an entity hierarchy where "the company" means a rollup of subsidiaries rather than a single ID.

None of these are reasoning failures. They're knowledge failures. The model isn't thinking badly - it doesn't have the information, and it has no mechanism for noticing that it doesn't.

Retrieval-first: metadata before SQL

The fix is to stop treating query generation as one step. We split it in two, and made 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 a shortlist of candidate tables with their usage contract attached.

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

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

If you've built retrieval-augmented generation for documents, this is the same instinct pointed at schemas instead of prose. The difference is that a RAG answer degrades gracefully when retrieval is mediocre - you get a vaguer answer. A SQL answer doesn't degrade. It's right or it's 370.

A basic example

Here's the shape of it, reduced to something you can run. Two tools, exposed to the model, with the ordering enforced rather than suggested.

from typing import TypedDict

class TableSpec(TypedDict):
    name: str
    description: str
    certified: bool
    required_joins: list[str]
    required_filters: list[str]
    notes: str  # fiscal year, entity semantics, known gotchas

def search_metadata(question: str, limit: int = 5) -> list[TableSpec]:
    # Stage one. Resolve a question to certified tables and their usage contract.

    # This is a vector search over curated table documentation — not over the
    # information_schema. The whole point is the human-written context that
    # the schema does not contain.

    embedding = embed(question)
    hits = catalog_collection.query(query_embeddings=embedding, n_results=limit)
    return [h for h in hits if h["certified"]]

def run_query(sql: str, user_context: "UserContext") -> list[dict]:
    # Stage two. Execute, under the requesting user's own credentials.
    with warehouse_connection(user_context) as conn:
        return conn.execute(sql).fetchall()

The important part is not the code, it's the system prompt contract around it:

TOOL_POLICY = """You do not have schema knowledge. You must call search_metadata before
    run_query, in every case, with no exceptions.

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

    Never compute arithmetic yourself. Use the calculator tool.

    That last line is doing more work than it looks like."""

Take the arithmetic away from the model

A model asked for a growth rate will produce a growth rate. It will be approximately correct, which in a financial context is a category error.

We routed every calculation through a deterministic expression parser. The model decides what to compute and hands the arithmetic to something that cannot be creative about it:

from decimal import Decimal
import ast, operator

_OPS = {
    ast.Add: operator.add, ast.Sub: operator.sub,
    ast.Mult: operator.mul, ast.Div: operator.truediv,
    ast.Pow: operator.pow, ast.USub: operator.neg,
}

def calculate(expression: str) -> Decimal:
    # Evaluate arithmetic deterministically. No model in the loop.
    def _eval(node):
        if isinstance(node, ast.Constant):
            return Decimal(str(node.value))
        if isinstance(node, ast.BinOp):
            return _OPS[type(node.op)](_eval(node.left), _eval(node.right))
        if isinstance(node, ast.UnaryOp):
            return _OPS[type(node.op)](_eval(node.operand))
        raise ValueError(f"Unsupported expression: {ast.dump(node)}")

    return _eval(ast.parse(expression, mode="eval").body)

Thirty lines that remove an entire class of silent error. This is the cheapest reliability win available in an AI data product and it's routinely skipped.

Governance: identity, not instructions

Now the part that determines whether the thing can actually ship to a whole company.

The standard pattern is to connect through one privileged service account and filter results afterwards - usually by telling the model which rows the user is allowed to see. This is not access control. It's a request, addressed to a system that is probabilistic by construction. One prompt injection, one confused-deputy path, one unusual phrasing, and the filter is gone.

We inverted it. Every user gets their own warehouse service principal, with OAuth token refresh, and catalog grants derived from their assigned data domain:

def warehouse_connection(user_context: "UserContext"):
    # 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
    # rompt that can talk its way around it.

    principal = principal_store.get(user_context.user_id)
    token = principal.access_token()          # refreshed out of band
    return warehouse_client.connect(
        credentials=token,
        catalog=user_context.domain_catalog,  # grant-derived, not user-supplied
    )

The behavioural difference matters. Under the service-account pattern, a user without finance access asks a finance question and receives a filtered answer - which means the system is always one bug away from receiving an unfiltered one. Under this pattern, the query never runs. The failure is loud, and it happens in the engine rather than in the prompt.

That single decision is what made it possible to open the assistant to the entire organisation rather than a vetted pilot group. Governance wasn't a feature we added to the AI layer. It was the thing that let the AI layer exist at scale.

What the benchmark actually showed

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

Our pipeline won 13. The native engine won 1. The rest were comparable.

Two honest caveats, because a benchmark without them isn't worth much. It's an internal evaluation, not a third-party one, and it's LLM-judged with human spot-checking rather than fully hand-scored. And 21 questions is a sample that tells you a direction, not a precise margin.

But the direction was unambiguous, and the failure analysis was more useful than the score. Almost every native-engine loss traced back to the same root cause: a table chosen on name similarity, without the join and filter contract that makes that table correct. Which is exactly the gap retrieval-first closes.

What we'd tell you before you build this

The metadata layer is the product. We spent more effort curating table documentation than on prompt engineering, and it wasn't close. Retrieval-first only works if there's something worth retrieving. If your catalog is stale, fix that before you write a line of agent code - otherwise you've built a very sophisticated way to be confidently wrong.

Enforce tool ordering in code, not in prose. "Always call search_metadata first" in a system prompt is a suggestion. Under load, with a long conversation and 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.

Loud failures beat filtered ones. Every place we let the system return a degraded answer instead of an error, we later regretted it. Users cannot distinguish a degraded answer from a good one. They can absolutely distinguish an error message.

Benchmark against the incumbent, early. We could have spent months building before discovering the native feature was adequate. One afternoon of head-to-head testing told us it wasn't, and gave us a number we could point at every time someone reasonably asked why we were building this ourselves.

Conclusion

The instinct with a capable model is to give it the schema and let it work. It's the shortest path to a demo, and it's 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 query generation is slower to build. It requires curating metadata that 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 where a wrong answer is a bug you can find and fix, rather than an emergent property you can only apologise for.

For enterprise data, that trade is not close.