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

A developer introduced a shadow gate CI job that replays analyst questions through an LLM SQL generation pipeline and diffs results against golden snapshots on a frozen fixture database to catch semantic drift. The approach uses DuckDB and a provider-agnostic generate() function, with MonkeyCode offering free model access for CI runs. The developer emphasized adversarial fixtures and encoding past bugs as golden tests.

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.