How we built a DuckDB transpiler<br>Blog

{heroImage && }

--> engineering<br>Jul 23, 2026<br>How we built a DuckDB transpiler

TL;DR

We built a transpiler that allows you to write one SQL dialect, DuckDB, and have it execute across many different databases without installing any server-side software.

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.

If you’re interested in creating your own extensions, or working with the internals of DuckDB, you should definitely check out this extensions workshop by Rusty Conover. It’s incredibly well done, and frankly summarizes what took us far too long to figure out on our own.

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.

Take this example of converting a CocoSQL query into a ClickHouse query.

In the case of this query, 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.

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.

See it in action

Coco Alemana ships the transpiler directly with the product. Free for students, or 14-day free trial for professionals.

Download

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