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

> Source: <https://dev.to/dataio_4921/shadow-gate-your-llm-generated-sql-a-replay-test-against-a-frozen-fixture-database-2cce>
> Published: 2026-08-10 08:11:39+00:00

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 artifact below is a working skeleton you can adapt.

``` python
# 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.

```
# 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.

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.

``` python
# 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.`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.)| 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).

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.