Why LLM-Generated SQL Fails in Production: A Three-Layer Architecture for Reliable Text-to-SQL

Why Direct LLM-to-SQL Fails

In intelligent query systems, letting an LLM output SQL directly (Text2SQL) seems simple but hits four hard problems in production:

Unverifiable: SQL is a string; structural rules cannot easily validate semantic correctness (e.g., field existence, aggregation validity).

Unoptimizable: Post-generation query rewrites (permission injection, filter push-down) are impossible.

Unadaptable: Different database dialects (MySQL, Dameng, Oracle) require different syntax; a single model struggles to cover all.

Undebuggable: When SQL fails, root cause—model misunderstanding vs. wrong field name—is hard to isolate.

Therefore, an intermediate representation layer—DSL (Domain Specific Language)—decouples semantic understanding from syntax generation.

Overall Architecture

The engine comprises three layers:

Semantic Layer: Structured business knowledge (subject models, metric/dimension dictionaries, vector semantic index, business rule library).

LLM Layer (NL2DSL): Retrieval-augmented generation (RAG) + LLM produces structured DSL (JSON).

Execution Layer (DSL2SQL): Deterministic rule-based translation of DSL to dialect-specific SQL.

Semantic Layer: Structured Business Knowledge Foundation

Maintained jointly by data engineers and business users; the challenge is continuous updates, not one-time build. Strategies:

Automated extraction: Pull field Chinese names, enum values from data dictionaries, metadata platforms, existing SQL comments.

Manual enrichment: Annotate synonyms and calculation grains for key metrics (e.g., "sales amount") and confusing dimensions (e.g., "region").

Version control: Store semantic layer config in Git for diff and rollback.

Core components:

Subject models: Pre-defined fact tables, dimension tables, common metrics per business domain (sales, inventory, finance). Start with one wide table, split gradually.

Metric/Dimension dictionary: Contains field name, Chinese name, synonyms, data type, unit, business grain. Example: sales_amount – Chinese "销售额", synonyms "营收|GMV", unit "万元", grain "excludes refunds".

Vector semantic index: Embed field comments, synonyms, business descriptions for retrieval augmentation.

Business knowledge base: Long-text business rules, typical query SQL/DSL templates for few-shot retrieval.

Practical tip: Start with high-frequency query fields (20% fields support 80% queries), iterate later.

LLM Layer: Natural Language to Structured DSL

DSL is a JSON intermediate representation. A production-grade DSL example:

{
  "version": "1.0",
  "query_type": "compare",
  "dataset": {
    "type": "join",
    "relation": [
      { "name": "sales_fact", "alias": "s", "type": "fact" },
      { "name": "product_dim", "alias": "p", "type": "dim", "join": { "type": "inner", "left": "s.product_id", "right": "p.id" } },
      { "name": "region_dim", "alias": "r", "type": "dim", "join": { "type": "inner", "left": "s.region_id", "right": "r.id" } }
    ]
  },
  "select": [
    { "expr": "p.category", "alias": "品类" },
    { "expr": "SUM(CASE WHEN s.year=2025 THEN s.amount ELSE 0 END)", "alias": "销售额_2025" },
    { "expr": "(SUM(...) - SUM(...)) / NULLIF(SUM(...), 0) * 100", "alias": "增长率(%)" }
  ],
  "filter": {
    "operator": "and",
    "conditions": [
      { "field": "s.quarter", "operator": "in", "value": [1] },
      { "field": "r.region_name", "operator": "in", "value": ["华东", "华南"] }
    ]
  },
  "group_by": ["p.category"],
  "order_by": [
    { "expr": "增长率(%)", "direction": "desc", "nulls": "last" }
  ],
  "limit": 10
}

DSL Field Definitions

version (string): DSL version for compatibility.

query_type (string): Query type – select (detail), aggregate (aggregation), compare (comparison), etc.

dataset (object/string): Data source; single table name or multi-table join description.

dataset.type (string): join for multi-table; omitted for single table.

dataset.relation (array): List of tables with name, alias, join type and conditions.

select (array): Output fields/expressions (simple fields, aggregations, CASE WHEN, arithmetic).

filter (object): WHERE conditions, supports nested AND/OR trees.

group_by (array): Grouping fields.

