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.

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.

So, if we have a dialect that is extremely close to DuckDB’s, we write a fraction of the code and only override when we absolutely need to. Pretty neat.

The following query

SELECT name, price % 2.4 FROM products
WHERE price > 10

converts into the AST below

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

Notice that the first and third queries are character-for-character identical. 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

I triple what?

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.

Take the following example of a divide-by-zero case

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

Division is just one example, but the amount of logic that went into aligning these systems to do proper, basic arithmetic was enormous.

Handling null values

Ufff. This was a hellhole of “gotchas”. 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.

For the majority of use cases, we managed to unify the functionality, but there are strange cases where we weren’t able to. In those cases, we document them, and/or throw errors when you try to write a query that would trip this.

How we chose what to build

Before we even wrote a line of code, we defined exactly what we needed in terms of guarantees. We also determined what we weren’t going to do, which is oftentimes more important than what you are going to do.

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.

If the results from DuckDB differ from the downstream database, then you’ve solved nothing. We care far less about the prettiness of the query (which the user won’t see), and much more about the final result.

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.

Where this deviates

DuckDB, by default, preserves row order without an explicit ORDER BY. This is a unique attribute of DuckDB that most systems just do not have. This was unfortunately unavoidable, and not easily fixable without causing immense performance degradation.

An example of this is using Amazon Athena with data stored in S3. The underlying files, whether parquet or CSV, have no inherent order, and cause simple previews to have inconsistent ordering when no ORDER BY is present. This doesn’t affect the accuracy of analytical results, however.

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. SQLGlot attempts to do many-to-many, and while it’s great software, it often falls short on specific use cases that we need.

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 (ClickHouse, for example, returns booleans as UInt8). 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.

This is what lets the rest of the product treat every source identically. A filter, a chart, or a join doesn’t care whether the frame came from a local file or Snowflake – the types will act the same either way. There are some slight exceptions to this, but you can’t have everything in life.

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. We don’t want to be a database management tool, and we have specific interfaces for creating new tables from your queries. Think CTAS.

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.

We make use of the existing, extensive tests that are provided by DuckDB. We also implement custom, dialect-specific tests to ensure we get the right results, and don’t encounter regressions when we add new dialects or features.

This was by far the biggest unlock in terms of development speed, in addition to our tree inheritance approach.

---