GyaanStack. Blog

Articles / agentssqlgenai-system-design

Design a text-to-SQL analytics agent for business users

Design a text-to-SQL agent for a large warehouse: schema retrieval, semantic layer, verified queries, clarifying questions, permissions, cost math and evals.

By Raghunath · · 22 min read

A sales manager types "what were sales by region last quarter?" and wants a number she can paste into a board deck. Behind that sentence sits a warehouse with thousands of tables, three columns that could mean "sales", two definitions of "quarter", and a rule that she may only see her own region. Writing SQL is the easy part. Picking the right tables, using the company's definition of revenue, and returning a correct result rather than valid SQL are where these systems fail.

My earlier post on text-to-SQL with guardrails covers the safety check that sits between the model and the database: read-only connection, one SELECT, allowlisted tables, forced LIMIT, timeout. I won't repeat it here. This post is the system around that guard, with cost math, an evaluation method, and the failure modes that pass every syntax check.

Most write-ups stop at "retrieve the schema, prompt the model, run the SQL". They rarely mention that warehouse scan cost can be several times the model cost, or that the public benchmarks have high annotation error rates. Both change how you budget and evaluate.

Requirements and scale#

I'll design for a mid-size company. These are assumptions, so change them for your case.

  • Data. A warehouse catalog with about 20,000 tables, most of them staging, backups and one-off extracts. Around 300 are curated models that analysts trust. Read-only access.
  • Users. 200 business users, about 10 questions each per working day, so roughly 44,000 questions a month.
  • Answers. Plain English, the number or table, a chart when it helps, and the SQL, always visible.
  • Conversation. Follow-ups work, and the agent asks when a question has two reasonable readings.
  • Permissions. Users see only what their own warehouse role allows, including row-level rules.
  • Correctness first. A wrong number that looks right is worse than "I can't answer that". Every question, SQL query and result is logged for audit.
  • Cost limits. A cap on bytes scanned per query and on model spend per user per day.

The architecture#

 OFFLINE (nightly + on change)                ONLINE (per question)

 warehouse metadata ──► table catalog          user question + chat history
 (columns, comments,    (summaries, sample            │
  query logs, lineage)   values, join keys)           ▼
                              │               ┌──────────────────┐
 semantic layer ──────────────┤               │ router / clarify │──► clarifying question
 (metrics, dimensions,        │               └────────┬─────────┘
  join paths)                 │                        ▼
                              ├──────────────► context builder
 verified query library ──────┤               (metrics, tables, examples)
 (question, SQL, owner)       │                        ▼
                              │               ┌──────────────────┐
 run log + feedback ──────────┘               │ plan → SQL       │◄─┐ error or
 (what was asked, what ran,                   └────────┬─────────┘  │ failed check
  thumbs up/down)                                      ▼            │ (max 2 retries)
                                              parse + guard + dry run
                                                       ▼            │
                                              execute as the user ──┘
                                              (RLS, bytes cap, timeout)
                                                       ▼
                                              sanity checks → explain + chart
                                                       ▼
                                              stream answer + SQL, write run log

The offline side does most of the work for accuracy. The online side is a short loop with hard limits. The catalog finds tables, the semantic layer carries definitions, the verified queries carry patterns, and the run log records what happened so you can improve the other three.

Finding the right tables in a large warehouse#

You can't put 20,000 table definitions in a prompt. Even the 300 curated tables, at around 150 tokens each, come to about 45,000 tokens per call. Schema retrieval narrows it to the 5 to 10 tables a question needs.

Three things go into a good table record, and raw DDL has none of them.

  1. A short summary of what a row means ("one row per order line, after returns"), drafted by an LLM from the DDL and the most frequent queries on the table, then reviewed by an analyst.
  2. Sample values for low-cardinality columns. If status holds completed, cancelled and refunded, the model should see that, or it will write status = 'complete' and get zero rows. Store distinct values for columns with fewer than about 50 of them, and keep a separate value index for high-cardinality text such as product names so "Pilot pens" can be matched to brand = 'PILOT'.
  3. Join keys and grain, from declared foreign keys where they exist and from query logs where they don't.

