SQLGlot Transpilation Tests for Open Data Migrations

A query can transpile cleanly and still change the meaning of the business.

SQL portability is not string replacement

SQLGlot is a Python SQL parser and transpiler. It can help teams move SQL across dialects, but open data migrations need more than converted syntax. They need evidence that meaning, lineage, and contracts survived the move.

Dialect differences show up in null handling, timestamp behavior, identifier rules, functions, data types, and optimizer assumptions. A migration that ignores those details can produce correct-looking SQL with wrong answers.

Transpilation tests need semantic checks

A good transpilation test suite compares source SQL, target SQL, expected output shape, sample result sets, lineage edges, policy filters, and data contract expectations. It should also keep the dialect edge cases that caused past bugs.

The AST matters because it gives teams structure to inspect. The output SQL matters because it runs. The result comparison matters because users care about meaning.

Migration evidence should travel

Open Data Infrastructure should attach transpilation tests to catalog metadata, lineage, and data contracts. When a data product moves engines, the migration record should show which queries changed, which contracts were tested, and which downstream products were affected.

For adjacent context, read SQLGlot dialect drift budgets, SQLGlot lineage for portable data models, and migration without downtime.

What breaks first

• Tests assert that SQL was generated, not that results match.
• Lineage is rebuilt after migration and loses the review trail.
• Policy filters change position or meaning during dialect conversion.
• Edge cases live in issue threads instead of a permanent migration suite.

Questions to ask

Ask which dialect differences are covered, which result fixtures exist, and which lineage edges are expected after conversion. Ask whether the migration suite can explain a changed answer before a user finds it.

Sources to start with

These primary sources anchor the technical claims in this guide.

• SQLGlot documentation
• SQLGlot GitHub repository
• OpenLineage object model documentation
• W3C PROV overview

Portable SQL is useful. Portable meaning is the goal.