Building Production-Grade Text-to-SQL: From Natural Language to Trustworthy Data Analysis

Introduction: Executable SQL ≠ Trustworthy Analysis

An operations analyst asks: "What is the week-over-week change in East China payment GMV last week?" A demo system can quickly generate SQL, return a number, and plot a trend. But whether it can support business decisions depends on deeper questions: Is GMV pre-payment or post-refund? Does "last week" mean a complete calendar week? Does East China refer to order region or customer current region? Is the result constrained by the user's tenant and column permissions? What is the data freshness timestamp?

The task of production-grade Text-to-SQL is not to generate an executable SQL statement, but to answer an analytical question within authorized scope, with explicit definitions and auditable evidence. The model handles understanding and candidate generation; the engineering system defines semantics, enforces permissions and budgets, validates execution, and retains evidence.

This article uses the "East China weekly payment GMV YoY" case throughout, providing an implementation path from semantic contracts, controlled query compilation, secure execution, to evaluation, auditing, and rollout/rollback. The approach: make one business domain reliable first, then expand capability boundaries.

1. Redefining Success: From SQL Success to Analytical Success

1.1 Three Levels of Success

Syntax Success : SQL parses and compiles. Still may be wrong : column or dialect valid but business definition incorrect.

Execution Success : Query returns within resource budget. Still may be wrong : JOIN explosion, time-window misalignment, permission policy misconfiguration.

Analytical Success : Correctly answers the authorized question, with definition, freshness, and evidence. Still requires continuous monitoring of data, semantic, and policy changes.

Product metrics should target the third level. Counting only "database didn't error" rates silently counts many errors as success.

Public benchmarks (Spider 2.0 for enterprise SQL workflows, BIRD for database content grounding and query efficiency) help compare research approaches but cannot replace enterprise acceptance. They show real tasks need metadata, business knowledge, and execution environment — not just "question + DDL". Do not cite an "accuracy" without dataset, identity, time anchor, model version, and evaluation date; never equate public leaderboard scores with internal platform availability.

1.2 Phase One: Narrow Product Boundaries

Phase one supports only read-only analysis on governed datasets (e.g., orders, sales, inventory). Write operations, database administration, external access, and arbitrary script execution are excluded from the same entry point. Requests like "mark order as paid" must be rejected; natural language data writes are a separate product requiring approval, preview, idempotency, compensation, audit, and risk grading — not safe just because they contain a WHERE clause.

Similarly, not all SQL needs support initially. Prioritize controlled filtering, aggregation, approved JOINs, sorting, and limited window functions; recursive CTEs, external tables, user-defined functions, and dialect extensions stay disabled until dedicated evaluation passes.

2. Semantic Contracts: Define "What Is the Answer" First

2.1 Metric Definitions Must Be Compilable and Versioned

"GMV maps to paid_amount" is not a complete definition. A governable metric requires at least: grain, status, time semantics, currency, refund rules, dimension attribution, data source, and version.

metric_id: paid_gmv
metric_version: 3
owner: sales_data_team
source: analytics.order_fact
grain: one_row_per_tenant_order
measure: sum(paid_amount)
status_filter: paid_status = 'PAID'
time_field: paid_at
time_type: timestamptz
business_timezone: Asia/Shanghai
currency: CNY
refund_policy: gross_before_refund
region_dimension: region_code_at_order
excluded_records: is_test = false

This GMV is CNY, pre-refund, payment-based; not a universal standard. Metrics must be confirmed by business and data owners and linked to data source change processes. time_type must not be omitted. Event fact times should be stored as timestamptz (true instant); business day/week concepts are computed via business_timezone. If legacy fields are timestamp without time zone, the controlled compiler must explicitly state source timezone and convert — never rely on session TimeZone or implicit casts, else identical queries may yield different results due to session config or DST rules.

High-frequency, fixed-definition questions should have the model output metric ID, dimensions, and filters, then compile SQL via semantic layer or templates. Free SQL is reserved for tasks beyond supported semantic expressions and must still pass capability profiling and safety checks.

2.2 Parse Natural Language into Server-Owned Intent

Assume request at 2026-10-11 Beijing time, week starts Monday, "last week" = most recent complete calendar week:

{
  "metric_id": "paid_gmv",
  "metric_version": 3,
  "region_code": "EC",
  "timezone": "Asia/Shanghai",
  "current_period": ["2026-09-28T00:00:00+08:00", "2026-10-05T00:00:00+08:00"],
  "previous_period": ["2026-09-21T00:00:00+08:00", "2026-09-28T00:00:00+08:00"],
  "comparison": "week_over_week"
}

Tenant ID, user identity, roles, accessible objects, and authorization scope come solely from server-side auth context; model and user text cannot override. Time intervals use half-open [start, end) to avoid missing sub-seconds at 23:59:59. Parse natural language in business timezone first, then convert to timezone-aware parameters; in DST regions, do not hardcode a day as 24 hours.

2.3 Ambiguity Should Trigger Clarification, Not Guessing

When "GMV" has both payment and post-refund definitions, ask; when user omits year, currency, or business domain and defaults are unreliable, ask. User answers business meaning; system maps to internal schema.

Before execution, show a concise definition card, e.g.:

Payment GMV; CNY; pre-refund; order-time region; Beijing-time complete calendar week; results cover only East China data you are authorized to view.

High-frequency metrics can use pre-confirmed defaults, but results must still display metric version and definition.

3. Metadata Retrieval: Retrieve the Subgraph That Can Fulfill the Task

3.1 Retrieval Objects Are More Than Tables and Columns

Structural Metadata : tables, columns, types, keys, views, dialect. Managed by automated collection and versioning.

Business Semantics : metrics, synonyms, grain, dimension meanings. Managed by owner review.

Data Characteristics : null rates, controlled enums, coverage time, update watermarks. Managed by periodic profiling; sensitive fields excluded.

Workload Knowledge : vetted SQL, recommended JOINs, failure cases. Managed by governance accumulation.

Permission Metadata : user-visible objects, row/column policies, masking rules. Managed by real-time or short-TTL fetch.

Column samples only usable under authorization and masking. Phone, email, ID numbers must not enter prompts for grounding; statistical features of amounts, dates, IDs are usually safer and more useful.

3.2 Recommended Retrieval Flow

Determine visible object set based on identity, tenant, and business domain.

Recall candidates via exact names, synonyms, full-text, and vector search.

Re-rank by structured intent, retaining objects covering metrics, dimensions, and filters.

Complete approved JOIN paths, join keys, tenant keys, time fields, and permission fields.

Output versioned schema subgraph and semantic descriptions within context budget.