To be fair, it’s more accurate to say that I wrote a data compiler. Let me explain myself after that unsettlingly clickbaity title…

I’m currently contracted on a project that requires the ingestion and fast serving of public data. This data arrives in huge, unruly CSV and JSON files. The largest is a 30 GB JSON file.

The files describe one-to-many relationships. One record carries many tags and many observations, each with its own set of attributes. Flattening that into table rows yields one row per combination, with the same facts repeated over and over. The resulting fan-out becomes a ridiculous, unfeasible explosion of data. It is enough to ruin anyone’s day.

With 100,000 records, 20 observations and 8 tags per record, a flat table has 16 million rows. Each observation is repeated once for every tag. Store the two relationships separately and there are 2 million observations and 800,000 record-to-tag edges: 2.8 million entries, with nothing duplicated just to express the shape of the data.

After download, the raw source files are parsed into Parquet by a small C consumer using libduckdb. C provides a small native layer around libduckdb with a stable ABI that can be called easily from other languages. Array and struct types contain the row count, while Parquet with zstd compression handles the repetition well. So, job done?

This works well in a warehouse and as the basis for building more consumable datasets. But it still doesn’t solve the low-latency serving problem for this application.

End users haven’t been promised arbitrary queries or aggregations. They’ve been promised a data-source API that can answer specific benchmarking questions over the collective data, indexed on known dimensions.

To do that, I wrote a compiler that scans the Parquet and writes immutable artifacts in a custom binary format. Every choice in that format exists to remove work from the read path.

Columns are dense, so position is the address. The value for the nth record in a shard sits at a fixed offset, making lookup arithmetic rather than search.

Strings are dictionary-encoded because the tag vocabulary is small and the same handful of labels recur across millions of records. Store each string once and comparisons collapse into integer comparisons against the dictionary, resolved before the column is touched at all.

Relationships are stored CSR-style: one array of offsets and one of neighbours. A record’s tags are a contiguous slice rather than a set of rows to gather. The problem is solved by layout instead of by joining. IDs within a slice are sorted, so they can be delta-encoded and written as LEB128. The gaps are small and most fit in a single byte.

Indexes are small enough to remain resident or memory-mapped, and they point directly to contiguous byte ranges in the larger data files. Only the required payload comes off blob storage.

The compiler itself leans on libduckdb for the sorting, grouping and joining that happens before anything is written. That’s a solved problem, and not one I wanted to solve again, so the C ingestion layer stays small. The artifacts it produces are disposable. Parquet remains the canonical, durable representation; the binary format is just a serving projection that can be regenerated at any time.

The artifacts are stored and served from blob storage such as S3. A small API sits in front of them, which I think of as a viewing grille. It looks up the index in memory, fetches the relevant byte range and hands it back as a JSON response.

The API is Python because of corporate alignment, pragmatism and ease of interoperability. It uses cffi to call into a native shared object, again written in very simple C.

There is no parsing, joining or query planning on the read path. The compiler has already done the work. With premium blob storage, responses from the data path take about 5 to 10 ms: an in-memory index lookup followed by an ETag-pinned range GET.

The source data changes slowly, which makes immutability useful rather than a nuisance. Need more serving capacity? Add replicas. The data is sharded and balanced ahead of time, with no leader election, gossip or consensus protocol to worry about. There are no writes.

It runs on Kubernetes, with node pools determining the size and shape of the ingestion and serving fleets. Ingestion nodes only scale up when there is work to do.

Why not use an existing database?

An existing database could represent these relationships without flattening everything, so the row explosion alone isn’t a reason to build something custom. Here, though, the questions are fixed and the source data changes slowly. The joins and fan-out can be dealt with once, ahead of time, rather than every time the API receives a request.

These artifacts are disposable serving projections, not the system of record. Parquet in blob storage remains the canonical format, and the serving layer can be rebuilt from it. If needed, the original source data can rebuild the Parquet.

ClickHouse or Druid would both be perfectly sensible choices. Anyone familiar with either will recognise many stolen ideas. But perhaps choosing one would just trade one can of worms for another. They’re mature, complex and awesome tools. We need approximately 1.5% of their feature set.

This took about a week and a bit to build, which was part of the appeal. The implementation remained proportional to the narrow problem it was solving.

Using something like ClickHouse well still involves schema design, ingestion, deployment, tuning and operations. For this narrow workload, I suspected I’d spend at least as much time bending a general-purpose system around the problem as compiling exactly the representation I needed.

Embracing strict constraints in the serving layer gives us predictable response times of 5 to 10 ms across tens of terabytes, without paying the infrastructure and operational tax for the 98.5% of a general-purpose database we don’t use.