order_by (array): Sorting rules, can specify nulls position ( nulls first/last).

limit (integer): Max rows returned.

Multi-table JOIN expression: dataset.relation array lists each table and its join condition sequentially. For complex joins (>3 tables), pre-define as logical wide table or use templates in semantic layer to avoid direct LLM generation.

NL2DSL Generation Process

Core is RAG + LLM (no fine-tuning). Steps:

Retrieve context: From semantic layer based on user question.

Construct prompt: Fill retrieved content into a carefully designed template with strict format constraints.

Call LLM: Models like Doubao, DeepSeek, GPT-4 output DSL; rich context and format constraints reduce hallucination.

Post-process & validate: Parse JSON, validate against DSL Schema. On failure, retry once (swap retrieval examples or lower temperature).

Why not generate SQL directly? SQL is a string, hard to validate; DSL is structured JSON, enabling precise checks (field existence, aggregation legality), permission injection, and dialect translation.

Ambiguity Detection & Clarification

Before LLM call, detect ambiguities in user question. Common types:

Metric ambiguity: "Sales amount" – order amount or received amount?

Time ambiguity: "This month" – calendar month or business month?

Dimension ambiguity: "North China region" – sales region or delivery region?

Implementation: Feed user question + potentially conflicting field descriptions from semantic layer to LLM to judge ambiguity and produce clarification questions. Agent asks user; user choice continues generation. Adds one interaction but significantly improves accuracy.

DSL Validation & Security Layer

DSL as structured data enables precise validation and optimization.

1. Syntax Validation

JSON Schema validation.

Field names must be legal for the dataset (cross-checked with semantic layer).

Operators match field types (e.g., date field cannot use like).

Aggregated fields must be numeric.

2. Permission Injection

Append row-level permission conditions to filter based on user role (e.g., dept_id = current_user.dept_id).

If user query already contains condition on same field, combine with AND (not override) to prevent bypass.

3. Logical Optimization

Merge redundant filter conditions.

Remove tautologies (e.g., 1=1).

Cap limit maximum (e.g., 10,000) to prevent runaway queries.

DSL2SQL Execution Layer: Deterministic Translation

DSL-to-SQL conversion is deterministic rule-driven , no LLM involved, guaranteeing 100% translation accuracy. Each database dialect (MySQL, Dameng, Oracle) has its own translator; core logic traverses DSL JSON object and concatenates SQL string segments.

MySQL translation example:

select → SELECT p.category, SUM(CASE WHEN ...) AS 销售额_2025, ... dataset.relation → FROM sales_fact s INNER JOIN product_dim p ON ... (supports arbitrary multi-table joins)

filter →

WHERE s.quarter IN (1) AND r.region_name IN ('华东','华南')

group_by → GROUP BY p.category having → HAVING SUM(...) > 1000000 order_by → ORDER BY 增长率(%) DESC NULLS LAST limit → LIMIT 10 Dialect adaptation: Same DSL yields different SQL syntax per dialect. Example: limit translates to LIMIT n in MySQL, ROWNUM <= n in Dameng. Upper business logic unchanged.

Feedback Loop: Continuous Accuracy Improvement

Accuracy gains rely on recycling user feedback.

Correction entry: Users click "correct" on query results, submit correct SQL or DSL.

Review mechanism: Corrections pass auto-validation (SQL executable) and sampled manual review before entering few-shot example library and feedback correction library.

Effect measurement: Monthly sampled evaluation of DSL generation accuracy (semantically correct and executable) guides optimization.

Through continuous iteration, production accuracy rises from 50% to 85-90% (varies with business complexity); single-table scenarios reach 95%+.

Summary

The presented SQL generation engine adopts Semantic Layer + NL2DSL + DSL2SQL architecture, delivering:

Controllability: DSL as structured intermediate representation – verifiable, optimizable, debuggable.

Accuracy: RAG enhancement, ambiguity detection, feedback loop achieve 85-90% production accuracy.

Security: Unified permission injection at DSL layer prevents SQL injection and unauthorized access.

Adaptability: Single DSL translates to multiple database dialects without upper-layer changes.

For teams struggling with Text2SQL accuracy, start with a single wide table plus a few few-shot examples, then incrementally introduce semantic layer and DSL—avoid over-engineering. This article provides a practical, battle-tested path.