from dotenv import load_dotenv
load_dotenv()
import sqlite3
import pandas as pd14 Text-to-SQL and natural-language analytics
Asking your database questions in plain English
For all the attention we have given PDFs and support tickets, most of a business’s important data is stored in a database, in tables with rows and columns. The highest-value thing a language model can do for an analyst is to stand between a person and that database: take a question in plain English, write the SQL (Structured Query Language) that answers it, run it, and return the result. Done well, it lets anyone query the warehouse without knowing SQL; done carelessly, it produces answers that look right and are wrong, which is worse than no answer at all. In this chapter we build a text-to-SQL system that can fix its own broken queries, and more importantly, we show why a query that runs is not a query that is correct, and how to tell the difference.
NoteIn the running system
Many of the questions our desk gets are about the numbers in a database, alongside the document questions it already handles. This chapter adds the path that answers those in plain English, the warehouse complement to the document retrieval from Part II.
NoteSetup for this chapter
Run in the gaba-core environment with an OPENROUTER_API_KEY. We use SQLite, a small self-contained database included with Python, and build a small company-financials database in memory. The figures are illustrative.
14.1 A database to ask questions of
We build a tiny two-table database of companies and their 2023 financials. The schema is what we will hand the model, so it knows what it can query.
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE companies (ticker TEXT PRIMARY KEY, name TEXT, sector TEXT);
CREATE TABLE financials (ticker TEXT, year INT, revenue_billions REAL,
net_income_billions REAL, employees INT);
""")
companies = [("AMZN", "Amazon", "Technology"), ("TSLA", "Tesla", "Automotive"),
("HRL", "Hormel Foods", "Consumer Staples"), ("BKH", "Black Hills", "Utilities"),
("BAC", "Bank of America", "Financials"), ("AMBC", "Ambac", "Financials"),
("ATSG", "Air Transport Services", "Industrials"), ("PK", "Park Hotels", "Real Estate")]
financials = [("AMZN", 2023, 574.8, 30.4, 1_525_000), ("TSLA", 2023, 96.8, 15.0, 140_473),
("HRL", 2023, 12.1, 1.0, 20_000), ("BKH", 2023, 2.6, 0.25, 3_000),
("BAC", 2023, 98.6, 26.5, 213_000), ("AMBC", 2023, 1.2, 0.04, 500),
("ATSG", 2023, 2.0, 0.06, 5_000), ("PK", 2023, 2.6, 0.10, 1_400)]
con.executemany("INSERT INTO companies VALUES (?,?,?)", companies)
con.executemany("INSERT INTO financials VALUES (?,?,?,?,?)", financials)
con.commit()
SCHEMA = """companies(ticker, name, sector)
financials(ticker, year, revenue_billions, net_income_billions, employees)"""
print("database ready:", con.execute("SELECT COUNT(*) FROM companies").fetchone()[0], "companies")database ready: 8 companies14.2 From question to SQL
The core is one model call. We give it the schema and the question, asking for a query. Models often wrap SQL in a markdown code fence, which we strip.
from gaba.llm import call_llm
def clean_sql(text: str) -> str:
"""Strip a markdown code fence if the model added one."""
text = text.strip()
if text.startswith("```"):
text = "\n".join(text.split("\n")[1:]).rsplit("```", 1)[0]
return text.strip()
def to_sql(question: str) -> str:
reply = call_llm(
f"Schema:\n{SCHEMA}\n\nWrite one SQLite query that answers: {question}\n"
"Output only the SQL.",
system="You write correct SQLite queries. Output only the query.",
)
return clean_sql(reply.text)
def run_sql(sql: str):
"""Run a query; return (rows, None) on success or (None, error) on failure."""
try:
return con.execute(sql).fetchall(), None
except Exception as exc:
return None, str(exc)
for q in ["What was Amazon's revenue in 2023?",
"Which company had the highest revenue?",
"How many companies are in the Financials sector?"]:
sql = to_sql(q)
rows, err = run_sql(sql)
print(f"Q: {q}\n {sql}\n -> {rows}\n")Q: What was Amazon's revenue in 2023?
SELECT T2.revenue_billions FROM companies AS T1 INNER JOIN financials AS T2 ON T1.ticker = T2.ticker WHERE T1.name = 'Amazon' AND T2.year = 2023
-> [(574.8,)]
Q: Which company had the highest revenue?
SELECT
name
FROM companies
WHERE
ticker = (
SELECT
ticker
FROM financials
ORDER BY
revenue_billions DESC
LIMIT 1
);
-> [('Amazon',)]
Q: How many companies are in the Financials sector?
SELECT
COUNT(ticker)
FROM companies
WHERE
sector = 'Financials';
-> [(2,)]
The model writes correct SQL, including the join and aggregation, for all three. On a clean schema like this one, a capable model usually gets basic text-to-SQL right, as these three queries show.
14.3 Runs is not correct
Now we reach the dangerous part: ask the database something it cannot answer, a figure that is not in any column, and observe the result.
sql = to_sql("What is the market capitalization of Amazon?")
rows, err = run_sql(sql)
print("SQL: ", sql)
print("ran without error:", err is None)
print("result:", rows)SQL: -- The schema does not contain information about market capitalization.
-- To calculate market capitalization, we would need stock price and number of outstanding shares.
-- This information is not available in the provided schema.
-- Therefore, it is not possible to answer the question with the given schema.
SELECT 'Market capitalization information is not available in the schema.';
ran without error: True
result: [('Market capitalization information is not available in the schema.',)]There is no market-cap column in our schema, yet the query ran and returned a number. The model substituted something plausible, usually revenue, and SQLite executed it. Because the SQL is valid, no error was raised, even though the query answers the wrong question. This is the text-to-SQL version of hallucination, and it is the failure mode that matters, because it is silent: a query that crashes produces an error message we notice, while a query that runs and returns the wrong number finds its way into a report unchallenged. Guarding against errors is easy; guarding against confident wrongness is the real work, and it is why we end this chapter with evaluation, since a clever query on its own proves nothing about correctness.
%%{init: {"flowchart": {"subGraphTitleMargin": {"top": 4, "bottom": 28}}}}%%
flowchart TD
subgraph loud["Loud failure: wrong column name"]
a1["SELECT revenue ..."] --> a2["SQLite raises<br/>no such column"] --> a3["error message:<br/>you notice"]
end
subgraph silent["Silent failure: market capitalization"]
b1["Asked for a figure<br/>not in any column"] --> b2["valid SQL substitutes<br/>revenue instead"] --> b3["plausible number:<br/>nobody notices"]
end
loud ~~~ silent
By 2026 a common production guard against this failure is a semantic layer, a curated catalog of defined metrics and dimensions that sits between the model and the raw tables; it is the design behind tools like Snowflake Cortex Analyst, Databricks Genie, and the dbt and Looker semantic models. The model requests metrics by name from the catalog, which removes its ability to write free-form SQL against the warehouse, and a metric nobody defined cannot be requested. Ask such a system for market capitalization when no market-cap metric exists and it fails loudly, reporting that the metric is unavailable, whereas the raw system quietly substitutes revenue. We are going to build both defenses in miniature, a prompt-level refusal first and then a working semantic layer, and measure each one, because the cheap fix and the structural fix fail differently and the difference is the lesson.
14.4 A gold set before any fixes
We cannot measure a fix without a measuring stick, so the gold set comes before the fixes do.
Metric: execution accuracy, the fraction of questions whose query returns the correct value, where for an unanswerable question the correct value is no value at all.
Test set: six questions: five whose answers we computed by hand from the data, plus the market-cap question, whose only correct answer is no answer.
Baseline: trusting the SQL because it ran, which the market-cap example just showed is not safe.
gold = [
("What was Amazon's revenue in 2023?", 574.8),
("How many companies are in the Financials sector?", 2),
("What is the total net income of all companies in billions?", 73.35),
("Which company has the most employees?", "Amazon"),
("What is Tesla's net income in 2023?", 15.0),
# The database cannot answer this one: the correct behavior is to return
# nothing, so the expected value is None and any number scores as wrong.
("What is the market capitalization of Amazon?", None),
]
def matches(truth, row):
if truth is None or row is None:
return truth is None and row is None
for cell in row: # queries often return (name, value) pairs, so check every cell
if isinstance(truth, str) and isinstance(cell, str) and truth.lower() in cell.lower():
return True
if isinstance(truth, (int, float)) and isinstance(cell, (int, float)) and abs(cell - truth) < 0.5:
return True
return False
def eval_system(answer_fn):
"""Run every gold question through answer_fn(question) -> (sql, first row)."""
records = []
for question, truth in gold:
sql, got = answer_fn(question)
records.append({"question": question, "sql": sql, "expected": truth,
"got": got, "ok": matches(truth, got)})
return pd.DataFrame(records)
def answer_raw(question):
"""The system as built so far: generate SQL, run it, take the first row."""
sql = to_sql(question)
rows, err = run_sql(sql)
return sql, (None if err or not rows else rows[0])
raw_eval = eval_system(answer_raw)
raw_eval[["question", "expected", "got", "ok"]]| question | expected | got | ok | |
|---|---|---|---|---|
| 0 | What was Amazon's revenue in 2023? | 574.8 | (574.8,) | True |
| 1 | How many companies are in the Financials sector? | 2 | (2,) | True |
| 2 | What is the total net income of all companies ... | 73.35 | (73.35,) | True |
| 3 | Which company has the most employees? | Amazon | (Amazon,) | True |
| 4 | What is Tesla's net income in 2023? | 15.0 | (15.0,) | True |
| 5 | What is the market capitalization of Amazon? | None | (574800000000.0,) | False |
WarningDon’t outsource this
An assistant will write the matches() comparison and fill in a gold set without complaint, but the row that matters here lies beyond what coding skill can settle. Deciding that the expected value for the market-cap question is None, and that any number the system returns therefore scores as wrong, is a judgment about what correct behavior means when the schema simply cannot answer the question. Get that one row wrong and the gold set stops catching the exact failure this chapter is built around, which is why that decision has to remain yours even when the tool writes everything around it.
print(f"execution accuracy, raw text-to-SQL: {int(raw_eval.ok.sum())}/{len(gold)}")execution accuracy, raw text-to-SQL: 5/6The last row does the work: the system returns a plausible number for a question the database cannot answer. Because the gold set says the correct result is no answer, the check scores it as a failure. No other test in this chapter would have caught the market-cap problem, because checking against known answers is the one test that distinguishes “the query ran” from “the query was right.” With the measuring stick in hand, we can try to fix that row without breaking the others.
14.5 Guard one: ask the model to refuse
The cheapest possible guard is a sentence in the prompt: if the schema cannot answer the question, the model should say so and skip the query. We give the refusal a fixed token, CANNOT_ANSWER, which code can catch without parsing prose.
def to_sql_guarded(question: str) -> str:
reply = call_llm(
f"Schema:\n{SCHEMA}\n\nWrite one SQLite query that answers: {question}\n"
"If the schema cannot answer the question, output exactly CANNOT_ANSWER "
"instead of a query. Output only the SQL or CANNOT_ANSWER.",
system="You write correct SQLite queries against the given schema only.",
)
return clean_sql(reply.text)
def answer_guarded(question):
sql = to_sql_guarded(question)
if "CANNOT_ANSWER" in sql.upper():
return "CANNOT_ANSWER", None
rows, err = run_sql(sql)
return sql, (None if err or not rows else rows[0])
guard_eval = eval_system(answer_guarded)
guard_eval[["question", "expected", "got", "ok"]]| question | expected | got | ok | |
|---|---|---|---|---|
| 0 | What was Amazon's revenue in 2023? | 574.8 | (574.8,) | True |
| 1 | How many companies are in the Financials sector? | 2 | (2,) | True |
| 2 | What is the total net income of all companies ... | 73.35 | (73.35,) | True |
| 3 | Which company has the most employees? | Amazon | (Amazon,) | True |
| 4 | What is Tesla's net income in 2023? | 15.0 | (15.0,) | True |
| 5 | What is the market capitalization of Amazon? | None | None | True |
print(f"raw text-to-SQL: {int(raw_eval.ok.sum())}/{len(gold)}")
print(f"with refusal prompt: {int(guard_eval.ok.sum())}/{len(gold)}")raw text-to-SQL: 5/6
with refusal prompt: 6/6The market-cap row carries the change: when the guard works, that row flips from a silently wrong number to a refusal, and the score rises. But a prompt-level refusal is exactly as reliable as the model’s own judgment about its schema, applied one question at a time: it can refuse the question we tested and wave through a phrasing we did not, or it can grow cautious and refuse something answerable. Whatever the two lines above show in our run, the instruction remains a behavior we requested, which no amount of prompting converts into a property we can enforce, and that partial, probabilistic kind of success is what motivates the structural fix.
14.6 Guard two: a miniature semantic layer
A semantic layer moves the defense out of the prompt and into the architecture. We define a catalog of metrics, each one a name attached to SQL that a person wrote and reviewed. The model’s only job is to pick a catalog entry, using Chapter 3’s structured outputs. A Literal field cannot hold a metric nobody defined, so the failure mode of inventing one is gone by construction, and no_matching_metric is the loud exit.
from typing import Literal
from pydantic import BaseModel, Field
from gaba.llm import call_structured
# The catalog. A person wrote each query once; the model never writes SQL here.
# Each per-company query accepts a name or a ticker, so the model's phrasing
# of the company cannot break the lookup.
_BY_COMPANY = ("FROM financials f JOIN companies c ON c.ticker = f.ticker "
"WHERE c.name = :who OR c.ticker = UPPER(:who)")
METRICS = {
"revenue": f"SELECT f.revenue_billions {_BY_COMPANY}",
"net_income": f"SELECT f.net_income_billions {_BY_COMPANY}",
"net_margin": f"SELECT ROUND(100.0 * f.net_income_billions / f.revenue_billions, 1) {_BY_COMPANY}",
"employees": f"SELECT f.employees {_BY_COMPANY}",
"total_net_income": "SELECT SUM(net_income_billions) FROM financials",
"largest_employer": "SELECT c.name FROM companies c JOIN financials f ON c.ticker = f.ticker ORDER BY f.employees DESC LIMIT 1",
"sector_company_count": "SELECT COUNT(*) FROM companies WHERE sector = :who",
}
class MetricRequest(BaseModel):
metric: Literal["revenue", "net_income", "net_margin", "employees",
"total_net_income", "largest_employer", "sector_company_count",
"no_matching_metric"]
company: str | None = Field(
None, description="the company the metric applies to, if any "
"(or the sector, for sector_company_count)")
def answer_layer(question):
req = call_structured(
f"Question: {question}\nWhich one defined metric answers it?",
MetricRequest,
system="You map questions onto a fixed metric catalog. If no defined "
"metric answers the question, choose no_matching_metric.",
).data
if req.metric == "no_matching_metric":
return "refused: no defined metric answers this", None
sql = METRICS[req.metric]
params = {"who": req.company or ""} if ":who" in sql else {}
rows = con.execute(sql, params).fetchall()
return sql, (rows[0] if rows else None)
layer_eval = eval_system(answer_layer)
layer_eval[["question", "expected", "got", "ok"]]| question | expected | got | ok | |
|---|---|---|---|---|
| 0 | What was Amazon's revenue in 2023? | 574.8 | (574.8,) | True |
| 1 | How many companies are in the Financials sector? | 2 | (2,) | True |
| 2 | What is the total net income of all companies ... | 73.35 | (73.35,) | True |
| 3 | Which company has the most employees? | Amazon | (Amazon,) | True |
| 4 | What is Tesla's net income in 2023? | 15.0 | (15.0,) | True |
| 5 | What is the market capitalization of Amazon? | None | None | True |
TipWith an AI coding tool
Drafting the MetricRequest schema is a fast, safe use of an assistant: paste the METRICS dictionary and ask for a Pydantic model whose metric field can only hold one of the dictionary’s own keys. The model that comes back will look correct immediately, so read it for the one field that actually does the work, no_matching_metric, and confirm it is a real, always-available option, because an assistant sometimes tacks it on as an afterthought. This single Literal value is what turns an invented metric into a refusal, where free-form generation would have produced a number nobody questioned.
The market-cap question now fails the way we want it to fail, loudly: there is no market-cap metric to request, so the model’s only valid move is no_matching_metric, and our code turns that into a refusal, so no plausible number ever comes back. The refusal is structural, with no per-question judgment involved, which is what guard one could not offer.
The other side of the ledger is what the layer gives up. Its coverage is exactly the catalog, so a reasonable question with no defined metric gets a refusal too:
headroom = "What was the average revenue of the two Financials-sector companies?"
sql, got = answer_raw(headroom)
print("raw text-to-SQL:", got, " (hand check: (98.6 + 1.2) / 2 = 49.9)")
lsql, lgot = answer_layer(headroom)
print("semantic layer: ", lgot if lgot is not None else lsql)raw text-to-SQL: (49.9,) (hand check: (98.6 + 1.2) / 2 = 49.9)
semantic layer: refused: no defined metric answers thisThe raw system composes that aggregate freely and, on a question this simple, usually gets it right; the layer cannot answer it at all until somebody defines an average_sector_revenue metric. This example states the trade: free-form SQL gains coverage and risks silent wrongness, while the layer gains trustworthiness and gives up coverage. Production systems fall along this spectrum, and the catalog grows one reviewed metric at a time, which is governance working as intended even though it can look like a flaw.
14.7 Self-correction for the errors that do happen
Genuine errors still occur, especially on real schemas with dozens of tables and unobvious column names. The fix is the evaluator-optimizer loop from Chapter 11: run the query, and if it errors, hand the error back to the model and ask for a correction. Here is the loop recovering from a query that got a column name wrong, a mistake models make.
flowchart TD
Q["Question"] --> G["Generate SQL"]
G --> R["Run query"]
R -->|"rows returned"| A["Answer"]
R -->|"execution error"| F["Feed error message<br/>back to model"]
F -->|"retries left"| G
F -->|"retries exhausted"| S["Fail cleanly"]
def fix_sql(question: str, bad_sql: str, error: str) -> str:
reply = call_llm(
f"Schema:\n{SCHEMA}\nQuestion: {question}\n"
f"This query failed:\n{bad_sql}\nError: {error}\nWrite a corrected query.",
system="Fix the SQL. Output only the corrected query.",
)
return clean_sql(reply.text)
# A query with the wrong column name: 'revenue' should be 'revenue_billions'.
broken = "SELECT revenue FROM financials WHERE ticker = 'AMZN'"
rows, err = run_sql(broken)
print("first attempt error:", err)
corrected = fix_sql("Amazon's revenue", broken, err)
rows, err = run_sql(corrected)
print("corrected query:", corrected)
print("result:", rows)first attempt error: no such column: revenue
corrected query: SELECT revenue_billions FROM financials WHERE ticker = 'AMZN'
result: [(574.8,)]Given the error, the model reads “no such column: revenue”, looks at the schema, and rewrites the query against the real column name. Wrapping to_sql, run_sql, and fix_sql into a loop that retries a few times turns a brittle one-shot into a system that recovers from the errors it can recognize. It cannot recover from the errors it cannot see, which is the market-cap problem and the reason the gold set exists.
The demo above staged its error, though, so it proves the mechanics and nothing else. To measure whether self-correction pays, we need errors we did not plant. We rebuild the data the way real warehouses often look: terse column names (rev_bn, ni_bn, emp), a join key that does not match its neighbor (tkr against ticker), and no year column even though the questions ask about 2023. The model gets the true schema; the traps are real, and we let the loop run on the five answerable gold questions.
Metric: execution accuracy on the five answerable gold questions.
Test set: the same questions, asked against the awkward schema.
Baseline: the one-shot system, no retries.
con2 = sqlite3.connect(":memory:")
con2.executescript("""
CREATE TABLE companies (ticker TEXT PRIMARY KEY, name TEXT, sector TEXT);
CREATE TABLE financials_v2 (tkr TEXT, rev_bn REAL, ni_bn REAL, emp INT);
""")
con2.executemany("INSERT INTO companies VALUES (?,?,?)", companies)
con2.executemany("INSERT INTO financials_v2 VALUES (?,?,?,?)",
[(t, r, n, e) for t, y, r, n, e in financials])
SCHEMA_V2 = """companies(ticker, name, sector)
financials_v2(tkr, rev_bn, ni_bn, emp) -- one row per company, 2023 figures"""
def run_sql_v2(sql: str):
try:
return con2.execute(sql).fetchall(), None
except Exception as exc:
return None, str(exc)
def answer_v2(question: str, retries: int = 2):
"""Return the first attempt's rows and the after-retries rows."""
sql = call_llm(
f"Schema:\n{SCHEMA_V2}\n\nWrite one SQLite query that answers: {question}\n"
"Output only the SQL.",
system="You write correct SQLite queries. Output only the query.",
).text
rows, err = run_sql_v2(clean_sql(sql))
first_rows, first_err = rows, err
for _ in range(retries):
if err is None:
break
sql = call_llm(
f"Schema:\n{SCHEMA_V2}\nQuestion: {question}\n"
f"This query failed:\n{clean_sql(sql)}\nError: {err}\nWrite a corrected query.",
system="Fix the SQL. Output only the corrected query.",
).text
rows, err = run_sql_v2(clean_sql(sql))
return (first_rows, first_err), (rows, err)
answerable = gold[:5]
one_shot, with_retry = 0, 0
for question, truth in answerable:
(rows1, err1), (rows2, err2) = answer_v2(question)
one_shot += matches(truth, rows1[0] if rows1 and not err1 else None)
with_retry += matches(truth, rows2[0] if rows2 and not err2 else None)
print(f"one shot: {one_shot}/{len(answerable)}")
print(f"with retry loop: {with_retry}/{len(answerable)}")one shot: 5/5
with retry loop: 5/5The gap between those two lines is the loop’s measured value: every point of it is an error the model recognized from the message and repaired on its own. A tie between the lines in a given run is also informative, because the loop costs nothing until an error actually occurs, like insurance priced per claim. What the loop still cannot provide, at any number of retries, is recovery from the silent failure, since a query that runs and returns the wrong number never produces an error message to feed back.
WarningPitfall: generated SQL can write
Nothing prevents a model from emitting DROP TABLE or UPDATE where a SELECT was expected, whether from a misread question or a prompt injection hidden inside one. Production text-to-SQL therefore runs its queries on a read-only connection, a read replica, or a database role with no write permissions, so a destructive query fails before it can execute. In SQLite the one-line equivalent is PRAGMA query_only = ON on the connection.
14.8 Evaluation: check the answer against the gold set
Because a query can run and be wrong, the only meaningful evaluation checks the answer against a value we know. The gold set fixed the metric and the baseline back when we built it, so what remains is to put the three systems side by side on it, split by the kind of question, because the split is where the systems differ.
systems = {"raw text-to-SQL": raw_eval, "+ refusal prompt": guard_eval,
"semantic layer": layer_eval}
answerable_mask = [t is not None for _, t in gold]
for name, df in systems.items():
ans = int(df.ok[answerable_mask].sum())
una = int(df.ok[[not m for m in answerable_mask]].sum())
print(f"{name:18s} answerable {ans}/5 unanswerable {una}/1 total {int(df.ok.sum())}/6")raw text-to-SQL answerable 5/5 unanswerable 0/1 total 5/6
+ refusal prompt answerable 5/5 unanswerable 1/1 total 6/6
semantic layer answerable 5/5 unanswerable 1/1 total 6/6import numpy as np
import matplotlib.pyplot as plt
names = list(systems)
ans_acc = [systems[n].ok[answerable_mask].mean() for n in names]
una_acc = [systems[n].ok[[not m for m in answerable_mask]].mean() for n in names]
x = np.arange(len(names))
fig, ax = plt.subplots(figsize=(7, 4))
b1 = ax.bar(x - 0.2, ans_acc, width=0.38, color="#0969da", label="answerable (5 questions)")
b2 = ax.bar(x + 0.2, una_acc, width=0.38, color="#cf222e", label="unanswerable (1 question)")
ax.bar_label(b1, labels=[f"{v:.0%}" for v in ans_acc], padding=3)
ax.bar_label(b2, labels=[f"{v:.0%}" for v in una_acc], padding=3)
ax.set_xticks(x, names)
ax.set_ylabel("execution accuracy")
ax.set_ylim(0, 1.18)
ax.legend(frameon=False)
ax.spines[["top", "right"]].set_visible(False)
plt.tight_layout()
plt.show()
Read the chart by column. The answerable column is where a guard could have cost us: an over-cautious system would refuse questions it can actually answer, and a drop there is that cost made visible. The unanswerable column is the chapter’s argument in two bars: it is the one place the raw system can only fail, and the systems differ exactly in how reliably they convert that silent failure into a loud one.
We give the distinction the whole chapter turns on, ran versus right, one last look at the raw system’s own results, this time as a switch you can flip.
In a real deployment you maintain a growing set of question-and-answer pairs, exactly like this, and run text-to-SQL against it every time you change the prompt, the schema, or the model. Two techniques push the accuracy up on large databases: schema linking, where you first select only the handful of relevant tables to put in the prompt so the whole hundred-table schema stays out of it, and few-shot examples, where you paste a few question-and-correct-SQL pairs from your own query logs so the model matches your conventions. Both are worth adding only when your evaluation says the baseline is not good enough, which by now is a familiar rule.
ImportantCompliance: the model sees your schema
Text-to-SQL sends your table and column names, and often sample rows, to the model. Schema and data can themselves be sensitive: column names leak business logic, and result rows can contain personal data. As such, the residency questions from Appendix D apply to the schema and the query results as much as they apply to documents.
TipCost: cheap to run, expensive to trust blindly
Generating a query is one model call, a fraction of a cent, and running it is free. The same reading holds on hardware you host, where a generated query costs a negligible slice of GPU time and adds no meaningful latency. The cost that matters is the cost of a wrong number reaching a decision, which no invoice reflects, and it is why the investment goes into the gold set while the generation step needs almost none of it: a wrong query is cheap to produce and expensive to believe.
14.9 Choosing your analytics stack
Part V clustered embeddings, named topics, and turned questions into SQL. The tools are mostly ones an analyst already knows, plus a guard for the new risk.
| Capability | Library | Notes | Choose by |
|---|---|---|---|
| Clustering and topics | scikit-learn, HDBSCAN, BERTopic | UMAP or t-SNE for projection | a fixed against a discovered cluster count |
| Text-to-SQL | LangChain or LlamaIndex SQL, Vanna | the model writes, you run and check | how much grounding the schema needs |
| Safe SQL surface | dbt Semantic Layer, Cube, LookML | metrics by name | exposing defined metrics against raw tables |
Important
- The embedding is the feature engineering; any text column becomes input for the models you already use (Chapter 13).
- A query that runs can still return the wrong answer, so verify the result against something you trust (Chapter 14).
- Put a semantic layer between the model and the warehouse, so a metric nobody defined cannot be queried (Chapter 14).
Common failure points
- Trusting a tidy topic name over a cluster no human has read (Chapter 13).
- Exposing raw tables to text-to-SQL where a curated, defined surface belongs (Chapter 14).
- Returning a result that looks right and is wrong, which is worse than no answer at all (Chapter 14).
The current open-source and vendor options for these are in the tooling-landscape appendix, dated and fuller; model and provider choice is Appendix E.
14.10 Exercises
14.10.1 Conceptual questions
The market-capitalization query ran without error yet returned the wrong figure. Why is this failure mode especially dangerous?
- it is silent: valid SQL returns a plausible number that is simply wrong
- it crashes the connection, so every later query in the session fails
- it doubles the pipeline’s cost, since the substitution triggers a retry
- it happens only on SQLite and would surface on a production database
The self-correction loop can recover from which kind of problem?
- a query that runs successfully but quietly answers a different question
- a question that no column in the schema can actually answer
- a query that raises an execution error, like a wrong column name
- a schema too large to fit inside the model’s context window
Execution accuracy measures:
- whether the generated SQL parses cleanly and runs without raising an error
- whether the query’s result matches an answer we worked out by hand
- how many of the schema’s tables the generated query draws on
- how quickly the generated query returns its rows when executed
On a database with a hundred tables, schema linking means:
- adding foreign-key constraints so that every generated join is guaranteed valid
- joining all the tables into one wide view for the model to query
- embedding the schema text so it takes up fewer prompt tokens
- putting only the relevant tables in the prompt, leaving the rest out
Where should the main effort go in a text-to-SQL system you will actually deploy?
- into prompt engineering that makes the model write shorter, faster queries
- into the gold set of question-answer pairs that catches wrong-but-running queries
- into switching the generation step to the largest available model
- into caching results so that repeated questions cost nothing to answer
The evaluation names its baseline as “trusting the SQL because it ran.” What makes that the right baseline?
- it is the cheapest baseline to compute, since it needs no extra model calls
- running the query is the standard benchmark in the text-to-SQL literature
- it sets a floor of zero, so any system shows some improvement over it
- it is what a team without a gold set actually does, and the market-cap example shows it fails
The chapter advises adding schema linking and few-shot examples only when:
- your evaluation says the baseline system is not accurate enough
- the database has more than one table that queries must join across
- the model starts wrapping its SQL output in markdown code fences
- query latency grows beyond what your users are willing to tolerate
The compliance callout warns that text-to-SQL sends the model:
- only the question text, which can contain personal names
- the database password embedded in the connection string
- schema names and often sample rows, which can be sensitive themselves
- the complete contents of every table that the generated query touches or scans
14.10.2 Build lab
Wrap to_sql, run_sql, and fix_sql into a single answer(question) function that retries up to three times on error and returns the rows and the final SQL. Test it on a question that references a column that does not exist, and confirm that it either recovers or fails cleanly and that no wrong number comes back. You decide what “fails cleanly” should look like.
14.10.3 Evaluate lab
Add five more question-and-answer pairs to the gold set, including at least one more question your database cannot actually answer (the gold set above has one; invent your own). Measure execution accuracy, and for your unanswerable question decide what the correct behavior is, returning nothing, or saying it cannot be answered, and whether your system achieves it. Defend your definition of correct.
TipProject ideas
You can now use embeddings as features for clustering and topic discovery, and answer questions over a database in plain language with text-to-SQL. In these two projects you put both to work on data you choose.
- Text as features for a prediction. Embed a text column and feed the vectors into a classic model (logistic regression or gradient boosting) to predict an outcome, then compare that against a TF-IDF baseline and against an LLM doing the classification directly. Report which representation wins and what each costs to run. Data to try: a set that pairs free text with something to predict, such as support-ticket text with its resolution time, or news articles with their section.
- A natural-language query interface over a database. Build text-to-SQL with schema linking and a self-correction step that re-runs failed queries, and add a guard that blocks anything but reads. Test it on the questions a business user would ask. Data to try: the Chinook or Northwind database, the Spider benchmark, or a relational dataset from Kaggle.
In Part VI we turn to customizing models, so a small model can do one of these jobs cheaply and well.
NoteWhere we go next
That closes Part V. In every system so far we have used models exactly as the provider released them, and in Part VI we change the model itself. In Chapter 15 we ask the first and most important question of customization, whether you should fine-tune at all, before Chapters 16 and 17 show how.