Retrieval then runs in three steps. Retrieve 30 to 50 candidates with hybrid search (keywords plus embeddings) over the table records, rank them with a cross-encoder or a small LLM call, and prune to the final set plus the tables needed to join them. The CHESS paper describes the same idea with a separate schema selector agent and reports that pruning cut LLM tokens by about 5x while improving accuracy by about 2% on BIRD (CHESS, 2024).

The last step, join-path completion, is easy to miss. Bridge tables rarely share words with the question. "Sales by region" mentions neither customers nor geo_key, yet the only path from orders to regions goes through the customer table. This small scorer shows the effect: TF-IDF over table names, descriptions and columns, with a synonym map from the semantic layer, over 8 real tables and 300 noise tables.

# retrieve.py - score tables for a question, then add join neighbours.
# Standard library only. 8 real tables plus 300 noise tables.
import math, re
from collections import Counter

TABLES = {  # name: (description, columns, foreign keys)
    "fct_orders": ("one row per order, net revenue after discounts",
                   ["order_id", "customer_id", "order_date", "status", "net_revenue_inr"],
                   ["dim_customers"]),
    "fct_order_items": ("one row per order line",
                        ["order_id", "product_id", "qty", "unit_price_inr"],
                        ["fct_orders", "dim_products"]),
    "dim_customers": ("customer master, one row per customer",
                      ["customer_id", "geo_key", "segment", "signup_date"], ["dim_regions"]),
    "dim_regions": ("sales regions: north, south, east, west",
                    ["geo_key", "region_name"], []),
    "dim_products": ("product catalogue with category",
                     ["product_id", "category", "brand"], []),
    "fct_refunds": ("refunds issued against orders",
                    ["refund_id", "order_id", "amount_inr", "refund_date"], ["fct_orders"]),
    "fct_web_sessions": ("website sessions from analytics",
                         ["session_id", "customer_id", "started_at", "channel"], ["dim_customers"]),
    "dim_dates": ("calendar with fiscal quarter", ["date", "fiscal_quarter", "is_holiday"], []),
}
for i in range(300):  # the long tail every warehouse has
    TABLES[f"stg_marketing_{i}"] = (f"staging copy of campaign feed {i}",
                                    ["campaign_id", "spend", "load_ts"], [])

SYNONYMS = {"sales": "revenue", "revenue": "net_revenue_inr", "region": "region_name",
            "customers": "customer", "quarter": "fiscal_quarter", "returns": "refunds", "units": "qty"}

def tokens(text):
    out = []
    for t in re.findall(r"[a-z]+", text.lower()):
        out.append(t)
        if t in SYNONYMS:
            out += re.findall(r"[a-z]+", SYNONYMS[t])
    return out

docs = {n: tokens(n + " " + d + " " + " ".join(c)) for n, (d, c, _) in TABLES.items()}
df = Counter(t for toks in docs.values() for t in set(toks))
N = len(docs)

def score(question):
    q = tokens(question)
    s = {}
    for name, toks in docs.items():
        tf = Counter(toks)
        s[name] = sum(math.log(N / df[t]) * tf[t] / (tf[t] + 1) for t in set(q) if t in tf)
    return sorted(s, key=s.get, reverse=True)

def with_neighbours(top):
    picked = list(top)
    for name in top:
        for fk in TABLES[name][2]:
            if fk not in picked:
                picked.append(fk)
    return picked

GOLDEN = [  # question, tables the verified SQL uses
    ("sales by region last quarter", {"fct_orders", "dim_customers", "dim_regions", "dim_dates"}),
    ("top product categories by units sold", {"fct_order_items", "dim_products"}),
    ("refund amount as a share of revenue by customer segment",
     {"fct_refunds", "fct_orders", "dim_customers"}),
]

for k in (2, 4):
    for expand in (False, True):
        hits = total = 0
        for q, gold in GOLDEN:
            got = score(q)[:k]
            if expand:
                got = with_neighbours(got)
            hits += len(gold & set(got))
            total += len(gold)
        print(f"k={k} neighbours={expand!s:5}  table recall={hits}/{total} = {hits/total:.0%}")

q = "sales by region last quarter"
print("\nTop 4 for:", q)
for name in score(q)[:4]:
    print("  ", name)

Output from python3 retrieve.py:

