Testing a text to SQL feature (2026)

TL;DR

Testing a text to SQL feature requires more than checking whether generated SQL parses. Verify the interpreted intent, execute safely against adversarial fixtures, compare results with a reference or invariant, enforce row and column authorization, inspect query plans and limits, and test clarification for ambiguous requests.

Key Takeaways

• Judge business-answer correctness and result equivalence, not exact SQL string similarity.
• Test schema linking, joins, filters, grouping, time boundaries, nulls, duplicates, and business definitions as separate failure sources.
• Run generated statements through deterministic validation and a least-privilege read-only database identity in an isolated environment.
• Treat authorization as a data-layer invariant because a syntactically valid SELECT can still expose forbidden rows or columns.
• Require clarification when a question has multiple materially different valid interpretations.
• Use adversarial seed data that makes plausible wrong queries return visibly different results.
• Version the schema, semantic layer, prompt, model, validator, fixtures, and execution engine in every regression result.

Testing a text to SQL feature means proving that a natural-language question becomes the correct business answer through authorized, bounded database work. Valid SQL is not enough. A query can parse, run, and return plausible rows while using the wrong join, time zone, status definition, tenant filter, denominator, or grain.

The best test strategy separates intent interpretation, schema linking, SQL construction, policy validation, execution, result presentation, and conversation behavior. It uses carefully designed data that exposes subtle errors, compares result sets or business invariants instead of raw query strings, and runs every generated statement behind deterministic controls. The final oracle must reflect documented product definitions, authenticated user permissions, the supported database dialect, and the exact data snapshot used for evaluation.

TL;DR

Use the database as one oracle, but never let generated SQL reach a privileged production connection.

1. What Testing a Text to SQL Feature Covers

A text-to-SQL application usually receives a question, user and tenant context, schema or semantic metadata, examples, and conversation history. A model or planner chooses schema objects and produces SQL. A validator inspects it, a database executes it, and the application formats the result or explanation. Each boundary needs its own tests.

Functional success has several parts. The system understood the intended metric and grain. It selected authorized tables and columns. It applied correct business definitions. The query used the right joins, filters, grouping, ordering, and limits. Execution stayed within resource policy. The displayed answer preserved units, null meaning, precision, and provenance.

Security testing is not limited to obvious DROP TABLE prompts. A read-only SELECT can leak salaries, personal data, another tenant's records, hidden soft-deleted rows, or raw identifiers. A function called inside a query can have side effects in some databases. Comments, common table expressions, subqueries, quoted identifiers, and dialect features can bypass simplistic prefix checks. Enforce safety with parsing or database-native controls and least privilege.

Treat the generated query as untrusted code. A model's system instruction to produce only safe SELECT statements is not an authorization mechanism. For the underlying testing vocabulary, see the SQL testing interview guide and database testing checklist.

2. Define Correctness Beyond Exact-Match SQL

Many SQL statements are semantically equivalent. Join order can differ, a subquery can replace a common table expression, predicates can be reordered, and aliases can change. Exact string match therefore underestimates valid output. At the same time, execution match on one weak dataset can reward an incorrect query that happens to return the same rows.

Use several oracle types:

For each case, define acceptable interpretation, reference query or expected rows, required policy filters, forbidden objects, ordering semantics, precision, and whether clarification is required. Compare multisets when order is not requested. Compare ordered sequences when the question says top, latest, or specifies sorting. Use tolerances only for documented numeric behavior.

Do not let a reference query become unquestioned truth. Review it with a domain owner and run it against fixtures designed to challenge the intended rule. The oracle can contain the same misunderstanding as the generator.

3. Design a Schema and Data Fixture That Exposes Errors

A useful evaluation database is small enough to inspect and adversarial enough to distinguish plausible mistakes. Include tables with similar names, columns with overlapping meaning, optional relationships, many-to-many joins, slowly changing status, soft deletion, tenant IDs, timestamps around boundaries, null values, duplicate-looking rows, zero values, and currencies or units.

Suppose an order domain has orders, order_items, payments, customers, and refunds. Seed an order with two items so a careless join to payments doubles revenue. Seed a partial refund so gross and net revenue differ. Place one transaction at midnight UTC and define a business time zone. Add canceled and pending orders. Give two tenants the same customer name. Add a customer with no orders and an order with no successful payment.

Every row should earn its place by killing a class of wrong query. Document that purpose. A fixture containing only one clean row per table cannot reveal duplicate amplification, outer-join loss, null handling, or filter placement. Random data can supplement the suite, but handcrafted minimal counterexamples provide clear diagnosis.

Test schema evolution too. Rename a column in the semantic layer, add a similarly named metric, deprecate a table, change an enum, or introduce a new relationship. The system should use the version it received and fail clearly when metadata is stale. Cache invalidation is part of the feature.

