Designing Text-to-SQL Analytics

"Just ask the data" is one of the most requested enterprise AI features and one of the easiest to get subtly wrong. The model will happily write valid SQL that joins the wrong tables, counts cancelled orders as revenue, or uses calendar quarters when finance uses fiscal ones. The result is a confident, nicely charted, wrong number. The design principle: the LLM translates intent, but curated metadata defines meaning and the warehouse enforces access.

1. Clarify the task and the quality bar

Questions to ask:

• Which warehouse and how big? Assume Snowflake or BigQuery with ≈ 500 tables and thousands of columns, of which ≈ 80 are curated "gold" tables.
• Who asks? 5K business users (sales, marketing, operations, finance), not SQL experts.
• What do they get? A table, a chart and a short explanation, plus the SQL for transparency.
• Security model? Row- and column-level security already exists in the warehouse (sales reps see only their region; salary columns are masked).
• Is there a semantic layer? Assume a metrics layer (dbt Semantic Layer, Cube or similar) with definitions for key metrics. If not, building one is part of the project.
• What is "good"? The right number for the question the user meant, or a clarifying question when the meaning is ambiguous.

Functional requirements

• Plain-English questions ("revenue by region last quarter vs the one before") → table, chart, explanation, SQL
• Follow-up questions that refine the previous query ("only enterprise customers", "as a weekly trend")
• Clarifying questions when the question is ambiguous
• Save and share questions; analysts can correct and verify answers

Non-functional requirements

• 5K users, ≈ 10 questions each per day
• Answers respect row- and column-level security
• No writes and no runaway queries
• Latency of 5–15 s is acceptable, since warehouse queries often take seconds anyway

2. Choose the operating point

Correctness first. A confidently wrong number is worse than no answer, because it gets pasted into a board deck. Latency is secondary, and LLM cost is small next to warehouse compute and analyst time. So we spend tokens on better context, multiple candidates and verification.

3. Estimates

The LLM bill is not the problem. The real risks are wrong answers and expensive queries.

4. Architecture

Components

Request lifecycle

