Designing Text-to-SQL Analytics
A full walkthrough for letting business users ask questions of a 500-table data warehouse, covering schema retrieval, a semantic layer, verified examples, SQL validation and safe execution, row-level security, repair loops, and execution-based evaluation.
Lesson 8 of 10 13 min
1. Requirements
Functional
- Business users ask questions in plain English ("revenue by region last quarter vs the one before") and get a table, a chart and a short explanation
- Show the generated SQL for transparency, and allow follow-up questions
- Ask for clarification when a question is ambiguous
Non-functional
- 500 tables and thousands of columns in the warehouse
- 5K users, about 10 questions each per day
- Answers must respect row- and column-level security (sales reps see only their region)
- No expensive runaway queries, and no writes
2. Operating point
Correctness first. A confidently wrong number is worse than no answer. Latency of 5–15 s is acceptable, because the warehouse query often takes seconds anyway. LLM cost is small next to warehouse compute and analyst time.
3. Estimates
| Quantity | Value |
|---|---|
| Questions per day | 50K, peak ≈ 2 QPS |
| Tokens per generation | ≈ 6K in (schema snippets, metric definitions, examples), 300 out |
| LLM calls per question | ≈ 2–3 (clarify or route, generate, maybe repair, summarise) |
| LLM cost ($2 / $10) | 50K × 2.5 × ($0.012 + $0.003) ≈ $1.9K a day |
| Warehouse cost | Often larger. Enforce scan limits and cache frequent results |
4. Architecture
Question
Clarify / route
ambiguous? out of scope?
Context retrieval
tables, columns, metrics, examples
Generate SQL
structured output
Validate
parse, allow-list, cost
Execute as user
RLS, timeouts, limits
After execution: summarise the results in words, pick a chart type, and show the SQL. On a validation or execution error, attempt at most 2 repair loops, feeding the error back to the model.
5. Deep dives
Deep dive A: schema linking at scale
500 tables don't fit in a prompt, and dumping them all confuses the model. Build a catalogue index:
- Per table and column: name, type, human-written description, sample values, join keys, freshness, and popularity (query-log frequency).
- Embed the descriptions and combine vector search with keyword match on names and values ("EMEA" matches a
regioncolumn value). - Retrieve the top ~10 tables with their relevant columns and join paths, which is ≈ 2–4K tokens.
- Prefer curated, documented tables (gold marts) over raw ones. Hide deprecated tables.
Deep dive B: semantic layer and verified examples
- Metric definitions ("revenue = sum of net_amount where status = 'completed', excluding refunds") come from a semantic layer or metrics store. The model uses these definitions, or even generates semantic-layer queries instead of raw SQL. This removes the most common error: plausible SQL that computes the wrong definition.
- Verified examples: analysts approve question-to-SQL pairs. Retrieve the 3–5 most similar per question as few-shot examples. Every corrected answer becomes a new verified example, so the system improves with use.
Deep dive C: safe execution
| Check | How |
|---|---|
| Read-only | Parse the SQL AST, reject anything but SELECT (no DDL or DML, no multiple statements), and use a read-only connection role |
| Allow-list | Only catalogued tables and schemas |
| Limits | Inject a LIMIT on returned rows, and set a statement timeout |
| Cost guard | EXPLAIN or dry-run to estimate bytes scanned, and reject or ask for confirmation above a threshold |
| Permissions | Execute with the user's identity, so the warehouse's row- and column-level security applies. Never use a shared superuser |
| Injection | The user's question never becomes SQL by string concatenation. The model outputs SQL that is validated like untrusted code |
6. Evaluation
- A golden set of 300+ questions with reference SQL written by analysts, covering joins, time ranges, metrics and ambiguous cases.
- Execution accuracy: run the generated and reference SQL and compare result sets, ignoring column order and row order when unordered. Different SQL can be equally correct.
- Also track clarification appropriateness, validation failure rate, repair success rate, and cost per query.
- Online: thumbs, "edit SQL" rate, and analyst spot-checks of high-visibility answers.
7. Bottlenecks and follow-ups
| Likely question | Answer sketch |
|---|---|
| "The model picks the wrong revenue table" | Semantic layer, better descriptions, deprecate duplicates, add verified examples for that metric |
| "A query scans 50 TB" | Dry-run cost guard, partition filters required on large tables, confirmation step |
| "Sales reps see other regions' data?" | Execute as the user with warehouse RLS. Never rely on a WHERE clause the model adds |
| "Multi-step analysis?" | An agent loop with a SQL tool and a Python sandbox, with a step budget |
Key takeaways
- The hard part is choosing the right tables, columns and metric definitions, not writing SQL syntax. Retrieve schema context and use a semantic layer.
- Verified question-to-SQL examples, retrieved per question, are the strongest accuracy lever.
- Never execute generated SQL blindly. Parse, allow-list, force read-only, add limits, check cost, and run with the user's own permissions.
- Evaluate by executing generated and reference SQL and comparing result sets, not by comparing SQL text.
Go deeper
Finished reading? Mark it done to track your progress.