k=2 neighbours=False  table recall=6/9 = 67%
k=2 neighbours=True   table recall=7/9 = 78%
k=4 neighbours=False  table recall=8/9 = 89%
k=4 neighbours=True   table recall=9/9 = 100%

Top 4 for: sales by region last quarter
   dim_regions
   dim_dates
   fct_orders
   fct_order_items

At k=4 the scorer misses dim_customers, the bridge, for the first question. Following foreign keys from the picked tables brings it back. Three questions prove nothing statistically, but the method carries over: measure table recall on your own golden set before tuning anything else. If the right table never reaches the prompt, no prompt change fixes the answer.

The semantic layer: definitions, not guesses#

"Revenue" in most companies means something specific: net of discounts, excluding cancelled orders, booked on order date, in INR. If the model works it out from column names each time, it will sometimes pick gross_amount or forget the status filter, and two users get two numbers for the same question. That is metric drift, and users stop trusting the tool the first time they catch it.

A semantic layer stores those definitions once: metrics (revenue, active customers, refund rate), dimensions (region, segment, fiscal quarter), and the allowed join paths between them. Examples are the dbt Semantic Layer (MetricFlow) and Snowflake's semantic model spec.

Then decide who writes the final SQL. This design has two paths.

  • Metric path. When the question maps to defined metrics and dimensions, the model outputs a small structured request, for example {"metric": "revenue", "group_by": ["region"], "time": "last_fiscal_quarter"}, and the semantic layer compiles it to SQL. The model can't get the revenue definition wrong because it never writes it.
  • Free SQL path. When the question falls outside the defined metrics ("which customers bought pens and ink in the same order?"), the model writes SQL against curated tables, with the relevant metric definitions in the prompt as reference.

The answer tells the user which path ran. A "certified metric" badge on metric-path answers is cheap and helps users judge a number.

Few-shot from a verified query library#

Analysts already have hundreds of queries they trust. Store each one with the question it answers, the SQL, who verified it and when. At question time, retrieve the 2 or 3 closest by question similarity and put them in the prompt as examples. Snowflake's Cortex Analyst does the same with its Verified Query Repository, which stores question, sql, verified_by and verified_at per entry.

Every entry has an owner and a date, and entries that reference a renamed or dropped column fail a nightly check and get pulled. When a question matches a verified one except for the date range, run the verified SQL with new parameters instead of asking the model to write it again. That is faster and correct by construction.

The run log feeds the library: thumbs-up answers become candidates, and an analyst promotes them. Don't auto-promote, or one bad answer a user liked becomes a template.

Ambiguity and clarifying questions#

"Last quarter" can be calendar or fiscal. "Customers" can mean accounts or people. "Top products" can be by revenue, units or margin. The agent can ask, assume and say so, or decline.

Situation Action Example
Two readings give very different numbers, and the user can answer in one tap Ask "Fiscal Q2 (Jul to Sep) or calendar Q3?" as two buttons
A company default exists in the semantic layer Assume, state it in the answer "Using net revenue, fiscal quarters"
Small difference, low stakes Assume, state it "Top products by revenue"
No table or metric covers it Say so "I don't have marketing spend data"
The question needs data the user isn't allowed to see Say so, without revealing the data "That's outside your access"

Asking too often is its own failure. Cap it at one clarifying question per turn, produced by the small router model together with the routing decision, so the check costs one fast call. Every assumption is printed above the answer, where a wrong one is visible in seconds.

Follow-ups ("now by segment") work best when the last resolved request sits in session state and the model edits it, rather than rereading chat text. The agent memory post covers that working memory.

The agent loop#

question + context
      │
      ▼
 1. plan      short list: metric/tables, joins, filters, grain of the result
      │
      ▼
 2. SQL       from the semantic layer (metric path) or the model (free path)
      │
      ▼
 3. check     parse with a SQL parser → guard from the guardrails post
      │       → dry run: valid? bytes to scan under the cap?
      │                                   │ no
      ▼                                   └──► back to 2 with the error (max 2 retries)
 4. execute   as the user's identity, with timeout and bytes cap
      │                                   │ runtime error
      ▼                                   └──► back to 2 with the error
 5. sanity    zero rows? NULL totals? row count at the LIMIT? total wildly off
      │       from the same metric in the verified library?
      ▼
 6. explain   plain-English answer, assumptions, chart, SQL

