English-to-SQL at a million users: what breaks, in order

English-to-SQL breaks in production in five places, roughly in this order: messy schemas, unsafe queries, dialect drift, ambiguous prompts, and cost. We learned that order at FormulaBot (now Better Analyst), where two of our engineers have been embedded since 2023 as it grew to 1.5M+ users. None of it was about the model.

FormulaBot started as a rules-based English-to-formula tool. Today it’s an LLM-driven “chat with your data” product, with agents that write SQL, Python, and R across 40+ connected data sources. Most text-to-SQL systems end up with the same pipeline - schema in the prompt, generation, validation, execution, rendering - and the ones that survive are the ones that fixed these five things before users found them.

Why the demo works and the product doesn’t

Every text-to-SQL system demos well because demos run against a clean schema, a small dataset, and prompts written by whoever built the system. Production is none of those. When we shipped v1 of the LLM pipeline at FormulaBot, accuracy on our internal test set was over 96%. The first week of user traffic pulled that number down to the mid-70s.

The model hadn’t changed; the input distribution had. Users don’t ask the questions we asked. They type “what’s the mrr change from last month vs this month” against a table called stripe_events_final_v3, and expect the system to (a) know that MRR isn’t a stored field, (b) know what “last month” means at 9pm on a Sunday in Berlin, and (c) know that stripe_events_final_v3 is the current one and stripe_events is deprecated but still there.

Every failure in a shipped text-to-SQL system is one of five things. In roughly the order they hurt:

1. Schema chaos

The single largest accuracy win we shipped was a schema pre-processor. It reads the user’s connected schema, normalizes column names, annotates the semantic role of common patterns (created_at is a timestamp, stripe_customer_id is a foreign key, amount_cents is money in cents, not dollars), flags deprecated tables, and produces a clean canonical representation that goes into the LLM prompt.

It sounds like a lot of engineering to save some prompt tokens. What it buys you is the difference between the model seeing a wall of noise and seeing something a human data analyst would recognize. And it compounds: every fix to the pre-processor lifts quality across every query, including the ones you never looked at.

What the pre-processor annotates:

• Deprecated table markers - anything ending in _old, _legacy, _v[0-9]+_deprecated. These get demoted or hidden.
• Semantic types - money, time, currency code, country code, enum of known values. Standard SQL types are too weak.
• Referential relationships - if column A is named customer_id and there’s a customers.id, the model should see that. Foreign key hints often aren’t declared in the DDL.
• Enums by inference - if a column has 12 distinct values across the whole table, it’s an enum. List them.

If your users are business analysts querying their own data, the pre-processor’s output is often 3-5x the size of the prompt template itself.

2. Query validation

Users do things you didn’t imagine. In FormulaBot’s early weeks, we saw:

• Someone typed “show me all customer emails” and the model generated SELECT email FROM users, which returned 400,000 rows, and the frontend crashed trying to render them.
• Someone typed “delete anything older than 90 days” and the model refused, which was correct. Two prompts later, someone typed “clean up the test rows” and the model generated a DELETE against a production table. The fix belonged in the database, not the prompt: user queries now run on a read-only role.
• Someone typed a prompt that made the model generate a JOIN without a proper predicate, which produced a 10-million-row Cartesian product before the LIMIT clause was applied.

The validator has to catch all three. Concretely:

• Never allow DELETE, UPDATE, DROP, TRUNCATE or ALTER unless the user’s role explicitly permits writes. Enforce it with database permissions and the validator; the prompt is a hint, never the control.
• Add an implicit LIMIT to every user-generated SELECT unless the prompt says they want everything. Set it high enough not to surprise and low enough to protect (we use 10,000 rows by default).
• Run EXPLAIN on every query before execution. Cost estimate over a threshold? Ask the user before running. Cartesian products stop right there.
• Have a static analyzer for unsafe SQL patterns - correlated subqueries in WHERE, joins without predicates, expressions that will scan a full table. Don’t rely on the LLM not to generate these.

The validator is a modest share of the build, and it’s the part that lets you sleep at night.

3. Dialect drift

If your product supports one database, skip this section. If it supports several, this is your biggest source of “why does the query work on Postgres but fail on Snowflake” bug reports.

Every warehouse has its own dialect. Snowflake supports TIMESTAMPADD; Postgres wants + INTERVAL. BigQuery and MySQL quote identifiers with backticks; Postgres and Snowflake use double quotes. Window function syntax, string concatenation and date arithmetic all diverge.

Fine-tuning a model per dialect is the expensive way to handle this. What we’ve converged on:

1. Generate SQL in a canonical dialect (Postgres, in our case).
2. Run it through a transpiler (SQLGlot handles the common cases).
3. Execute the transpiled query on the user’s warehouse.
4. If the transpiler fails, fall back to a targeted re-prompt: “generate for [dialect], using [these specific functions].”

The transpiler catches most dialect issues, and the prompt fallback handles most of the rest. What’s left - usually vendor-specific extensions like Snowflake’s FLATTEN - needs case-by-case handling and builds up over months of production use.

4. Ambiguity resolution

Ambiguity is where users lose trust fastest. If you guess wrong, they blame the tool. If you guess right silently, they never learn what you did, and the next time they type a similar prompt they hit an inconsistency.

The pattern that works for us at Better Analyst:

