Building a Text-to-SQL Evaluation Pipeline: Test AI Queries Before Trusting Them

Large language models can translate natural-language questions into SQL queries, making business data more accessible to people who do not know SQL. But generating a query that looks correct is not the same as generating one that returns the correct answer.

A query can execute successfully and still produce misleading results. It might use the wrong join, apply an incorrect date filter, calculate an average instead of a weighted average, or silently exclude relevant records.

In an enterprise environment, these mistakes can influence revenue forecasts, customer segmentation, operational reports, and executive decisions.

The solution is to treat AI-generated SQL as software that must be tested before it is trusted.

This tutorial walks through a practical text-to-SQL evaluation framework using Python and SQLite. We will define a test database, create natural-language questions with expected results, generate SQL with a language model, validate queries, compare outputs, measure execution accuracy, and build regression tests for future model or prompt changes.

Why Text-to-SQL Needs a Dedicated Evaluation Framework

Text-to-SQL systems convert natural-language requests into executable database queries. Although the concept is straightforward, the underlying task involves several distinct challenges:

• Understanding what the user actually wants to measure.
• Mapping business terminology to the correct database tables and columns.
• Selecting appropriate joins, filters, and aggregation functions.
• Generating syntactically valid SQL for the target database dialect.
• Returning results that match the intended business logic.
• Operating within the user's access permissions and data governance rules.

Research benchmarks demonstrate why these challenges matter.

The Spider benchmark introduced a cross-domain text-to-SQL evaluation setting in which models must generalize across different databases and schemas rather than simply memorize query patterns.

The BIRD benchmark, published in 2023, expanded this challenge by incorporating large databases, real-world values, external knowledge, and computational efficiency.

More recently, the research paper Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows, published in 2024 and presented at ICLR 2025, introduced 632 enterprise-level workflow problems involving complex schemas, multiple SQL dialects, and real-world data environments. The authors reported a substantial performance gap between earlier benchmarks and enterprise workflows.

The implication is important: strong performance on a simplified SQL benchmark does not guarantee reliability against production databases.

An evaluation pipeline helps teams measure that gap using their own schemas, business questions, and expected outcomes.

1. Define a Test Dataset and Database Schema

Start with a small database that represents a meaningful business scenario.

For this example, we will use a sales database containing customers and orders. The goal is to evaluate questions such as:

• What is the total revenue?
• Which customers generated the most revenue?
• How many orders were placed in a given period?
• What is the average order value?

Create a file named setup_db.py:
`import sqlite3

DB_PATH = "sales.db"

def setup_database():
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

cursor.executescript("""
    DROP TABLE IF EXISTS orders;
    DROP TABLE IF EXISTS customers;

    CREATE TABLE customers (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        region TEXT NOT NULL
    );

    CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL,
        amount REAL NOT NULL,
        status TEXT NOT NULL,
        order_date TEXT NOT NULL,
        FOREIGN KEY (customer_id) REFERENCES customers(id)
    );
""")

cursor.executemany(
    "INSERT INTO customers VALUES (?, ?, ?)",
    [
        (1, "Acme Corp", "North"),
        (2, "Beta Inc", "South"),
        (3, "Gamma Ltd", "North"),
    ],
)

cursor.executemany(
    "INSERT INTO orders VALUES (?, ?, ?, ?, ?)",
    [
        (1, 1, 1000.00, "completed", "2026-01-10"),
        (2, 1, 500.00, "completed", "2026-02-15"),
        (3, 2, 750.00, "completed", "2026-02-20"),
        (4, 2, 200.00, "cancelled", "2026-03-05"),
        (5, 3, 1200.00, "completed", "2026-03-12"),
    ],
)

conn.commit()
conn.close()

if name == "main":
setup_database()
print("Database created successfully.")`

Run the script:
python setup_db.py

The resulting database is intentionally small, deterministic, and easy to inspect.

For a production evaluation suite, expand this dataset to include edge cases such as null values, duplicate records, empty result sets, date boundaries, multiple currencies, and customers with no orders.

The dataset should be version-controlled so that every evaluation run uses a known baseline.

2. Create Natural-Language Questions With Expected Results

The next step is to create a test suite that connects user intent with a verified answer.

Each test case should contain:

1. A natural-language question.
2. The expected result.
3. Optionally, a reference SQL query.
4. Metadata describing the business rule being tested.

For our example, create test_cases.py:
TEST_CASES = [
{
"id": "revenue_001",
"question": "What is the total revenue from completed orders?",
"expected": [(3450.0,)],
"reference_sql": """
SELECT SUM(amount)
FROM orders
WHERE status = 'completed'
""",
},
{
"id": "customer_001",
"question": "Which customer generated the highest revenue?",
"expected": [("Gamma Ltd", 1200.0)],
"reference_sql": """
SELECT c.name, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 1
""",
},
{
"id": "orders_001",
"question": "How many completed orders were placed in February 2026?",
"expected": [(2,)],
"reference_sql": """
SELECT COUNT(*)
FROM orders
WHERE status = 'completed'
AND order_date >= '2026-02-01'
AND order_date < '2026-03-01'
""",
},
]
Notice that the expected results are defined independently of the language model.

