Here's a situation every data engineer knows intimately: you're working with BigQuery for one project, Redshift for another, and ClickHouse for that real-time dashboard. Every query you write has to account for three different dialects, three different null handling behaviors, and three different modulo operators that all seem to handle edge cases slightly differently. The subtle differences let errors and inconsistencies slip through the cracks. One slight difference or error and your entire analysis is wrong.
The typical response is "just pick one database and stick with it." But that's not reality when you're dealing with enterprise data stacks, client requirements, or acquisitions that came with their own infrastructure baggage.
What if you could write all your SQL in one dialect, specifically, the most ergonomic one on the market, and have it transparently execute everywhere? That's exactly what the team behind Coco Alemana built, and the technical details of how they pulled it off are worth understanding.
The Case for a Single SQL Dialect
The core idea is deceptively simple: pick one SQL dialect, write all queries in that dialect, and build a transpiler that converts those queries into whatever the target database actually speaks. The obvious question is which dialect to pick as the source. The Coco Alemana team made an interesting choice, they chose DuckDB as their reference dialect, using DuckDB in production environments as their core execution engine and building their transpiler around its syntax.
Why DuckDB instead of standard ANSI SQL? The answer comes down to three things: ergonomics, type system flexibility, and extensibility.
Ergonomics
DuckDB's dialect is arguably the most developer-friendly SQL variant in existence. Common "gotchas" that plague other dialects simply don't exist. Trailing comma errors? Not a problem. Need to chain functions with dot notation? Built right in. Want to use the shorthand FROM table syntax instead of spelling out SELECT * FROM table? Go for it.
These aren't just cosmetic niceties. They translate into real productivity gains because most of these benefits carry over nicely into other dialects after transpilation.
Type System and Flexibility
DuckDB's type system is both rich and forgiving. Compare 1 to 1.0, no problem. Concatenate a number onto a string, it works. Cast a messy string to a date, DuckDB does what you'd expect without requiring a bunch of hacky workarounds. The software is designed to work for you, not against you.
Because DuckDB's types are a superset of most target dialects, the transpiler can always represent the user's intent locally, and then decide how to lower it into the target dialect's (usually narrower) type system.
Extensibility
DuckDB exposes its Parser, Binder, and function catalog directly through its C++ API. This is the key technical enabler. With access to the internals, the transpiler can plug directly into the Parser, Binder, create custom functions, override existing functionality, and work with the native DuckDB types. The DuckDB extensions workshop by Rusty Conover is an excellent resource for anyone interested in working with these internals.
What the Transpiler Actually Does
The transpiler converts provided DuckDB SQL into an equivalent query in the target dialect. Statement keywords, functions, casts, comparisons, offsets, null handling, everything gets converted to produce a result that is semantically identical to the DuckDB statement.
Take this example of converting a DuckDB query into a ClickHouse query:
In this case, things like the alias assignment, function dot chaining, function names, and null semantics with array slicing are handled automatically. The resulting query is much more complicated than the input, but maintains the correct output.
Some databases require additional session-level settings to match DuckDB behavior. ClickHouse is a prime example, it has roughly a million different session settings. For cases like these, the transpiler creates the converted query and then sets the correct settings at the session level before executing.
How It Works: Crawling the AST
The transpiler structure sits at the core of the product, driving both visual interface changes and custom SQL. Everything gets passed through the transpiler.
The implementation extends DuckDB's internals using C++ to make use of two critical components: the parsed Abstract Syntax Tree (AST) and the Binder. The query tree gets parsed once to maintain the user's query structure, then runs through the Binder to attach types to each expression and column. This type information becomes essential when specific edge cases aren't solvable without knowing the types.
The team built a base class that gives structure to walk each node in the AST, with default implementations that exist for DuckDB. This means that for most extensions, they simply create a new DialectTranspiler (such as RedshiftTranspiler) and only override virtual methods when absolutely necessary.
Internally, the query is structured as a tree of ParsedExpressions, with each node having children or constants depending on its type. This allows re-walking the tree and exposing methods like std::string ConvertExpression(const duckdb::ParsedExpression& expr) on a per-dialect basis.
For a query like:
SELECT name, price % 2.4 FROM products
WHERE price > 10
The AST looks like this:
The Type-Based Correction Nightmare
Not all conversions can occur by looking at the parsed node alone. Some dialects need additional checks at transpile time to prevent runtime issues that wouldn't throw an error but would produce incorrect results.
BigQuery's modulo support is a perfect example of why type information matters. BigQuery doesn't have a % operator at all, the closest thing is MOD(), which only accepts exact numeric types, refuses to mix them, and throws on a zero divisor. DuckDB's % has none of these restrictions, so the correct translation depends entirely on the operand types.
If either side is a float, MOD() can't be used at all. The transpiler falls back to an injected custom function:
-- price is a DOUBLE
SELECT price % 2.4 AS result FROM my_table
-- becomes
SELECT CUSTOM_FMOD(CAST(price AS FLOAT64), CAST(2.4 AS FLOAT64)) AS result FROM my_table
If both sides are plain integers, MOD() works natively, with NULLIF matching DuckDB's behavior of returning NULL on a zero divisor instead of erroring.
If an integer is mixed with a decimal, both sides get promoted to BIGNUMERIC.
The kicker? The first and third cases are character-for-character identical in DuckDB syntax. The only difference is the type of the price column, something the query text alone can never tell you. Without the bound types, there is no correct answer.
IEEE-754 Conformance and the Division by Zero Problem
Floating-point arithmetic is another area where databases diverge wildly. Many database providers don't have consistent arithmetic logic, whether due to legacy implementations, performance concerns, or platform-specific storage format decisions. DuckDB mostly conforms to IEEE 754, and the transpiler has to match its behavior.
Consider division by zero:
-- DuckDB
SELECT 1 / 0, -- Yields `Inf`, Correct
-- BigQuery (without transpilation)
SELECT 1 / 0, -- Yields `ERROR: division by zero: 1 / 0`
-- BigQuery (after proper translation)
SELECT IEEE_DIVIDE(1, 0), -- Yields `Inf` - Correct
The amount of logic required to align these systems for proper, basic arithmetic was enormous, according to the team.
The Design Philosophy: One-to-Many
A key architectural decision was to only convert DuckDB into target dialects, rather than building a many-to-many or bidirectional solution. This drastically simplified the problem and made development faster. While SQLGlot attempts many-to-many conversions, the Coco Alemana team found it often falls short on specific use cases they needed.
The transpiler also guarantees value equality and order equality. Value equality means the result yields the same cell values, columns, and order across systems. Order equality means rows come in the expected order based on the input query, accounting for differences like NULLS FIRST or NULLS LAST defaults.
Testing: The 3,500-Case Safety Net
To guarantee correct results, the team relies on extensive E2E testing and individual unit testing, over 3,500 test cases covering everything from structure to functions, types, null handling, and division consistency. They also make use of DuckDB's existing extensive test suite, combined with custom dialect-specific tests to prevent regressions when adding new dialects or features.
This testing approach was the biggest unlock in terms of development speed, alongside the tree inheritance approach that allows writing minimal code for dialects close to DuckDB's syntax.
What This Means for the Data Engineering Landscape
This transpiler approach represents a shift in how we think about database portability. Instead of forcing teams to standardize on a single database (a nearly impossible task in most organizations), it lets them standardize on a query interface while keeping their existing infrastructure.
The rise of DuckDB and Polars in modern data engineering pipelines has already challenged the assumption that enterprise analytics requires heavyweight infrastructure. This transpiler takes that philosophy further: you can keep your Snowflake cluster running, your Redshift warehouse humming, and your ClickHouse real-time pipeline intact, while writing all your analytical queries in a dialect that doesn't punish you for minor syntax errors.
The broader implication is that database switching costs might finally be coming down. If the query interface becomes database-agnostic, the actual database choice becomes less of a lock-in mechanism and more of a deployment decision based on performance and cost characteristics rather than ecosystem familiarity.
For those already using DuckDB for efficient CSV to Parquet conversion or replacing pandas to avoid memory bottlenecks, this transpiler extends DuckDB's reach even further into the enterprise data stack without requiring anyone to migrate their production databases.
The data engineering landscape has one less friction point today. Write your queries in a dialect that respects your time, and let the transpiler handle the rest.
