Shadow-Gate Your LLM-Generated SQL: A Replay Test Against a Frozen Fixture Database

In a previous post I built a zero-budget eval harness to score LLM-generated SQL before adopting a prompt. That harness answers one question: "is this generation setup any good?" This post answers a different, more operationally painful one: "the generation setup was good last month — is it still good today, after we tweaked the prompt, swapped the model, or the provider shipped a silent update?"

The failure mode I care about here is semantic drift: the SQL still parses, still runs, still returns rows — but the rows are subtly wrong. A LEFT JOIN quietly becomes an INNER JOIN. A timezone boundary shifts. NULL handling changes. Nothing throws, so your linter and your unit tests on the application code stay green while a dashboard silently lies.

The fix I'll walk through is a shadow gate: a small CI job that replays a fixed suite of analyst questions through your current LLM generation path, executes the resulting SQL against a frozen fixture database, and diffs the result sets against committed golden snapshots. No production data, no warehouse access, no credentials in CI.

The components

1. A frozen fixture database. A DuckDB file built from CSVs checked into the repo. Frozen means it changes only through reviewed PRs, never as a side effect of the test run.
2. A question suite. 15–40 real analyst questions with known-correct SQL, curated from actual incidents and code review history.
3. A generation step. Your real prompt template, filled with the real schema, run against whatever model you currently use.
4. A diff step. Execute generated SQL on the fixture DB, compare to golden output with explicit rules for column order, float tolerance, and row ordering.

The artifact below is a working skeleton you can adapt.

Step 1: Freeze a fixture database

# build_fixture.py — run once per fixture change, via PR only
import duckdb

con = duckdb.connect("fixture.duckdb")
con.execute("""
    CREATE TABLE orders AS
    SELECT * FROM read_csv_auto('fixtures/orders.csv', header=true);
""")
con.execute("""
    CREATE TABLE customers AS
    SELECT * FROM read_csv_auto('fixtures/customers.csv', header=true);
""")
con.close()

Keep the CSVs small (hundreds of rows) but adversarial: include duplicate customer names, NULL region values, orders exactly on a date boundary, a currency code that appears once. Golden-file tests are only as good as their edge cases — this is where prior production bugs earn their keep.

Step 2: The question suite

# suite.yaml
- id: q001
  question: "Total revenue per region for 2025, excluding cancelled orders"
  golden_sql: |
    SELECT c.region, SUM(o.amount) AS revenue
    FROM orders o JOIN customers c USING (customer_id)
    WHERE o.status <> 'cancelled'
      AND o.order_date >= DATE '2025-01-01'
      AND o.order_date <  DATE '2026-01-01'
    GROUP BY c.region
    ORDER BY c.region;
  order_matters: true

- id: q002
  question: "Customers with no orders, including those with NULL region"
  golden_sql: |
    SELECT c.customer_id, c.region
    FROM customers c
    LEFT JOIN orders o USING (customer_id)
    WHERE o.order_id IS NULL
    ORDER BY c.customer_id;
  order_matters: true

q002 is deliberately a trap: models love converting LEFT JOIN ... IS NULL into NOT IN, which silently drops nothing here but changes semantics the moment customer_id can be NULL in orders. Encode your scars.

Step 3: Generate and replay

For the generation step in CI you need a model endpoint that won't bill you per experiment and doesn't require you to stand up GPU infrastructure. I've been running this class of job through MonkeyCode, which offers free model access and a free server option — that combination maps well onto a CI job that fires a few dozen generation calls per PR and needs a throwaway runner. Disclosure: This article was prepared as part of MonkeyCode's product outreach. The gate logic below is provider-agnostic, though — swap the generate() body for whatever endpoint you use, including a local model, and nothing else changes.

# shadow_gate.py
import duckdb, yaml, sys, json

TOLERANCE = 1e-6

def generate(question: str, schema_ddl: str) -> str:
    """Fill your real prompt template; call your model endpoint here.
    Must return raw SQL only (strip markdown fences)."""
    ...  # provider-specific call

def run(con, sql):
    try:
        rel = con.execute(sql)
        cols = [d[0] for d in rel.description]
        return (cols, rel.fetchall(), None)
    except Exception as e:
        return (None, None, str(e))

