Why Data Agents Shouldn't Write SQL Directly – They Need a Business Compiler Layer

01 NL2SQL’s Bottleneck Is Not Just Model‑Generated SQL

In simple test environments a large language model can read a schema and emit SQL, but real‑world warehouses contain thousands of tables, diverse dialects, and scattered business rules. The Spider 2.0 benchmark reflects this: on Spider 2.0 the o1‑preview code agent achieves only 17.0% accuracy, versus 91.2% on the older Spider 1.0.

This gap shows that enterprise NL2SQL must do more than code generation; it must map business concepts, discover data relationships, and choose execution paths.

1. Business Vocabulary Does Not Map Directly to Physical Fields

When a user asks for “high‑value customers’ churn risk”, no column is named exactly that. The concept may combine recent spend, purchase frequency, profit contribution, and contract status, and the model may not know which internal definition of “high‑value” applies.

2. The Same Metric May Have Multiple Legal Definitions

“Revenue” can mean order amount, recognized revenue, cash receipt, tax‑inclusive amount, or net after refunds and discounts. Different departments use different time windows, order statuses, and exchange‑rate rules, so a syntactically correct SQL can still produce results that conflict with financial or operational definitions.

3. JOIN Is About Business Relationships, Not Field Matching

Customer, order, contract, product, inventory tables often lack clear foreign‑key links. Some relationships require mapping tables, key cleaning, or time‑bounded joins. Letting the model guess join paths leads to duplicate counts, missing rows, or overly broad result sets.

4. Permissions and Governance Cannot Rely on Prompt Engineering

Row‑level, column‑level, data‑sensitivity, domain boundaries, and compute‑resource limits must be enforced as formal constraints in the query planning and compilation stages, not as ad‑hoc reminders in the prompt.

Why the Semantic Layer Becomes a “Business Compiler”

A June 2026 study introduced a Semantic‑Layer‑mediated NL2SQL agent. Instead of feeding the raw schema to the LLM, the model first produces a Semantic Model Query (SMQ) against a curated semantic layer; a deterministic engine then compiles the SMQ into SQLite, BigQuery, or Snowflake dialect SQL.

The key change is a clear division of labor: the model expresses intent, the semantic layer maps intent to business objects, and the compiler generates verifiable SQL.

Model Responsibilities

Identify the business object, metric, dimensions, time range, and filters the user wants.

Translate natural language into a structured business query (SMQ).

Plan sub‑tasks for complex queries and combine compiler‑produced components.

Compiler Responsibilities

Map business metrics to concrete calculation expressions.

Select physical tables and join paths based on a declared relationship graph.

Inject filters, time windows, deduplication, and permission rules.

Emit dialect‑specific SQL and optionally preview or validate the result.

This mirrors traditional program compilation: high‑level intent → intermediate representation → executable code.

Why an Intermediate Representation Improves Governance

Free‑form SQL has an almost unlimited search space; a single mistake can invalidate results. SMQ constrains queries to predefined metrics, filters, and dimensions, enabling static validation, permission checks, and rule expansion before execution.

It also provides observability: the system can log which metric, filters, join path, and permissions were used, allowing teams to pinpoint whether a failure originated in intent parsing, semantic definition, compilation, or the underlying SQL engine.

Benchmark Insight

The study used Gemini 3 Pro on 547 Spider‑2‑snow tasks, achieving 94.15% execution accuracy (515/547), ranking third on the official leaderboard. This performance stems from the full stack—LLM, curated semantic layer, SMQ, deterministic compiler, and multi‑backend execution—not from the model alone.

Four Takeaways

The quality of the semantic layer, not just the model, drives enterprise NL2SQL performance.

Deterministic compilation does not eliminate all SQL generation; complex constructs like window functions or recursive CTEs may still be assembled by the agent using compiler‑provided building blocks.

Semantic layers risk over‑fitting if teams encode evaluation‑specific clues into field descriptions; they should be treated as code, subject to review, testing, and version control.

Only after business entities, states, permissions, and actions are formally defined can agents safely move from answering questions to taking actions.

Building the Business Compiler – A Practical Roadmap

1️⃣ Start with a bounded business domain (e.g., sales, supply‑chain, churn) that has clear users, data sources, core questions, and acceptance criteria.

2️⃣ Treat each semantic asset (metric, entity, relationship, rule) as a product: assign owners, version, and scope, and manage it through code‑like review and release processes.

3️⃣ Design a constrained business query language that separates intent from physical identifiers, supports static checks, and references the governed catalog.

4️⃣ Move deterministic logic—metric expansion, join selection, time‑window defaults, permission checks, dialect adaptation, resource limits—out of the model into reusable compiler components.

5️⃣ Create a real‑world validation suite: capture business questions, expected results, tolerances, role‑based permissions, cost budgets, and failure modes; run regression after any semantic or compiler change.

6️⃣ Expose actions gradually: begin with read‑only queries, then add suggestion generation, human‑in‑the‑loop approval, and finally automated execution, each gated by explicit preconditions, audit logs, idempotency, and rollback mechanisms.

Limitations of the Semantic Layer

Incorrect semantic definitions propagate errors at scale; coverage versus maintenance cost is a trade‑off; a closed DSL can hinder advanced analytics that require window functions or exploratory joins; and extending the layer to include actions introduces new governance challenges because mistakes can now modify business state.

Conclusion

SQL will remain the execution language of enterprise data systems, but its role will shift from being the sole carrier of business meaning to a verified output of a layered architecture. The next competitive edge for Data Agents lies not in raw model capability but in the robustness, completeness, and auditability of the underlying semantic system.