What breaks when agents meet a real warehouse

Every text-to-SQL demo you have seen runs against a schema somebody designed that morning. Six tables, unambiguous names, foreign keys that mean exactly what they say. The agent writes a correct query, the room nods, and someone asks why the data team still exists.

Mature warehouses do not look like that. A schema that has been in production for a decade typically contains several columns, across several schemas, that a reasonable person would call “customer”, and they do not agree on who counts as one. That is not a defect somebody failed to clean up. It is the accumulated residue of a business changing its mind about itself several times, which is what businesses do. The schema is a fossil record of old decisions, and the agent has no idea which layer it is standing on.

The benchmarks have caught up with this, and the numbers are stark. On Spider 1.0, the clean academic benchmark, GPT-4o scored 86.6%. On Spider 2.0, which uses real enterprise warehouse schemas, often over 1,000 columns, across BigQuery and Snowflake dialects, the same model scored 10.1%. o1-preview managed 17.1%. The task did not change. The schema got real.

That gap is the entire subject of this post.

The gap isn’t capability, it’s consequence

Here is the part that surprised me: the SQL generation was supposed to be the hard problem, and it largely stopped being one. Point a frontier model at a well-described schema with a clear question and it writes competent SQL: window functions, nested CTEs, the lot. Better than some humans, faster than all of them.

The problem is not that the agent writes bad SQL. It is that the agent writes plausible SQL against a schema it has misread, and the result looks exactly like a correct answer.

Consider the shape of a typical failure. Someone asks for revenue by region last quarter. There are three revenue columns in the warehouse: one gross, one net of refunds, one net of refunds and partner commission. The names distinguish them, but only if you already know the business well enough to decode names chosen in 2019. The agent picks one. It produces a number. The number is in the right ballpark, revenue figures being all roughly the same size, and it is wrong by the eleven percent that goes to partners.

Nobody catches this in the meeting. It is a plausible number, presented confidently, and everyone in the room has more to do than audit it. It gets caught three weeks later when someone reconciles against finance, and by then two decisions have been made on top of it.

The asymmetry here is the thing worth internalizing. A wrong query that errors costs nothing, you fix it and move on. A wrong query that returns costs a decision, and then costs the credibility of the system that produced it. And trust in a data system does not degrade gracefully. It is closer to a step function: people trust it, then one confidently wrong number in a high-visibility setting, then they check everything manually forever. Rebuilding that takes quarters, not weeks, and no amount of subsequent correctness accelerates it.

This is why the benchmark numbers matter more than they first appear. A 10% execution accuracy does not mean the agent is useless 90% of the time. It means that on a meaningful fraction of attempts it produces something that runs and is wrong, and in a production setting, that is the failure mode that carries all the cost.

What tends to hold

A semantic layer the agent must go through. This is the single highest-value guardrail, and it is not an AI intervention at all. It is the thing good data teams have always wanted and rarely had budget for. If “revenue” resolves to one governed definition rather than three ambiguous columns, the agent cannot pick the wrong one, because there is only one to pick. Agents did not create the case for a semantic layer. They made it urgent, and they made it fundable.

Deliberately restricted surface area. Point the agent at a curated set of well-named, well-documented marts rather than the whole warehouse. This feels like under-using the technology and it is exactly right. The raw and staging layers exist for engineers who know what the quirks mean. Every table you expose is a table the agent can misread.

Forced citation of source tables. Require every answer to state which tables and columns it used. Two benefits, and the second is the one that matters. The obvious one: answers become auditable. The subtler one: it changes the reviewer’s job from “does this number feel right”, which nobody can do reliably, to “is this the right table,” which a domain expert can answer in seconds. You are converting an intractable verification task into a tractable one.

Human review on anything leaving the team. Internal exploration, no review. Anything reaching a customer, an executive, or a decision, reviewed by someone who knows the domain. This is not a permanent state of affairs, but it is the correct state while trust is being established, and skipping it to demonstrate velocity is how you spend the trust you have not yet earned.

Evaluation against questions you already know the answer to. Build a fixed set of questions with verified answers and run it on every change to the schema, the prompts, or the model. This is the least exciting item on the list and the one that catches regressions nobody would otherwise notice until a user did.

What doesn’t

Documentation as a substitute for structure. The theory: write thorough column descriptions, feed them to the agent, and it will disambiguate correctly. In practice, documentation of a messy schema describes the mess accurately. Three revenue columns with three excellent descriptions still require the agent to infer which one the question meant, and that inference is precisely the thing it is bad at. Documentation helps a well-structured schema. It does not rescue a badly-structured one.

Confidence scores as a review trigger. Asking the model how sure it is, and routing low-confidence answers to a human, is an appealing design. The failure mode is that confidence correlates with fluency rather than correctness. The answers that most need review are the ones where the model has confidently misread the schema, exactly the case where it reports high confidence. Filtering on that signal systematically routes away the errors you most need to catch.

Giving the agent broad access and relying on prompts to constrain it. The instruction “only use the analytics schema” works until a question is easier to answer another way. Constraints belong in the permission model, not in the prompt. If the agent should not touch a table, revoke access to it, do not ask it not to.

Assuming users will verify. They will, for about a fortnight. Then the system will be right often enough that verification feels like theatre, and they will stop. Design for the world where nobody checks, because that is the world you will be operating in by month three.

Where I’ve landed, for now

The framing I have settled on: an agent is a very fast analyst with no institutional memory and no instinct for when a number smells wrong.

That instinct is most of what senior people on a data team are actually paid for. It is not the SQL. It is looking at a result and thinking that revenue figure is too round before anyone else has finished reading the slide. Automating query writing without automating the smell test does not remove the analyst, it changes what the analyst spends the day doing, and shifts the scarce skill from production to verification.

Which leads to a prediction I will put a name to, and which you are welcome to disagree with.

The organizations that get real value from agents against their warehouse will be the ones that already invested in semantic layers, test coverage and clean marts, and the gap between them and everyone else will widen rather than close. The common assumption is the opposite: that agents are a leveller, letting teams with messy data skip the unglamorous modelling work and query their way out of it. I think that is backwards. Agents are a multiplier on the quality of your semantic foundation, and a multiplier applied to a poor foundation produces confident nonsense at scale.

If that is right, the correct response to agentic analytics is not a pilot. It is finishing the data modelling work you have been deferring for three years, because that work is now the constraint on something the business actively wants.

The uncomfortable corollary: “our data is too messy for this” is not a reason to wait. It is the finding. It was always true, it was always costing you, and until now it was merely invisible.