4. Cover SQL Logic and Business Semantics

Organize cases by reasoning pattern rather than by screens. Basic selection covers equality, ranges, sets, patterns, nulls, and boolean flags. Relational cases cover inner and outer joins, anti-joins, many-to-many relationships, missing children, and duplicate amplification. Aggregation covers count versus count distinct, sum, average, ratios, group grain, having, and window functions.

Time cases are especially important. Test inclusive and exclusive boundaries, current partial periods, week start, fiscal calendars, daylight-saving transitions, UTC storage versus business-local interpretation, and effective-dated records. A question such as revenue last month is incomplete until the product defines revenue, calendar, time zone, status, refunds, and currency conversion.

Business vocabulary needs a semantic contract. Active customer, churn, bookings, revenue, conversion, and open ticket rarely map to one obvious column. Provide approved definitions or a semantic layer, then assert that generated queries apply them. When a definition is absent or has alternatives, the system should clarify rather than invent policy.

Metamorphic testing adds strong oracles. Adding an unrelated tenant must not change the current tenant's result. Duplicating a dimension row should not change a metric if the relationship is supposed to be unique. Moving a timestamp across a month boundary should move exactly one record. Adding a canceled order should not increase completed revenue. These transformations expose logical defects without requiring a new full expected table each time.

5. Test Ambiguity and Clarification Behavior

Natural language is often underspecified. Show top customers could mean highest lifetime revenue, current-year revenue, order count, or account value. Last quarter could mean calendar or fiscal quarter. Customers in India could refer to billing address, shipping address, legal entity, or current profile. If interpretations produce materially different answers, silently choosing one is risky.

Label ambiguous cases with the acceptable response: ask a targeted question, state a documented default, or present clearly named alternatives. The clarification should focus on the missing decision, such as Should revenue be gross or net of refunds?, not ask the user to restate everything. Once answered, verify the SQL incorporates the choice and the displayed result states it.

Test conversation context. Follow Show revenue by region with Only enterprise customers, Now compare with last year, and Remove India. Verify referents, filters, time ranges, and grain update correctly without retaining obsolete conditions. Start a new conversation and confirm previous private context does not leak. Switch tenant or role and ensure the system revalidates every carried assumption.

Adversarial ambiguity matters too. A user may ask the model to ignore definitions, reveal hidden schema, omit tenant filters, or disguise a mutation as analysis. Untrusted instructions cannot override database policy. The UI should distinguish generated interpretation, executed query, and result so a user can review what actually happened.

6. Enforce Authorization and Data Privacy

Authorization belongs below the language model. Decide which schemas, tables, columns, rows, functions, and aggregations each principal may access. Sensitive columns may remain forbidden even when aggregated. Small groups may require suppression to prevent re-identification. Tenant and regional rules may be implemented with database roles, views, row-level security, a semantic service, or a policy-aware query rewriter. Test the actual chosen boundary.

Create canary rows and columns for another tenant and a higher-privilege role. Ask directly, indirectly, through aggregates, with alternative names, and through conversation follow-ups. Confirm forbidden objects never appear in the validated query, execution plan, result, explanation, error, trace, cache, or export. A database error that reveals a hidden table name is still information disclosure.

Verify identity propagation. The database session or policy service must receive the authenticated principal, not a user ID invented in the prompt. A generated WHERE tenant_id = 7 is not equivalent to enforced row security because the model can omit or change it. Test connection pooling to ensure session context is reset between users.

Caching requires authorization-aware keys and invalidation. Two users asking the same words may have different permitted results. Test role changes, tenant switches, revoked access, and expiring entitlements. Observability data needs the same care because generated SQL and result samples can contain sensitive values.

The API authorization testing guide provides reusable broken-object and broken-function authorization patterns for the services surrounding the database.

7. Validate and Execute Generated SQL Safely

Use multiple independent controls. Parse exactly one statement with a dialect-aware parser in production. Allow only approved query forms and schema objects. Reject mutations, DDL, transaction control, comments or functions your policy forbids, and unresolved identifiers. Add a server-side row cap where semantics permit, a statement timeout, resource groups, and cancellation. Then execute through a dedicated least-privilege read-only database role against a replica or governed query service when possible.

String checks such as startsWith('SELECT') are insufficient. A query can include comments, writable common table expressions in some systems, dangerous functions, or multiple statements. Conversely, a valid read-only query may begin with WITH. Use parser output and database authorization, with the database control acting as the backstop.

For PostgreSQL, a session can use a read-only transaction and local timeout, but the connecting role should also lack write and sensitive-object privileges:

BEGIN READ ONLY;
SET LOCAL statement_timeout = '3s';
SELECT region, SUM(net_amount) AS revenue
FROM analytics.authorized_orders
WHERE ordered_at >= DATE '2026-06-01'
  AND ordered_at < DATE '2026-07-01'