This distinction is fundamental. If the same model generates both the SQL query and its expected answer, the evaluation can reproduce the model's own mistakes instead of detecting them.

Test business semantics, not just SQL syntax

Suppose a user asks for completed-order revenue. A model might generate:
SELECT SUM(amount) FROM orders;

This query is valid SQL, but it includes a cancelled order. Its result is $3,650 rather than the correct $3,450.

A syntax validator will not catch this error. An execution-based comparison will.

For more complex datasets, expected outputs should be verified by domain experts or independently reviewed reference queries. Avoid treating a single reference query as infallible: the reference logic itself can contain defects.

3. Generate SQL Queries With an LLM

Now introduce the language model. The model should receive the user's question and the relevant database schema, not unrestricted access to the entire production environment.

A simple generation interface could look like this:
def generate_sql(question, schema, llm_client):
prompt = f"""
You are a SQL query generator.

Generate one read-only SQLite SELECT query
that answers the user's question.

Database schema:
{schema}

User question:
{question}

Rules:
- Return SQL only.
- Use only the tables and columns in the schema.
- Do not generate INSERT, UPDATE, DELETE, DROP,
  ALTER, CREATE, or other data-modifying statements.
- Do not invent columns or tables.
"""

response = llm_client.generate(prompt)
return response.strip()

The llm_client.generate() method is a placeholder for your provider's SDK or internal model endpoint. Its implementation will depend on the model you choose.

For this evaluation, the model must not receive the expected output. Otherwise, the test measures whether it can reproduce a known answer rather than independently interpret the question.

A fixed prompt, model version, temperature, and schema snapshot also make the results easier to reproduce.

Include difficult and ambiguous questions

A good test suite should include more than straightforward aggregations.

Consider adding questions involving:

• Joins across multiple tables.
• Grouping and sorting.
• Date ranges and time zones.
• Null handling.
• Distinct counts.
• Percentages and ratios.
• Comparisons between time periods.
• Ambiguous business terminology.
• Requests for data the user is not authorized to access.

For ambiguous questions, the correct behavior may be to ask for clarification rather than generate a query based on an unsupported assumption.

4. Validate SQL Syntax and Restrict Execution Permissions

Never execute model-generated SQL against a production database simply because it looks plausible.

A safer evaluation pipeline separates query generation from query execution and applies multiple controls.

Parse and inspect the query

Libraries such as SQLGlot can parse SQL and help identify the statement type.

import sqlglot

def validate_sql(sql):
try:
statements = sqlglot.parse(sql, read="sqlite")
except sqlglot.errors.ParseError:
return False

if len(statements) != 1 or statements[0] is None:
    return False

return statements[0].key == "SELECT"

This is a basic illustration, not a complete security boundary. A production validator should explicitly handle permitted statement types, read-only CTEs, dialect-specific syntax, and any functions or operations that must be prohibited.

For example, a WITH statement can still modify data in dialects that support data-modifying CTEs. Validation must consider the actual database dialect rather than relying on a simple string prefix.

Use read-only database access

For SQLite, open the database in read-only mode:
import sqlite3

def open_readonly_database(path):
uri = f"file:{path}?mode=ro"
return sqlite3.connect(uri, uri=True)

You should also:

• Use a dedicated database account with read-only privileges for server databases.
• Restrict accessible schemas, tables, and columns.
• Apply query timeouts, row limits, and resource limits.
• Use parameterized SQL when passing user-provided values.
• Log generated SQL, validation outcomes, execution duration, and failures.
• Keep credentials and sensitive data out of prompts and logs.
• Run evaluations against synthetic data or isolated snapshots whenever possible.

A read-only connection prevents ordinary write operations through that connection, but it does not automatically prevent every expensive query or guarantee that all accessible information is safe to disclose.

Security validation and semantic evaluation are separate requirements. Both must pass.

5. Compare Returned Results With Expected Outputs

Once a query passes validation, execute it against the test database and compare its output with the expected result.

Create evaluate.py:
import sqlite3
from setup_db import DB_PATH
from test_cases import TEST_CASES

def evaluate_query(sql, expected):
conn = sqlite3.connect(
f"file:{DB_PATH}?mode=ro",
uri=True,
)

try:
    cursor = conn.execute(sql)
    actual = cursor.fetchall()

    # This example compares ordered rows exactly.
    passed = actual == expected

    return {
        "passed": passed,
        "actual": actual,
        "expected": expected,
        "error": None,
    }

except sqlite3.Error as exc:
    return {
        "passed": False,
        "actual": None,
        "expected": expected,
        "error": str(exc),
    }

finally:
    conn.close()

For a fully automated run, pass each question to your LLM adapter, validate the generated SQL, and then call evaluate_query().

The code above uses exact row comparison because the sample expected outputs have a defined order. That is not appropriate for every evaluation.

Choose a comparison strategy based on the question:
** Ordered results:** Compare rows in their expected order when sorting matters.
** Unordered results:** Normalize row order when SQL does not guarantee it.
** Numeric results:** Apply an explicit tolerance when floating-point arithmetic is involved.
** Aggregations:** Verify the metric, filters, grouping, and calculation logic.
** Business-specific results:** Check relevant invariants, such as totals reconciling across categories.

