| name | sql-migrate |
| description | Convert SQL between dialects (Postgres, MySQL, BigQuery, Snowflake, ClickHouse, DuckDB, …) with duck_diff as the mechanical acceptance loop. Use when asked to migrate, port, translate, or rewrite SQL/views/dbt models to another database or dialect, or to verify a converted query produces identical results. |
SQL dialect migration with a duck_diff acceptance loop
You are converting SQL from one dialect to another. Correctness is decided by
table_diff, not by inspection: a section is DONE only when the diff between
the original's output and the converted query's output is 100% identical.
Setup (once)
- Get a DuckDB with duck_diff:
INSTALL duck_diff FROM community; LOAD duck_diff;
(or LOAD the locally built extension if this is the duck_diff repo).
- Connect DuckDB to the source and/or target systems (postgres/mysql core
extensions, bigquery/snowflake community extensions — see demo/ for
env-var-based ATTACH patterns), or work from exported snapshots.
- Extract and freeze the inputs, in a file-backed cache
(
duckdb migration_cache.db) so iterations are fast and stable:
- List every source table the query reads (FROM / JOIN / CTE inputs).
- Snapshot each one locally:
CREATE TABLE IF NOT EXISTS src_<t> AS FROM postgres_query('pg', 'SELECT * FROM <t>');
- Freeze the expected output — run the original query ON the source engine
once:
CREATE TABLE IF NOT EXISTS golden_<section> AS <original section's output>;
- Do all conversion work against these frozen tables (a DuckDB target
reads the
src_* snapshots directly).
SET TimeZone = 'UTC'; and parameterize nondeterministic inputs
(now(), random()) on both sides before comparing.
The loop — one section at a time
Split the query into sections (CTEs, subqueries, views, dbt models) and
convert them in dependency order. For each section:
- Convert the section's SQL to the target dialect.
- Shape check first:
FROM schema_diff('FROM golden_<s>', $$ <converted> $$) WHERE status <> 'identical'; — fix missing/renamed columns before looking
at rows. (type_differs rows are usually handled by upcasting, below.)
- Acceptance check:
SELECT n_total = n_identical AS accepted FROM table_diff_summary( 'FROM golden_<s>', $$ <converted> $$, pk := '<key>', require_matching_columns := false, upcast_types := true);
- If not accepted, diagnose from the drift — never from re-reading the SQL
alone:
- changed-column histogram:
SELECT unnest(json_keys(diff_data)) col, count(*) FROM table_diff(…) WHERE diff_status = 'different' GROUP BY col ORDER BY 2 DESC;
- 20 concrete counterexamples:
FROM table_diff(…) WHERE diff_status <> 'identical' LIMIT 20;
Fix, re-run step 3.
- Only when accepted: materialize the converted section's output as the
input for downstream sections, record "section
: accepted, N rows",
and move on.
Do not stop, summarize progress as if finished, or skip the acceptance query
for any section. Continue until every section passes.
Tolerances need sign-off
If a diff failure is a genuine engine difference rather than a conversion
bug — float summation order, sub-second timestamp precision, '' vs NULL —
do NOT silently add a tolerance. Show the user the exact drifted rows and
the smallest option that would accept them (numeric_tolerance := <ε>,
timestamp_precision := 'second', null_equals_empty := true) and wait for
their decision. A tolerance is an accepted business difference, not a fix.
Finish
- Keep the acceptance queries: turn them into a regression test (see
examples/ in the duck_diff repo for the sqllogictest pattern) so future
edits to the converted SQL are diffed against the goldens in CI.
- Report per-section row counts and any tolerances the user approved.
