How does text to SQL work for plain-English data queries?

01Why text to SQL breaks in established products

The demo is easy. Point a model at a database, ask a question, get a number. The trouble starts in a product that has years of tables, renamed columns, soft-deleted rows, tenant boundaries and metrics that mean something specific to your customers. This is part of our larger guide, How do you build a RAG chatbot on your knowledge base?.

A schema tells the model what columns exist. It does not tell the model what your customers mean by active account, net revenue retention or completed course. It does not say which of three date columns is the one finance uses, or that a status of 4 means archived. When the model guesses, it does not fail loudly. It returns a confident, well-formatted, wrong number.

Public benchmarks show how hard this is even in clean conditions. BIRD, a benchmark that tests text to SQL on large, real databases across dozens of professional domains, still shows a gap between the best published systems and human data engineers. Your product adds more: tenant isolation, permissions, odd naming from old releases and users who will forward the answer to their board.

So the question is not whether a model can write SQL. It is whether your product can control which SQL it writes, under whose permissions, and how it explains the result.

02What a useful answer contains

We design every answer as a small bundle, not a paragraph. Each part has a job, and each part prevents a specific failure.

The data support signal matters most. It is not the model rating its own confidence, which is unreliable. It is a plain fact computed in code: how many records matched, how many periods are covered, whether any source was missing. When the number is low, the answer says so.

03How we approach it: start narrow, then widen

We build this feature in steps, and each step has a check before the next one starts.

1. 01

   Pick the first audience.

   Start with internal teams or customer admins, not every end user. They know the data, they will tell you when an answer looks wrong, and a mistake costs less.

2. 02

   Define the metrics before the model sees anything.

   Write down the twenty or thirty questions people already ask your support or analytics team. For each, define the metric, the tables, the join path and the filters. Vendors now build this into their products: Snowflake's Cortex Analyst, for example, relies on a semantic view that defines logical tables, metrics with their aggregation logic and the relationships that set join paths.

3. 03

   Give the model training material per customer.

   Table definitions, documented question and SQL pairs, and notes on what columns mean. Different customers configure your product differently, so one global prompt is rarely enough.

4. 04

   Constrain execution.

   Use a read-only connection, the asking user's row-level permissions, row limits and timeouts. Validate each generated query before it runs, for example with the database's own EXPLAIN, so planning errors are caught without reading data.

5. 05

   Show the work.

   Every answer can expand to the SQL, the assumptions and the raw results table.

6. 06

   Compute the data support signal in code.

   Base it on row counts, period coverage and missing sources. Set thresholds with the people who know the data.

7. 07

   Let the model choose the form.

   Through tool calling, the model can decide whether a chart, a table or plain text fits the question, from a fixed set of components you control.

8. 08

   Collect and review feedback.

   Store flagged answers, review them weekly, and turn each confirmed failure into a new documented question and SQL pair or a metric fix.

04Designing the data support signal

Keep it simple enough that a user understands it in one glance. Three levels usually work: enough data, limited data and too little data to say. Each level should have a rule a product manager can read, such as fewer than a set number of matching records, or fewer than a set number of complete weeks in the period asked about.

Write the wording carefully. Too little data to say is better than low confidence, because it tells the user what is wrong and what to do: widen the date range, add a segment or ask a different question.

Do not hide the signal on good answers. If users only see it when something is wrong, they stop noticing it. Showing it every time also teaches them that the product is honest about its limits, which is what makes them trust the confident answers.

05Permissions, tenants and cost

In a multi-tenant product the generated query must never reach another customer's rows. Do not rely on the prompt for that. Enforce it in the database connection or in a query rewriting layer that adds the tenant and permission filters to every query, whatever the model wrote.

Role matters too. A customer admin may see every location; a manager may see only theirs. The query runs as the person asking, with the same rules your existing reports already apply.

Cost needs its own plan. Long schema prompts, retries and repair loops add up. Cache answers to repeated questions, keep the schema context to the tables a question needs, and measure cost per answered question rather than per call. We cover the wider cost picture in how much production AI costs in SaaS.

06What we see in production

For an early-stage business intelligence venture, we built an agentic data copilot over company databases and documents. It was first proven with a Fortune 500 company and then used at two companies before it became a multi-tenant product.

Two decisions carried it. First, a training layer: table definitions, question and SQL pairs, documentation and an automatic training pass over the tables a customer selects, so text to SQL improves for each customer instead of staying generic. Second, transparency: every answer shows the generated SQL, its assumptions and a results table. Tool calling lets the agent answer with a chart, a table or text. The model works where the data lives, inside the customer's environment, and access follows roles for super admins, organization admins and agents.

For a marketing analytics startup, the admin console includes an ask the data assistant that returns a summary, a confidence indicator based on data volume and an automatically chosen chart. We started it with the admin audience. The founder insisted that metrics be correct and defensible, so we iterated metric definitions with him screen by screen before writing code, and a feedback button stores flagged results for review.

For an enterprise internal-app builder still in development, we chose checks that can be verified. Agents grade every join against the data catalog and pause for human approval when a join is guessed. Every generated query is checked with EXPLAIN on Postgres, MySQL or Snowflake before any sample run, and errors feed a repair loop. An evaluation harness runs golden prompts through the real build agent and scores answer accuracy against reference SQL and whether personal data was used.

In a retail sales assistant, one answer had to combine documents, CSV files and live database rows. Returning comparisons as structured output, rendered as tables and cards, worked better for users than paragraphs of text.

Frequently asked questions