Semantic Layer + NL2DSL + DSL2SQL: Building a Production Text-to-SQL Engine

In intelligent query systems, the SQL generation engine is the core module determining accuracy and user experience. Directly prompting LLMs to output SQL (Text2SQL) often drops production accuracy below 50% due to four fundamental issues: SQL strings are unverifiable, unoptimizable, unadaptable across dialects, and undebuggable. The solution is a structured intermediate representation — a Domain Specific Language (DSL) — that decouples semantic understanding from syntax generation.

Overall Architecture

The engine follows a three-layer architecture: Semantic Layer (structured business knowledge), LLM Layer (natural language to DSL via RAG), and Execution Layer (deterministic DSL-to-SQL translation). This design enables verification, optimization, security, and multi-dialect support.

Semantic Layer: Structured Foundation of Business Knowledge

The semantic layer is the "knowledge base" maintained jointly by data engineers and business users. The real challenge is not initial construction but continuous updates. Practical strategies include:

Automated extraction : Pull field Chinese names, enum values from data dictionaries, metadata platforms, and existing SQL comments to reduce manual entry.

Manual supplementation : Annotate synonyms and calculation calibers for key metrics (e.g., "sales amount") and easily confused dimensions (e.g., "region").

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

Core components:

Subject models : Predefine fact tables, dimension tables, and common metrics per business domain (sales, inventory, finance). Start with a single wide table and split gradually.

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

Vector semantic index : Vectorize field comments, synonyms, and business descriptions for retrieval-augmented generation.

Business knowledge base : Stores long-text business rules and typical query SQL/DSL templates for few-shot example retrieval.

Practical advice: Don't aim for completeness initially. Prioritize high-frequency query fields (20% of fields support 80% of queries) and iterate.

LLM Layer: Natural Language to Structured DSL

DSL is a JSON-formatted intermediate representation. Below is a production-grade DSL example (field meanings explained afterward).

{
  "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 management.

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

dataset (object/string): Data source; can be a single table name or a complex multi-table JOIN description.

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

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

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

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

group_by (array): Grouping field list.

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

limit (integer): Maximum returned rows.

Multi-table JOIN expression: The dataset.relation array describes arbitrary multi-table JOINs by listing each table and its join conditions sequentially. For 5-table joins, the array contains 5 elements; the DSL2SQL converter builds the JOIN chain in array order. For complex joins exceeding 3 tables, production practice recommends predefining "logical wide tables" in the semantic layer or using template filling to avoid direct LLM generation.

NL2DSL: How DSL Is Generated

NL2DSL uses RAG (Retrieval-Augmented Generation) + LLM without fine-tuning. Steps:

Retrieve context : Based on the user question, fetch relevant content from the semantic layer.

Construct prompt : Fill retrieved content into a carefully designed prompt template that enforces strict output format.

Call LLM : Models (e.g., Doubao, DeepSeek, GPT-4) generate DSL. Rich context and format constraints minimize hallucinations.

Post-process and validate : Parse model output as JSON, validate against DSL Schema. On failure, retry once with alternative retrieval examples or lower temperature.

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

Ambiguity Detection and Clarification

Before invoking the LLM, the system detects ambiguities in the user question. Common types:

Metric ambiguity : "Sales amount" — order amount or actual received amount?

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

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

Implementation: Feed the user question plus potentially conflicting field descriptions from the semantic layer to an LLM to judge ambiguity and produce clarification questions. Detected ambiguities trigger an interactive clarification step; this adds one interaction round but significantly improves accuracy.

DSL Validation and Security Layer

Because DSL is structured data, it can be precisely validated and optimized.

Syntax Validation

JSON Schema validation.

Field names must belong to the dataset's legal fields (cross-checked with semantic layer).

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

Aggregated fields must be numeric.

Permission Injection

Based on user role, row-level permission conditions are appended to the filter (e.g., dept_id = current_user.dept_id). If the user's original query already contains a condition on the same field, the system combines with AND rather than overwriting, preventing bypass.

Logical Optimization

Merge redundant filter conditions.

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

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

DSL2SQL Execution Layer: Deterministic Translation

DSL-to-SQL conversion is deterministic rule-driven , not LLM-dependent, guaranteeing 100% translation accuracy. Each database dialect (MySQL, Dameng, Oracle) has an independent converter that traverses the DSL JSON object and concatenates SQL fragments.

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

The same DSL can be translated into different SQL dialects via separate converters. For example, limit becomes LIMIT n in MySQL but ROWNUM <= n in Dameng; upper-layer business logic remains unchanged.

Feedback Loop: Continuous Accuracy Improvement

Accuracy gains rely on recycling user feedback.

Correction entry : Users can click "Correct" on query results, submitting correct SQL or DSL.

Audit mechanism : Corrections pass automatic validation (SQL executability) and sampled manual review before entering the few-shot example library and feedback correction library.

Effect measurement : Monthly sampling evaluates DSL generation accuracy (semantically correct and executable), guiding optimization.

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

Summary

The presented SQL generation engine adopts the Semantic Layer + NL2DSL + DSL2SQL architecture, delivering four core advantages:

Controllability : DSL as a structured intermediate representation is verifiable, optimizable, and debuggable.

Accuracy : RAG enhancement, ambiguity detection, and feedback loops push production accuracy to 85-90%.

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

Adaptability : One DSL translates to multiple database dialects without modifying upper-layer logic.

For teams struggling with text-to-SQL accuracy, start with a single wide table and a few few-shot examples, then gradually introduce the semantic layer and DSL — avoid over-engineering. This article aims to provide a practical, landable path.