Text-to-SQL Copilot with Schema Retrieval and Guarded Execution

Tier: Advanced · Workflow area: Analytics Automation & NL Interfaces

When to use

• •
  Analysts/PMs repeatedly ask the data team for one-off aggregates that follow known patterns.
• •
  You have a documented (or documentable) warehouse schema and want an NL interface with controlled blast radius.
• •
  You are evaluating a vendor "chat with your data" tool and need a baseline to judge it against.

Do not build this over an undocumented schema with ambiguous column semantics — fix the semantic layer first, or the copilot will confidently query the wrong revenue column.

Prerequisites

• •
  Read-only warehouse credentials scoped to a sandbox role (statement timeout + row/byte limits set at the role level, not in app code).
• •
  sqlglot for parsing/validation; the embedding stack from pb-17 for schema retrieval.
• •
  30–50 real question→SQL pairs from your team's history — these become the eval set and few-shot pool.

The workflow

1. 1.

   Build the schema corpus. One document per table: DDL, column descriptions, enum values, 3 sample rows (synthetic if sensitive), join keys, and — critically — "gotcha" notes ("orders.amount is cents; use orders_v2 after 2024-03"). If you have dbt, generate these from model YAML docs.

2. 2.

   Schema retrieval, not schema dumping. Embed the table documents; at question time retrieve top-k tables (k=5–8) plus their FK-linked neighbors. For 20+-table warehouses this beats stuffing the full DDL — both on accuracy (less distraction) and cost.

3. 3.

   Generate with a structured contract. Ask for SQL plus machine-checkable metadata:

   Copy

   class SQLDraft(BaseModel):
       sql: str
       tables_used: list[str]
       assumptions: list[str]         # surfaced to the user, always
       needs_clarification: bool      # model may refuse to guess
       clarifying_question: str | None
   

   Few-shot with 5–8 pairs retrieved from your question→SQL history by similarity to the incoming question.

4. 4.

   Validate before execution — the guard stack. Every draft passes through, in order:

   Copy

   import sqlglot
   from sqlglot import exp
   tree = sqlglot.parse_one(draft.sql, dialect="snowflake")   # 1. parses at all
   assert not list(tree.find_all(exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create))  # 2. read-only
   used = {t.name for t in tree.find_all(exp.Table)}
   assert used <= ALLOWED_TABLES                               # 3. allowlist
   if not tree.args.get("limit"): tree = tree.limit(10000)     # 4. inject LIMIT
   final_sql = tree.sql(dialect="snowflake")
   

   Add an EXPLAIN dry-run and reject plans over a scan-bytes threshold on BigQuery/Snowflake-class warehouses.

5. 5.

   Execute, then self-check the result. Run in the sandbox role; hand the result preview (schema + 5 rows + rowcount) back to the model: "Does this result plausibly answer the question? List anomalies." Empty results, suspicious 1-row aggregates, and unit mismatches get flagged to the user instead of presented as answers.

6. 6.

   Evaluate on execution accuracy. Score the eval set on execution match (result equivalence against reference SQL), not string match — many correct SQL formulations differ textually. Track: execution-accuracy, guard-rejection rate, clarification rate, and p95 latency. Iterate on schema docs before iterating on prompts; missing column semantics cause most failures.

Tool variants

• Anthropic / OpenAI: identical architecture; use structured outputs (pb-14) for the SQLDraft contract. A frontier-tier model (e.g. claude-opus-4-8) for generation + cheap tier for the result self-check is a sensible split.
• sqlglot dialects: transpile between dialects (read="postgres", write="snowflake") if your few-shot history and target warehouse differ.
• Semantic layer route: if dbt Semantic Layer/Cube/LookML exists, generate metric queries against it instead of raw SQL — smaller surface, fewer wrong-column disasters. The guard stack still applies.
• Local variant: open-weights coder models (Qwen2.5-Coder-32B-class) via vLLM are credible for SQL generation; keep hosted-tier evals as the benchmark.

Verification

• •
  Execution accuracy ≥70% on the held-out eval set (typical good result on a documented warehouse; report your number honestly).
• •
  100% of destructive-statement injection attempts ("...; DROP TABLE users") blocked by the guard stack in a red-team pass of ≥20 attempts.
• •
  Every executed query ran under the sandbox role — verify via warehouse query history audit, not assumption.
• •
  Assumptions surfaced in the UI for every answer; spot-check that 10/10 sampled answers display them.

Pitfalls

• Validating with regex instead of a parser. String-matching "DROP" misses EXECUTE IMMEDIATE, comments, and dialect quirks; only an AST check (sqlglot) counts as validation.
• Full-schema stuffing. Works at 10 tables, silently degrades at 80 — the model joins plausible-but-wrong tables. Retrieval with FK-neighbor expansion is the fix.
• String-match evals. Penalizes correct SQL and masks real regressions; always execution-match.
• Answering when it should ask. Ambiguous metrics ("revenue" — gross? net? recognized?) must trigger clarification; encode metric definitions in the schema corpus.
• No cost guard. One SELECT * cross-join on a BI warehouse pays for the entire project's API budget; scan-bytes limits are non-negotiable.

Restricted-environment variant

• •
  Schema DDL is usually shareable even when data is not — the copilot needs schema + synthetic sample rows, never real rows, in prompts. Verify sample rows are synthetic before any hosted call.
• •
  Result self-check (step 5) sends real query output to the API; in restricted engagements replace it with local heuristics (empty-result, rowcount, null-share checks) or a local model.
• •
  Fully on-prem: local coder model + sqlglot + sandbox role reproduces the whole architecture; budget accuracy loss and measure it on the same eval set.

Success criteria

• •
  A working copilot with measured execution accuracy, a guard stack that survived red-teaming, and warehouse-level (not app-level) read-only enforcement.
• •
  Eval harness with execution-match scoring that reruns in minutes after any prompt/schema-doc change.
• •
  Clarification behavior demonstrated on ambiguous questions.
• •
  Schema documentation improved as a by-product — the highest-leverage artifact even if the copilot is shelved.