Why Text To Sql Agents Fail

Text-to-SQL agents fail because they guess at columns, join the wrong tables, and never learn from their mistakes. The root cause is missing context: the agent sees a schema but not the canonical table, the business definitions, or the corrections humans already made. Fix context and accuracy jumps from around 40 percent to the high 80s.

If you have spent an afternoon debugging why your text-to-SQL agent returned the wrong revenue number, you already know the failure modes are repetitive. It picked a staging table instead of the mart. It joined orders to users on the wrong key. It summed gross instead of net. None of those are reasoning problems — they are context problems, and they can be solved with a better retrieval and memory layer. See AI for data infrastructure for the broader framing.

Failure Mode 1: Wrong Table Picked

The most common failure is selecting a table that looks right but is not the canonical source. Warehouses usually contain three or four tables that could plausibly answer a revenue question: a raw events table, a staging transform, a mart, and a dashboard extract. A naive agent ranks them by name similarity and picks the shortest. A production agent has to know which one humans actually trust.

The fix is a canonicality signal on every table — a scored flag that says this is the one finance uses, this is the one product uses, and this one is deprecated. That signal comes from query logs, dashboard references, and human labels. Without it, every question is a coin flip between four near-duplicates.

Failure Mode 2: Wrong Join Key

Even with the right tables, agents pick wrong join keys. Orders has user_id and customer_id; users has id and external_id; the correct join is orders.customer_id to users.external_id, but an LLM will happily try orders.user_id to users.id because the names match. The result runs without errors and returns totally wrong numbers.

Foreign key constraints help when they exist, but most warehouses do not declare them. Lineage and query logs are better: if nobody has ever joined those two columns in production, the agent should not invent the join. A production-grade agent consults the query history before proposing any new join.

Failure Mode 3: Ambiguous Business Definitions

Revenue, churn, active user, and MRR all have multiple defensible definitions inside one company. Finance counts revenue net of refunds on invoice date. Product counts it gross on booking date. Sales counts committed ARR including pending renewals. A text-to-SQL agent that does not know which definition to use will pick one, produce a number, and leave the user wondering why it does not match the dashboard.

The fix is a business glossary that maps terms to specific SQL templates, owned by a human, versioned, and passed to the agent as context. See business definitions for AI agents for the full pattern.

Failure Mode 4: No Memory of Past Corrections

Most text-to-SQL agents forget every correction the moment the session ends. A user tells the agent that the right table is fct_revenue, not stg_orders, and the next day the same agent makes the same mistake. The gap is a corrections log — a persistent store of what the agent got wrong and what humans taught it — fed back into retrieval so the same mistake never happens twice.

Failure Mode 5: Context Bloat

Stuffing the entire schema into the prompt does not work. Warehouses have thousands of tables and tens of thousands of columns. Past about 30 candidate tables, accuracy drops because the model cannot keep track. The fix is progressive disclosure: retrieve a tight shortlist first, let the agent ask for detail only on the tables it actually needs, and expand the window only when necessary.

• •Canonical table signal — scored per dataset, based on query logs and human labels
• •Validated join graph — only propose joins that already appear in production queries
• •Business glossary — terms mapped to owned SQL templates
• •Corrections log — every human fix feeds back into retrieval
• •Progressive disclosure — shortlist first, expand on demand
• •Result validation — run sanity checks before returning numbers

What a Reliable Text-to-SQL Agent Looks Like

A reliable agent does five things before generating SQL. It retrieves a canonical table shortlist. It checks the join graph for validated edges. It loads business definitions for any term in the question. It pulls relevant corrections from memory. And it runs the generated SQL through a validator that catches row-count anomalies and schema drift before showing a number to the user.

Accuracy improvements compound. A team that goes from 40 percent to 85 percent accuracy on text-to-SQL is suddenly able to put the agent in front of non-technical users without a human reviewer in the loop. That is the threshold where text-to-SQL stops being a demo and starts being infrastructure. To see how Data Workers wires canonical signals, join graphs, glossaries, and corrections into one retrieval layer, book a demo.

How Teams Measure the Improvement

The only way to know your text-to-SQL agent is improving is to benchmark it continuously. Build a set of 100 to 300 real questions from your users with verified correct answers, and run the benchmark on every context update. Accuracy over time is the single most important metric. Teams that track it systematically see steady improvement; teams that do not ship regressions without noticing and lose user trust within weeks.

A good benchmark includes questions at multiple difficulty levels: simple lookups, multi-table joins, aggregations with business definitions, and exploratory questions that require reasoning. Each level exercises a different layer of the context system, so regressions can be localized. When accuracy drops on simple lookups, the catalog is broken. When it drops on joins, the validated join graph is stale. When it drops on aggregations, the glossary is outdated.

Benchmarks should run automatically on every deploy and every context refresh, with alerts on regression. The alerting catches problems before users do, which is the difference between a resilient system and a fragile one. Data Workers ships a benchmark framework and a reliability dashboard by default so teams do not have to build the measurement infrastructure from scratch.

Rollout Playbook

Rolling out a reliable text-to-SQL agent starts with a one-week pilot on a single domain. Pick finance or product, connect the agent to the warehouse, enable the full context stack (canonicality, join graph, glossary, corrections), and run the benchmark daily. If accuracy hits 80 percent within the week, expand to the next domain. If it does not, debug the context stack before expanding.

The debug loop is mechanical. Classify every failure as retrieval, ranking, generation, or validation, fix the most common class, rerun the benchmark, repeat. Most teams converge within two weeks once they commit to the loop. The ones that flounder usually skip classification and guess at fixes, which wastes weeks.

Once two domains are live, the rollout scales naturally. New domains adopt the same pattern and the platform team supports them with templates and training. Expect three months from kickoff to full enterprise coverage in a mid-sized data organization, and five to six months in a large one with multiple warehouses.

Text-to-SQL agents do not fail because LLMs are weak. They fail because the context layer underneath them is missing. Fix the context, fix the corrections loop, and fix progressive disclosure, and the same base model that gave you 40 percent accuracy will give you production-grade answers.