How we built a DuckDB transpiler

Overview

As a data practitioner, it’s common that you’ll encounter the use of multiple databases. Maybe you’ll work with Postgres for one task, and ClickHouse for another. The subtle differences between each dialect let errors and inconsistencies slip through the cracks. One slight difference or error can cause your entire analysis to be wrong.

We ship a dialect-subset of DuckDB that transpiles to other dialects, which we call CocoSQL. This article will focus on how we built it.

Why build a transpiler?

Our core driving principle is to Make working with data effortless. As part of our product, we allow our customers to combine visual transformations – such as renaming a column, applying an Excel-style filter, or editing values inline – with custom SQL to fit their every need.

We also want customers to be able to work with their data, wherever it lives. Most of the time that means in a database or data warehouse that we don’t control.

Being able to offer both of these requires building a unified execution layer. We also didn’t want to create our own custom query solution that customers would have to learn. SQL is ubiquitous and extremely flexible.

The only reasonable solution was to use a single ANSI SQL dialect, and create a transpiler.

Why choose DuckDB?

On top of the most obvious reason – performance – DuckDB’s dialect is arguably the most ergonomic and forgiving in the world. It’s runnable locally, extremely well-written internally, and easy to extend. We designed our whole product with DuckDB as the core execution engine for these very reasons.

Ergonomics

DuckDB’s dialect rocks! Common “gotchas” that occur in other dialects, like trailing-comma errors, do not exist. Niceties like function .dot() chaining are built in, and quick shortcuts like FROM table make most operations extremely quick.

Most of these benefits translate nicely into other dialects as well.

Type system & flexibility

DuckDB’s type system is rich, and forgiving. Compare 1 to 1.0, concatenate a number onto a string, cast a messy string to a date – DuckDB does what you’d expect, and without a bunch of hacks or workarounds. The software is designed to work for you, not against you.

Additionally, there are a ton of helpful built-in types. Because DuckDB’s types are a superset of most target dialects, we 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.

With the internals, we’re able to plug right into the Parser, Binder, create our own functions, override existing functionality, etc., all using the native DuckDB types.

What does the transpiler actually do?

In simple terms, the transpiler converts provided CocoSQL (DuckDB) into an equivalent query in the target dialect. Statement keywords, functions, casts, comparisons, offsets, null handling, etc. are converted to produce a result that is semantically identical to the DuckDB statement.

Additional considerations

In certain cases, the transpilation is not enough. Certain databases or warehouses have settings that produce different results for the same query. The most prominent example is ClickHouse, which has about 1 million different “Session Settings”. In cases like these, we create the transpiled query, and then set the correct settings at the session level before executing the query.

How does the transpiler work?

We structure our transpiler at the core of our product. It drives visual interface changes, as well as custom SQL, and custom columns. Anything that you do in Coco Alemana will inevitably be passed through our transpiler.

We extend DuckDB’s internals using C++ to make use of two critical components – the parsed abstract syntax tree (AST), and the Binder. Specifically, we parse the query tree once, maintaining the user’s query structure, then we run it through the Binder to attach types to each expression and column. This comes in handy when specific edge cases are not solvable without knowing the types.

Traversing the AST

We built a base class that gives us structure to walk each node in the AST. It also gives us default implementations that exist for DuckDB. This means that for most extensions, we simply create a new DialectTranspiler, such as RedshiftTranspiler, and only override virtual methods when we absolutely need to.

Internally, the query is structured as a tree of ParsedExpressions, with each node having children, or constants, depending on its type. This allows us to re-walk the tree, and expose methods like std::string ConvertExpression(const duckdb::ParsedExpression& expr) on a per-dialect basis.

Applying type-based corrections

Not all conversions can occur via looking at the parsed node alone. For some dialects, additional checks are needed at transpile time in order to prevent runtime issues that wouldn’t throw an error, but would produce incorrect results. This is where the importance of traversing the Binder comes into play. It allows us to have not only expression-based logic, but also expression-type-based logic.

A perfect example is BigQuery’s finicky modulo support (%). 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.

Floats

If either side is a float, MOD() can’t be used at all. We fall back to an injected custom function, which matches DuckDB’s IEEE math bit-for-bit:

-- 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

Integers

If both sides are plain integers, MOD() works natively. The NULLIF matches DuckDB’s behavior of returning NULL on a zero divisor, instead of erroring:

-- price is an INTEGER
SELECT price % 3 AS result FROM my_table

-- becomes
SELECT MOD(price, NULLIF(3, 0)) AS result FROM my_table

Mixed integers & decimals

If an integer is mixed with a decimal, MOD() refuses to reconcile the two types, so we promote both sides to BIGNUMERIC:

-- price is an INTEGER
SELECT price % 2.4 AS result FROM my_table

-- becomes
SELECT MOD(CAST(price AS BIGNUMERIC), NULLIF(CAST(2.4 AS BIGNUMERIC), 0)) AS result FROM my_table

IEEE-754 conformance

The IEEE Standard for Floating-Point Arithmetic, IEEE 754, defines how floating-point numbers (FLOAT, DOUBLE) are represented, and more importantly for our case, how they’re operated on.

For whatever reason, many database providers don’t have consistent arithmetic logic. Part of this may be due to legacy implementations, performance, or other platform specific decisions like storage formats. DuckDB mostly conforms to this standard, and our transpiler matches its functionality.

Handling null values

Each system has its own way of handling NULLs, in places like LISTS, arguments of functions, offsets, etc. Some systems like ClickHouse can’t even represent NULL when you have a LIST type.

How we chose what to build

Value equality

Obviously the most important single point. This means that the result of a query yields the same cell values, columns, and order (if applicable) across systems.

Order equality

This means that the rows come in the order that is expected based on the input query. This takes into account differences like NULLS FIRST or NULLS LAST defaults, etc.

One-to-many conversion

We realized quickly that we really only need to convert DuckDB into target dialects, instead of having a many-to-many, or a bi-directional solution. This drastically simplified the problem, and made development quicker.

Type encapsulation

Results should come back with DuckDB’s types, no matter which backend produced them. A BOOLEAN column should stay a BOOLEAN – even when the warehouse’s wire protocol quietly erases it. Because the Binder already computed the expected type of every output column at transpile time, we can restore that fidelity at the executor level after the results arrive.

SELECT statements only

We decided that statements outside of SELECT didn’t make sense to replicate, because for the majority of analytical tasks, reading is the whole job.

Testing

To guarantee we get the results we expect, we make extensive use of E2E testing as well as individual unit testing. As it stands, we have over 3,500 test cases that cover all parts of the query – from structure, to functions, types, null handling, division consistency, and more.