Text-to-SQL in Production: Why the Demo Works and the Deployment Fails

The demo is thirty seconds long. Someone types “top 10 customers by revenue last quarter,” a query appears, a bar chart renders, the room nods. Two months later the same system tells the VP of Sales that EMEA enterprise revenue was $4.1M when the board deck says $3.6M, and nobody can explain the gap because nobody looked at the SQL.

I have shipped this twice. The first deployment generated SQL directly against the warehouse schema and I killed it after six weeks. The second one has been running for over a year, answers roughly 300 questions a week, and has not produced a silently wrong number that we know of. The difference was not the model. Both used a frontier model. The difference was that the second one never lets the model touch a table name.

The architecture-level version of this story is in RAG over warehouse data. This post is narrower and more specific: what breaks between the demo and the deployment, in the order you will hit it, and what to build instead.

The demo schema has five tables and one meaning per column

Every text-to-SQL benchmark and every vendor demo runs on a schema you could draw on a napkin. customers, orders, order_items, products, regions. One join path between any two tables. Column names that mean what they say.

My production warehouse had 412 tables in the analytics database alone, 31 of which had a column named status, and three tables that could plausibly answer “how much revenue.” One was the raw billing extract in cents including tax. One was a mart with refunds deducted but recognized on order date. One was the finance-blessed model with ship-date recognition that reconciles to the general ledger. The correct answer to almost every revenue question is the third table, and there is nothing in INFORMATION_SCHEMA that says so.

That gap — between schema and semantics — is the entire problem. The model is not bad at SQL. Give it a clean spec and it writes better window functions than most of my analysts. It is bad at guessing which of your three revenue tables the CFO considers real, because that fact does not exist anywhere the model can read.

The four failures, in the order you hit them

Week one: wrong table. The model picks raw.billing_events because the name contains “billing” and the question said “billing.” Numbers come back 18% high because tax is included. Nobody notices, because 18% high on a number nobody has memorized looks fine.

Week three: wrong join path. orders joins to customers on customer_id, except the 2019-and-earlier rows that need legacy_customer_map. The model writes the obvious join, drops 40,000 historical orders, and reports a growth rate that is wrong in a flattering direction.

Week six: fan-out. The model joins the order header to the line items to get product category, then sums the header-level order_total. Every order is now counted once per line. This is the single most common numeric error I have seen, and it is the same one humans make — see SQL joins and fan-out for the mechanics.

Week eight: the bill. Someone asks a vague question, the model writes SELECT * FROM fct_events with a WHERE clause on an unpartitioned column, and you scan 3.2 TB. On BigQuery on-demand pricing that is roughly $16 for one question. My worst single day before guardrails was $340 from 40 questions, against a model spend of about $2.

Notice that three of those four are correctness failures that produce a number, not an error. That is what makes text-to-SQL dangerous in a way that a broken dashboard is not. A broken dashboard is obviously broken.

Generate a spec, not SQL

The fix that worked: the model never emits SQL. It emits a JSON query spec that references only names defined in a semantic layer, and my code compiles that spec into SQL.

flowchart TD
  q["User question"] --> llm["LLM with semantic layer in context"]
  llm --> spec["Query spec as JSON"]
  spec --> val{"Names resolve? Grain valid?"}
  val -->|no| refuse["Refuse and explain"]
  val -->|yes| comp["Deterministic SQL compiler"]
  comp --> guard["Read-only role, LIMIT, timeout, tag"]
  guard --> wh["Warehouse"]
  wh --> ans["Answer plus the SQL shown"]

The model chooses. The compiler writes. Anything the compiler cannot express is a refusal, not an improvisation.

The semantic layer is a YAML document that names metrics, the dimensions they can be sliced by, the verified join paths, and the grain of each. It is the same artifact whether you build it by hand, use the dbt Semantic Layer, or use Cortex Analyst.

# semantic/finance_core.yml — versioned next to the dbt project
model: finance_core
base: analytics.gold.fct_order_lines
grain: [order_line_id]

joins:
  # Only these paths exist. The compiler refuses anything else.
  - name: customer
    to: analytics.gold.dim_customer
    on: customer_sk
    cardinality: many_to_one
  - name: product
    to: analytics.gold.dim_product
    on: product_sk
    cardinality: many_to_one

