Natural Language to SQL (NL2SQL) Pipelines

+---------------------------------------------------------------------------------+
|                         ENTERPRISE NL2SQL ARCHITECTURE                          |
+---------------------------------------------------------------------------------+
|  User Query: "Which region had highest churn last quarter?"                    |
|                                                                                 |
|  +--------------------+     +-------------------+     +---------------------+  |
|  | Schema + Metadata  | --> | Prompt & Few-Shot | --> | LLM SQL Generation  |  |
|  +--------------------+     +-------------------+     +---------------------+  |
|                                                                  |              |
|                                                                  v              |
|  +--------------------+     +-------------------+     +---------------------+  |
|  | Result Table / Viz | <-- | Execute Read-Only | <-- | AST SQL Validator   |  |
|  +--------------------+     +-------------------+     +---------------------+  |
+---------------------------------------------------------------------------------+

Why Traditional Text-to-SQL Fails in Enterprise Environments

Naively feeding a database schema to an LLM produces SQL queries that frequently fail due to:

1. Hallucinated column names or non-existent join keys.
2. Ambiguous business metrics: Is "revenue" booked, recognized, or invoiced?
3. Performance catastrophes: Unindexed cross-joins across billions of rows.
4. Security risks: SQL injection or unauthorized read/mutation operations.

The Production-Grade NL2SQL Architecture

A production-ready Text-to-SQL system relies on four pillars:

1. Dynamic Schema Pruning: Instead of dumping 500 tables into the prompt, use vector similarity on table/column descriptions to select only the top 3-5 relevant tables.
2. Golden SQL Few-Shot Exemplars: Maintain a curated vector store of verified business questions and their vetted SQL translations.
3. SQL AST Validation: Parse the query with sqlglot to verify syntax and enforce strict read-only constraints (SELECT only, no DROP, ALTER, or INSERT).
4. Self-Correction Execution Loop: If the database engine returns an error, feed the error trace and SQL back to the LLM to auto-correct.

sequenceDiagram
    autonumber
    actor User
    participant App as NL2SQL Engine
    participant DB as Warehouse (Snowflake)
    User->>App: "Monthly recurring revenue by tier"
    App->>App: Retrieve Relevant Schemas & Golden Queries
    App->>App: Generate Candidate SQL
    App->>App: Validate with sqlglot (Enforce READONLY & LIMIT)
    App->>DB: Execute Query
    alt Execution Fails
        DB-->>App: SQL Syntax Error / Unknown Column
        App->>App: Self-Correction Prompt with Error
        App->>DB: Retry Corrected Query
    end
    DB-->>App: Query Results
    App-->>User: Formatted Data & Visualization

Code Example: AST Validation and Query Safeguards

import sqlglot
from sqlglot.expressions import Select, Drop, Delete, Update, Insert

def validate_safe_read_only_query(sql_string: str) -> bool:
    try:
        parsed = sqlglot.parse_one(sql_string, read="postgres")
    except Exception as e:
        print(f"Syntax parsing failed: {e}")
        return False

    # Block any destructive or mutation statements
    for expr in parsed.find_all((Drop, Delete, Update, Insert)):
        raise PermissionError(f"Mutating SQL statement detected and blocked: {type(expr)}")

    # Ensure top-level statement is a SELECT
    if not isinstance(parsed, Select):
        raise ValueError("Only SELECT queries are permitted.")

    # Enforce safe LIMIT clause to prevent out-of-memory crashes
    if not parsed.args.get("limit"):
        parsed = parsed.limit(1000)

    print("Query validated successfully!")
    return str(parsed)

# Test query
query = "SELECT user_id, email, signup_date FROM users WHERE active = true"
safe_query = validate_safe_read_only_query(query)
print("Safe SQL:", safe_query)