Text to SQL with LLMs, Part 4: Production-Grade Systems: Semantic Layers, Agents, Guardrails, and Evals

Parts 1, 2, and 3 built the accuracy stack: curated schema context, retrieved golden queries, glossary definitions, and value grounding. A system with all of that is a strong prototype. What separates a prototype from something your operations lead uses for a Friday board report is everything in this final part: metrics defined once, generation that checks its own work, guardrails enforced where they cannot be talked around, and an evaluation harness that replaces "seems accurate" with a number.

This mirrors where the research went too. The systems topping realistic benchmarks like BIRD are not single prompts; they are pipelines with selection, generation, execution feedback, and revision stages. And on Spider 2.0, the enterprise-scale benchmark where launch-era frontier agents solved fewer than a quarter of tasks, the April 2026 leader is Oracle's SOMA-SQL at 72.02 percent, a system whose published components read like this series in outline: retrieved query logs, schema enrichment, structured planning, a critique phase, and execution-grounded ambiguity probing. Production is a systems problem, and the leaderboards are now won by teams that treat it as one.

Define every metric exactly once: the semantic layer

The quiet killer of analytics trust is metric drift: finance, marketing, and the AI assistant each computing "revenue" slightly differently, all correct by their own logic, all mutually contradictory. Part 2's reporting views and Part 3's glossary hold the line informally. A semantic layer formalizes it: metrics and dimensions are defined once, in version-controlled config, and every consumer (BI dashboards and your text to SQL assistant alike) compiles queries from the same definitions.

Two strong open source options are Cube and dbt's MetricFlow. A Cube model over the Part 2 view looks like this:

cubes:
  - name: orders
    sql_table: analytics.order_facts

    measures:
      - name: revenue
        sql: order_total
        type: sum
        filters:
          - sql: "{CUBE}.order_status IN ('paid', 'shipped')"

      - name: order_count
        type: count

    dimensions:
      - name: state_code
        sql: state_code
        type: string
      - name: ordered_at
        sql: ordered_at
        type: time

With this in place, the assistant's job shrinks from "write correct SQL from scratch" to "map the question onto defined measures and dimensions," a dramatically smaller target to miss. The revenue rule now lives in one reviewed file instead of being re-derived per query. A full semantic layer is a real project, so sequence it honestly: for many SMBs, Part 2's views plus Part 3's glossary carry you a long way, and the semantic layer is the graduation step once metric consistency across tools becomes the pain point.

Generation that checks its own work

Production systems do not trust a first draft, and neither should yours. The pattern that consistently wins, from DIN-SQL's self-correction module onward, is a loop: generate, validate, execute, inspect, revise.

def answer(question: str, context: str, max_attempts: int = 3):
    feedback = ""
    for attempt in range(max_attempts):
        sql = llm_generate(question, context, feedback)

        try:
            sql = guard(sql)                      # static validation
            result = run_readonly(sql, row_cap=10_000)
        except Exception as e:
            feedback = f"Attempt failed: {e}. Revise the query."
            continue

        check = llm_review(question, sql, result.preview())
        if check.ok:
            return sql, result
        feedback = check.critique                 # e.g. "empty result; the
                                                  # status filter may not
                                                  # match stored values"
    return needs_human(question)

Two details carry most of the value. First, execution errors are gold: a database error message fed back into the prompt fixes a large share of failures on the second attempt, essentially free accuracy. Second, the sanity review catches the silent failures, like a plausible query returning zero rows because a literal did not match stored values, which is exactly the class of wrong answer users find most corrosive. Top systems extend this pattern to generating multiple candidate queries and selecting among them by execution behavior; SOMA-SQL's execution-grounded ambiguity probing, which runs targeted checks against the data to decide between competing interpretations, is the 2026 leaderboard version of the same loop with a wider search, and you can grow into it.

The guard() step deserves its own code. Parse the SQL before anything touches the database, using sqlglot, the open source parser and transpiler that has become standard kit for this:

import sqlglot
from sqlglot import exp

def guard(sql: str, dialect: str = "tsql") -> str:
    statements = sqlglot.parse(sql, read=dialect)   # raises on bad syntax
    if len(statements) != 1:
        raise ValueError("Exactly one statement is allowed")
    tree = statements[0]
    if not isinstance(tree, (exp.Select, exp.Union)):
        raise ValueError("Only SELECT queries are allowed")
    return tree.sql(dialect=dialect)

This rejects malformed SQL, multi-statement payloads, and anything that is not a read, before execution. sqlglot also transpiles between dialects, which is how you keep one golden query library serving a SQL Server warehouse today and a ClickHouse migration next year.

Guardrails the model cannot talk its way around

Application-level checks are necessary and insufficient. The guarantees that matter get enforced by the database, on a dedicated role that cannot write, cannot see restricted schemas, and cannot run forever, no matter what SQL reaches it.

PostgreSQL:

CREATE ROLE ai_reader LOGIN PASSWORD '...';

