Text-to-SQL Is Only as Safe as the Layer Underneath It

If you have wired an LLM to your warehouse and watched it answer questions in plain English, you already know the demo is excellent. What is less obvious is why it is dangerous in production, and what specifically you have to build to fix it.

Short version: the model does not fail loudly. It fails with a successful query.

The actual failure mode

Give a language model access to a raw schema and ask "what was revenue last quarter." It will:

• pick a table whose name contains revenue or orders
• guess a join key based on column naming (customer_id → id, usually right, occasionally catastrophically not)
• include every row, because nothing told it that env = 'test' exists
• sum a column that might be gross, might be net, and might include internal transfers

Then it returns a number. The SQL is syntactically valid. The query plan is fine. There is no exception to catch, no non-zero exit code, nothing for your monitoring to alert on.

This is qualitatively different from most production failures. You are not debugging a crash — you are debugging trust, after the fact, usually because someone noticed a discrepancy in a board deck.

Guardrail 1: never expose raw tables

The single highest-impact change. The model should never see your ingestion layer.

Expose a curated set of modelled marts — tables that have already resolved the ambiguities. Test accounts filtered. Refunds normalised. Currency converted. Deleted records handled according to an explicit policy rather than whatever the source system does.

-- What the model should NOT see
raw_stripe__charges
raw_salesforce__opportunity
events_firehose_v2

-- What it should see
mart_revenue_daily        -- net, recognised, USD, excludes internal
dim_customer              -- excludes test + internal accounts
fct_subscription_events   -- deduplicated, with explicit status enum

If the mart layer resolved the ambiguity, the model cannot get it wrong. This is not a prompt-engineering problem — it is a data-modelling problem, and prompt engineering is a very poor substitute for it.

Guardrail 2: a semantic layer with real metric definitions

Marts remove ambiguity from rows. A semantic layer removes ambiguity from calculations.

Define metrics once, declaratively, and make the model call the metric rather than reconstruct it:

metrics:
  - name: active_customers
    description: Customers with at least one session in the trailing 28 days
    type: count_distinct
    expr: customer_id
    filters:
      - "last_session_at >= dateadd(day, -28, current_date)"
      - "is_internal = false"

Now "how many active customers" resolves to a definition your business agreed on, not to whatever the model infers active means. The window is 28 days because someone decided that; the model has no business relitigating it per query.

Guardrail 3: constrain the join graph

Most genuinely wrong answers come from bad joins, particularly fan-out joins that silently multiply rows and inflate sums.

Declare permitted join paths explicitly and reject generated SQL that uses anything else. If orders may join to customers on customer_id and to nothing else, enforce it. A model that cannot express an invalid join cannot produce an invalid number through one.

The practical implementation is a validation pass over the generated AST before execution — parse it, walk the joins, compare against an allowlist, reject with a message the model can retry against.

Guardrail 4: test the transformations in CI

None of the above helps if the marts themselves drift. Minimum viable test suite on every model:

models:
  - name: dim_customer
    columns:
      - name: customer_id
        tests: [unique, not_null]
      - name: is_internal
        tests:
          - accepted_values: { values: [true, false] }
    tests:
      - dbt_utils.expression_is_true:
          expression: "count(*) > 0"

Plus freshness checks on sources, and row-count anomaly detection on the marts. Freshness matters disproportionately here: a stale mart returns a confident answer about last week while claiming to describe today.

Guardrail 5: make the answer auditable

Every AI-generated answer should ship with the SQL that produced it and the metric definitions it used, one click away. Not buried in a debug log — visible in the response.

This does two things. It gives an analyst a path to verify a suspicious number in thirty seconds rather than reconstructing it. And it changes user behaviour: people who can see the query start noticing when the filter list looks wrong, which turns your entire user base into a distributed test suite.

What this costs

Worth being honest about the tradeoff. Doing all five of these is real engineering work — realistically several weeks of modelling and infrastructure before the chat interface is safe to widen beyond a pilot group.

The alternative is faster and worse. You ship in a week, it works impressively for a month, and then someone discovers a wrong number in a context that matters. Trust in an analytics platform is close to binary and expensive to rebuild, which makes the shortcut a bad trade.

There is also a bill attached: query volume goes up sharply once asking is cheap. Expect warehouse compute to climb thirty to sixty percent within two quarters. Budget for materialisation strategy and query monitoring as an ongoing role rather than a one-off.

---

The full buyer-oriented version of this — cost ranges, the five disciplines inside an analytics engagement, engagement models, and vendor evaluation questions — is here: Data Analytics Services: A 2026 Guide for Technical Buyers.

If you are building this and want a second pair of eyes on the architecture, TechCirkle does LLM integration work.

Frequently Asked Questions

Why does text-to-SQL return wrong answers without errors?

The model guesses column meanings and join keys from names. The resulting SQL is syntactically valid and executes successfully, so nothing raises an exception — the only symptom is a number that is quietly incorrect.

Should an LLM have access to raw warehouse tables?

No. Expose only curated marts where the ambiguities are already resolved: test accounts filtered, refunds normalised, currency converted, deletion policy applied. If the mart resolved it, the model cannot get it wrong.

What does a semantic layer add on top of marts?

Marts remove ambiguity from rows; a semantic layer removes ambiguity from calculations. It defines metrics declaratively once, so the model calls an agreed definition rather than reconstructing it per query.

How do you prevent bad joins in generated SQL?

Declare permitted join paths and validate the generated query's AST against that allowlist before execution, rejecting anything else. Fan-out joins that silently multiply rows are the most common source of inflated numbers.

What tests should run on analytics transformations?

At minimum: uniqueness and not-null on keys, accepted-values on enums, source freshness checks, and row-count anomaly detection on marts. Freshness matters especially, since a stale mart answers confidently about the wrong period.

Should AI-generated answers show their SQL?

Yes, visibly rather than in a debug log. It lets an analyst verify a suspicious number quickly, and users who can see the query start catching wrong filters themselves.

How much does warehouse compute grow after adding natural-language querying?

Typically thirty to sixty percent within two quarters, because cheap questions get asked far more often. Plan for materialisation strategy and query monitoring as an ongoing responsibility.