For instance, a query that returns the correct total revenue but includes the wrong customers may still be wrong. A single scalar result is not always enough to validate business semantics.

For higher-confidence testing, combine expected-result comparisons with independent business-rule assertions.

6. Measure Execution Accuracy and Failure Rates

A text-to-SQL evaluation framework should report more than the number of queries that executed successfully.

Define execution accuracy as:

Execution Accuracy

Correct query results
Total test cases
×
100
\text{Execution Accuracy} = \frac{\text{Correct query results}}{\text{Total test cases}} \times 100

Track syntax failures, execution errors, incorrect results, security rejections, and timeouts separately.

A basic reporting function:
def summarize_results(results):
total = len(results)

if total == 0:
    return {
        "total": 0,
        "execution_accuracy": 0.0,
        "failure_rate": 0.0,
    }

passed = sum(r["passed"] for r in results)
failed = total - passed

return {
    "total": total,
    "passed": passed,
    "failed": failed,
    "execution_accuracy": round(
        passed / total * 100, 2
    ),
    "failure_rate": round(
        failed / total * 100, 2
    ),
}

The results list should contain the outcomes of the complete pipeline, including rejected and unexecuted queries. Otherwise, excluding failures can artificially inflate accuracy.

For example, if a model produces correct results for 82 out of 100 questions, its execution accuracy is 82%. The remaining 18 cases should be investigated, not hidden behind a successful SQL-generation rate.

7. Add Regression Tests for Prompt or Model Changes

Evaluation should be part of the development lifecycle, not a one-time experiment.

A prompt change, model upgrade, schema modification, or retrieval adjustment can improve one group of questions while breaking another.
Store your test cases in version control and run the suite whenever one of these components changes.

A simple CI workflow could be:

Pull request
|
v
Load fixed test dataset
|
v
Generate SQL for each question
|
v
Validate SQL and security rules
|
v
Execute against isolated database
|
v
Compare actual and expected results
|
v
Publish evaluation report
|
v

Pass or fail release gate

Define release thresholds based on business risk rather than selecting an arbitrary accuracy target. For example, a team might require zero unauthorized-query attempts to pass the security suite and require execution accuracy to remain above an agreed baseline.

Keep a separate set of hidden or holdout questions to reduce overfitting to the evaluation dataset. Include representative edge cases and track performance by difficulty, query type, and business domain.

For more advanced evaluations, consider the following research resources:

• Spider 2.0: Enterprise-level text-to-SQL workflows and database environments.
• BIRD: Evaluation of text-to-SQL systems against realistic databases and values.
• PICARD — Parsing Incrementally for Constrained Auto-Regressive Decoding from Language Models: Research on constraining language-model decoding to produce syntactically valid SQL and other formal-language outputs.

PICARD is particularly relevant to the generation stage: constraining output can reduce invalid SQL, but syntactic validity alone does not establish that a query answers the intended question.

8. Turn the Pipeline Into a Repeatable Engineering Practice

A mature text-to-SQL evaluation framework should have four layers:

1. Dataset layer: Versioned database snapshots, schemas, questions, expected results, and business rules.

2. Generation layer: A consistent interface for testing different models, prompts, and schema-context strategies.

3. Validation layer: SQL parsing, permission checks, query limits, controlled execution, and result comparison.

4. Reporting layer: Accuracy, failure categories, latency, cost, regression history, and release thresholds.

Keep these layers independent. This makes it possible to compare two models using the same dataset or test a new prompt without changing the evaluation logic.

The same approach also works for AI analytics agents that generate SQL behind a natural-language interface. Instead of assuming that a fluent answer is correct, teams can verify the query and its underlying result before presenting it to users.

Conclusion

Text-to-SQL makes data more accessible, but accessibility without verification can introduce a new class of business risk.

A reliable evaluation pipeline starts with a controlled dataset and independently verified expected results. It generates queries with an LLM, validates their syntax and permissions, executes them in an isolated environment, compares outputs, and tracks regressions over time.

The goal is not simply to make an AI model generate more SQL. It is to establish measurable evidence that the system can answer business questions accurately, securely, and consistently.

As natural-language analytics becomes part of enterprise workflows, evaluation should be treated as a core engineering capability rather than an optional quality check.

How Rootlenses Insight Fits Into AI-Powered Analytics

The same principle applies when organizations want business teams to explore enterprise data without writing SQL manually.

Rootlenses Insight is an AI-powered data analytics solution that allows users to ask questions in natural language and obtain insights from business data without requiring advanced SQL knowledge.

For teams building or adopting conversational analytics, the important consideration goes beyond query generation. The solution must also account for the underlying data sources, access permissions, data governance, and the reliability of the resulting analysis.

A disciplined text-to-SQL evaluation pipeline provides one practical way to test these capabilities before relying on AI-generated answers in business decisions.

The takeaway: Make AI-generated queries measurable, testable, and auditable before putting them to work on enterprise data.