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.

Running example: NLQ

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

Ground
What the model is told
Question
In plain language
Schema Grounding
Tables, columns, business rules
Generate
Write it
LLM Writes SQL
Check and Run
Before anything executes
Validate
Syntax and safety
Execute
Read-only
Answer
Show the working
Format
Answer
With the SQL shown

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:

User Question
Embed Question
Search Schema
Table and column descriptions
Top-K Tables
Only the relevant tables and columns
LLM + Schema
Writes the SQL

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 subtract refund_amount and filter to status = '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, not order_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.

FailureOn the running exampleWhat catches it
Wrong metric definitionSums order_value without netting refundsThe business rules in the schema context, and a verified query for revenue
Wrong date columnFilters on order_date instead of delivered_dateThe same business rules, and showing the SQL to the user
Wrong time windowTreats "last quarter" as the last 90 daysA defined meaning of "last quarter" in context
Wrong grainCounts rows as orders, when orders holds one row per order line itemThe grain note in the schema context
Data the user should not seeReturns regions outside the user's scopeTable 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

PlatformFeatureHow it works
Snowflake Cortex AnalystYAML semantic model + verified queriesBest for Snowflake-native analytics
Databricks GenieUnity Catalog metadata + instructionsBest for lakehouse environments
Azure AI Search + OpenAISchema in RAG + function callingFlexible, any database
LangChain SQL AgentSchema introspection + tool useOpen-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.