Text to SQL: Natural-Language Analytics That Won't Lie

Who this is for: founders, analysts, and product managers who keep hearing "just ask your data a question" and want to know what actually works in 2026, what still breaks, and how to ship a text-to-SQL feature without handing an LLM a loaded write connection to your production database.

Text to SQL sounds like it should be solved by now. It isn't, not fully, and the gap between demo and production is where most teams get burned. This guide covers what current models can reliably do, how to structure schema context so accuracy holds up on real databases, the guardrails that keep a natural-language query tool from becoming an incident, and which tools are worth paying for versus building yourself.

What text to SQL actually is (and isn't)

Text to SQL is the task of turning a question like "what were our top 5 products by revenue last quarter, excluding refunds" into a working SQL query against your actual schema. It is not a replacement for a BI team, and it is not a general database admin agent — those are different products wearing the same demo.

Three distinct use cases get lumped together and shouldn't be:

1. Self-serve analytics for non-technical staff — a support lead asking "how many tickets came in from enterprise accounts this week." Read-only, low stakes if wrong, high volume.
2. Analyst acceleration — a data analyst who already knows SQL using natural language to draft a query faster, then reviewing it before running it. Higher stakes, but a human is in the loop.
3. Autonomous reporting agents — a scheduled agent that generates and runs queries unattended to populate a dashboard or Slack digest. Highest stakes because there's no human checking the query before it hits the database.

Your guardrails need to match which of these three you're building. A tool aimed at case 1 needs much stricter query validation than a tool aimed at case 2.

How accurate is text to SQL right now

Public benchmarks like Spider 2.0 and BIRD put frontier models (Claude Opus 4.x, GPT-5-class models) in the 55-72% exact-match range on genuinely hard, enterprise-scale schemas with joins across 8+ tables. On simpler schemas — a handful of tables, clear naming, no ambiguous business logic — accuracy on execution match (does the query return the right result, even if written differently) climbs into the 85-95% range.

The catch: exact-match and execution-match numbers on academic benchmarks do not predict your accuracy, because your schema has your naming conventions, your NULL handling quirks, and your business logic buried in a status column with values like active, A, and 1. Real deployments I've seen land closer to 70-85% "correct on the first try" for well-scoped internal tools, and that's with heavy schema context work, not out of the box.

The honest framing: treat text to SQL as a fast first draft generator with a verification layer, not an oracle. The teams that get burned are the ones that show a raw LLM-generated number to an executive without any check.

Where it consistently fails:

• Ambiguous business terms. "Active users" could mean logged in this week, has a paid subscription, or hasn't churned — the model will guess, and it won't tell you it guessed.
• Multi-hop joins with non-obvious keys. If orders.customer_ref joins to customers.legacy_id instead of customers.id, no amount of prompting fixes this without you documenting it.
• Date and timezone math. "Last month" relative to what timezone, whether it's calendar month or rolling 30 days — models default silently and inconsistently.
• Aggregation double-counting. Joining a one-to-many relationship before aggregating (e.g., orders to order_items) inflates SUM() results. This is the single most common silent error I've seen in generated SQL.

Schema context: the difference between a toy and a real tool

Accuracy on your specific database is mostly a function of how much context the model has about your schema, not which model you pick. This is the highest-leverage work in the whole project.

What to feed the model

• Table and column descriptions in plain language, not just names. cust_stat tells the model nothing; a comment saying "customer lifecycle stage: lead, trial, active, churned" tells it everything.
• Foreign key relationships explicitly, even ones not enforced at the database level. Most production databases have "soft" foreign keys that were never given real constraints.
• Sample rows or value distributions for enum-like columns. Showing the model that status actually contains 'shipped', 'returned', 'cancelled' prevents it from inventing plausible-but-wrong values.
• A glossary of business terms mapped to SQL logic: "active customer = has an order in the last 90 days AND subscription_status != cancelled." Write this once, reuse it everywhere.
• Known gotchas as inline warnings: "orders table includes test accounts, filter WHERE is_test = false."

A minimal working example

Here's the shape of a schema-context block that measurably improves accuracy, paired with a query:

-- Schema context (fed to the model as system context, not user input)
-- Table: orders
--   id: primary key
--   customer_id: FK -> customers.id
--   status: enum ('pending','paid','refunded','cancelled')
--   created_at: UTC timestamp
--   NOTE: revenue reporting must EXCLUDE status = 'refunded' and 'cancelled'
-- Table: order_items
--   order_id: FK -> orders.id (one order has MANY order_items)
--   unit_price, quantity: use unit_price * quantity for line revenue
--   NOTE: join to orders BEFORE aggregating, or aggregate order_items
--         separately to avoid double-counting order-level fields

-- Generated query for: "top 5 products by revenue last quarter"
SELECT
  oi.product_id,
  SUM(oi.unit_price * oi.quantity) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status NOT IN ('refunded', 'cancelled')
  AND o.created_at >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
  AND o.created_at < DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY oi.product_id
ORDER BY revenue DESC
LIMIT 5;

This level of specificity in the context — the explicit double-counting warning, the explicit refund exclusion — is what closes most of the gap between benchmark accuracy and production accuracy. It's tedious to write once and pays off on every query afterward.

Keep the schema context small and current

Don't dump your entire information_schema at the model for every question. Retrieve only the tables relevant to the question (a lightweight embedding search over table/column descriptions works fine here — see vector databases compared if you're building this retrieval step yourself), and re-generate the context automatically whenever the schema changes. Stale schema docs are worse than none, because they produce confident, wrong queries.

Guardrails: the non-negotiables

This is where most homegrown text-to-SQL tools fail, not on the SQL generation itself.