A plan before the SQL catches grain mistakes early ("result grain: one row per region" makes a join to line items look wrong), and it doubles as the explanation a business user can read.

Self-correction works for errors the database reports, such as an unknown column or a dialect function name: feed the exact error back with the failed SQL. It does little for logic errors, because a wrong join runs without complaint. Step 5 covers those with a few lines of code each: zero rows when the question implies some, a row count equal to the forced LIMIT (the answer must say "showing the first 1,000"), or a total more than, say, 20% off the metric path's number for the same period.

Cap the retries at two. If three attempts fail, the usual cause is wrong context (missing table, missing definition), and a fourth attempt burns money without changing that. Return "I couldn't answer this" with the last error, and log it as a candidate for the golden set.

Explaining results and choosing charts#

The explanation call gets the question, plan, SQL and a result summary (row count, column types, first 50 rows), never the full result, and writes two or three sentences with the headline number, assumptions and caveats.

Pick the chart with a rule on the result shape, not the model:

  • One row, one number: a big number, no chart.
  • A date column plus one measure: a line chart.
  • A category with fewer than about 12 values plus one measure: a sorted bar chart.
  • Anything else: a table, with an optional "chart this" button.

Stream the plan and SQL as they are produced. A user who sees "joining orders to customers to regions, fiscal Q2" after two seconds will wait for the number at second ten, and may spot a wrong assumption before the query finishes.

Permissions and row-level security#

The agent must run each query as the user, not as a shared service account with access to everything. Pass the user's identity through (OAuth to the warehouse, or a session variable that row policies read) and let the database enforce it. Postgres row-level security and Snowflake row access policies both filter rows per user inside the engine, where a prompt can't reach them. One Postgres detail: table owners bypass row security unless the table is set to FORCE ROW LEVEL SECURITY, so never let the agent connect as the owner.

Schema retrieval should also filter the catalog by what the user can read, so restricted table names and descriptions never appear in their prompt.

  • Cost estimates interact with row security. BigQuery dry runs return 0 bytes for tables with row-level security, to prevent side-channel attacks (BigQuery cost best practices), so also set maximum bytes billed on every query job.
  • Prompt injection can live in the data. A support ticket field can contain "ignore previous instructions and...". Result rows in the explanation prompt are untrusted input: mark them as data, give that call no tools, and never let result content choose the next SQL (OWASP on prompt injection). My guardrails and observability post has the broader set of controls.

Caching#

Three caches, each keyed so it can't leak data between users.

Cache Key Saves Invalidate when
Prompt prefix System prompt + semantic layer text About 95% of input cost on the cached part Semantic layer changes
Request to SQL Normalised structured request + user's role The model calls Semantic layer or library changes
SQL to result SQL text + role + RLS context + data freshness Warehouse scan The underlying tables load new data

Two users with the same SQL but different regions must not share a cached result, and keying on role alone fails if a row policy reads the region from a session variable.

Prompt caching only pays if the stable part (instructions, then the semantic layer) comes first and is byte-identical between calls. On Claude Sonnet 5.5, cache hits cost $0.10 per million tokens against $2 for normal input (Anthropic pricing, prompt caching docs).

Cost and latency per question#

These are estimates: the token counts are my assumptions for this design, so replace them with your own logs. Prices, checked on 21 Nov 2026: Claude Haiku 5.5 at $0.10 input and $0.50 output per million tokens (prompts under 100,000 tokens), Claude Sonnet 5.5 at $2 input, $10 output and $0.10 for cache hits, and BigQuery on-demand at $6.25 per TiB scanned (BigQuery pricing).

Step Model Tokens (assumed) Cost
Router and clarify check Haiku 5.5 1,500 in, 100 out 1,500 × $0.10/M + 100 × $0.50/M = $0.0002
Plan and SQL Sonnet 5.5 6,000 cached + 6,000 uncached in, 400 out $0.0006 + $0.0120 + $0.0040 = $0.0166
Retry (25% of questions) Sonnet 5.5 6,000 cached + 7,000 uncached in, 400 out 0.25 × $0.0186 = $0.0047
Explanation Sonnet 5.5 2,000 in, 300 out $0.0040 + $0.0030 = $0.0070
Model total about $0.028

