Translate SQL Across Dialects with a Transpiler-First Workflow

Deterministic converters handle the 80% of mechanical syntax reliably and repeatably; the LLM earns its keep on the residue — dialect idioms with no direct equivalent, vendor functions, and semantic traps like NULL sorting and implicit casts. Parity queries, not eyeballs, decide when a translation is done.

When to reach for this

Use this for any warehouse-to-warehouse migration involving more than a handful of queries, and for the steady drip of "port this report's SQL" requests during a platform transition.

Triggers:

• •
  A migration wave with dozens-to-thousands of views, procs, or scheduled queries to convert.
• •
  A single gnarly query using vendor features (CONNECT BY, QUALIFY, PIVOT variants, proprietary date math).
• •
  Recurring cross-dialect porting between a client's old and new platforms.

The workflow

1. 1.

   Run the deterministic transpiler first. SnowConvert AI (to Snowflake), Lakebridge transpilers (to Databricks), or sqlglot for lighter-weight cases. Capture its output and its issue report — the items it flags as unconverted or approximated are your actual LLM workload.

2. 2.

   Triage the residue. Classify flagged items: pure syntax (safe to hand the LLM), semantic differences (needs a decision — collation, NULL ordering, integer division, timezone behavior), and features requiring redesign (cursors, dynamic SQL). Only the first two go through translation; the third gets a human design first.

3. 3.

   Prompt the LLM on residue with both dialects pinned.

   Copy

   Translate this Oracle fragment to Snowflake SQL. The deterministic
   transpiler flagged it as unconvertible.
   
   Rules:
   - Semantics must match Oracle exactly. Where Snowflake defaults differ
     (NULL sort order, implicit casts, division), make behavior explicit
     rather than relying on defaults — and add a one-line comment naming
     the difference at each site.
   - If exact semantic equivalence is impossible, say so and present the
     closest options with their differences. Do not silently choose.
   - No refactoring, no reformatting beyond the translation.
   
   Fragment:
   [paste flagged SQL]
   Context DDL:
   [paste table definitions]
   

4. 4.

   Verify each translated object with parity queries — inline, before moving on. Same inputs, both engines, three levels:

   Copy

   -- Level 1: row count
   SELECT COUNT(*) FROM converted_view;
   
   -- Level 2: aggregate fingerprint per natural grouping
   SELECT region, COUNT(*) AS n, SUM(amount) AS amt,
          MIN(order_date) AS d_min, MAX(order_date) AS d_max
   FROM converted_view
   GROUP BY region ORDER BY region;
   
   -- Level 3: sampled full-row comparison on a deterministic sample
   SELECT * FROM converted_view
   WHERE MOD(ABS(order_id), 997) = 0
   ORDER BY order_id;
   

   Run the same three on the source engine and diff results. Level 2 catches most semantic drift cheaply; level 3 catches column-level corruption that aggregates hide.

5. 5.

   Feed mismatches back, scoped and evidenced. Paste the differing rows and the relevant source fragment: "these 14 rows differ in net_amount; the source treats NULL discount as 0, your translation propagates NULL. Fix that one behavior only." Never accept a wholesale regeneration — you would restart verification from zero.

6. 6.

   Bank the patterns. Each resolved semantic trap (Oracle blank-string-as-NULL, Teradata date arithmetic, integer division) goes into a migration-conventions doc that becomes part of every subsequent prompt. Waves get faster because the prompt gets smarter.

Verify before you trust it

• •
  Transpiler issue report fully dispositioned: every flagged item is translated, redesigned, or consciously deferred — none merely deleted.
• •
  Three-level parity (count, aggregate fingerprint, sampled rows) passes per object against identical input snapshots.
• •
  Known trap probes run explicitly: a query touching NULL ordering, one with empty strings, one crossing a DST boundary, one dividing integers.
• •
  Decimal aggregates match exactly, not approximately — rounding differences are findings, not noise.
• •
  The conventions doc has an entry for every semantic difference discovered; a second engineer can apply it without rediscovery.

Pitfalls

• LLM-only translation of whole codebases. It works on the demo, then drifts: inconsistent function mappings across files, hallucinated target-dialect functions. Determinism first is what makes thousand-object migrations auditable.
• Oracle's empty-string-is-NULL. The classic. WHERE col <> '' filters differently on the target; only trap-specific probe queries catch it reliably.
• NULL sort order flipping report output. Oracle sorts NULLs last ascending, others first — invisible to counts and sums, glaring to report consumers. Level-3 sampled comparison with ORDER BY surfaces it.
• Silent function substitution. The LLM maps a vendor function to a "close enough" target function with different edge behavior (rounding mode, week-numbering). The comment-each-difference rule in the prompt forces these into the open.
• Verifying against stale snapshots. If source and target inputs are not frozen identically, parity failures send you chasing translation ghosts that are actually data drift. Snapshot both sides first.

Tools

• SnowConvert AIfree deterministic conversion into Snowflake with assessment reports; the standard first pass on Snowflake-bound migrations.
• Databricks Lakebridgeopen-source transpiler suite (including LLM-based conversion for stubborn dialects) plus a reconcile module, for Databricks targets.
• Claude / frontier LLM assistantthe residue translator and mismatch debugger; pairs with sqlglot when no vendor transpiler fits the pair of dialects.

Client-safe variant

Migration SQL is client IP and often embeds client business vocabulary. Both flagship transpilers run without public AI exposure — SnowConvert AI locally, Lakebridge as OSS in the client environment — so the deterministic 80% never leaves the boundary. For the LLM residue, use Bedrock or Azure OpenAI in the client's tenancy; failing that, genericize identifiers with a scripted mapping before prompting a local Ollama model, and restore them after. Parity queries and snapshots run entirely inside the client's engines, producing a verification report the client can audit without reference to any AI tool.