The Semantic Orchestrator: Why Text-to-SQL Is the Wrong Answer

The pitch for text-to-SQL is seductive. Users type a question in plain English. The LLM writes SQL. The database answers. Done.

It's not done.

---

The two problems with text-to-SQL

The first problem is precision. SQL is unforgiving. A slightly wrong table name, a missing join condition, an off-by-one in a date range — any of these produces either no result or the wrong one. LLMs are stochastic machines. They have a non-zero error rate, always, by design. Combine an unforgiving target language with a probabilistic generator and you get a system with no guarantee of correctness at any given moment. At best, the query fails and the user sees an error. At worst, it returns a plausible-looking number that's wrong, and nobody notices until a decision has been made on it.

That's bad. But it's not the deeper problem.

The deeper problem is that your database doesn't know what your business means.

Think about what lives in a database schema: tables, columns, foreign keys, indexes. Types and relationships. What it doesn't contain: the definition of "monthly recurring revenue" your finance team agreed on three years ago. The rule that "active user" counts differ between the product and billing teams. The fact that "budget variance" for engineering means something different than it does for sales.

That knowledge lives in the application layer — in code, in internal wikis, in the heads of your most experienced analysts. It's the reason your business layer exists at all. It sits above the database precisely to encode the meaning your raw data can't carry.

Text-to-SQL asks an LLM to reconstruct that meaning from scratch, every single query, by guessing at your schema. It asks the model to rewrite your business logic in SQL — imperfectly, probabilistically, without access to the reasoning behind it.

You've spent years building that logic. Text-to-SQL discards it.

---

Where business knowledge actually lives

In a service-oriented architecture, the interesting stuff happens between services, not inside them. Any single service — users, billing, products — does its job cleanly. But "why is churn up this month?" doesn't live in any one service. It requires knowing which users qualify as churned by your definition, which billing events confirm it, which product signals precede it, and how to weight them.

That sequencing — that stitching together of APIs in the right order with the right logic — is where your company's analytical knowledge lives. And typically, only a handful of people know how to do it: your senior product managers, your BI team, the engineer who's been there since the beginning.

This is institutional memory in the original sense. It isn't written down anywhere that a machine can read. It exists as habit, as tribal knowledge, as the answer you get when you walk over to the right desk and ask the right person. When that person leaves, the knowledge degrades. When your company scales past the point where everyone knows everyone, it siloes.

The question isn't how to let an LLM guess at that knowledge. The question is how to make it explicit, callable, and maintainable.

---

The Semantic Orchestrator pattern

The pattern has three layers:

Layer 1: User intent. Natural language, business-framed, ambiguous. "Why is churn up?" "Is the Q3 budget on track?" "Which accounts are at risk this week?"

Layer 2: The semantic layer. This is the new piece — a tool or knowledge base that translates natural language intent into higher-order business concepts. It knows that "churn" means this specific calculation, across these specific services, with these edge cases. It holds what your BI team holds in their heads, but in a form a system can call.

Layer 3: Service APIs. The actual calls the orchestrator makes once it knows what it's trying to do. Existing services, existing logic, unchanged.

The LLM sits between layers 1 and 2, and between 2 and 3. At the first seam, it resolves intent: what is the user actually asking, in business terms? At the second seam, it orchestrates: which tools, in what order, to produce the answer?

The LLM is the reasoning surface. It is not the execution surface.

It never touches raw data. It calls tools that return processed, permissioned results. The business logic doesn't live in the prompt — it lives in the tool definitions, where it can be tested, versioned, and audited.

---

Security without exposure

When the LLM never touches raw data — only tools — the security model becomes tractable. You control exactly what each tool exposes. Row-level permissions, rate limits, audit logging: enforce them at the tool layer once, rather than hoping a SQL-generating model doesn't accidentally query data it shouldn't see.

With text-to-SQL, your entire database schema is effectively part of the model's attack surface. A clever prompt injection, a misconfigured system prompt, a model that helpfully decides to answer a slightly different question than the one asked — any of these can produce a query that returns data the user shouldn't have. The tool layer is a hard boundary. The LLM can only do what the tools allow.

