Text-to-SQL Over Real Schemas: Linking, Validation, and Correction

Text-to-SQL systems often fail in production not because the Large Language Model (LLM) doesn’t know SQL, but because it doesn’t know your database. In controlled environments like the Spider dataset, schemas are small, clean, and well-named. In the real world, a schema might have 500 tables, ambiguous column names like col_1, and inconsistent data types. The core engineering challenge is shifting from "generating SQL" to "grounding natural language in a specific, noisy schema."

This process relies on three mechanical stages: Schema Linking (reducing the search space), Query Validation (pre-execution safety), and Self-Correction (error-driven refinement).

Schema Linking: Reducing the Search Space

The first bottleneck is context window limits and noise. Feeding an entire database schema (DDL statements for 500 tables) into an LLM prompt causes two failures:

1. Context Overflow: The prompt exceeds token limits.
2. Attention Dilution: The LLM struggles to attend to relevant columns among thousands of irrelevant ones, leading to hallucinated joins or column references.

The solution is Schema Linking: a pre-processing step that selects only the relevant tables and columns for a given user query. This is critical for effective nl2sql performance, as it ensures the model focuses on the correct subset of metadata rather than guessing across the entire catalog.

Mechanism: Embedding-Based Retrieval with Heuristics

We treat schema elements (table names, column names, descriptions) as text documents. When a user asks, "Show me the total revenue from Q3 2023," we:

1. Embed the Query: Convert the natural language query into a vector using an embedding model (e.g., text-embedding-3-small).
2. Retrieve Candidate Schema Elements: Use vector similarity search (e.g., FAISS, pgvector) to find the top-K most similar columns/tables from the database metadata index.
3. Apply Heuristic Filters: Not all high-similarity matches are correct. We filter based on:
   • Table Size: Exclude large fact tables if the query seems to target dimension tables only, preserving them for explicit aggregation requests.
   • Column Cardinality: Exclude high-cardinality columns (like user_id) for aggregate queries unless explicitly requested.
   • Keyword Matching: Boost matches containing exact terms from the query (e.g., "revenue" → revenue column).

Example: Schema Linking Pipeline

# Pseudocode for schema linking
def link_schema(query, db_metadata):
    # 1. Embed query
    query_vector = embed(query)
    
    # 2. Retrieve top 50 relevant schema elements (tables + columns)
    candidates = vector_db.search(query_vector, k=50)
    
    # 3. Filter by heuristic: exclude tables with > 1M rows if query has no LIMIT
    if "limit" not in query.lower():
        candidates = [c for c in candidates if c.table_row_count < 1_000_000]
    
    # 4. Format selected schema into prompt context
    context = format_ddl(candidates)
    return context

This reduces the prompt size by 90%+ and forces the LLM to focus on relevant entities. Without this, the LLM often picks the wrong table due to name similarity (e.g., customers vs. customer_orders).

Query Validation: The Pre-Execution Guardrail

Once the LLM generates SQL, it is not ready for execution. LLMs frequently produce syntactically valid but semantically invalid SQL. Running such queries against production databases is risky and expensive.

Mechanism: Abstract Syntax Tree (AST) Parsing and Type Checking

We parse the generated SQL into an AST and validate it against the database schema before sending it to the database engine. This catches:

1. Syntax Errors: Missing commas, unmatched parentheses.
2. Type Mismatches: Joining an INT column to a VARCHAR column.
3. Semantic Drift: Referencing a column that doesn’t exist in the selected schema.

Example: Validation Logic

-- Generated SQL from LLM
SELECT u.name, o.total_price
FROM users u
JOIN orders o ON u.id = o.customer_id
WHERE o.total_price = 'high'

A validator would flag:

• o.total_price = 'high': Type mismatch. total_price is likely DECIMAL, not STRING.
• u.id = o.customer_id: If customer_id is BIGINT and id is INT, this may cause implicit conversion issues or index misses.

We can use lightweight libraries like sqlglot or sqlparse to parse and validate. If validation fails, we skip execution and move to self-correction.

Self-Correction: Error-Driven Refinement

Even with validation, some errors slip through (e.g., logical errors in GROUP BY clauses). The most robust mechanism is self-correction: using database error messages to refine the query. This self correction loop is essential for handling the ambiguity inherent in natural language queries.

Mechanism: Feedback Loop with Error Parsing

When a query is executed, the database returns an error code and message (e.g., PostgreSQL’s ERROR: column "revnue" does not exist). We feed this error back into the LLM as part of the prompt, instructing it to fix the query.

Step-by-Step Correction Flow

1. Execute Query: Attempt to run the SQL.
2. Catch Error: If an exception occurs, capture the error message.
3. Classify Error:
   • Syntactic: e.g., syntax error at end of input. → LLM can fix this easily.
   • Semantic: e.g., column does not exist. → LLM may have linked the wrong column.
   • Logical: e.g., division by zero. → Requires business logic clarification.
4. Refine Prompt: Add the error message to the conversation history and ask the LLM to regenerate.

Example: Self-Correction Prompt

prompt = f"""
User Query: {user_query}
Generated SQL: {generated_sql}
Database Error: {error_message}
Please fix the SQL and return only the corrected SQL.
"""
corrected_sql = llm.generate(prompt)

This loop can run up to 3 times. If it fails after 3 attempts, we fall back to returning an error to the user or escalating to a human.

Tradeoffs and Considerations

• Latency: Schema linking adds ~100-200ms latency. Validation adds ~50ms. Self-correction can add seconds. For real-time apps, consider caching schema links.
• Cost: Each self-correction step requires an LLM call. Limit the number of retries.
• Security: Always parameterize queries if possible. Even with self-correction, never trust LLM-generated SQL directly in production without strict allowlists or sandboxing.
• Validation Techniques: Effective query validation relies on strict type checking and schema-aware parsing. Tools like sqlglot allow us to verify that generated SQL aligns with the database schema before execution, preventing runtime errors and ensuring that the query structure is sound.

Conclusion

Text-to-SQL over real schemas is not a single LLM prompt problem. It is a system engineering problem involving:

1. Retrieval to reduce schema noise via schema linking.
2. Validation to prevent bad queries.
3. Correction to handle edge cases.

By treating these components as distinct, testable parts, you can build nl2sql systems that are reliable enough for production use. The key to successful text to sql implementation lies in rigorously managing the schema context and validating every step of the generation pipeline.