Valtech · 2025 · Delivered
Migration Validation Platform
Six-Agent SQL Validation Framework
A six-stage validation pipeline that turns plain-English test cases into executable SQL, runs them against the warehouse, and reports pass/fail per table — cutting per-table validation from five days to one.
400+
test cases per run
5d → 1d
per-table validation
~80%
cycle-time reduction
~3.8
person-years projected saved
Role
Sole engineer — design, build, eval
Status
Delivered · demoable in a browser, no warehouse access required
The problem
Migrating a data platform is the easy half of a migration project. Proving the migrated data is correct — right row counts, right types, transformation logic preserved, referential integrity intact, business rules still hold — is the half that eats person-years.
Traditionally this is done by an analyst opening the source and target tables side by side and writing one-off SQL, table by table, column by column. It's slow, inconsistent between analysts, hard to re-run when the migration changes, and it doesn't scale past a handful of tables without turning into a full-time job for several people.
The target environment was a Microsoft Fabric Lakehouse (Spark SQL). That created a second, quieter problem: Fabric access is permissioned, so most people who'd want to see how the validation actually works — reviewers, other teams, anyone evaluating the approach — simply don't have a seat. A tool that's genuinely useful but impossible to show is a hard sell.
The approach: a six-stage validation pipeline
Rather than one script that does everything, the system is split into six focused stages, each independently testable and swappable. That separation of concerns is also what later made the tool portable — see below.
1. Schema Extractor
Connects to the warehouse and pulls live schema for every table: column names, types, nullability, row counts, sample rows, and basic numeric stats. This becomes the grounding context for every SQL query generated later — the model never guesses a column name or type — and results are cached per run so a 400-test batch doesn't re-query the same table schema 400 times.
2. Test-Case Ingestion
Business analysts write validation rules as plain English in a spreadsheet ("customer_id is never null", "loan balance is always positive", "no orphaned records in the payments table"), one row per test, mapped to a target table and column. The agent reads this directly, so non-engineers can extend test coverage without touching code or waiting on an engineer.
3. SQL Generator
Turns each English test case into executable SQL — a hybrid, not a pure LLM call. A pattern library handles the common cases (not-null, uniqueness, positive-value, foreign-key-integrity) with deterministic templated SQL: fast, free, and 100% consistent every run. Anything that doesn't match a known pattern falls through to GPT-4, prompted with the actual schema, sample rows and table description, and constrained to always return a single test_result column in Spark-SQL-compatible syntax.
4. Test Executor
Runs each generated query against the warehouse, captures PASS/FAIL/ERROR, execution time, and the raw error message for anything that broke, rather than letting a single failure kill the run. Tests whose SQL failed to generate are marked SKIPPED rather than silently dropped, so nothing goes unaccounted for in the final report.
5. Orchestrator
Chains the above into one repeatable pipeline: extract schema → load test cases → generate SQL → execute → summarize. Supports filtering by table, column or test ID, so a single table can be re-validated in seconds after a fix instead of re-running the whole suite.
6. Reporting
Every run is persisted to timestamped CSV/JSON, with failed tests broken out into their own file so a data engineer can jump straight to what needs fixing, plus a console summary — pass rate, timing, per-test breakdown — at the end of every run.
The hybrid SQL generator was a deliberate trade-off
Templated SQL for the tests that are structurally identical, LLM generation reserved for genuinely novel business logic. That split also shrank the eval surface — only the LLM-generated fraction needed manual SQL review, not the whole suite.
Making it demoable: swapping the backend for DuckDB
Because schema extraction and query execution are cleanly isolated behind their own modules, the Fabric/Spark SQL dependency touched only two files — the schema extractor and the test executor, both of which just needed a connection object. Re-pointing those at DuckDB — an embedded, zero-install, file-based SQL engine — loaded with representative sample data instead of the live warehouse, made the whole thing portable.
Everything else — test-case parsing, the pattern library, the LLM prompting, the pass/fail reporting — is unchanged. The generated SQL is the same SQL that would run against the lakehouse; only the connection target differs. That makes the full validation flow — English test case in, SQL generated, query executed, pass/fail out — runnable locally in seconds, with no cluster, no credentials, and no client data anywhere in the loop.
What I'd do differently
The pattern-matching layer grew organically as test cases were reviewed; building it test-case-first — mining the spreadsheet for recurring phrasing before writing patterns — would have caught more of the common cases up front and reduced LLM calls further.
Sample-data-in-prompt is useful grounding but adds token cost per call at scale; worth revisiting with a cached, compact per-table schema summary instead of re-sending sample rows on every generation call.
Right now a failed SQL generation and a failed SQL execution both surface as red flags in the report — splitting those out more clearly would make it faster to tell "the model wrote bad SQL" from "the data genuinely fails the rule."
Stack