This is also the structural answer to the prompt injection problem. When the LLM has no direct access to data or execution — only a defined set of tools with explicit permission scopes — a malicious instruction in the context can't do more damage than the most permissive tool allows. The attack surface is bounded by design, not by trust in the model.

---

The honest caveat

This is still a single point of failure. If the orchestrator reasons incorrectly, everything downstream is wrong. If it's unavailable, nothing works.

The mitigation is that the tool layer remains independently testable regardless. You can't fully unit test the orchestrator's reasoning — it's stochastic — but you can unit test every tool it calls. You can log every tool invocation and inspect the orchestrator's decision trail after the fact. The system is auditable even when it isn't perfectly predictable.

---

The real work — and the real failure mode

Here's the uncomfortable truth: the semantic layer is genuinely hard to build. Making implicit business knowledge explicit — turning "at-risk account" from tribal knowledge into a callable, documented tool definition — requires conversations with people who'd rather not be pulled into technical meetings. It requires decisions about what "correct" means that your organization may have been avoiding.

That's also the point. The orchestrator pattern is partly a forcing function. It makes you do the work that text-to-SQL lets you skip — and which eventually bites you anyway when the SQL starts returning numbers nobody recognizes.

But "tool definitions are living contracts" only holds if you actually maintain them. Here's the failure case: your finance team quietly changes the definition of churn in Q3 — they start excluding trial conversions that cancel within 30 days. The tool definition still uses the old logic. The orchestrator calls the tool, gets a number, presents it with confidence. The number is wrong by a meaningful percentage. Nobody knows, because the tool didn't fail — it executed perfectly against an outdated spec.

This failure mode is subtler than a bad SQL query. It's the same silent wrong answer, just with more architectural ceremony around it. The mitigation is treating tool definitions the way you treat any business-critical specification: owned, versioned, with a review process when the underlying definition changes. That's an organizational discipline, not a technical one. The pattern gives you the structure; you still have to maintain it.

Once the semantic layer exists and is maintained, it's genuinely reusable. Every tool definition encodes knowledge that can be called by the orchestrator, inspected by engineers, and extended as the business evolves. You've built a queryable representation of how your business actually works.

The tool schema is the product. The LLM is the interface.

---

When to use it (and when not to)

The Semantic Orchestrator pattern is right for:

• Analytics products where users ask business questions that span multiple services
• Domains where business logic is meaningful, established, and worth preserving
• Systems where security and auditability matter — finance, HR, healthcare

It's the wrong choice for:

• Simple single-table lookups where SQL is genuinely the right abstraction
• Early-stage products where the business logic is still being invented
• Teams that don't have the bandwidth to build and maintain the semantic layer properly

The setup cost is real. If your business logic is shallow or still fluid, text-to-SQL's lower friction may be the right trade-off for now. But if you're building something that needs to be right, maintainable, and secure — the semantic layer is the investment that makes it possible.

---

How this pattern fits with others

The Semantic Orchestrator defines where the LLM sits and what it's allowed to touch. It doesn't answer what you do when the orchestrator gets it wrong.

That's where the next pattern in this series comes in. Each tool call the orchestrator makes produces an output — and those outputs are exactly the kind of structured, claim-bearing results that benefit from a formal output contract. The tool layer is the natural boundary: a tool that calculates churn either returns a number against a verifiable definition or it doesn't. Apply deterministic assertions there, and you've closed the loop between the orchestration pattern and the verification pattern.

Other patterns in this series will address further questions this one leaves open — state management across multi-turn sessions, memory that persists across users, failure handling when the orchestrator's reasoning goes wrong in ways the tool layer can't catch. The orchestrator is a load-bearing piece of the architecture, not a complete solution on its own.

---

	Text-to-SQL	Semantic Orchestrator
Business logic	Reconstructed each query	Encoded once, reused
Error mode	Silent wrong answers	Explicit tool failure
Maintenance	Schema drift = broken queries	Tool definitions = living contracts
Security	Raw data in LLM context	Data behind tool boundary
Setup cost	Low	High — the semantic layer is real work