metrics:
  - name: net_revenue
    label: Net revenue
    expr: sum(gross_amount - refund_amount - credit_amount)
    grain_date: shipped_at          # ship-date recognition, matches GL
    agg_safe_at: order_line          # guards against fan-out
  - name: order_count
    expr: count(distinct order_id)
    grain_date: ordered_at

dimensions:
  - name: region
    expr: customer.region
    values: ['AMER', 'EMEA', 'APAC']
  - name: segment
    expr: customer.segment
  - name: month
    expr: date_trunc('month', {grain_date})

filters:
  - name: exclude_trials
    expr: customer.is_trial = false

The model receives that document — not DDL — and returns:

{
  "metrics": ["net_revenue"],
  "dimensions": ["month"],
  "filters": [{"dimension": "region", "op": "=", "value": "EMEA"},
              {"named": "exclude_trials"}],
  "time_range": {"start": "2026-01-01", "end": "2026-03-31"},
  "order_by": [{"field": "month", "dir": "asc"}],
  "limit": 500
}

The compiler is a hundred lines of deliberately boring Python:

def compile_spec(spec: dict, model: SemanticModel) -> str:
    """Compile a validated spec. Raises on any unknown name."""
    metrics = [model.metric(name) for name in spec["metrics"]]

    # All requested metrics must share a grain date, or the time
    # filter is ambiguous and we refuse rather than pick one.
    grains = {m.grain_date for m in metrics}
    if len(grains) > 1:
        raise Unsupported(f"metrics span grain dates {sorted(grains)}")

    dims = [model.dimension(d) for d in spec.get("dimensions", [])]
    needed = {j for m in metrics for j in m.joins} | {j for d in dims for j in d.joins}

    select = [d.sql for d in dims] + [f"{m.expr} as {m.name}" for m in metrics]
    from_ = model.base_sql(joins=needed)          # only declared paths
    where = model.render_filters(spec.get("filters", []), grain=grains.pop())
    group = list(range(1, len(dims) + 1))

    return dedent(f"""
        select {", ".join(select)}
        from {from_}
        where {where}
        {"group by " + ", ".join(map(str, group)) if group else ""}
        order by {model.render_order(spec.get("order_by", []))}
        limit {min(int(spec.get("limit", 500)), 5000)}
    """).strip()

Everything dangerous is now impossible by construction. The model cannot invent a join, cannot pick the wrong revenue table, cannot fan out a header metric across line items, cannot write DML. If a question needs something the spec cannot express, Unsupported fires and the user sees “I cannot answer that with the defined metrics” — which is a far better outcome than a confident number.

Free-form SQL generation is still worth keeping as a fallback for power users, behind a flag, with the output clearly labeled as unverified. In my logs about 8% of questions fall through to it, and those answers get a visible warning banner.

Cost control belongs in the compiler

Model tokens are the cheap part. At roughly 6k tokens of semantic layer plus a 300-token answer, a question costs me about half a cent. The warehouse query is where the money goes.

Three rules, all enforced in the compiler rather than in a prompt:

-- 1. Every generated query is time-bounded on the partition column,
--    even when the user did not ask for a range.
--    Default window: 13 months. Longer needs an explicit request.
where shipped_at >= dateadd(month, -13, current_date)

-- 2. Route the workload to a small, isolated warehouse so a bad
--    query cannot starve the ELT jobs.
create warehouse if not exists ask_wh
  warehouse_size = 'xsmall'
  auto_suspend = 60
  statement_timeout_in_seconds = 45
  max_concurrency_level = 4;

-- 3. Tag everything so cost is attributable per user and per session.
alter session set query_tag = '{"app":"ask","user":"u_8812","sess":"s_44f1"}';

Pruning is what keeps this affordable. A question that filters on a clustered or partitioned column costs cents; the same question with a LOWER(region) = 'emea' predicate defeats pruning and costs dollars. The compiler emits predicates that match the physical layout — which means the semantic layer author has to know the layout. If you have not done that work, start with micro-partitions and pruning or the BigQuery partitioning guide.

I also cache aggressively. Identical spec plus identical semantic layer version equals identical result; a 15-minute result cache keyed on the spec hash removed about 30% of my warehouse spend, because people ask the same five questions every morning.

Evaluation: execution accuracy or nothing

The number that told us we were not ready was 41 out of 60.

Build a golden set before you build the UI. Sixty questions minimum, each with a verified answer computed by hand or taken from a dashboard finance already trusts. Score by comparing result sets, not SQL strings — there are twenty correct ways to write the same aggregation.

