How to Catch Silent SQL Drift in LLM Pipelines Using Frozen Fixture Tests

A software developer has outlined a method called a 'shadow gate' to detect subtle errors in SQL generated by large language models (LLMs), even when the queries appear to run correctly. The core problem addressed is semantic drift, where LLM-generated SQL silently returns wrong data after a prompt tweak, model swap, or provider update, without triggering any linter or unit test failures. The proposed solution involves a lightweight CI job that replays a fixed set of analyst questions through the current LLM pipeline and executes the resulting SQL against a frozen DuckDB fixture database built from version-controlled CSVs. Generated query results are then compared against pre-committed golden output snapshots using explicit rules for float tolerance, column order, and row ordering. The fixture data is intentionally designed with edge cases such as NULL values, duplicate names, and date boundaries to ensure the tests catch real-world failure patterns.