GROUP BY region
ORDER BY revenue DESC
LIMIT 100;
COMMIT;

Do not concatenate user values into queries outside the generated-statement boundary. If the architecture separates a query template from literal parameters, bind parameters through the database driver. Log validation decisions and a normalized query fingerprint, while protecting literal sensitive values.

8. Test Performance, Availability, and Result Presentation

A read-only query can still exhaust resources. Test Cartesian joins, broad scans, high-cardinality grouping, expensive regex, recursive queries, huge offsets, missing predicates, adversarial sort, and repeated retries. Capture planning and execution time, rows scanned, rows returned, temporary storage, cancellation behavior, and concurrent workload impact. Use representative statistics because plans on tiny fixtures differ from production-like data.

Set query budgets by use case. An interactive chart and a scheduled analyst report may have different time and row limits. When a query exceeds policy, the system should cancel it, return a useful bounded explanation, and avoid automatic retries that multiply load. Test client disconnects and model timeouts to confirm database work is canceled.

Presentation can introduce semantic errors after correct SQL. Verify column labels, units, currency, decimal precision, percentages, null versus zero, time zone, sort order, pagination, truncation, and chart choice. If only the first 100 rows are shown, the UI must not imply the result is complete. If a textual summary is model-generated from rows, test that summary as another grounding step.

Expose enough provenance for review: interpreted question, filters, time range, metric definition, freshness, and optionally the SQL for authorized users. A polished chart without those details can make a subtle query error harder to notice.

9. A Runnable Read-Only Evaluation Harness

This Python program uses the standard sqlite3 API to create adversarial fixtures, block write-oriented operations with an authorizer, limit virtual-machine steps, execute candidate SQL, and compare unordered results. Save it as text_to_sql_eval.py and run python text_to_sql_eval.py. SQLite is a compact test oracle, not a substitute for dialect-specific integration tests.

import sqlite3
from collections import Counter

connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  net_amount_cents INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 10, 'completed', 5000),
  (2, 10, 'completed', 7000),
  (3, 10, 'cancelled', 9000),
  (4, 20, 'completed', 990000);
