Jaco van der Laan

Validating SQL Query Equivalence with Python & GenAI

12/10/2025  ·  Data Engineering  ·  GenAI / AI-Assisted  ·  SQL Code Generation

‹ ›

How to automatically test that optimized or migrated SQL still produces the same results

Summary

When we migrate or optimize SQL queries — for performance, readability, or platform migration — we risk breaking semantics.
This article shows how to build a Python-based differential testing framework that automatically validates whether an optimized query produces the same results as the original, using synthetic and golden datasets, property-based testing, and canonicalized output comparison.
It’s like unit testing for SQL — but smarter, reproducible, and GenAI-assisted.

The problem: SQL optimization without breaking semantics

In real data engineering projects, queries evolve.
They get refactored for:

  • performance (CTE inlining, join reordering),
  • platform migration (e.g., from Oracle to Databricks),
  • code clarity (macros, dbt models, Jinja templating).

Yet every “improvement” introduces the risk of changing semantics.
Even something as simple as reordering joins or pushing filters earlier can create subtle data differences — especially when NULLs, duplicates, or float rounding come into play.

So how can we prove equivalence automatically, without manually inspecting millions of rows?

The principle: Differential Testing for SQL

The idea is borrowed from compiler verification: run both versions of a program on the same inputs and compare outputs.
If they’re identical (after canonicalization), the transformation is semantically safe.

Our testing framework for SQL follows six steps:

  1. Define equivalence — same schema, same rows, within float tolerance.
  2. Generate diverse datasets — realistic, edge-case, and random.
  3. Run both queries — old vs new.
  4. Canonicalize outputs — align order, dtypes, nulls, rounding.
  5. Compare and report — diff, count mismatches, show examples.
  6. Automate — run in CI/CD with reproducible seeds.

Step 1. Define what “same results” really means

Equality in SQL isn’t trivial.
We need clear, engine-independent rules:

With these principles, your tests become deterministic and fair.

Step 2. Build a Hybrid Dataset Strategy

You’ll need multiple data sources to cover the unknowns.

  • Golden datasets — curated corner cases that historically break logic (NULL joins, duplicates, boundaries).
  • Synthetic data — generated automatically, respecting keys, constraints, and domain ranges.
  • Production samples — optional, but useful for statistical sanity checks.

By mixing these, your tests become resilient to hidden semantics.

Step 3. Canonicalize before comparison

Before comparing, normalize everything:

  • sort rows deterministically,
  • align column order and types,
  • round floats,
  • replace missing values consistently.

This ensures we compare semantics, not formatting.

def canonicalize(df):
df2 = df.reindex(sorted(df.columns), axis=1)
df2 = df2.sort_values(list(df2.columns), kind=”mergesort”, na_position=”last”)
df2 = df2.reset_index(drop=True)
return df2.round(10)

Step 4. Compare with actionable diffs

The output should not just say “different”.
It should explain how.

For example:

❌ Row count mismatch: A=12450, B=12449
⚠️ Missing row example: {‘customer_id’: 1023, ‘amount’: 0.0}
⚠️ Mismatched float: amount differs by 1e-8

This gives engineers confidence — and the context to fix it.

Step 5. Automate the loop

All of this can be wrapped in a Python test harness using DuckDB and Hypothesis:

import duckdb, pandas as pd, numpy as np
from hypothesis import given, settings, strategies as st

@given(st.just(0))
@settings(max_examples=25)
def test_equivalence(_):
customers = gen_customers().example()
orders = gen_orders(customers)
res1 = duckdb.query(ORIGINAL_SQL).to_df()
res2 = duckdb.query(OPTIMIZED_SQL).to_df()
a = canonicalize(res1)
b = canonicalize(res2)
assert a.equals(b), “Mismatch detected!”

You can expand this to:

  • read schemas from YAML or a catalog,
  • generate tables dynamically,
  • connect to Databricks or BigQuery,
  • and push results to CI/CD pipelines.

Step 6. Add GenAI for test case synthesis

GenAI can act as a test data generator and referee:

  • Propose new edge cases based on schema semantics.
  • Suggest invariants (“If order amount < 0, exclude row”).
  • Autogenerate Hypothesis strategies for unseen column types.
  • Summarize test diffs in plain English.

This makes your testing framework self-learning — adapting as new queries or anomalies appear.

CI/CD Integration Example

  1. Pre-commit hook: run fast DuckDB equivalence checks locally.
  2. Pull Request: rerun on sampled + synthetic data, upload diff report.
  3. Nightly job: stress test with 100+ generated datasets.
  4. Release gate: fail build if any semantic diff > threshold.

That’s how data teams keep SQL logic safe across optimization and migration.

Key Takeaways

✅ Treat SQL like code — it deserves unit tests.
✅ Automate equivalence checks between old and optimized queries.
✅ Use canonicalization to remove irrelevant differences.
✅ Generate data with property-based testing to explore edge cases.
✅ Integrate into CI to prevent regressions before they hit production.
✅ Let GenAI propose new test scenarios and summarize diffs.

Why it matters

Migrating hundreds of analytical queries to new platforms (like Databricks or BigQuery) becomes manageable only when correctness can be proven automatically. This approach transforms SQL testing from manual inspection into continuous semantic assurance — fast, explainable, and repeatable.

By combining Python, DuckDB, and generative AI, we can finally bring test-driven discipline to the world of SQL.

🧩 Related articles