Build a Text-to-SQL Service on the QueryGPT Pattern

Tier: Advanced · Workflow area: Text-to-SQL & AI-ready serving

The pattern that works in production (popularized by Uber's QueryGPT, which reports ~70% query-authoring time cut — vendor-reported, treat as directional) is a pipeline: intent classification → RAG over schemas and curated example queries → scoped generation → self-correction loop, running against a read-only role. This playbook builds that pipeline small enough to own.

When to reach for this

• •
  Analysts and PMs queue on the data team for routine "how many / how much / trend" queries.
• •
  The warehouse is too big for schema-in-the-prompt (more than a few dozen tables).
• •
  You can invest in curation — this system's accuracy is bought with curated marts and examples, not model choice.

Don't build this before the semantic groundwork exists (see the semantic-layer curation playbook); text-to-SQL over a raw 5,000-table swamp automates the confusion, not the answers.

The workflow

1. 1.

   Scope v1 to one domain workspace. Pick a single subject area (e.g., orders) with 10–30 curated tables. Domain "workspaces" narrow the retrieval search space — this is the single highest-leverage architectural decision in the pattern.

2. 2.

   Index two corpora for retrieval. (a) Schema cards: one document per table — columns, types, descriptions, join keys, common filters. (b) Curated example queries: 30–100 known-good question→SQL pairs from your team's actual work, each tagged with the tables it touches. Embed both into pgvector.

3. 3.

   Route intent, then select tables. A first LLM call classifies the question into a workspace and picks candidate tables from retrieved schema cards. Keeping this as a separate structured step makes wrong-table failures visible and fixable:

   Copy

   from pydantic import BaseModel
   
   class TableSelection(BaseModel):
       workspace: str
       tables: list[str]
       reasoning: str
   

4. 4.

   Generate SQL with retrieved context. The generation prompt gets: the selected schema cards, the 3–5 most similar example queries, and house rules (dialect, date conventions, always-filter-soft-deletes):

   Copy

   import anthropic
   
   class GeneratedSQL(BaseModel):
       sql: str
       assumptions: list[str]
   
   client = anthropic.Anthropic()
   response = client.messages.parse(
       model="claude-opus-4-8",
       max_tokens=2048,
       thinking={"type": "adaptive"},
       messages=[{
           "role": "user",
           "content": (
               f"Write one {DIALECT} SELECT statement answering: {question}\n\n"
               f"Schemas:\n{schema_cards}\n\nSimilar known-good examples:\n{examples}\n\n"
               "Rules: SELECT only; use exact column names from the schemas; "
               "if the question is ambiguous, state the assumption you made."
           ),
       }],
       output_format=GeneratedSQL,
   )
   candidate = response.parsed_output
   

5. 5.

   Run the self-correction loop. Execute with EXPLAIN first (catches syntax and missing-column errors free), then run with a row limit. On error, feed the SQL plus the exact database error back for one repair attempt — cap at 2 rounds, then surface the failure honestly:

   Copy

   for attempt in range(2):
       ok, err = try_explain(candidate.sql)
       if ok:
           break
       candidate = repair(client, question, candidate.sql, err)  # same parse() shape
   

6. 6.

   Execute inside guardrails. The service's database role is read-only, statement-timeout-limited, row-limited (LIMIT 1000 appended if absent), cost-capped where the warehouse supports it, and blocked from sensitive schemas. Guardrails live in the database role, not in the prompt — prompts are requests, grants are law.

7. 7.

   Return SQL + assumptions + results, and log everything. Show users the generated SQL and the model's stated assumptions alongside results — informed skepticism is part of the product. Log question, retrieved context, SQL, and outcome; accepted queries become new curated examples, which is the flywheel that improves accuracy without touching the model.

Verify before you trust it

• Execution accuracyon a 30-question eval set (see the text-to-SQL evaluation playbook) measured before any wider rollout; results compared against known-correct answers, not just "runs without error".
• Wrong-table rate tracked separatelytable selection is its own step so you can score it independently.
• Guardrail testprove the role can't write, can't read blocked schemas, and times out on a deliberately expensive query.
• Latency budgetintent + retrieval + generation + one correction round should land under your users' patience (~15–30s); measure p95, not the demo.

Pitfalls

• Skipping intent/table selection. One-shot generation over retrieved-everything produces subtly wrong joins on plausible-looking tables — the failure users can't detect.
• Uncurated example pool. Examples scraped from query history include the warehouse's bad habits; every example must be reviewed before it teaches the model.
• Unbounded self-correction. Loops that retry until something executes will eventually execute the wrong thing confidently; cap rounds and fail visibly.
• Prompt-level "guardrails". A prompt instruction not to query hr.salaries is a suggestion; a role grant is a control. Never rely on the former.
• Treating launch accuracy as permanent. Schemas evolve; retrieval indexes and schema cards need refresh automation, or accuracy decays with every migration.

Tools

• LlamaIndex / LangChainretrieval orchestration over schema cards and example queries; free path.
• pgvectorembedding store for both corpora; keeps the whole stack in Postgres for v1.
• Langfuseend-to-end traces of question → retrieval → SQL → outcome, the raw material for the improvement flywheel.

Client-safe variant

Schema names, column comments, and example queries all leak business information, so on client engagements the retrieval indexes and logs must live inside the client's environment. Confirm in writing which LLM provider may see schema metadata and example queries (schemas are often approvable where row data is not — this pattern sends metadata, not data, to the model). Run generation against a client-owned read-only role with the guardrails from step 6, and route result sets only within client systems. If no external model is approved, the pipeline runs unchanged against a self-hosted coder model via vLLM — expect lower first-pass accuracy and lean harder on curated examples and the correction loop. Hand off the eval set, the curation process, and the accuracy dashboard so the client can keep the flywheel turning.