Read-only, always, by default

The database credential the model's queries execute under should be a role with SELECT only, no exceptions, on a read replica if you have one. Not "the app will only send SELECT statements" — that's a prompt-level promise, not a security boundary. Enforce it at the database grant level.

-- Postgres example: dedicated read-only role for the text-to-SQL service
CREATE ROLE analytics_readonly WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE analytics TO analytics_readonly;
GRANT USAGE ON SCHEMA public TO analytics_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_readonly;

Static query validation before execution

Before running any generated query, parse it and reject anything that isn't a single SELECT statement. Reject ;-chained multi-statements, DDL keywords, and any reference to system tables outside your allowed schema. Libraries like sqlglot (Python) parse SQL into an AST cheaply enough to do this on every request without noticeable latency.

Row and cost limits

Cap LIMIT server-side regardless of what the generated query says, and set a query timeout (2-5 seconds is reasonable for interactive dashboards). An LLM asked a vague question will sometimes generate a full table scan with no filter — that's fine on a 10k-row table and an outage on a 200-million-row one.

Human confirmation for anything above read-only self-serve

If the answer to a question will drive a real decision (pricing, headcount, a board deck number), route it through a "show me the SQL before you run it" step. This single UX choice catches most of the ambiguous-business-term failures before they become bad decisions, and it doubles as a trust-building mechanism for skeptical analysts.

Warning: never let a text-to-SQL feature run on the same database connection used by your application's transactional writes, even with a read-only role. Isolate it to a replica. A single expensive analytical query generated from a vague prompt can degrade production latency for every other user.

Tool comparison

If you're already committed to a BI tool, use its native AI layer first and only build custom when it can't handle your schema's complexity. If you're building a customer-facing analytics feature inside your own product, the custom pipeline is usually worth it because you control the guardrails end to end — this is the same build-vs-buy tradeoff we cover more generally in RAG for SMBs and evaluating LLM apps.

Evaluation: don't ship without a test set

Before any text-to-SQL feature goes live, build a fixed test set of 40-80 real questions your users actually ask, each with a known-correct SQL query or expected result. Run this set against every model or prompt change and track execution-match accuracy over time. This is the same discipline as eval-driven AI development applied to a narrow domain — and it's non-negotiable here because SQL correctness is binary in a way that chatbot tone isn't.

A practical eval loop:

• Pull your 6 months of support tickets or Slack questions about "can you tell me X from the data" — that's your real question distribution, not a hypothetical one.
• For each, write the ground-truth SQL by hand once.
• Score new prompt/model versions by running the generated query and diffing the result set against ground truth, not by comparing SQL text (two different queries can be equally correct).
• Re-run the eval on every schema migration. A renamed column silently breaks queries that used to work.

Where text to SQL genuinely pays off today

• Internal support and ops teams answering repetitive "how many / how much / which customers" questions that used to sit in an analyst's queue. This is the highest-ROI use case because the volume is high and the stakes per query are low.
• Drafting for analysts, cutting the time to write a first-pass query on an unfamiliar table from 15 minutes to 30 seconds, with the analyst still reviewing before running.
• Exploratory data work where being roughly right fast beats being exactly right slow — early-stage product analytics, hypothesis testing.

Where it's not there yet: financial reporting that goes straight into a filing, anything where a wrong number causes real harm and there's no human review step, and schemas your team hasn't documented at all (garbage in, garbage out applies harder here than almost anywhere else in LLM engineering).

FAQ

Can text to SQL replace a data analyst?

No, not for anything beyond routine lookups. It replaces the "write a quick SELECT for me" requests that clog an analyst's queue, freeing them for modeling, causal analysis, and judgment calls the model can't make. Teams that try to remove analysts entirely end up with confidently wrong numbers in exec decks.

Is text to SQL safe to connect to a production database?

Only with a read-only database role enforced at the grant level, a query timeout, a row limit, and static validation that rejects anything other than a single SELECT statement. Never rely on the prompt or the model's own judgment as the safety boundary — treat it the same way you'd treat any untrusted input.

Which model is best for text to SQL in 2026?

Claude Opus 4.x and GPT-5-class models perform similarly on execution-match benchmarks for well-documented schemas; the bigger accuracy lever is schema context quality, not model choice. Pick based on your existing API relationship, latency needs, and cost, then invest the saved effort into schema documentation instead of model-hopping.

How do I stop the model from double-counting in joins?

Document one-to-many relationships explicitly in your schema context and give the model a worked example, like the order/order_items pattern above. Add this as a permanent rule in your system prompt rather than hoping the model infers cardinality from column names alone — it usually won't.

What's the difference between text to SQL and a general SQL-writing AI agent?

Text to SQL is scoped to answering analytics questions against a known, read-only schema. A general SQL agent might also run migrations, alter tables, or manage the database — a much larger blast radius that needs a completely different permissions model, closer to what we describe in Claude Code permissions and safety.

Do I need a semantic layer before building this?

Not strictly, but it helps a lot. A semantic layer (dbt metrics, LookML, or even a hand-written glossary) gives the model pre-resolved business logic instead of making it re-derive "active customer" from raw tables every time, and it's the single biggest accuracy lever after schema descriptions themselves.

Get help wiring this into your stack

If you're weighing whether to build a text-to-SQL feature in-house or bolt one onto an existing BI tool, we can scope it in a free 30-minute call — book through the contact form or message us directly on WhatsApp.

Sources

• Spider 2.0 benchmark
• BIRD-SQL benchmark
• Snowflake Cortex Analyst docs
• Databricks Genie docs
• Vanna AI documentation
• sqlglot library