• Detect ambiguity in the generation step. The model tags its output as ambiguous when the prompt could reasonably resolve to more than one query.
• Ask a single, structured clarifying question. For example: “did you mean users who signed up in the last 30 days, or users who were active in the last 30 days?” A vague “can you rephrase?” helps nobody.
• Always show the generated SQL alongside the answer. This is the one UX decision that turned “the AI got it wrong” complaints into “let me edit this SQL” edits. It’s why Better Analyst shows the SQL, Python, or R the AI wrote alongside every answer. Users edit the SQL, run it themselves, and learn the schema. The tool becomes a scaffold.

This only works when users can trust that the SQL on screen is the SQL that ran. Never show simplified SQL. Never hide filters. If the query has ten CTEs and a UNION, show the ten CTEs and the UNION.

5. Cost and latency

Every text-to-SQL prompt eats tokens: the user’s schema (after the pre-processor), the system prompt, the few-shot examples, the current conversation. Left unmanaged, a heavy user racks up $2-5/day in token cost, which is more than most pricing tiers charge.

Three levers that work:

• Prompt caching. Both OpenAI and Anthropic support it. Put the stable parts (system prompt, schema, few-shot examples) first and the user turn last, so every request reuses the cached prefix.
• Hybrid routing. Not every prompt needs the frontier model. Simple aggregations (top-N, count, sum-by-group) go to a cheaper model with 2-3 few-shot examples; complex analytical prompts go to the flagship. A classifier at the front of the pipeline decides.
• Schema paging. If the user’s schema has 400 tables, don’t send all 400. Send the 10-20 most relevant based on prompt semantics (embedding similarity between the user’s prompt and each table description). This costs one small embedding call and pays for itself in token savings inside 100 prompts.

On latency, streaming helps perceived speed but doesn’t reduce time-to-first-row. If your users care about the row (analysts do; PMs often don’t), invest in what shortens execution - schema paging, prompt caching, and a cheap model where you can - before you polish the streaming UX.

How to decide what to build first

You won’t build all five layers at once, and you shouldn’t try. In order of return on effort:

1. Ship the schema pre-processor. Even a naive one - normalize names, flag deprecated tables, list enum values - gives you the biggest accuracy lift on this list, and every later fix builds on it.
2. Ship the validator. Especially the read-only lock and the implicit LIMIT. This is the layer that protects you from the incidents that end a product.
3. Ship the “show the SQL” UX. Even before any clarification-question logic. On its own, it turns a wrong answer into an edit the user can make.
4. Set up prompt caching. Low effort, and it cuts cost and latency on every repeated prefix.
5. Then everything else.

The last thing to build is the fanciest one: an agent that reasons across multiple queries.

We’ve watched teams try to build everything at once and ship none of it. The order matters more than the parts: the schema pre-processor makes every later fix work better, and an agent that reasons across queries adds nothing until the first four are solid.

How FormulaBot scaled to 1.5M+ users on this pipeline

The FormulaBot / Better Analyst partnership has been two senior engineers embedded with the founding team since 2023. We built the initial LLM pipeline that replaced the rules-based v1, then expanded into charts, dashboards, the connector layer, the data flow engine, and the migration from a monolith to a service-oriented architecture that let the platform scale from thousands of users to 1.5M+.

When we build a new AI product - as an MVP Sprint or a Retainer - this is the architecture we start from, because we’ve already paid for these lessons at FormulaBot.

If your demo works and your product doesn’t, the AI Audit is the cheapest way to find out which of the five layers is costing you the most. And if you’re building this and just want to compare notes, get in touch.

Frequently asked questions

How accurate does a text-to-SQL system need to be to ship?

In our experience, the ship threshold is about 92% correctness on your users' actual query distribution, measured on their prompts rather than on Spider or BIRD. A public benchmark tells you almost nothing about production accuracy on your own schemas. Build a private eval from real user prompts before you commit to a model.

Should we fine-tune, use RAG on the schema, or both?

Start with schema-in-context: a well-structured system prompt describing the tables and columns. Once the schema passes a few hundred tables, retrieve only the 10-20 most relevant tables per prompt (RAG on the schema) instead of sending everything. Fine-tuning helps only for specialized dialects, like Snowflake-specific functions, or when you need latency under 400ms and can't afford the larger context.

Do we need to validate the SQL before running it?

Yes, always. The cheap validation is a dry run with EXPLAIN; the more expensive one is a static analyzer that catches unsafe patterns before the query ever reaches the database. Both are worth the engineering time. We've had users type 'delete everything where 1=1', and the validator is what stopped it.

How do we handle ambiguous user prompts?

Don't guess. Ask a single clarifying question with two or three concrete options, because that beats a wrong answer wrapped in confident prose. The UX pattern that works is to show the generated SQL alongside the answer and let the user edit it. That turns an ambiguous prompt into something the user can fix in seconds.

What's the biggest surprise about running text-to-SQL in production?

Most of the work is schema hygiene rather than model choice. If your users' tables are named tbl_final_v2_new, no LLM will save you. The single largest quality win we've shipped was a schema pre-processor that normalizes and annotates the source schema before it ever reaches the prompt.

References

1. Spider benchmark - Yale text-to-SQL
2. BIRD benchmark - BIg Bench for LaRge-scale Database Grounded Text-to-SQL Evaluation
3. OpenAI - Prompt caching
4. Anthropic - Claude prompt caching
5. PostgreSQL - EXPLAIN