1. A sales manager asks "How did EMEA revenue compare with last quarter?"
2. The router checks scope (it's an analytics question) and ambiguity. "Last quarter" is fiscal by company convention, recorded in the glossary, so no clarification is needed.
3. Retrieval returns the revenue metric definition, the fct_orders and dim_region tables with join keys, the fiscal calendar table, and two verified examples about quarterly revenue.
4. The model generates SQL (or a semantic-layer query). The validator parses it, confirms it only touches allowed tables, injects a row limit and estimates the scan cost.
5. The query runs with the user's identity, so row-level security limits it to regions they may see.
6. The results go back to the model, which writes a two-sentence explanation using only values in the result set, picks a bar chart, and shows the SQL.

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 (how often it appears in the query log).
• Bootstrapping descriptions: an LLM drafts descriptions from column names, sample values and existing queries; data owners review them. Undocumented tables are the main source of errors.
• Mining the query log for real join paths and commonly used filters: how analysts actually query the warehouse is better documentation than the schema.
• Retrieval: embed descriptions and combine vector search with keyword matching on names and values ("EMEA" matches a region value). Retrieve the top ~10 tables with their relevant columns and join paths, ≈ 2–4K tokens.
• Prefer curated gold tables over raw ones, and hide deprecated or duplicate tables. Five tables called revenue_* is a data-governance problem the AI will expose.

Deep dive B: semantic layer, glossary and verified examples

• Metric definitions ("revenue = sum of net_amount where status = 'completed', excluding refunds") come from the semantic layer. The model uses these definitions, or better, generates semantic-layer queries instead of raw SQL, so the metric logic can't be re-invented wrongly. This removes the most common error: plausible SQL computing the wrong definition.
• Business glossary: "last quarter" means fiscal quarter, "active customer" means a purchase in the last 90 days, weeks start on Monday. These go into context when the question uses the terms.
• 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: a data flywheel.

Deep dive C: ambiguity and clarification

Many questions have several reasonable interpretations: "top customers" (by revenue? orders? this year?), "growth" (absolute or percentage? versus which period?). Options:

• Ask: "Top customers by revenue or by order count, and over which period?" with suggested choices.
• Assume and state it: "Showing top 10 by revenue for the current fiscal year. Change?"
• Refuse politely when the data isn't there ("we don't track website visits in this warehouse").

A small classifier or the router's structured output decides. Over-asking is annoying; under-asking produces wrong numbers. Tune the balance on the eval set, which should include ambiguous questions with "clarify" as the expected behaviour.

Deep dive D: generation, candidates and repair

• Structured output: the model returns {plan, sql, tables_used, assumptions}. The plan (a short description of the approach) improves accuracy and lets the UI show assumptions.
• Dialect awareness: the prompt names the SQL dialect and its date functions; many errors are dialect mismatches.
• Multiple candidates: for hard questions, generate 3 candidates, execute them cheaply (with tight limits) and compare results. If they agree, confidence is high; if not, ask the user or pick by a verifier model. This self-consistency check is one of the strongest accuracy gains for complex queries.
• Repair loop: on a validation or execution error, feed the error message back to the model, at most 2 times. Errors like "column not found" are usually fixed in one pass.
• Follow-ups modify the previous query ("only enterprise" adds a filter) rather than starting over, with the previous SQL in context.

Deep dive E: safe execution

Data values can also carry instructions (a customer named "Ignore previous instructions…"). Results passed back to the model for summarisation are treated as data, and the summariser has no tools.

See Sandboxing and permissions.

Deep dive F: trustworthy answers

• Numbers only from results. The explanation may only cite values present in the result set; a post-check compares every number in the text with the result table and flags mismatches. LLMs are poor at arithmetic, so derived numbers (percentage changes) are computed in SQL or code, not by the model.
• Show the work: the SQL, the metric definitions used and the assumptions, so analysts can audit.
• Chart selection: rules first (time series → line, categories → bar, one number → a big-number tile), with the model choosing only among valid options.
• Freshness: show when the underlying tables were last updated; "revenue today" from a table loaded nightly is misleading.

Deep dive G: multi-step analysis

Some questions need more than one query: "why did churn increase in March?" An agent loop with a SQL tool and a Python sandbox (for statistics and charts) can explore: break churn down by segment, compare periods, and summarise findings. It needs a step budget, cost limits per session, and a clear distinction in the UI between facts from queries and the model's interpretation. See The agent loop.

6. Evaluate, safeguard, operate

Evaluation

• A golden set of 300+ questions with reference SQL written by analysts, covering joins, time ranges, metrics, ambiguous cases (expected: clarify) and out-of-scope cases (expected: refuse).
• Execution accuracy: run the generated and reference SQL and compare result sets, ignoring column and row order when unordered. Different SQL can be equally correct, so comparing SQL text is misleading.
• Pin the data: run evals against a snapshot or a fixed time window, or results drift as data changes and tests become flaky.
• Re-run on schema changes: a renamed column or new table can silently break accuracy.
• Also track clarification appropriateness, validation failure rate, repair success rate, and cost per question.
• Public benchmarks (Spider, BIRD) are useful for choosing models but say little about your warehouse; your golden set is what matters.

See Offline evals and golden sets.

Online

• Thumbs, "edit SQL" rate, analyst corrections, repeat questions, and spot-checks of high-visibility answers (anything shared to a dashboard).

Observability

• Trace each question: retrieved tables and examples, candidates, validation results, the executed SQL, bytes scanned, latency and the final answer.

Failure modes

7. How the design evolves

• Pilot: 20 curated tables for one team, analyst-reviewed answers, and a 100-question golden set.
• Expansion: the catalogue index, semantic layer integration, verified-example flywheel, safe execution for all users.
• Advanced: multiple candidates with result voting, multi-step analysis with a sandbox, and scheduled insights ("alert me when EMEA revenue drops 10% week over week").

8. Bottlenecks and follow-ups