SQL Dialect Translation: By Hand vs With an AI Agent
What the manual rewrite costs, what an agent takes over, and what still needs your sign-off
SQL dialect translation by hand means rewriting each statement for the target warehouse, then proving the result returns the same rows, and the hard part is the second half. Types alone show why: Snowflake's NUMBER type holds up to 38 digits of precision (Snowflake numeric data types), while BigQuery's NUMERIC has 38 digits of precision with a fixed scale of 9 (BigQuery data types). A rename that compiles can still change the answer. An agent can do the mechanical rewrite and flag what is not portable; a human still decides whether the result is correct.
Key Takeaways
- •Dialect translation fails quietly: a rewritten statement that runs is not evidence that it returns the same result.
- •The mechanical work (function renames, syntax shape, identifier quoting) is where an agent saves effort; the semantic review is where humans still earn their keep.
- •The useful output of a translation agent is the translated statement plus a list of flagged non-portable constructs, not the statement alone.
- •Every translated statement that touches money, counts or access rules needs a human approval before it replaces production SQL.
- •Evaluate an agent on a corpus of your own statements, with known-tricky constructs, and measure how many it flags rather than how many it silently converts.
The job and who does it today
Dialect translation shows up whenever a team changes warehouses, consolidates two stacks after an acquisition, or moves a workload to a cheaper engine. The inventory is rarely small: views, stored procedures, dbt models, BI extracts, and the one-off queries buried in notebooks. Someone has to turn each of those into the target dialect, and that someone is usually an analytics engineer or a senior data engineer who already has a full queue.
The work lands on experienced people because the failure mode is subtle. A junior engineer can swap function names from a cheat sheet, but spotting that a null-handling rule, a timezone default or an identifier-case convention changed requires knowing both engines. So the job is both repetitive and judgment-heavy, which is the worst mix for a team trying to protect its roadmap.
We scope this post to one unit of work: a single SQL statement moving between warehouse dialects. Whole-migration planning, data movement and cutover are separate jobs. Keeping the unit small is deliberate, because a statement is the smallest thing you can translate, review and test on its own, and it is the unit our dw-sql-dialect-translation skill works on.
The manual workflow, step by step
The manual path usually runs in five steps. First, someone exports or collects the source statements and decides the order. Second, an engineer rewrites each one, working from the target warehouse's documentation and whatever notes the team has accumulated. Third, they run the new statement against the target. Fourth, they compare outputs against the source system. Fifth, a reviewer reads the diff and approves it. Each step has a handoff, and each handoff is a chance to lose context.
The rewrite step is mostly lookup. Snowflake's DATEADD(day, 7, order_date) becomes DATE_ADD(order_date, INTERVAL 7 DAY) in BigQuery. IFF(cond, a, b) becomes IF(cond, a, b). A LATERAL FLATTEN over a VARIANT column becomes an UNNEST over an array, and colon-path access into semi-structured data becomes a JSON function call. None of this is hard individually; the cost is volume and the need to remember every case on every statement.
The compare step is where time goes. The engineer needs row counts, aggregates and sampled rows from both systems for the same logical input, which means the source and target must hold the same data at the same point in time. If the data load is still in flight, a mismatch could be a translation bug or a load lag, and the engineer has to work out which before touching the SQL.
Where the manual workflow breaks
The first break is silent semantic drift. Numeric types are the cleanest example: the 38-digit precision of Snowflake NUMBER versus BigQuery's NUMERIC scale of 9 means a column with more than nine decimal places can lose resolution on the way across. BigQuery also offers BIGNUMERIC, documented at 76 digits of precision, so the fix exists, but only if someone notices the column needs it. A statement that runs and returns plausible numbers will not tell you.
The second break is identifier and case rules. Snowflake folds unquoted identifiers to upper case, while other engines preserve or fold differently, so a statement that referenced Orders and ORDERS interchangeably can break or, worse, resolve to the wrong object. Quoted identifiers copied across mechanically can lock in a case convention the target team never wanted.
The third break is consistency. Ten engineers translating five hundred statements will translate the same construct ten ways, and the reviewer sees each statement in isolation. A shared cheat sheet helps, but it ages as soon as someone finds a new edge case and does not tell the rest of the team. The result is a migrated codebase that works but reads like a patchwork, which raises the cost of every later change.
What an agent does instead
The Migration Agent runs the dw-sql-dialect-translation skill, which is scoped narrowly: translate a single SQL statement between warehouse dialects, preserve semantics, and flag non-portable constructs. That is the whole claim. The agent takes a source statement and the source and target dialects, and returns a rewritten statement along with a list of the places where the target cannot express the source exactly.
The flag list is the important design choice. A translator that only outputs SQL invites the reviewer to trust it, and trust is exactly what a silent type or null-handling difference exploits. By listing non-portable constructs (a VARIANT path expression, a precision-sensitive numeric, a function with no direct equivalent) the agent turns an invisible risk into a visible review item. The reviewer starts from a short list instead of a blank diff.
Because the agent is an MCP server, a engineer can invoke it from Claude Code, Cursor or any MCP-compatible client, in the same place they already edit SQL. The Model Context Protocol specification defines how tools like this are exposed to clients, which means the translation step does not require a new interface or a separate platform to check. The statement goes in from the editor and the result comes back to it.
Manual vs agent, side by side
| Step | By hand | With the agent |
|---|---|---|
| Rewrite syntax and function names | Engineer looks up each construct in target docs | Agent rewrites the statement in one call |
| Find non-portable constructs | Depends on the engineer's memory of both engines | Agent returns an explicit flag list per statement |
| Consistency across statements | Varies by engineer and by day | Same skill, same handling of the same construct |
| Validate results | Engineer runs both systems and compares | Still a human or test-harness step |
| Approve for production | Reviewer reads the full diff | Reviewer starts from the flagged items, then signs off |
| Effort profile | Scales with statement count and engineer attention | Mechanical work shrinks; review work remains |
What still needs a human approval
Three things stay with people. The first is the decision on every flagged construct. If the agent says a numeric column may lose scale, someone who knows the data decides whether that matters, whether to use a wider type, or whether to change the model. The agent can surface the question, but the answer depends on business meaning it does not own.
The second is output validation. Our skill is defined as translating and flagging, not as certifying equivalence, so row-count and aggregate comparisons remain a step in your process. We would treat any tool that claims to prove equivalence from the SQL text alone with caution, because equivalence depends on the data, the session settings and the engine version.
The third is promotion. In the open-source edition, Data Workers is read-only by default, so nothing is written to your warehouse unless you move to a tier with writes and approval workflows. Even then, a translated statement that replaces a production view should pass through a pull request and a named approver. The agent shortens the path to that review; it does not remove it.
How to evaluate an agent for this job
Start with your own statements, not a vendor demo. Pull a sample of fifty to a hundred real statements and deliberately include the ugly ones: semi-structured access, window functions with QUALIFY, date arithmetic, procedural blocks, and anything that touches high-precision numerics. A test set of only easy SELECTs will make every translator look good.
Score three things separately. Did the output run on the target? Did it return the same result on identical data? And did the agent flag the constructs you already know are risky? The third score is the one that predicts trust. An agent that converts a risky construct without flagging it is worse than one that flags it and leaves the fix to you, because the first hides the problem and the second hands it over.
Finally, check where it runs and what it can touch. Ask whether source SQL leaves your infrastructure, whether the tool is read-only by default, and whether it leaves an audit trail of what it translated. Those answers matter more at migration time than at any other point, because the statements you are translating encode your business logic.
Frequently Asked Questions
Can an AI agent translate SQL between dialects without changing results?
It can translate the syntax reliably for common constructs, but no tool should be trusted to guarantee identical results from the text alone. Differences in types, null handling and session defaults only show up against real data. That is why our skill flags non-portable constructs and why we keep a validation step with a person in the loop.
Which SQL constructs are hardest to translate?
Semi-structured data access, procedural code, high-precision numerics, identifier-case rules and date or timezone functions cause most of the trouble, because the target often has a similar but not identical feature. Plain joins, filters and aggregates usually carry across with only light edits.
Does the agent write to my warehouse?
No, not in the open-source edition, which is Apache 2.0, runs locally and is read-only by default. Writes and approval workflows are part of the Pro tier, and we still recommend routing translated SQL through your normal code review before it replaces anything in production.