Text-to-SQL
Text-to-SQL converts natural language questions into executable database queries. A user asks "What was revenue last quarter by region?" and the system generates the correct SQL, executes it, and returns results. It is one of the most requested enterprise AI use cases because it democratises data access without requiring SQL literacy.
The challenge is not generating SQL - LLMs do that well. The challenge is generating correct SQL that respects your schema, business logic, and security boundaries.
This page is where the running example is built. A portfolio company's finance team asks "What was revenue last quarter by region?" and expects a governed answer, not a plausible one. The walkthrough below follows that one question from schema to result.
Architecture
Schema Grounding
The LLM needs to know your database structure to generate correct SQL. This is the most critical step.
What to include in context
schema_context = """
Database: analytics_prod
Schema: gold
Tables:
- orders (order_id, customer_id, order_date, delivered_date, status, order_value, refund_amount, region)
- status values: pending, confirmed, shipped, delivered, cancelled
- Use delivered_date for revenue calculations, not order_date
- 1 row per order line item
- customers (customer_id, name, segment, created_date, country)
- segment values: enterprise, mid-market, smb
- products (product_id, name, category, unit_price, active)
Relationships:
- orders.customer_id -> customers.customer_id
- orders.product_id -> products.product_id
Business rules:
- "Revenue" means SUM(order_value - COALESCE(refund_amount, 0)) WHERE status = 'delivered'
- "Last quarter" means the most recent complete calendar quarter
- "Active customers" means customers with at least one delivered order in the last 90 days
"""
Schema selection for large databases
With hundreds of tables, you cannot fit the entire schema in context. Use retrieval:
Walking Through the Running Example
Take the question as asked: "What was revenue last quarter by region?" Three parts of it carry business rules the model cannot infer from column names.
- "Revenue" is not
order_value. The schema context defines it as delivered-order value net of refunds, so the query has to subtractrefund_amountand filter tostatus = 'delivered'. - "Last quarter" means the most recent complete calendar quarter, not the trailing 90 days and not the quarter in progress.
- The date that counts is
delivered_date, notorder_date. An order placed in the last week of one quarter and delivered in the first week of the next belongs to the second.
"By region" is the only part a model would get right unaided, because region is a column on orders. Everything else comes from the business-rules block above. That is the point of schema grounding: the SQL is easy, and the definitions are the work.
The query that comes out should look like this, and it should be shown to the user alongside the number:
SELECT region,
SUM(order_value - COALESCE(refund_amount, 0)) AS net_revenue
FROM gold.orders
WHERE status = 'delivered'
AND delivered_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
AND delivered_date < DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY region
ORDER BY net_revenue DESC
Query Generation
def generate_sql(question: str, schema: str, examples: list[dict]) -> str:
messages = [
{"role": "system", "content": f"""You are a SQL expert. Generate a single SQL query
to answer the user's question.
Rules:
- Use only tables and columns from the schema provided
- Generate SELECT queries only (never INSERT, UPDATE, DELETE, DROP)
- Use explicit column names (never SELECT *)
- Include appropriate WHERE clauses for date ranges
- Use CTEs for complex queries (readable over clever)
Schema:
{schema}"""},
]
# Add few-shot examples
for ex in examples:
messages.append({"role": "user", "content": ex["question"]})
messages.append({"role": "assistant", "content": ex["sql"]})
messages.append({"role": "user", "content": question})
response = client.chat.completions.create(
model="gpt-4o", messages=messages, temperature=0
)
return response.choices[0].message.content
Validation
Never execute LLM-generated SQL without validation.
import sqlparse
def validate_sql(sql: str, allowed_tables: set[str]) -> tuple[bool, str]:
"""Validate generated SQL before execution."""
# Parse SQL
parsed = sqlparse.parse(sql)
if not parsed:
return False, "Could not parse SQL"
statement = parsed[0]
# Only allow SELECT
if statement.get_type() != "SELECT":
return False, f"Only SELECT allowed, got {statement.get_type()}"
# Check tables against allowlist
tables_used = extract_tables(sql)
unauthorized = tables_used - allowed_tables
if unauthorized:
return False, f"Unauthorized tables: {unauthorized}"
# Reject dangerous patterns
dangerous = ["DROP", "DELETE", "INSERT", "UPDATE", "ALTER", "EXEC", "--", ";"]
sql_upper = sql.upper()
for pattern in dangerous:
if pattern in sql_upper and pattern not in ("--",): # Allow inline comments
return False, f"Dangerous pattern detected: {pattern}"
return True, "Valid"
Production Patterns
Verified queries (for critical metrics)
Pre-approve SQL for common business questions. Return these verbatim instead of generating.
VERIFIED_QUERIES = {
"monthly revenue": """
SELECT DATE_TRUNC('month', delivered_date) AS month,
SUM(order_value - COALESCE(refund_amount, 0)) AS net_revenue
FROM gold.orders
WHERE status = 'delivered'
GROUP BY 1 ORDER BY 1
""",
"active customers by segment": """
SELECT c.segment, COUNT(DISTINCT c.customer_id) AS active_customers
FROM gold.customers c
JOIN gold.orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered' AND o.delivered_date >= CURRENT_DATE - 90
GROUP BY 1
"""
}
def answer_question(question: str):
# Check verified queries first
for key, sql in VERIFIED_QUERIES.items():
if is_similar(question, key, threshold=0.9):
return execute_and_format(sql, verified=True)
# Fall back to generation
sql = generate_sql(question, schema, examples)
valid, reason = validate_sql(sql, ALLOWED_TABLES)
if not valid:
return f"Could not generate a safe query: {reason}"
return execute_and_format(sql, verified=False)
Self-correction
When generated SQL fails, feed the error back to the LLM for correction.
def generate_with_retry(question: str, max_attempts: int = 3) -> str:
sql = generate_sql(question, schema, examples)
for attempt in range(max_attempts):
valid, reason = validate_sql(sql, ALLOWED_TABLES)
if not valid:
sql = fix_sql(sql, error=reason)
continue
try:
results = execute_query(sql)
return format_results(results, sql)
except DatabaseError as e:
sql = fix_sql(sql, error=str(e))
return "Could not generate a valid query after multiple attempts."
Where It Goes Wrong
Every failure below produces SQL that parses, passes validation, and returns a number. None of them raise an error, which is what makes them expensive.
| Failure | On the running example | What catches it |
|---|---|---|
| Wrong metric definition | Sums order_value without netting refunds | The business rules in the schema context, and a verified query for revenue |
| Wrong date column | Filters on order_date instead of delivered_date | The same business rules, and showing the SQL to the user |
| Wrong time window | Treats "last quarter" as the last 90 days | A defined meaning of "last quarter" in context |
| Wrong grain | Counts rows as orders, when orders holds one row per order line item | The grain note in the schema context |
| Data the user should not see | Returns regions outside the user's scope | Table allowlists in validation, and permissions enforced in the database rather than the prompt |
Test the pattern against a set of business questions with known correct answers, and score whether the numbers match, not whether the SQL looks plausible. A query can be well-formed and wrong in every one of the ways above.
Platform Implementations
| Platform | Feature | How it works |
|---|---|---|
| Snowflake Cortex Analyst | YAML semantic model + verified queries | Best for Snowflake-native analytics |
| Databricks Genie | Unity Catalog metadata + instructions | Best for lakehouse environments |
| Azure AI Search + OpenAI | Schema in RAG + function calling | Flexible, any database |
| LangChain SQL Agent | Schema introspection + tool use | Open-source, any database |
Key Takeaways
- Schema grounding determines accuracy. Invest in table/column descriptions and business rule documentation.
- Validate every generated query before execution. Allowlist tables, reject mutations, check syntax.
- Use verified queries for critical business metrics. Do not let the LLM generate SQL for board-level KPIs.
- Self-correction (feed errors back to LLM) handles 80% of generation failures without human intervention.
- Security is non-negotiable: read-only connections, parameterised execution, row-level access controls.
Next: The AI Layer - text-to-SQL generates the query; the AI layer supplies the governed definitions that make its answer trustworthy.