Text-to-SQL systems for governed analytics, repair loops, and query safety.

Course 6: Text-To-SQL Systems

One-hour objective: defend a governed text-to-SQL architecture that connects user intent to schema grounding, semantic constraints, validation, dry-run repair, execution evaluation, and permission-safe BigQuery access.

Course 6 Reading List

Keep this to three required sources. The optional refresher is for BigQuery dry-run mechanics if that API detail is not fresh.

Readiness Checklist

You are ready for the interview version of this topic when you can describe the system without saying "the model writes SQL and we run it."

• You can draw the request path from user question to intent clarification, schema retrieval, semantic-layer lookup, SQL draft, dry run, repair, execution, and answer summary.
• You can explain schema linking for a large warehouse, including table descriptions, column names, relationship hints, metric definitions, query examples, and dialect docs.
• You can separate semantic correctness from syntactic validity: a dry run can validate structure and cost, but not whether the query answers the business question.
• You can design a bounded repair loop that feeds parser, dry-run, and execution errors back into the generator without retrying forever or hiding uncertainty.
• You can enforce permission-safe context assembly so unauthorized tables, columns, row samples, and prior query outputs never reach the prompt or execution path.
• You can propose evals for execution accuracy, result-set equivalence, metric-definition adherence, cost ceilings, safety violations, and answer provenance.

Interview Drill: AI Infra System Design

Prompt: design a governed text-to-SQL service for a BigQuery analytics assistant used by enterprise customers with thousands of tables, strict data permissions, and high expectations for answer correctness.

• Start with the interface contract: accept natural language, user identity, workspace context, candidate datasets, and clarification state; return SQL, validation receipts, result summary, and provenance.
• Describe grounding: rank candidate tables, retrieve semantic-layer metrics and join rules, include only permission-filtered metadata, and ask a clarification question when intent or grain is ambiguous.
• Design generation as planning plus SQL: have the model produce a compact query plan and SQL draft, then parse the SQL and reject disallowed statements before any warehouse call.
• Add validation gates: dry-run for syntax and estimated scan, enforce cost and timeout budgets, run limited execution only after approval, and feed structured errors into a capped repair loop.
• Make evaluation production-grade: use golden business questions, expected-result fixtures, execution checks, adversarial permission tests, and regression dashboards by dataset family.
• Close with operating controls: trace every prompt section and tool call, log dry-run and repair receipts, isolate customer metadata, and provide a human review path for high-impact queries.