---

How to automatically validate migrated SQL with generated test datasets

---

Summary

When modernizing data platforms, SQL logic is often rewritten or migrated — for example, from Azure Databricks to Snowflake, or between different engines within the same architecture.
The challenge: how do we prove that these migrated or optimized queries still produce the same results?

In this article, we show how to use synthetic and golden datasets to automatically validate query equivalence across modern data platforms — without exposing production data.

The challenge: ensuring semantic equivalence after migration

As SQL workloads move from Databricks to Snowflake (or vice versa), subtle differences can arise even when syntax looks identical.
For example:

• Databricks uses date_format() and date_sub()
• Snowflake uses TO_VARCHAR() and DATEADD()
• Databricks timestamps have millisecond precision
• Snowflake timestamps default to microseconds

Those tiny differences can lead to rounding mismatches, missing rows, or drift in aggregation results.
And when you’re migrating hundreds of queries, manually checking output parity becomes impossible.

What’s needed is an automated equivalence test: one that runs both versions of a query on the same controlled dataset, canonicalizes the outputs, and flags any true semantic differences.

---

Why synthetic data is the solution

Synthetic data allows you to validate logic safely and deterministically.
 It can reproduce:

• edge cases like NULL handling and out-of-range dates,
• duplicates and referential gaps,
• text variations (casing, whitespace, locale differences), and
• controlled numeric precision for float comparisons.

Because the data is generated on demand, you can:

• test transformations repeatedly,
• simulate thousands of variations, and
• do so without touching production data.

Synthetic data essentially makes SQL validation reproducible, explainable, and compliant.

---

The hybrid dataset approach

A reliable validation framework combines three data sources:

This hybrid approach maximizes test coverage and realism.

---

The architecture of the validation framework

The testing flow for Databricks–Snowflake equivalence is straightforward:

Define schema metadata
Store structure, constraints, and relationships in YAML or DuckDB:

tables:
orders:
columns:
order_id: int
customer_id: int
amount: double
status: string
order_date: date
fk: { customer_id: customers.id }
customers:
columns:
id: int
country: string
signup_date: date
is_vip: boolean
pk: id

Generate synthetic data
Use the metadata to create reproducible DataFrames, with realistic distributions and boundary cases.

Deploy the same data to both environments

• Write as Delta tables in Azure Databricks.
• Stage the same Parquet files in Snowflake via external stage or Snowpipe.

Execute both queries
Run using respective connectors (databricks-sql-connector and snowflake-connector-python).

Canonicalize results

• Sort by key columns.
• Round floats to consistent precision.
• Normalize timestamp formats and NULL representations.

Compare outputs
 Compute checksums, row-level diffs, and sample mismatches.

Integrate into CI/CD
Run as part of your pull request validation or Databricks Workflow jobs.

---

Example: Azure Databricks → Snowflake migration

Here’s a realistic SQL migration scenario:

Azure Databricks version

SELECT
customer_id,
COALESCE(amount, 0) AS total_amount,
current_timestamp() AS load_ts,
date_format(order_date, ‘yyyy-MM-dd’) AS order_day
FROM orders
WHERE status NOT IN (‘cancelled’, ‘returned’)
AND order_date >= date_sub(current_date(), 30)

Snowflake version

SELECT
customer_id,
IFNULL(amount, 0) AS total_amount,
CURRENT_TIMESTAMP() AS load_ts,
TO_VARCHAR(order_date, ‘YYYY-MM-DD’) AS order_day
FROM orders
WHERE status NOT IN (‘cancelled’, ‘returned’)
AND order_date >= DATEADD(day, -30, CURRENT_DATE())

At first glance, these queries appear equivalent — but the following differences matter.

---

Example: Python-based test harness

import duckdb, pandas as pd, numpy as np

def gen_orders(n=200):
df = pd.DataFrame({
“order_id”: np.arange(1, n+1),
“customer_id”: np.random.randint(1, 50, n),
“amount”: np.round(np.random.normal(100, 50, n), 2),
“status”: np.random.choice([“open”,”closed”,”cancelled”,”returned”], n),
“order_date”: pd.to_datetime(“2024-12-01”) – pd.to_timedelta(np.random.randint(0, 60, n), unit=”D”)
})
return df

Synthetic dataset

orders = gen_orders()
con = duckdb.connect()
con.register(“orders”, orders)

Execute both SQL versions locally

databricks_sql = “”“SELECT …”“” # Databricks query
snowflake_sql = “”“SELECT …”“” # Snowflake query
res_dbx = con.execute(databricks_sql).df()
res_snow = con.execute(snowflake_sql).df()

Canonicalize and compare

def canonicalize(df):
return df.sort_values(df.columns.tolist()).reset_index(drop=True).round(6)
assert canonicalize(res_dbx).equals(canonicalize(res_snow)), “Mismatch detected!”

This framework runs fast, works offline, and scales to thousands of queries.

---

Bringing GenAI into the loop

Generative AI can make this process more intelligent by:

• Inferring schema constraints directly from SQL.
• Generating new edge cases when logic changes (e.g., detecting NULL-sensitive expressions).
• Explaining differences between query results in natural language.
• Proposing equivalent rewrites when mismatches occur.

In this setup, GenAI acts as both assistant and auditor — continuously learning which differences matter and which are safe.

---

CI/CD in a modern data stack

Integrated into your platform, the process becomes continuous:

1. Developer commits updated SQL to Git.
2. Pipeline triggers synthetic data tests using Databricks or DuckDB.
3. Framework executes equivalent queries on both Databricks and Snowflake.
4. Results and diffs are stored in a Delta table for traceability.
5. CI/CD gates enforce semantic parity before merge or deployment.

This makes query correctness a measurable, automated deliverable — not a manual review step.

---

Key Takeaways

✅ Synthetic data can safely reproduce critical logic edge cases.
✅ Metadata defines structure, constraints, and data distributions.
✅ Canonicalization ensures fair comparison across SQL engines.
✅ GenAI can infer rules and highlight mismatches automatically.
✅ CI/CD integration transforms semantic testing into a continuous process.

---

Why this matters

Data modernization isn’t just about moving pipelines — it’s about preserving business meaning. By combining synthetic data generation, metadata-driven validation, and GenAI-assisted insights, we can ensure that every SQL rewrite or migration delivers identical semantics with measurable confidence.

This approach turns “trust but verify” into “trust because verified.”

---

🧩 Related articles

• Metadata-Driven SQL Optimization and Migration
• Validating SQL Query Equivalence with Python & GenAI
• GenAI-assisted Data Quality and Plausibility Checks