""")

DENIED = {
    sqlite3.SQLITE_INSERT, sqlite3.SQLITE_UPDATE, sqlite3.SQLITE_DELETE,
    sqlite3.SQLITE_CREATE_TABLE, sqlite3.SQLITE_DROP_TABLE,
    sqlite3.SQLITE_ALTER_TABLE, sqlite3.SQLITE_ATTACH, sqlite3.SQLITE_DETACH,
}

def authorize(action, _arg1, _arg2, _database, _trigger):
    return sqlite3.SQLITE_DENY if action in DENIED else sqlite3.SQLITE_OK

steps = 0
def limit_steps():
    global steps
    steps += 1
    return 1 if steps > 10_000 else 0

connection.set_authorizer(authorize)
connection.set_progress_handler(limit_steps, 100)

def rows(sql):
    global steps
    steps = 0
    cursor = connection.execute(sql)
    return cursor.fetchall()

def assert_unordered_equal(actual, expected):
    assert Counter(actual) == Counter(expected), (actual, expected)

candidate = """
SELECT SUM(net_amount_cents)
FROM orders
WHERE tenant_id = 10 AND status = 'completed'
"""
assert_unordered_equal(rows(candidate), [(12000,)])

try:
    rows("DELETE FROM orders")
    raise AssertionError("Write unexpectedly succeeded")
except sqlite3.DatabaseError:
    pass

print("Text-to-SQL safety and result checks passed")

Production validation must understand the target dialect and block more than this compact example. Run the same cases on the actual database engine with the same views, permissions, functions, collation, time-zone rules, and query planner settings used by the product.

10. Testing a Text to SQL Feature in CI

Build three tiers. A fast suite validates structured output, single-statement policy, schema allowlists, common intent cases, and small result fixtures. A dialect integration suite runs reference and candidate queries on the supported database with real authorization constructs. A scheduled robustness suite uses larger statistics, concurrency, paraphrases, multi-turn questions, malicious inputs, and model variability.

Store the natural-language question, identity, schema version, semantic definitions, expected interpretation, reference SQL or invariants, fixture version, generated SQL, validator decision, plan, result, explanation, prompt, model, and grader versions. Compare releases by case and slice. New access-policy violations, write attempts, severe semantic errors, or resource-budget breaches are hard failures regardless of average accuracy.

Use failure taxonomy: intent, schema linking, join, filter, aggregation, time, null, authorization, unsafe statement, execution, performance, presentation, conversation, or oracle defect. That taxonomy directs fixes and reveals systemic gaps. A prompt change will not repair missing row-level security, and an index will not repair a wrong revenue definition.

Add every production incident as a minimal sanitized counterexample plus nearby variations. Re-run after schema migrations, semantic-layer changes, model or prompt updates, validator changes, role-policy updates, database upgrades, and driver changes. Canary releases should compare query fingerprints, denials, latency, and answer differences before broad rollout.

11. Audit Explanations, Downloads, and Exports

The generated query is not the last risk boundary. Many products add a natural-language explanation, chart, CSV download, scheduled report, or shared link. Test each path with the same identity and data policy as interactive execution. A safe query can still become a leak if an export service uses a broader credential, a cached file has a guessable URL, or a shared chart preserves rows after access is revoked.

Compare explanations with the executed result and interpretation. Seed a result containing zero, null, negative, and truncated values, then verify the narrative does not convert null to zero, reverse a comparison, invent a trend, or claim completeness. Confirm downloads preserve precision, time zone, units, escaping, column order, and authorized row limits. Spreadsheet-formula prefixes in textual cells need an explicit export policy.

Test scheduled execution after role changes, schema changes, and expired credentials. The scheduler should reauthorize at execution time rather than reuse an old approval indefinitely. Audit events should connect the question, generated SQL fingerprint, principal, policy decision, execution, artifact, recipients, and deletion. End-to-end review prevents a correct SQL engine from being surrounded by an unsafe delivery workflow.

Interview Questions and Answers

Q: Why is valid SQL not enough for text-to-SQL quality?

A valid query can use the wrong business definition, join, filter, tenant, time range, or aggregation and still return plausible results. I evaluate interpretation, authorization, execution result, and presentation separately. Syntax is only one gate.

Q: How do you compare generated SQL with expected SQL?

I prefer result equivalence on adversarial fixtures plus structural and business-invariant checks. Exact match is useful only when one canonical statement is required. I compare unordered multisets unless the question makes order meaningful.

Q: How do you execute model-generated SQL safely?

I parse one statement, enforce an allowlist, validate objects and functions, and apply row and resource limits. Execution uses a least-privilege read-only identity, database-native authorization, timeouts, cancellation, and isolated or governed infrastructure. Prompt instructions are not a security boundary.

Q: How do you test ambiguous natural-language questions?

I label materially different interpretations and expect a focused clarification or a clearly stated approved default. After clarification, I verify the selected definition, filters, grain, and time range in both SQL and presentation.

Q: What data makes a strong text-to-SQL fixture?

I include duplicates, nulls, missing relationships, many-to-many joins, soft deletes, status variations, tenant canaries, boundary timestamps, refunds, and values that separate competing definitions. Each row is designed to make a common wrong query visibly fail.

Q: How do you test authorization in text-to-SQL?

I seed forbidden rows and columns and test direct, indirect, aggregate, and multi-turn requests across roles. I verify policy at query validation and database execution, then inspect results, errors, explanations, traces, and caches for leakage.

Q: Which metrics would you report?

I report execution and result accuracy, clarification quality, policy violation counts, severe semantic errors, timeout and budget failures, and slices by reasoning pattern and risk. I do not let aggregate accuracy hide a cross-tenant read or a consistently wrong financial metric.

Common Mistakes

• Treating any parseable or executable SQL as correct.
• Comparing only exact query strings and rejecting semantically equivalent statements.
• Using a clean toy dataset where wrong joins and filters return the same result.
• Letting the model choose or omit tenant filters instead of enforcing authorization below it.
• Checking that text starts with SELECT rather than parsing and using database-native read-only controls.
• Executing generated queries with the application's broad service credential.
• Ignoring functions, comments, common table expressions, multiple statements, and dialect-specific side effects.
• Testing totals without nulls, duplicates, refunds, canceled states, and time boundaries.
• Silently guessing ambiguous business terms instead of clarifying or stating defaults.
• Comparing unordered results when the user requested top or latest records.
• Omitting query-plan, timeout, cancellation, and concurrent-load tests.
• Showing a correct query result with misleading units, truncation, precision, or labels.

Conclusion

Testing a text to SQL feature is a combined language, database, security, and product-semantics problem. Define the interpretation, create adversarial fixtures, compare meaningful results, enforce authorization below the model, validate and bound every statement, and test the presented answer as carefully as the SQL.

Begin with ten high-value business questions and build the smallest dataset that makes common wrong interpretations return different answers. Run those cases under at least two roles, add hard safety gates, and version every component needed to reproduce the result.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• How to Add CI to a test framework (2026)
• How to Add logging to a test framework (2026)
• How to Add parallel execution to a framework (2026)
• How to Add reporting to a test framework (2026)
• How to Shift testing left in a sprint (2026)
• Testing a RAG chatbot end to end (2026)