Text-to-SQL AI Agents: A Practical Guide to Asking Your Data Questions

"What was revenue by region last quarter, excluding refunds?" is a question a finance lead can ask in five seconds and an analyst can answer in fifteen minutes. Text-to-SQL agents promise to close that gap. In demos they look magical. On a real warehouse with 400 tables, three definitions of "revenue" and a column called amt_2, they quietly return wrong numbers with total confidence.

This guide explains how text-to-SQL actually works inside an AI agent, where it fails, and the specific design choices that make it reliable: narrow schema context, a semantic layer, read-only execution, a self-correction loop, and always showing the SQL. Everything here applies whether you use a commercial BI tool, a framework like LangChain or LlamaIndex, or the self-hosted AgentSwarms platform.

What a text-to-SQL agent actually does

A plain text-to-SQL prompt is one model call: question in, SQL out. A text-to-SQL agent runs a loop with tools. A typical loop looks like this:

1. 1Understand the question. Identify the metric, filters, time range and grouping.
2. 2Find relevant tables. Search table and column descriptions instead of dumping the whole schema into the prompt.
3. 3Draft SQL against only those tables, using approved metric definitions.
4. 4Validate before running. Parse the query, confirm it is read-only and that every referenced column exists.
5. 5Execute with limits. Row caps, timeouts and the caller's own permissions.
6. 6Check the result. Empty set? Implausible totals? Error message? Revise and retry a bounded number of times.
7. 7Answer with evidence. A short explanation, the numbers, a chart if useful, and the exact SQL that produced them.

Why text-to-SQL demos break on real data

Academic benchmarks such as Spider and BIRD measure how often models produce correct SQL across many databases. BIRD was designed to be closer to messy real-world data, and scores on it are noticeably lower than on cleaner benchmarks. In practice, most failures come from the same few places:

• Ambiguous business terms. "Active customer", "revenue" and "churn" have company-specific definitions the model cannot guess.
• Too much schema. Pasting hundreds of tables into the prompt dilutes attention and raises cost. Retrieval over table descriptions works better.
• Cryptic names. cust_flg_3 means nothing without a description.
• Join traps. Many-to-many joins silently double-count. This is the most common source of confidently wrong totals.
• Dates and time zones. "Last quarter" depends on fiscal calendars; timestamps may be stored in UTC while users think locally.
• Dialect differences. Date functions and quoting rules differ between PostgreSQL, BigQuery, Snowflake and DuckDB.

Give the model a semantic layer, not just a schema

A semantic layer is a curated set of business definitions sitting between raw tables and the people (or agents) asking questions: metrics, dimensions, approved joins and plain-language descriptions. Tools like dbt's semantic layer, Cube and LookML popularised the idea for BI. For agents it is even more important, because it replaces guessing with lookup.

metrics:
  - name: net_revenue
    description: Gross order value minus refunds, in USD, by order date.
    sql: SUM(orders.amount_usd) - COALESCE(SUM(refunds.amount_usd), 0)
    joins: [orders.id = refunds.order_id]
dimensions:
  - name: region
    sql: customers.region
    description: Sales region assigned at signup (EMEA, AMER, APAC).
time:
  fiscal_year_start: "02-01"

Even a small file like this, covering your ten most-asked metrics, will usually improve accuracy more than switching to a bigger model. Start with the questions people actually ask, not with full coverage.

Make execution safe by construction

Prompt instructions like "never run DELETE" are not security. Safety has to live in the execution layer:

• Read-only credentials. The agent's database role should only have SELECT on the schemas it may query.
• Parse and allowlist. Reject anything that is not a single SELECT (or WITH ... SELECT) before it reaches the database.
• Run as the user. Row-level security or row filters should apply to the person asking, so the agent can never see more than they can.
• Limits. Statement timeouts, row caps and cost caps on warehouses that bill per query.
• Log everything. Question, generated SQL, rows returned, latency and who asked.

Add a self-correction loop (with a budget)

Letting the agent see database errors and retry is one of the cheapest accuracy wins. A missing column or type mismatch is usually fixed on the second attempt. Two rules keep this from spiralling: cap retries (two or three is plenty), and treat "query ran but returned zero rows" as a signal to re-check filters rather than as a final answer.

for (let attempt = 0; attempt < 3; attempt++) {
  const sql = await draftSql(question, context, lastError);
  const check = validateReadOnly(sql);        // parse + allowlist
  if (!check.ok) { lastError = check.reason; continue; }
  const result = await runAsUser(sql, { timeoutMs: 15_000, maxRows: 5_000 });
  if (result.error) { lastError = result.error; continue; }
  if (result.rows.length === 0) { lastError = "Zero rows: re-check filters and date range."; continue; }
  return explain(question, sql, result);       // always return the SQL too
}
return askUserToClarify(question, lastError);

How to evaluate a text-to-SQL agent

Build a small golden set of 30 to 100 real questions with known-correct answers, taken from past analyst tickets. Compare result sets, not SQL strings, because two different queries can be equally correct. Track three numbers over time: execution accuracy (correct result), validity (query ran at all) and clarification rate (how often the agent rightly asked a follow-up instead of guessing). Our guide to LLM-as-a-judge evaluation covers how to grade the written explanation around the numbers.

When text-to-SQL is the wrong tool

• Regulated reporting that must match an audited definition exactly: use fixed dashboards, and let the agent explain them.
• Write operations. Updating records should go through application APIs with approvals, not generated SQL.
• Very large, poorly documented warehouses. Document the top tables first; no model fixes missing context.

Try it hands-on

On agentswarms.fyi you can build an agent with the SQL tool in the playground and inspect each query in its trace. When you're ready to point an analyst at your own data, the self-hosted AgentSwarms platform combines ETL pipelines, a lakehouse, BI dashboards and the AI analyst behind one set of permissions.