Databases hold answers to most business questions. But getting those answers requires SQL -- a skill that most stakeholders, analysts, and even many developers don't use daily. The gap between "I want to know X" and SELECT ... FROM ... WHERE ... is where text-to-SQL fits in.

Text-to-SQL systems accept a natural language question (e.g., "What were our top 10 customers by revenue last quarter?"), generate the corresponding SQL query, execute it against a database, and return the results. The translation is handled by a large language model (LLM) that has been given enough context about the database schema to produce valid, executable SQL.

This is not a new idea -- natural language interfaces to databases (NLIDBs) date back to the 1970s. What changed is that modern LLMs are good enough at SQL generation to make these systems practical for real workloads.

How Text-to-SQL Works

A text-to-SQL system has four stages: schema context injection, natural language parsing, SQL generation, and result delivery.

Schema Context Injection

Before the LLM can generate SQL, it needs to understand the database structure. This means providing table names, column names, data types, primary and foreign key relationships, and ideally sample values or column descriptions. This context is injected into the LLM prompt alongside the user's question.

The quality of schema context directly determines output quality. An LLM that knows orders.customer_id references customers.id will generate correct joins. Without that context, it may hallucinate column names or produce syntactically valid but semantically wrong queries.

Schema context can be provided statically (a fixed schema description in the prompt) or dynamically (retrieved at query time based on the user's question). Dynamic retrieval is more scalable for large databases with hundreds of tables, because it limits the prompt to only the relevant tables.

Natural Language Parsing

The LLM interprets the user's intent from their natural language input. This involves:

• Identifying the entities referenced (tables, columns, metrics)
• Understanding temporal references ("last quarter," "year-over-year")
• Resolving ambiguity ("revenue" might mean gross_revenue, net_revenue, or total_amount depending on the schema)
• Recognizing aggregation intent ("top 10," "average," "total")

This step is where most errors originate. Natural language is inherently ambiguous, and the same question can map to different SQL queries depending on business context.

SQL Generation

The LLM produces a SQL query based on the parsed intent and schema context. A well-constructed prompt might yield:

SELECT c.name, SUM(o.total_amount) AS revenue
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
  AND o.created_at < DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 10

The generated SQL must be syntactically correct for the target database dialect (PostgreSQL, MySQL, DuckDB, etc.), use only columns and tables that actually exist, and correctly express the user's intent.

Execution and Result Delivery

The generated SQL is executed against the database, and results are returned to the user. In production systems, this step includes validation (checking the SQL for syntax errors or disallowed operations before execution), sandboxing (running the query with restricted permissions), and result formatting (converting tabular results into a human-readable response).

NSQL: Natural SQL as an Emerging Concept

NSQL (natural SQL) refers to an emerging approach where the boundary between natural language and SQL becomes fluid. Rather than treating text-to-SQL as a strict translation problem -- natural language in, SQL out -- NSQL systems allow users to express queries in a mix of natural language and SQL fragments.

For example, a user might write: "Show me all orders WHERE total > 1000 from last week." The system interprets the natural language portions ("from last week") and passes through the SQL fragments (WHERE total > 1000) directly.

NSQL is still an early concept, but it reflects a practical reality: power users often know parts of the SQL they want and prefer to specify those directly rather than relying entirely on LLM interpretation.

Common Approaches to Text-to-SQL

Prompt Engineering with General-Purpose LLMs

The most common approach uses a general-purpose LLM (GPT-4, Claude, Llama) with a carefully engineered prompt that includes the database schema, query examples, and instructions for SQL generation. This requires no model training and can be deployed immediately.

The prompt typically includes:

• The database schema (DDL statements or structured descriptions)
• A few examples of natural language to SQL mappings (few-shot learning)
• Instructions about the target SQL dialect
• Business-specific terminology mappings

This approach works well for straightforward queries but can struggle with complex joins, subqueries, and domain-specific logic.

Fine-Tuned Models

Fine-tuning a base LLM on a dataset of (question, SQL) pairs from a specific database produces a model that is specialized for that schema. Fine-tuned models are typically more accurate for their target database than general-purpose models, but they require training data, compute resources, and retraining when the schema changes.

Open-source models like SQLCoder and NSQL-Llama have been fine-tuned specifically for text-to-SQL tasks and achieve competitive accuracy on benchmarks like Spider and BIRD.

RAG over Schema Metadata

For databases with hundreds or thousands of tables, including the full schema in every prompt is impractical. Retrieval-augmented generation (RAG) addresses this by retrieving only the relevant schema elements at query time.

When a user asks a question, the system:

1. Embeds the question and searches a vector index of table and column descriptions
2. Retrieves the most relevant tables and their schemas
3. Constructs a prompt with only the relevant schema context
4. Generates SQL using the focused context

This approach scales to large databases and reduces hallucination by limiting the schema surface area the LLM must reason about.

Challenges in Text-to-SQL

Ambiguity in Natural Language

"Show me active users" could mean users who logged in today, users with active subscriptions, or users who have made a purchase in the last 30 days. Without explicit business definitions, the LLM must guess -- and it often guesses wrong.

The most reliable mitigation is to include business glossaries or metric definitions in the prompt context: "active user = a user with at least one login event in the last 30 days."

Complex Joins and Multi-Table Queries

Single-table queries are relatively straightforward. Performance degrades significantly for queries that require multiple joins, correlated subqueries, window functions, or CTEs (common table expressions). These queries demand that the LLM correctly trace foreign key relationships across several tables -- a task that increases in difficulty with schema size.

Hallucinated Column and Table Names

LLMs can generate SQL that references columns or tables that don't exist. This is particularly common when the schema context is incomplete or when column names are ambiguous. A model might generate SELECT user_email FROM users when the actual column is email_address.

Schema validation before execution catches these errors, but it doesn't fix them. More advanced systems re-prompt the LLM with the error message, allowing it to self-correct.

SQL Dialect Differences

SQL is not a single language. PostgreSQL, MySQL, BigQuery, DuckDB, and SQL Server each have their own syntax for date functions, string operations, window functions, and type casting. A text-to-SQL system must generate queries in the correct dialect for the target database.

Evaluating Text-to-SQL Systems

Two primary metrics are used to evaluate text-to-SQL accuracy:

Execution accuracy measures whether the generated SQL, when executed, produces the correct result set. This is the more practical metric -- it doesn't matter if the SQL is different from the reference query as long as the results match.

Exact match accuracy measures whether the generated SQL exactly matches a reference query. This is stricter and less useful in practice, since many different SQL queries can produce the same results.

On the Spider benchmark (a widely used text-to-SQL evaluation dataset), state-of-the-art systems achieve 85-90% execution accuracy on simple queries but drop to 50-70% on complex queries involving multiple joins, nested subqueries, and aggregations.

Production Patterns for Text-to-SQL

SQL Validation and Sandboxing

Never execute LLM-generated SQL without validation. At minimum, production systems should:

• Parse the SQL and verify that all referenced tables and columns exist
• Check for disallowed operations (DROP, DELETE, UPDATE in read-only contexts)
• Execute the query with a read-only database user and strict resource limits (timeouts, row limits)
• Log every generated query for audit and debugging

Result Verification

After execution, verify that the results are reasonable. Common checks include:

• Row count sanity checks (a query asking for "top 10" should return at most 10 rows)
• Type validation (a "revenue" column should contain numeric values)
• NULL handling (unexpected NULLs often indicate a wrong join)

Multi-Turn Refinement

When the first query doesn't match the user's intent, a conversational interface allows the user to refine their question. The system can use the previous query, its results, and the user's feedback to generate an improved query. This iterative approach significantly improves practical accuracy.

Text-to-SQL with SQL Federation

Text-to-SQL becomes more powerful when combined with SQL federation. In a federated environment, a single SQL query can access data from PostgreSQL, Databricks, S3, and dozens of other sources simultaneously. Text-to-SQL on top of federation means a user can ask a natural language question that spans multiple data systems -- without knowing where the data lives or how to connect to each source.

For example, "Compare customer satisfaction scores from our CRM with order volumes from the warehouse" becomes a federated query that joins data across two different systems, all triggered by a single natural language question.

The combination also benefits from LLM inference served alongside the data layer. When the LLM that generates SQL and the federation engine that executes it are co-located, the round-trip time from question to answer is minimized. Tool calling patterns enable LLMs to invoke SQL execution as a tool, creating agentic workflows where the model can iteratively query, inspect results, and refine its approach.

Spice provides both SQL federation and LLM inference in a single runtime, making it possible to build text-to-SQL applications that query across 30+ data sources with sub-second latency.

Advanced Topics

Schema-Aware Prompting

The difference between a text-to-SQL system that works in demos and one that works in production often comes down to how schema context is structured in the LLM prompt. Naive approaches dump the full DDL (CREATE TABLE statements) into the prompt and hope the model figures out the relationships. Production systems are more deliberate.

Effective schema-aware prompting includes several layers of context beyond raw DDL:

• Column descriptions: Natural language annotations explaining what each column contains. orders.status might be an enum with values pending, shipped, delivered, cancelled -- without this context, the LLM cannot correctly filter by status.
• Foreign key annotations: Explicit statements like "orders.customer_id references customers.id" guide the model toward correct joins. Without these, the model may join on column name similarity, which often produces wrong results.
• Sample values: Including 3-5 representative values for categorical columns (e.g., region: ['us-east', 'us-west', 'eu-west', 'ap-southeast']) helps the LLM generate correct filter predicates.
• Business glossary entries: Definitions like "active customer = a customer with at least one order in the last 90 days" resolve ambiguity before the model encounters it.

For large schemas, dynamic schema retrieval is essential. The system embeds the user's question, searches a vector index of table and column descriptions, and includes only the top-k most relevant tables in the prompt. This keeps the prompt focused and reduces hallucination of non-existent columns.

Query Validation Pipelines

LLM-generated SQL cannot be trusted without validation. A production validation pipeline applies multiple checks before execution:

Syntactic validation parses the SQL using the target dialect's parser. This catches malformed queries, unclosed parentheses, and invalid keywords before they reach the database. Parsing also produces an AST (abstract syntax tree) that subsequent checks can inspect.

Schema validation verifies that every table and column referenced in the query actually exists in the database catalog. This is the most effective defense against hallucinated column names -- the most common failure mode in text-to-SQL systems. Schema validation also checks data types: if the query compares a string column to an integer, the validator flags the type mismatch.

Policy validation enforces security and governance rules. Common policies include: no DDL statements (DROP, ALTER, CREATE), no DML mutations (INSERT, UPDATE, DELETE) in read-only contexts, no queries against restricted tables or columns, and mandatory WHERE clauses on large tables to prevent full table scans. Policy validation inspects the AST to detect disallowed operations.

Cost estimation uses the database's EXPLAIN output to estimate query cost before execution. Queries that exceed a cost threshold (indicating a potential full table scan or cartesian join) are rejected or flagged for human review. This prevents runaway queries that could overload the database.

When validation fails, the most effective recovery strategy is to feed the error message back to the LLM and ask it to regenerate the query. Most validation errors (wrong column name, missing join condition) are easily correctable with one retry.

Multi-Turn SQL Conversations

Single-turn text-to-SQL -- one question, one query -- covers the simplest use cases. Production systems increasingly support multi-turn conversations where users iteratively refine their queries.

In a multi-turn flow, the system maintains a conversation context that includes: the original question, the generated SQL, the query results, and any follow-up questions. When the user says "now filter that to just the US region" or "break that down by month," the system must modify the previous query rather than generating a new one from scratch.

The technical challenge is context window management. Each turn adds the previous SQL and results to the prompt, consuming token budget. Production systems manage this by summarizing previous turns (replacing full result sets with row counts and column summaries), maintaining a running SQL query that accumulates modifications, and capping conversation depth (typically 5-10 turns before resetting context).

Multi-turn conversations also enable a powerful debugging pattern: when a query returns unexpected results, the user can ask "why are there NULL values in the revenue column?" and the system can inspect the query, identify the likely cause (a LEFT JOIN that produced unmatched rows), and suggest a correction. This iterative refinement loop makes text-to-SQL practical for exploratory analysis where the user doesn't know the exact question upfront.