The table assumes the cached prefix is a hit; a cache write costs $2.50 per million tokens, which at this traffic happens a few times an hour and barely moves the average.

Now the warehouse. A query that scans 40 GB of an unpartitioned fact table costs 40 / 1,024 TiB × $6.25 = $0.244. The same question with a date partition filter that scans 2 GB costs $0.012.

At 44,000 questions a month:

  • Model: 44,000 × $0.028 is about $1,250.
  • Warehouse at 40 GB a query: about $10,740.
  • Warehouse at 2 GB a query: about $540.

With these assumptions, the scan bill is about 8 times the model bill when queries hit unpartitioned tables. The model choice moves the total by hundreds of dollars a month; partitioning, a bytes-billed cap, the result cache and pre-aggregated tables move it by thousands. Note that a LIMIT clause doesn't reduce scan cost on non-clustered BigQuery tables, as the cost best-practices page states. Put partition columns and "prefer agg_daily_sales for daily totals" in the table records so retrieval and the model both see them.

Latency, also estimated: router 0.5 s, retrieval 0.3 s, plan and SQL 4 to 7 s, dry run 0.5 s, execution 1 to 10 s, explanation 2 to 3 s. That's 9 to 20 seconds end to end, hence the streaming. A verified-query hit skips SQL generation and saves the biggest model step. Measure per step from day one; my cost and latency tracker post shows one way to log it.

Evaluation#

Execution accuracy is the standard metric: run the predicted and gold SQL and compare result sets, not SQL text. Public benchmarks help you shortlist models but predict little about your warehouse.

  • Spider has 10,181 questions and 5,693 unique SQL queries over 200 databases (Yu et al., 2018). The databases are small and clean.
  • BIRD has 12,751 question-SQL pairs over 95 databases totalling 33.4 GB, with dirty values and questions that need outside knowledge. At release, ChatGPT scored 40.08% execution accuracy against 92.96% for humans (Li et al., 2023).
  • Spider 2.0 has 632 enterprise workflow problems on BigQuery, Snowflake and local databases, often with over 1,000 columns. An o1-preview-based agent solved 21.3%, against 91.2% on Spider 1.0 and 73.0% on BIRD (Lei et al., 2024).

The same agent drops from about 90% to about 20% once the schema looks like a real warehouse. The gold labels are also unreliable: an expert audit found annotation errors in 52.8% of BIRD Mini-Dev and 62.8% of Spider 2.0-Snow examples, and re-scoring 16 agents on a corrected BIRD subset moved their leaderboard positions by up to 9 places (Jin et al., 2026). Treat a one or two point leaderboard difference as noise.

Your own golden set decides whether you ship. Start with 100 to 200 real questions from the run log and analysts, each with verified SQL and an owner, plus clarifying-question cases (the expected output is a question), refusals, and questions users re-asked because the first answer was wrong.

Execution accuracy on one database has a known weakness: a wrong query can match the gold result by coincidence. If the test data has no cancelled orders, a query that forgets the status filter returns the right totals. Zhong et al. address this with test-suite accuracy, which runs both queries on several generated databases chosen to exercise the gold query (Zhong et al., 2020). You can get much of the benefit by hand: keep a second, perturbed copy of the test data with the rows that expose your common mistakes. The checker below compares result bags (order-insensitive, with duplicates counted) on both.

# exec_check.py - execution-accuracy checker for a golden set.
# Standard library only. Compares result sets, not SQL text, on two databases:
# the normal one and a "perturbed" one with rows that expose common mistakes.
import sqlite3
from collections import Counter

SCHEMA = """
CREATE TABLE customers(customer_id INT, region TEXT);
CREATE TABLE orders(order_id INT, customer_id INT, status TEXT, net_revenue REAL);
CREATE TABLE order_items(order_id INT, product TEXT, qty INT);
"""
BASE = """
INSERT INTO customers VALUES (1,'north'),(2,'south');
INSERT INTO orders VALUES (10,1,'completed',500),(11,2,'completed',300);
INSERT INTO order_items VALUES (10,'pen',5),(11,'ink',3);
"""
# Perturbations: a cancelled order, and an order with two line items.
PERTURB = """
INSERT INTO orders VALUES (12,1,'cancelled',900),(13,2,'completed',200);
INSERT INTO order_items VALUES (12,'pen',9),(13,'pen',1),(13,'ink',1);
"""