GRANT USAGE ON SCHEMA analytics TO ai_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ai_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
    GRANT SELECT ON TABLES TO ai_reader;

ALTER ROLE ai_reader SET default_transaction_read_only = on;
ALTER ROLE ai_reader SET statement_timeout = '15s';

SQL Server:

CREATE LOGIN ai_reader WITH PASSWORD = '...';
CREATE USER ai_reader FOR LOGIN ai_reader;

GRANT SELECT ON SCHEMA::analytics TO ai_reader;
DENY SELECT ON SCHEMA::dbo TO ai_reader;
DENY INSERT, UPDATE, DELETE, EXECUTE TO ai_reader;

Layer on what your engine offers: row-level security where different users should see different slices, masked or excluded PII columns (the views from Part 2 are the natural enforcement point), query timeouts, and result caps. Point the assistant at a read replica or warehouse rather than the primary OLTP database, both for safety and so an expensive question never slows checkout. The design goal is that the worst possible outcome of any generated query is a slow, wrong, harmless read, and even prompt injection buys an attacker nothing the role could not already see.

Plugging into where your team works: MCP

A production assistant should not be one more browser tab. The Model Context Protocol (MCP), the open standard Anthropic introduced in late 2024 and the industry broadly adopted, lets you expose your database capabilities as a set of tools that any MCP-capable client (Claude, IDEs, internal apps) can use. In practice you wrap everything this series built into a small tool surface: get the enriched schema, look up glossary terms, ground a value, run a guarded read-only query, fetch similar golden queries. Your context layer becomes infrastructure, consumable everywhere your team already works, instead of logic trapped inside one chat UI. We build MCP servers over production warehouses as a core service, and this composability is the reason.

Evaluation: replace vibes with a number

Here is the uncomfortable question for any text to SQL deployment: what is your accuracy? Not "it usually seems right." A number, on your data, that you re-measure every time you change a prompt, a model, or the schema. Without it, every change is a gamble and every user-reported error is an anecdote you cannot prioritize.

The method is exactly what the benchmarks use, pointed at your own database. Hold out 30 to 50 verified question-SQL pairs (grown from the Part 3 flywheel, disjoint from the retrieval set). For each, execute the generated SQL and the gold SQL and compare result sets, not SQL text, since different queries can be equally correct. The pass rate is your execution accuracy.

CREATE TABLE ai_eval.runs (
    run_id        UUID,
    question_id   INT,
    generated_sql TEXT,
    executed_ok   BOOLEAN,
    result_match  BOOLEAN,
    latency_ms    INT,
    created_at    TIMESTAMPTZ DEFAULT now()
);

-- Accuracy per run, tracked over time
SELECT run_id,
       AVG(CASE WHEN result_match THEN 1.0 ELSE 0 END) AS execution_accuracy,
       AVG(latency_ms) AS avg_latency_ms
FROM ai_eval.runs
GROUP BY run_id
ORDER BY MIN(created_at);

Run it in CI on every prompt or context change. When accuracy drops, you find out from the harness, not from your CFO. One more adversarial note this series owes you: even the public benchmarks we have cited carry known annotation errors, and researchers have shown that correcting them reshuffles leaderboard rankings. Treat published numbers as directional, and treat your own eval set, on your own data, as the only number that governs decisions.

In production, log every interaction: question, retrieved context, generated SQL, outcome, and the user's thumbs up or down. A short weekly review of failures feeds the flywheel: each one becomes a new golden query, glossary entry, or schema description. This loop, more than any single technique, is why mature systems keep improving after launch.

What about fine-tuning?

Last, deliberately. Once context, retrieval, correction, and evals are in place, fine-tuning a model on your query patterns can add a further increment, and the open model results are real: Snowflake's Arctic-Text2SQL-R1 work showed relatively small tuned models topping the BIRD leaderboard against far larger general models in 2025. But weights go stale with every schema change, and training runs are a poor substitute for an example library you can edit in seconds. Earn the right to fine-tune by maxing out everything cheaper first; many businesses never need to.

The complete architecture

The full stack, assembled across this series: a request pipeline that grounds values, retrieves golden queries and glossary entries, assembles enriched schema context, generates against views or a semantic layer, validates with sqlglot, executes through a locked-down read-only role, self-corrects on failures, and logs everything; an eval harness gating every change; and a feedback flywheel turning real usage into growing accuracy. Every layer exists because Part 1's baseline approach fails without it. That is the difference between a demo and a system a business runs on.

Ship it with Field Craft Labs

This series is our working playbook. Field Craft Labs designs and builds production text to SQL systems end to end for small and mid-sized businesses: schema and context audit, reporting views and documentation, golden query and glossary buildout, guarded deployment with MCP integration, and an eval harness that proves the accuracy number before your team depends on it. The outcome is simple: anyone on your team can retrieve data and write reports in plain English, with answers you can defend.

We have built this stack over live SQL Server, PostgreSQL, and ClickHouse environments, and we would rather show you than tell you. Book a call and bring a hard question about your own data.

Start of series: Part 1: Why the Baseline Approach Fails.