def rows_equal(golden, candidate, tol=TOLERANCE):
    if len(golden) != len(candidate):
        return False
    for g_row, c_row in zip(golden, candidate):
        for g, c in zip(g_row, c_row):
            if isinstance(g, float) or isinstance(c, float):
                try:
                    if abs(float(g) - float(c)) > tol:
                        return False
                except (TypeError, ValueError):
                    return False
            elif g != c:
                return False
    return True

def main():
    con = duckdb.connect("fixture.duckdb", read_only=True)
    schema_ddl = open("schema.sql").read()
    suite = yaml.safe_load(open("suite.yaml"))
    failures = []

    for case in suite:
        gen_sql = generate(case["question"], schema_ddl)
        g_cols, g_rows, g_err = run(con, case["golden_sql"])
        c_cols, c_rows, c_err = run(con, gen_sql)

        if c_err:
            failures.append({"id": case["id"], "kind": "execution_error",
                             "detail": c_err, "sql": gen_sql})
            continue
        if [c.lower() for c in g_cols] != [c.lower() for c in c_cols]:
            failures.append({"id": case["id"], "kind": "column_mismatch",
                             "detail": f"{g_cols} vs {c_cols}", "sql": gen_sql})
            continue
        g_sorted = g_rows if case.get("order_matters") else sorted(map(str, g_rows))
        c_sorted = c_rows if case.get("order_matters") else sorted(map(str, c_rows))
        if not rows_equal(g_sorted, c_sorted):
            failures.append({"id": case["id"], "kind": "semantic_drift",
                             "detail": "result sets differ", "sql": gen_sql})

    print(json.dumps({"total": len(suite), "failures": failures}, indent=2, default=str))
    sys.exit(1 if failures else 0)

if __name__ == "__main__":
    main()

Three design decisions worth defending:

• read_only=True on the connection. Generated SQL is untrusted input. A hallucinated DELETE against your fixture shouldn't be survivable. (For a real warehouse you'd also want a statement timeout and a deny-list on DDL/DML keywords before execution.)
• Exact match on columns, tolerance on floats, configurable row order. Anything looser misses real drift; anything stricter drowns you in noise from harmless formatting differences.
• The failure report includes the generated SQL. When the gate goes red, the first thing a reviewer needs is the diff between golden and generated query text — not a stack trace.

Step 4: When to run it

Trigger	Why
PR touching the prompt template or schema context	The obvious case; drift source is explicit
Model version change in config	Provider-side behavior changes are the classic silent-drift source
Nightly scheduled run	Catches provider updates that happen without any change on your side
Every application code PR	Overkill unless generation is on the hot path; cost and latency rarely justify it

Gate policy matters more than gate mechanics. My recommendation: semantic_drift failures block merge, execution_error failures block merge only if they didn't occur on the previous nightly baseline (a model that occasionally emits unparseable SQL may be tolerable in a human-in-the-loop workflow, but a regression in parse rate is not).

Limitations and who should skip this

• It tests equivalence to golden SQL, not correctness of intent. If your golden SQL encodes a wrong business rule, the gate faithfully protects a wrong answer. Golden queries need the same review rigor as production code.
• Sampling cost. A single generation per question will flake on borderline cases. For a tighter signal, generate k=3 samples per question and require a majority to match — which triples your model calls per run, so budget accordingly.
• Frozen fixtures age. When the real schema evolves, fixture and goldens must evolve in the same PR, or the gate tests a database that no longer exists.
• This is not a substitute for an adoption-time eval. The eval harness scores candidates before you pick a generation setup; the shadow gate watches the setup you picked. They answer different questions and you probably want both — but if you can only build one and your generation setup is stable, build the gate.
• Skip this entirely if generated SQL never runs unattended, if you have fewer than ~10 recurring question patterns (manual review is cheaper), or if your queries are trivially re-derived from an ORM (there's no free-form generation surface to drift).

Closing

The uncomfortable truth about LLM-generated SQL is that "it still runs" is a nearly meaningless health signal. A replay gate against a frozen fixture is the cheapest way I know to convert silent semantic drift into a loud, reviewable diff. If you want to try the pattern without provisioning anything, MonkeyCode's free model access and free server tier is a low-friction place to host the generation step while you find out whether your suite catches anything — mine caught a JOIN-flavor regression within the first week, which paid for the setup effort on the spot.