Five parts · Machine

External Tables, Partitions, CTEs, Window Functions, and Partitions

Adapted from a piece I wrote in July 2024, about work I did starting in the fall of 2022.

What I think about after building a pipeline. It's based on a pipeline for reports from a bunch of suppliers, each with their own file formats and delivery schedules. Nothing here is specific to that domain, though. If third parties send you files, and newer files contain updated versions of stuff that was in older files, and you need to serve whatever is current, this is for you.

This pipeline uses external tables for the primary data store instead of a typical time series (CRUD style) database. That decision affects everything else, including the fact that the word PARTITION ends up meaning two different things in the same query.

The architecture

The pipeline consists of:

Everything above the dashed line happens at load time and doesn't care about order. The ordering happens at query time. Click a stage for what it does and which part covers it.

In a CRUD system, the state of the database depends on the order the updates were applied, so if you ever need to rebuild it, you have to replay all your inputs from the beginning, in order. Here, the ordering doesn't happen at load time at all. It happens at query time. So you can load all your files in parallel, in any order, retry whichever ones failed, and you still get the same result.

ETL, ELT, and where the transforms happen

The usual way to explain this is the letter order. ETL means you shape the data before it lands. ELT means you land it raw and shape it inside the warehouse. This pipeline does both.

io-ts parses the supplier file and encodes the types before anything gets written. That is the T in ETL, and it happens once. Then the views do the rest. All the logic about what data is current and what is valid lives on the query side. Window functions surface the most recent version of a record, joins handle exceptions and replace bad identifiers, and when faster access is required the view gets materialized or copied into a delivery table. Some of them run well over a hundred lines of SQL. That is the T in ELT, and it happens on every query. Defending the boundary is about the first one. Current truth with window functions is about the second.

Schema on read

External tables are read only and schema on read. You define the table columns after the fact, and you can always alter the column definitions in the future without concern about altering data. Some of that comes free with external tables.

You can change your mind. Suppliers change their formats, and you get things wrong, and you can redefine a column later without reloading anything.

And the raw file is still sitting there, untouched. That is what makes the whole thing rebuildable, and it is the same property as loading in any order.

io-ts is not there to make up for schema on read. It is there because third parties will send you broken files no matter what your store does.

DuckDB will do it over a folder of files, figure out on its own that one field is a timestamp and another is a number, and answer the question.

Which means there is a version of this where you skip the io-ts step entirely. Burlap writes a generation manifest every time it makes something, and that format has changed a lot over the year and a half I have been building it. I pointed DuckDB at all of them at once, every version of the format, with no parsing step in between, and materialized the whole thing.

At scale, though, you probably don't want that.

The series

Also: SQL as the data language · Window functions, the mental model · DuckDB, where have you been all my life