Design a Text-to-SQL System

How many tables should the model see?

Sending more tables gives you more chances, but each extra one costs tokens and gives the model another wrong option. Without documentation, even 20 tables miss the right one nearly half the time here. Better metadata beats a bigger K.

In one breath

Two paths: offline and at question time

Offline: build the index

1. Summarise tables: an LLM reads the schema, the docs and recent queries, and writes what the table holds and when to use it.
2. Summarise past queries: for each query, write what it was for and which tables it used. Better still, list the questions it could answer, and strip out dates and IDs so it generalises.
3. Prefer good tables: index trusted, documented tables first. The rest add look-alikes.
4. Keep it fresh: update the index on a schedule, so removed tables disappear.

At question time

1. Retrieve: embed the question, and search both indexes for the top N tables.
2. Rank: combine several matches per table, and weigh trust signals like tier, owner and freshness.
3. Reselect: an LLM reads the summaries and keeps the top K that fit in the context.
4. Confirm: show the tables to the user, who can change them before any SQL is written.
5. Generate: the prompt holds the question, the SQL dialect, schemas and similar past queries. For columns with few distinct values, include them, so the model writes 'WEB' and not 'web'.
6. Validate and run: see below.

Validate before running

• Check table and column names against the catalogue.
• Run EXPLAIN. If it fails, retry with the error message, up to a fixed limit.
• Use a read-only role, with the user's own permissions, a default row limit and a limit on cost.
• Show the SQL, the tables, the date range, the sources and warnings, like a suspicious join or an empty result.

Valid SQL can still be wrong. A query can run perfectly and count the wrong thing, so the user must be able to see what it did.

The numbers worth saying out loud

All of these are assumptions for the interview, not figures from a real system.

Traffic isn't the scaling problem. The size of the warehouse and the cost of one bad query are. A single unfiltered query on a huge table can cost more than a day of LLM calls.

What every LLM design gets asked

• Quality and evaluation: measure table retrieval and SQL separately. Public benchmarks use a few clean tables, which is much easier than a real warehouse, so build your test set from your own queries and experts. Compare results on the data as well as the SQL text, since two different queries can both be right.
• Reliability: if the LLM or the index is down, the user can still write SQL by hand, so fail visibly. Limit the fix-and-retry loop. Stop queries that run too long.
• Scalability: load is low, so scale the index. Update it in small daily batches, and filter by metadata like team or tier before vector search.
• Latency and cost: stream the SQL as it's written. Use a smaller schema, with only names and types, and drop columns tagged as unimportant. Cache table summaries, since they change slowly.
• Safety and security: run with the user's permissions, so the model can't reach anything they can't. Use a read-only role. Sample values and summaries can leak private data into prompts, so apply the same access rules to the index.

When sources disagree

Past queries can be old or plain wrong. Set an order of trust: expert docs for business rules, the catalogue for names and types, past queries checked against the schema, then general knowledge. Give more weight to queries that succeeded and are recent.

From a chatbot to an agent

The real system later became an agent. It classifies the task, searches tables, queries and docs in parallel, and fixes its own failed queries within a limit. Start with the simple pipeline, and move to an agent once retrieval and validation work.

What the real system reported

• The first version: users chose the tables themselves, which proved hard with hundreds of thousands of tables. So table retrieval came next.
• Documentation: the search hit rate was 40% without table documentation in the embeddings, and rose with its weight, up to 90%.
• Acceptance: generated SQL accepted on the first try rose from 20% to above 40%, as the system matured and users got used to it.
• Speed: task completion was 35% faster, on real data that doesn't control for differences between tasks.
• Later agent: a summary of the later post reports 2,500+ analytical users and a warehouse being cut from about 400,000 tables towards 100,000, and 40% of analysts using the agent within two months.
• Still hard: multi-step logic, undocumented business terms, queries across domains and recently removed tables still in the index.

Self-reported numbers, read through summaries. The later post's figures come from an AI-written summary, so check the original before quoting them.

"The SQL runs, so the answer is right"

It sounds right, since validation passed and numbers came back. But EXPLAIN only proves the query is valid. It can still use a look-alike table, the wrong definition of a metric or a join that duplicates rows, and return a confident wrong number. So show the SQL, tables and definitions used, and warn about risky joins.