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