Build the QueryGPT pattern small enough to fit on a laptop and rigorous enough to defend.
You build it from:
Uber's QueryGPT team reported roughly 70% query-authoring time saved (vendor-reported — treat as directional); their harder-won lesson is the one this lab drills: accuracy comes from curation — certified metric definitions and value aliases — not from a bigger model, and the benchmark must be a living process, not a launch-day screenshot. Eight of your 30 eval questions are answerable only through the metadata layer, so you can measure that claim instead of taking it on faith.
~150 minutes hands-on · Advanced tier (maps to module M8).
Laptop-only. The warehouse, retrieval, guardrails, and eval harness are fully offline and $0; the generation step calls the Claude API (a full 30-question eval run costs a few cents) and everything around it can be built and verified first with the harness's self-test mode, no key required.
mkdir -p ~/labs/de-lab-14 && cd ~/labs/de-lab-14
python3 -m venv .venv && source .venv/bin/activate
pip install duckdb sentence-transformers chromadb anthropic
Create build_warehouse.py — a deterministic retail warehouse (200 customers, 60 products, 2,000 orders, ~4,900 order lines, 127 refunds):
import duckdb, random
from datetime import date, timedelta
random.seed(14)
con = duckdb.connect("retail_dw.duckdb")
con.execute("""
CREATE OR REPLACE TABLE dim_customers (
customer_id INTEGER PRIMARY KEY, customer_name VARCHAR, region VARCHAR,
segment VARCHAR, signup_date DATE);
CREATE OR REPLACE TABLE dim_products (
product_id INTEGER PRIMARY KEY, product_name VARCHAR, category VARCHAR,
unit_cost DOUBLE, list_price DOUBLE);
CREATE OR REPLACE TABLE fct_orders (
order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date DATE,
status VARCHAR, channel VARCHAR);
CREATE OR REPLACE TABLE fct_order_items (
order_id INTEGER, product_id INTEGER, quantity INTEGER, net_price DOUBLE);
CREATE OR REPLACE TABLE fct_refunds (
refund_id INTEGER PRIMARY KEY, order_id INTEGER, refund_date DATE,
amount DOUBLE, reason VARCHAR);
""")
REGIONS = ["north", "south", "east", "west"]
SEGMENTS = ["enterprise", "smb", "consumer"]
CATS = ["electronics", "furniture", "apparel", "grocery"]
STATUSES = ["completed", "cancelled", "returned"]
CHANNELS = ["web", "store", "partner"]
REASONS = ["damaged", "wrong_item", "late_delivery", "changed_mind"]
customers = [(i, f"Customer {i:03d}", random.choice(REGIONS), random.choice(SEGMENTS),
date(2024, 1, 1) + timedelta(days=random.randint(0, 700))) for i in range(1, 201)]
products = [(i, f"Product {i:03d}", random.choice(CATS),
round(random.uniform(5, 400), 2), 0.0) for i in range(1, 61)]
products = [(pid, name, cat, cost, round(cost * random.uniform(1.2, 2.2), 2))
for pid, name, cat, cost, _ in products]
orders, items, refunds = [], [], []
rid = 1
for oid in range(1, 2001):
cid = random.randint(1, 200)
odate = date(2025, 1, 1) + timedelta(days=random.randint(0, 570))
status = random.choices(STATUSES, weights=[85, 8, 7])[0]
orders.append((oid, cid, odate, status, random.choice(CHANNELS)))
for _ in range(random.randint(1, 4)):
pid = random.randint(1, 60)
lp = products[pid - 1][4]
items.append((oid, pid, random.randint(1, 5), round(lp * random.uniform(0.8, 1.0), 2)))
if status == "returned":
refunds.append((rid, oid, odate + timedelta(days=random.randint(3, 21)),
round(random.uniform(10, 500), 2), random.choice(REASONS)))
rid += 1
con.executemany("INSERT INTO dim_customers VALUES (?, ?, ?, ?, ?)", customers)
con.executemany("INSERT INTO dim_products VALUES (?, ?, ?, ?, ?)", products)
con.executemany("INSERT INTO fct_orders VALUES (?, ?, ?, ?, ?)", orders)
con.executemany("INSERT INTO fct_order_items VALUES (?, ?, ?, ?)", items)
con.executemany("INSERT INTO fct_refunds VALUES (?, ?, ?, ?, ?)", refunds)
for t in ["dim_customers", "dim_products", "fct_orders", "fct_order_items", "fct_refunds"]:
print(t, con.execute(f"SELECT count(*) FROM {t}").fetchone()[0])
Run it and confirm the five row counts (200 / 60 / 2000 / ~4899 / ~127).
Write the guardrails first — they are the platform's spine. Create guardrails.py. Three independent layers: a statement-shape allowlist (SELECT/WITH only), a keyword blocklist, and — the layer that actually cannot be talked around — a read-only database connection. The row cap wraps every query so a runaway SELECT * cannot melt a dashboard.
import duckdb, re
MAX_ROWS = 200
def guarded_execute(sql: str):
stripped = sql.strip().rstrip(";")
if not re.match(r"(?is)^(select|with)\b", stripped):
raise PermissionError("only SELECT/WITH statements are allowed")
if re.search(r"(?is)\b(insert|update|delete|drop|alter|create|copy|attach"
r"|install|load|pragma|set)\b", stripped):
raise PermissionError("statement contains a blocked keyword")
con = duckdb.connect("retail_dw.duckdb", read_only=True)
try:
return con.execute(f"SELECT * FROM ({stripped}) LIMIT {MAX_ROWS}").fetchall()
finally:
con.close()
def result_sets_match(a, b) -> bool:
def norm(rows):
return sorted(tuple(round(v, 2) if isinstance(v, float) else v for v in r)
for r in rows)
return norm(a) == norm(b)
Test all three layers now: guarded_execute("DELETE FROM fct_orders") must raise; guarded_execute("SELECT 1; DROP TABLE fct_orders") must raise; len(guarded_execute("SELECT * FROM fct_order_items")) == 200 must hold. Note the honest limitation in your notes: a regex blocklist can false-positive on column names like created_at in edge cases — production platforms parse the AST (sqlglot) — but the read-only connection is the guarantee that survives any bypass of the string checks.
Curate the semantic metadata layer. Create metadata.py with two lists of strings. The schema cards describe each table with column meanings and the allowed values a model cannot guess; the glossary defines certified business vocabulary. This file is the DE's real lever — Uber's Finch team cut WHERE-clause errors with exactly this kind of curation, and Databricks Genie / Snowflake Cortex Analyst accuracy depends on the same inputs.
SCHEMA_CARDS = [
"TABLE dim_customers(customer_id, customer_name, region, segment, signup_date). "
"region values: north, south, east, west. segment values: enterprise, smb, consumer.",
"TABLE dim_products(product_id, product_name, category, unit_cost, list_price). "
"category values: electronics, furniture, apparel, grocery. "
"unit_cost is our cost; list_price is the sticker price.",
"TABLE fct_orders(order_id, customer_id, order_date, status, channel). "
"status values: completed, cancelled, returned. channel values: web, store, partner. "
"Join to dim_customers on customer_id.",
"TABLE fct_order_items(order_id, product_id, quantity, net_price). One row per order line; "
"net_price is the actually-charged unit price. Join to fct_orders on order_id, "
"dim_products on product_id.",
"TABLE fct_refunds(refund_id, order_id, refund_date, amount, reason). "
"reason values: damaged, wrong_item, late_delivery, changed_mind.",
]
GLOSSARY = [
"GMV (gross merchandise value) = sum(quantity * net_price) over order items of "
"COMPLETED orders only. Never include cancelled or returned orders in GMV.",
"AOV (average order value) = GMV / count(distinct completed order_id).",
"return rate = returned orders / all orders, as a percentage.",
"big-ticket order = an order whose item total sum(quantity * net_price) exceeds 2000.",
"margin of a product = list_price - unit_cost.",
"Revenue questions default to completed orders unless the user says otherwise.",
]
CURATED_EXAMPLES = [
{"q": "How many completed orders came from the east region?",
"sql": "SELECT count(*) FROM fct_orders o JOIN dim_customers c USING (customer_id) "
"WHERE o.status = 'completed' AND c.region = 'east'"},
{"q": "What was GMV in January 2026?",
"sql": "SELECT round(sum(i.quantity * i.net_price), 2) FROM fct_order_items i "
"JOIN fct_orders o USING (order_id) WHERE o.status = 'completed' "
"AND o.order_date >= '2026-01-01' AND o.order_date < '2026-02-01'"},
{"q": "Which category sold the most units?",
"sql": "SELECT p.category FROM fct_order_items i JOIN dim_products p USING (product_id) "
"JOIN fct_orders o USING (order_id) WHERE o.status = 'completed' "
"GROUP BY p.category ORDER BY sum(i.quantity) DESC LIMIT 1"},
]
Build schema-RAG. Create context.py: embed every card, glossary entry, and example question with all-MiniLM-L6-v2; at question time retrieve the top-3 cards, top-2 glossary entries, and top-2 examples by cosine similarity and assemble them into the prompt context. This is the "workspace" trick from QueryGPT scaled down: a real platform has hundreds of tables, and sending all of them both blows the context budget and hurts accuracy by burying the relevant join path. Reuse the embedding pattern from Lab 09 (encode with normalize_embeddings=True, score with a dot product, take top-k) — about 20 lines.
Write the generator with self-correction. Create ask.py. Generation returns bare SQL; the self-correction loop executes it through the guardrails and, on a database error, feeds the error text back for a repair — maximum two repairs, and PermissionError is never retried (a guardrail hit is a policy event, not a syntax accident).
import sys
from guardrails import guarded_execute
from context import build_context # your step-3 retriever
def generate_sql(question: str, context: str, error: str | None = None) -> str:
import anthropic
client = anthropic.Anthropic()
prompt = (
"You write DuckDB SQL for the retail warehouse described below. "
"Reply with a single SELECT statement and nothing else — no markdown fences.\n\n"
f"{context}\n\nQuestion: {question}"
)
if error:
prompt += f"\n\nYour previous attempt failed with this database error — fix it:\n{error}"
msg = client.messages.create(
model="claude-opus-4-8", max_tokens=500, thinking={"type": "adaptive"},
messages=[{"role": "user", "content": prompt}])
return "".join(b.text for b in msg.content if b.type == "text").strip()
def answer(question: str, max_attempts: int = 3):
context = build_context(question)
error = None
for attempt in range(1, max_attempts + 1):
sql = generate_sql(question, context, error)
try:
return guarded_execute(sql), sql, attempt
except PermissionError:
raise # policy violations are never retried
except Exception as e:
error = str(e)
raise RuntimeError(f"failed after {max_attempts} attempts: {error}")
if __name__ == "__main__":
rows, sql, attempts = answer(" ".join(sys.argv[1:]))
print(f"-- attempt {attempts}\n{sql}\n{rows[:10]}")
Create the 30-pair eval set. Create make_eval_set.py writing eval_set.jsonl: 22 schema-answerable questions (counts, filters, joins, group-bys over customers/products/orders/refunds) and 8 questions whose vocabulary exists only in the glossary — GMV for 2025, overall AOV, return rate, west-region GMV, best-return-rate channel, big-ticket order count, product-12 margin, partner-channel 2026 GMV. Each line: {"qid", "question", "gold_sql", "needs_metadata"}. Write the gold SQL yourself against the schema — authoring gold queries is half the learning — then validate every one executes and returns rows via guarded_execute before trusting it as truth.
Build the execution-accuracy harness and self-test it offline. Create evaluate.py: for each pair, run the platform's SQL and the gold SQL, compare with result_sets_match (order-insensitive, floats rounded — execution accuracy, not string match, because count(*) vs count(customer_id) with different aliases are both right). Before any API call, run python evaluate.py --self-test, which scores gold against gold: it must print 30/30, and a deliberately broken pair (change one gold constant) must drop it to 29/30. A harness you have not seen fail is not yet a harness.
Run the ablation that proves the curation claim. With an API key: python evaluate.py (full context) and python evaluate.py --no-glossary (schema cards and examples only — add the flag to build_context). Report accuracy overall and split by needs_metadata. Expected shape: schema-only questions score high in both runs; the 8 metadata questions collapse without the glossary — the model either refuses to guess or, worse, guesses plausibly (GMV including returned orders) and produces a wrong number that looks right. That silent-wrong class is why certified definitions beat bigger models. Without a key, document the expected shape and mark those cells "requires API run"; the harness, guardrails, retrieval, and gold set are still fully verified offline.
Write the living-benchmark note. Close platform-report.md with the operating model: every user-reported wrong answer becomes a new eval pair; the eval runs in CI against every change to cards, glossary, examples, or model version; accuracy is tracked per category over time. Uber's lesson verbatim — the benchmark is a process, not an artifact.
The metadata layer is a governance surface, not a prompt hack. "GMV excludes returns" is a business decision someone must own and certify; the text-to-SQL platform just makes uncertified vocabulary visibly fail instead of silently diverging across dashboards.
eval_set.jsonl executes through guarded_execute and returns at least one row.evaluate.py --self-test prints 30/30; a sabotaged pair drops it to 29/30.answer() demonstrates a self-correction: feed it a question after temporarily renaming a column in a schema card to something wrong and watch attempt 2 repair against the real schema error.needs_metadata pairs.A de-lab-14/ folder containing build_warehouse.py, guardrails.py, metadata.py, context.py, ask.py, make_eval_set.py, evaluate.py, eval_set.jsonl, and platform-report.md — the guardrail test evidence, harness self-test output, the accuracy table (overall and split by needs_metadata, ablation columns if API-run), and the living-benchmark operating note. Self-grading rubric (pass = 8/10):
random.seed(14) runs before any data generation and that you did not reorder the generator blocks.result_sets_match false-negatives: mixed int/float columns — the rounding normalizer only rounds floats; cast in the gold SQL if a metric returns DECIMAL.generate_sql's return, or tighten the "no markdown fences" instruction.error variable threading.--no-glossary scores the same as full: your metadata questions are answerable from schema cards alone — make the vocabulary genuinely absent from the cards (that is the design).The eight metadata-dependent pairs and the trap in each: GMV 2025 and partner GMV 2026 (must join items→orders, filter status='completed' — the plausible wrong answer includes returned orders); AOV (GMV over distinct completed orders, not over order lines); return rate (returned/all orders — not refunds/orders, which misses the distinction between the returned status and the refunds table); west-region GMV (two joins plus the completed filter); best return-rate channel (lowest rate wins — "best" is ambiguous without the glossary's framing); big-ticket count (the 2000 threshold exists nowhere in the schema); product-12 margin (list_price - unit_cost — a model without the glossary may reach for net_price). If your no-glossary run gets several of these "right", check whether the numbers are actually correct or merely confident.
Everything except the generation call runs locally: warehouse, retrieval, guardrails, and the eval harness never leave the machine, and the generation prompt contains only schema cards, glossary text, and example SQL — no rows of data. That makes the client-safe posture straightforward: schema names and business definitions still need client approval to leave the boundary (some MSAs treat schemas as confidential), so on restricted engagements run generation against the client's approved tenancy or an internal model endpoint, and keep the guardrail layer exactly as built — read-only role, statement allowlist, row cap, and full query logging are what let a client's security team say yes. The eval set doubles as the acceptance test you hand the client: accuracy on their 30 certified questions, re-run on every change, is the deliverable.