A natural-language-to-SQL demo often ends when a query returns rows. Enterprise scrutiny starts there: how are net sales defined, which regions may this user access, which time zone determines last month, have joins inflated the amounts, and did the query consume excessive resources? Reliability requires a connected set of checks and evidence. This article follows a hypothetical retail analytics task to show how these constraints connect a question to an inspectable result.
1. Translate the question into testable business intent
The question “What was the year-over-year change in East China net sales last month?” contains at least five conditions: metric, region, month, comparison period, and authorization scope. Net sales could mean payments less refunds or revenue recognized on fulfillment. East China could refer to shipping address, store location, or sales organization. Choosing a column named revenue without resolving those definitions can produce a perfectly valid query that answers a different question.
Build a structured intent first: metric identifier and version, permitted dimensions, filters, time interval, attribution time field, and user identity. When approved business defaults exist, show them and continue. When a material definition is missing, ask a specific question that resolves it. Users should not need to restate their request in database terminology, and a model’s interpretation of a familiar word is not an organization’s formal definition.
For example, order O101 has a CNY 100 payment, two item rows worth CNY 60 and CNY 40, and a completed CNY 20 refund. Under the agreed net-receipts definition, its value is CNY 80. Summing payment amounts after joining the item rows may instead produce CNY 200 and then subtract refunds incorrectly. This small example separates three acceptance criteria: valid syntax, successful execution, and a correct business result.
2. Make the semantic layer control grain and calculation paths
A metric layer turns agreed meaning into reusable calculation constraints. dbt’s semantic-model documentation describes entities, dimensions, time dimensions, and metrics. Regardless of the tool, maintain versioned definitions for the sources of monetary values, refund statuses, excluded orders, currency treatment, supported dimensions, and time attribution. Record the scope and effective period of every change.
In this example, payments and refunds can first be aggregated to order grain and then combined into net receipts. A request to break the metric down by product also needs a rule for allocating refunds. Without a defensible allocation rule, the metric should not claim to support that dimension. Letting a model choose any tables that can be joined confuses structural connectivity with business comparability and produces precise-looking results that cannot be explained.
A useful division of responsibility lets the model match intent, select approved metrics, and explain limitations, while deterministic compilation or query construction implements established metrics. Exploratory questions can use a controlled ad hoc path, clearly marked as having an uncertified definition. A semantic layer is not proof of correctness: key uniqueness, historical dimension behavior, and declared join cardinality still require continuous validation against the data.
3. Retrieve business relationships, not a pile of table names
Enterprise databases may contain orders, orders_v2, order_archive, and order_test, all apparently relevant to an order question. Name similarity alone can retrieve deprecated or test tables. The retrieval catalog should include business purpose, domain, owner, certification status, field descriptions, enumeration meanings, key relationships, and update time. Prefer restricting retrieval to catalog objects the user is authorized to access.
Retrieve the business domain and metric first, expand the necessary table relationships, and then select the required columns. The context needs grain and valid join paths, not merely names. Payment records, refunds, and an order-region dimension may suffice here; including order items can create unnecessary opportunities for a wrong join. If sample values help explain codes, use only necessary, approved values rather than placing sensitive detail in the model’s context.
The original Spider 2.0 paper describes enterprise Text-to-SQL as a workflow requiring metadata, dialect-documentation, and project-knowledge retrieval. This supports treating retrieval as a separate engineering stage instead of continually increasing prompt length. Evaluate whether the correct tables, relationship edges, and metric descriptions were retrieved. If the context omits the refund definition, further tuning of SQL-generation instructions usually cannot recover the missing fact.
4. Put time definitions in both the query and the answer
Assume the question is asked on September 27, 2026, and the business time zone is Asia/Shanghai. Last month should mean the interval from local midnight on August 1 up to, but excluding, local midnight on September 1. An inclusive start and exclusive end avoid precision problems around the month’s final second. If storage uses UTC, controlled logic should convert those boundaries instead of letting the model casually use the database server’s current date.
Year-over-year comparison should also specify the same calendar month in 2025 rather than subtracting a fixed number of days. Counting payments in their payment month and refunds in their completion month yields a different result from attributing refunds back to the original order month. Both can be defensible, but the metric definition must choose a policy and the answer should state it briefly. Historical regional attribution must likewise distinguish the region at transaction time from the customer’s or store’s current region.
The data cutoff and business interval are separate fields. A query may cover all of August while the refund source is synchronized only through September 26, leaving room for later historical entries to revise the answer. Include a data version or cutoff marker and distinguish provisional from settled results. If the comparison period’s value is zero, return the agreed undefined or business-specific representation instead of substituting an arbitrary denominator to manufacture a percentage.
5. Enforce execution permissions in the database and gateway
An instruction to “generate only SELECT” is not a read-only boundary. Before execution, parse the full statement using the target dialect, allow only approved query structures, reject multiple statements, write operations, and unapproved functions, and parameterize external input. Give the execution identity only the required object-level read permissions and use an appropriate read-only transaction or controlled read-only source. A parser failure must not fall back to keyword matching.
PostgreSQL’s SET TRANSACTION documentation defines the commands restricted by a read-only transaction and describes this as a high-level notion of read-only. Combine it with least privilege; do not interpret it as complete isolation from arbitrary functions, external access, or resource consumption. The model must not choose a more privileged role, change protective session settings, or retry through a more powerful account. Those decisions belong to the execution service.
Test row-level permissions using real execution roles. PostgreSQL documents that superusers, roles with BYPASSRLS, and normally table owners bypass row security. Success under an administrator account does not prove that an East China manager can see only East China. Result-cache keys must also include effective authorization scope and policy version, with invalidation when permissions change. Reusing answers solely by question text can disclose one user’s result to another.
6. Make cost and retries part of the execution plan
Returning ten rows does not mean scanning ten rows. BigQuery’s cost documentation explains that LIMIT on non-clustered tables is not a scan-cost control and describes dry runs and maximum bytes billed for on-demand pricing. A gateway should use the target engine’s estimates and hard limits, require necessary partition filters, bound execution time and concurrency, and limit the result volume returned to the model.
Estimates also have limits. Some permission policies or query forms can leave pre-execution estimates incomplete; missing or zero estimates must not automatically mean free execution. Certified metrics can have routine budgets, while complex exploratory queries receive stricter runtime quotas, with actual consumption recorded. Validate thresholds against data size and user needs. They are service policies, not spending authority for a model to set on its own.
Automatic repair loops need an aggregate budget. Syntax errors may be repaired a limited number of times using sanitized error details. Permission failures and budget refusals must not trigger attempts to rewrite around the restriction. Record each query version, error category, scan consumption, and final selection. Otherwise, a request that looks like one interaction in the interface can become a series of expensive probes while the user remains uninformed about why it failed.
7. Test business correctness with counterexamples
Report syntax validity, execution success, authorization compliance, and business-result correctness separately. Different SQL text can yield equivalent results, while similar text can fail at a boundary. Build business-approved questions, metric definitions, and expected results. Fix the data snapshot and authorization role, then compare aggregate values, grouping sets, null behavior, ordering requirements, and permitted numeric tolerances.
A correct answer on one ordinary sample can still be accidental. Include refunds across months, multiple items per order, regions with no sales, duplicate dimension keys, canceled orders, multiple currencies, and a zero comparison-period value. For O101, adding an item row without changing the order total must leave net receipts at CNY 80. Such invariant tests address the business objective more directly than checking that generated SQL contains SUM.
Test whether the system recognizes when it cannot answer. Missing material definitions, unauthorized regions, or incomplete data may call for one clarifying question, refusal of the unauthorized portion, or a bounded partial result. Track correct answers, incorrect answers, justified abstentions, and unnecessary abstentions separately. A superficial success rate should not reward a model for producing a confident number in every situation.
8. Deliver verifiable evidence alongside the answer
A usable answer might state that East China net receipts in August 2026 were a given amount, with a specified year-over-year change; payments are attributed by occurrence date, refunds by completion date, store region is taken at transaction time, and data is current to a stated cutoff. Populate the numbers from the actual execution result. Presenting definitions, intervals, and freshness in business language helps users judge suitability for analysis more than immediately displaying a large SQL statement.
For investigation, expose the query identifier, metric version, authorization scope, referenced data objects, executed statement, and resource consumption. Generate explanations from the parsed query plan and result metadata, not from a model’s recollection of filters it may never have used. Audit records should distinguish user intent, model candidates, gateway rewrites, and final execution so that an error can be traced to the responsible stage.
Begin deployment with a small set of stable metrics, clear permissions, and results that can be reconciled with existing reports. Expand domains using observed failure cases. Validate retrieval, definitions, execution, and evaluation before widening question coverage. A reliable Text-to-SQL service produces correct answers, makes their basis understandable, and stays within an explicit boundary when evidence is insufficient. That is a more useful enterprise capability than simply generating more SQL.