Why not just let the AI write SQL?
Understand the failure modes of text-to-SQL and why routing the LLM through the semantic layer is the safer, better design.
The obvious way to build an analytics agent is "text-to-SQL": give the LLM the schema and let it write queries. It demos beautifully and fails quietly in production. Understanding why is the key insight of Part 2.
The failure modes of text-to-SQL
- Inconsistent definitions. The model re-derives "revenue" every query — sometimes with the discount, sometimes without, sometimes including refunds. Two identical questions can get two different numbers.
- Silent correctness bugs. It writes a join that fans out and reports inflated counts, with total confidence and no error.
- Security surface. Generated SQL against a live database is a large attack and accident surface — a malformed query can be slow, expensive, or destructive.
- Unauditable. When a number is wrong, you're debugging freshly-generated SQL, not a definition you can point to.
The better design: the LLM picks names, the layer runs SQL
Language models are excellent at one thing here: mapping a fuzzy human question to precise names. "How much did we make from paid channels last quarter?" → metric revenue, dimension channel, filter on channel, time_grain: quarter. That's a translation task, which is exactly an LLM's strength.
So we let the model do only that. It calls a tool with a structured request of metric and dimension names; the semantic layer — which you tested and trust — produces the SQL and the number. The model never sees or writes SQL.
The LLM translates language into a governed request; the semantic layer turns that request into a trustworthy number. Neither does the other's job. This is the whole design, and it's why the answers are safe to show a CFO.
This pattern — LLM chooses from a governed catalog rather than generating raw queries — is how serious teams put AI on top of data. You get natural-language flexibility and the consistency of a metrics layer.