def score(golden: list[Case], run) -> dict:
    results = {"exact": 0, "refused": 0, "wrong": 0, "error": 0}
    for case in golden:
        try:
            out = run(case.question)
        except Unsupported:
            # A refusal on an answerable question is a miss, but a
            # refusal on an unanswerable one is a pass.
            results["refused" if not case.answerable else "wrong"] += 1
            continue
        except Exception:
            results["error"] += 1
            continue
        # Compare as sorted tuples with a tolerance on floats.
        results["exact" if frames_match(out, case.expected, tol=0.005) else "wrong"] += 1
    return results

Include unanswerable questions in the set — roughly 20% of mine. A system that answers everything is a system that hallucinates, and “refuses correctly” is a metric worth optimizing. Rerun the whole set on every semantic layer change, prompt change, and model upgrade. Mine runs in CI and takes four minutes.

We launched at 87% execution accuracy to internal analysts who can see the SQL. I would not ship generated aggregations to a customer-facing surface at any accuracy I have measured.

Where teams get this wrong

Dumping DDL into the prompt. Four hundred CREATE TABLE statements gives the model more ways to be plausibly wrong, not fewer. Schema is not semantics.

Letting the model emit raw SQL as the primary path. Once the model can write arbitrary SQL, every guardrail becomes a string-matching exercise against a generative system. Compile from a spec instead.

No grain guard on metrics. The fan-out bug survives every other control. Declare which grain each metric is safe to aggregate at and refuse combinations that violate it.

Treating refusal as failure. Product pressure to “always answer something” is how you get confident wrong numbers. Make refusal a first-class, well-worded response and measure it separately.

Semantic layer drift. A metric renamed in dbt but not in the YAML is a silent wrong answer for as long as nobody checks. CI should diff the dbt manifest against the semantic layer and fail the PR.

Shipping without cost attribution. Without query tags you will discover the spend in the monthly invoice, aggregated, unattributable, and three weeks late. Tag from the first query.

FAQ

Should I fine-tune a model on my schema?

No. Fine-tuning bakes today’s schema into weights that go stale on the next sprint, and it does not give you refusals or auditability. The semantic layer is data in git, updated in the same pull request as the dbt change. For facts that change, context beats weights.

Is a smaller model good enough if the semantic layer is strong?

Largely, yes, and this is the strongest argument for the spec approach. Once the task is “pick metrics and filters from this list and emit JSON,” mid-tier models score within a few points of frontier models on my golden set at roughly a fifth of the cost. The hard work moved into the YAML.

How do I handle questions that need a metric we have not defined?

Refuse, log the question, and review the log weekly. My highest-value backlog for two quarters was the list of refused questions — it is a directly prioritized list of the metrics the business actually wants, written in their own words.

What about joins across two fact tables?

Do not let the compiler do it implicitly. Either model the combination as a first-class metric on a pre-joined table, or refuse. Ad hoc fact-to-fact joins are where fan-out and double-counting live, and no prompt reliably prevents them.

How large can the semantic layer get before it stops fitting?

Mine is about 40 metrics and 30 dimensions in roughly 7k tokens, and that is comfortable. Past that, retrieve the relevant slice of the semantic layer per question — retrieval over your own metric definitions works well and is far cheaper than a longer context.

Do users actually look at the generated SQL?

Analysts do, constantly, and they catch bad specs in seconds. Business users do not, which is exactly why they should only see metrics validated by the golden set. Showing the SQL costs nothing and changes the failure mode from “trusted wrong number” to “caught in review.”

What this means for your pipelines

Text-to-SQL is a data modeling project with a chat window bolted on the front. The work that makes it succeed is work your team already knows how to do: define metrics once, declare join paths, understand your physical layout well enough to write prunable predicates, and test the whole thing against known-correct answers. None of that is AI work.

The practical consequence is that you should sequence it the other way around from how most teams do. Build the semantic layer first and make your BI tool consume it. If the layer is good enough that your dashboards agree with finance, it is good enough to compile LLM specs against. If it is not, a chat interface will surface every inconsistency you have been tolerating, at speed, to an audience that cannot evaluate the answers.

Treat the golden set the way you treat dbt tests: part of the build, run on every change, blocking on regression. The model will get better on its own. Your join paths, metric definitions, and refusal behavior will not.