def make_db(*scripts):
    con = sqlite3.connect(":memory:")
    for s in (SCHEMA, *scripts):
        con.executescript(s)
    return con

def result(con, sql):
    rows = con.execute(sql).fetchall()
    # Bag of rows, order-insensitive, floats rounded so 800.0 == 800.
    return Counter(tuple(round(v, 2) if isinstance(v, float) else v for v in r) for r in rows)

GOLD = """SELECT c.region, SUM(o.net_revenue) FROM orders o
JOIN customers c USING(customer_id) WHERE o.status='completed' GROUP BY c.region"""

CANDIDATES = {
    "correct, different text": """SELECT region, SUM(net_revenue) AS rev FROM customers
        JOIN orders ON orders.customer_id = customers.customer_id
        WHERE status = 'completed' GROUP BY region ORDER BY rev DESC""",
    "forgot status filter": """SELECT c.region, SUM(o.net_revenue) FROM orders o
        JOIN customers c USING(customer_id) GROUP BY c.region""",
    "fan-out join": """SELECT c.region, SUM(o.net_revenue) FROM orders o
        JOIN customers c USING(customer_id) JOIN order_items i USING(order_id)
        WHERE o.status='completed' GROUP BY c.region""",
}

dbs = {"base": make_db(BASE), "perturbed": make_db(BASE, PERTURB)}
for name, sql in CANDIDATES.items():
    verdict = {db: result(con, sql) == result(con, GOLD) for db, con in dbs.items()}
    print(f"{name:26} base={verdict['base']!s:5} perturbed={verdict['perturbed']!s:5}")

Output from python3 exec_check.py:

correct, different text    base=True  perturbed=True 
forgot status filter       base=True  perturbed=False
fan-out join               base=True  perturbed=False

On the base data all three candidates pass. Only the perturbed data shows that two of them are wrong. The fan-out case is the one I'd test for first: joining orders to order lines repeats each order's revenue once per line, so order 13 with two lines counts as 400 instead of 200. The SQL is valid, it runs fast, and the number is plausible.

The candidates are hand-written so the example runs without an API key; in a real harness they come from your agent. Run the golden set on every change to prompts, models, the semantic layer or the catalog, and track execution accuracy on answered questions next to the share of questions answered at all. An agent that refuses half the time can show high accuracy on the other half. My post on evaluating an AI agent in Python covers the harness side.

Failure modes#

Failure Why it gets through Defence
Wrong join that runs fine (fan-out, missing bridge) Valid SQL, plausible number Grain in the plan, join paths from the semantic layer, perturbed test data
Metric drift (two definitions of revenue) Each answer looks reasonable alone Metric path through the semantic layer, "certified" badge
Silent truncation at the LIMIT The top rows look complete Row count equals LIMIT means the answer must say so
Wrong value spelling ('complete' vs 'completed') Zero rows, which can look like a real answer Sample values in table records, zero-row sanity check
Stale verified query after a schema change It was correct last month Nightly compile check, owner and date on each entry
Fiscal vs calendar periods Both readings are valid Default in the semantic layer, stated in the answer, or ask
Cached result shown to the wrong user Cache keyed on SQL only Key includes role and row-level context
Runaway scan cost Dry run reports 0 bytes on RLS tables Maximum bytes billed on every job, per-user daily cap
Instructions injected through data Result rows go into a prompt Treat rows as data, no tools in the explanation call

When you need which pieces#

Situation What to build
One database, under about 30 tables, analysts as users Full schema in the prompt, the guard, a 50-question golden set
Hundreds of tables, business users Table catalog with summaries and sample values, retrieve-rank-prune, verified query library
Users compare numbers across teams A semantic layer and the metric path, before anything else
Per-user data rules Identity passthrough and database row security, filtered catalog, cache keys with row context
Warehouse billed by scan Bytes cap, partition hints in table records, result cache, pre-aggregated tables
Mostly repeated questions with new dates Verified queries with parameters; the model mostly routes

If you build only one piece beyond the guard, build the golden set with a perturbed copy of the test data, because it tells you whether each other piece is worth its cost. For more designs in this format, see the agentic system design interview guide.

Sources#