How to Build a Text-to-SQL Agent

Key takeaways

• 01

  NL2SQL quality is mostly schema packing and gold evals; a larger model will still join the wrong table if you dump 400 relations into context.

• 02

  The database role is the security boundary: read-only, column grants, timeout, row cap, and server-injected tenant predicates.

• 03

  Dry-run with EXPLAIN (or equivalent) catches full scans and parse errors; it does not catch wrong business grain — golden result hashes do.

• 04

  Hybrid retrieval over table/column descriptions (and synonym maps) is how the agent finds `arr` when the user says “revenue.”

• 05

  Ship behind shadow mode: generate and score SQL without writing results to the user until execution accuracy holds.

A text-to-SQL, or NL2SQL, agent turns a natural-language question into a database query, runs it, and returns results. It is not a general analyst: it should refuse questions the schema cannot support, and it should not UPDATE. Compared with a RAG chatbot, the failure mode is worse — a fluent wrong number looks like a metric, not like a bad paragraph.

Treat SQL generation as function calling: the model proposes, the executor authorizes and runs. If you paste the question into a notebook with admin credentials, you have a demo, not an agent.

Models write better SQL when they see the right 4 tables, not 400. Store descriptions and sample values; retrieve with hybrid search so “NPS” hits `nps_score` and “customer” does not hit every table with `customer_id`. Include join paths among the retrieved tables. If two tables share a name pattern, show the grain in the description (`orders` is one row per checkout, `order_items` is one row per SKU).

Refresh the catalog on schema change. Stale columns produce confident SQL against missing fields. For large warehouses, maintain a verified subset for the agent; ungoverned sandbox tables do not belong in retrieval until an owner writes a description.

Generate for the engine you run. Date functions, identifier quoting, JSON operators, and LIMIT/TOP differ enough to make SQLite few-shots harmful. Keep a dialect template and a lint that rejects vendor-wrong syntax before the round-trip.

Create a dedicated read-only role: SELECT on the allowed views, no INSERT/UPDATE/DELETE/DDL, statement timeout, and a row cap. Prefer exposing views that already encode grain and PII policy. EXPLAIN or dry-run before fetch; kill queries over a bytes or slot-ms budget. The model must not set the timeout to zero.

Query-time ACLs for SQL mean the executor attaches predicates from the authenticated user: tenant id, region, and column grants. Database RLS is the strong form; if you cannot use it, wrap SQL in a subquery that applies the filter and reject statements that reference disallowed catalogs. Treat “forget the filter” as a tested attack, same as prompt injection.

Do not return errors that reveal forbidden tables (“relation hr_salary does not exist” vs “permission denied”) in a way that maps the schema for an unauthorized user. Log those attempts.

Exact SQL match is brittle (aliases, CTE vs subquery). Execution accuracy compares result sets (sorted, typed) to a gold hash. Keep both: exact match for regressions on a pin, execution accuracy for semantic equivalence. Include questions that must refuse: missing grain, write requests, and ambiguous metrics without a definition.

A four-week path: week 1 catalog and gold set, week 2 packer plus read-only executor, week 3 dialect guards and shadow queries, week 4 CI gate and runbooks. The client owns the golden queries, views, and evals. No token markup; warehouse cost is usually the surprise bill — budget it next to the model.

• Pack: retrieve tables/columns/samples, do not dump the warehouse
• Generate: SELECT/CTE only, dialect templates, SQL as a tool
• Execute: read-only role, ACL inject, EXPLAIN, timeout, row cap
• Eval: result hashes, refuse cases, shadow mode, CI on the real engine

If the warehouse has no descriptions, no primary keys, and five competing “revenue” tables, you will generate plausible fiction. Fix the semantic layer first, or restrict the agent to a handful of certified views. If users need writes, that is a different product with change tickets — not this agent.

If questions are policy and runbook text, use document RAG instead. Text-to-SQL is for questions whose answer is a result set. Mixing both in one unconstrained agent is how SQL ends up in a wiki tool and wiki text ends up in a JOIN.

Step-by-step build

1. 1

   Catalog schema with business names

   Export tables, columns, types, primary/foreign keys, and owners. Add a short business description and 3–5 sample values per column people filter on. Record dialect (Snowflake, BigQuery, Postgres, T-SQL) and quoting rules. This catalog is the retrieval corpus, not a dump into every prompt.

2. 2

   Pack schema per question

   Embed table and column descriptions; retrieve the top tables with hybrid search (names and synonyms are BM25 work). Cap context: typically a handful of tables, their columns, join keys, and samples. If retrieval misses, ask a clarifying question instead of guessing a table.

3. 3

   Generate SQL as a tool, not as chat

   Expose `propose_sql` with a schema for the statement string and a rationale. Constrain to a single SELECT/CTE. Include dialect few-shots (date trunc, identifier quoting). The model never gets a connection string.

4. 4

   Validate, dry-run, and inject ACLs

   Parse the SQL. Reject anything that is not read-only. Inject tenant/user predicates from the session. Run EXPLAIN (or BigQuery dry run) with a bytes/cost ceiling. Cap rows and timeout. Log the final SQL before execution.

5. 5

   Execute and present with citations

   Run as the read-only role. Return a truncated result grid plus the SQL the user can inspect. Cite tables used. On error, feed the engine message back once; if it still fails, refuse. Do not auto-loop into a write.

6. 6

   Evaluate on golden queries and shadow

   Keep 50–200 questions with gold SQL and result hashes, including dialect traps and “should refuse” items. Score exact match, execution accuracy (result equivalence), and ACL-omission attempts. Shadow on live questions; promote when both exact-enough SQL and safe rejects hold.

Common pitfalls

The mistakes that show up in real deployments — each one costs a week if you learn it the hard way.

Frequently asked questions