DuckDB Parquet benchmarks that came out oddly flat, and missing fields that error instead of null — Feb 24, 2025

Filter before unnest — 60× faster

Dan ran a synthetic set — around 400,000 patients and 84 million observations — through the latest DuckDB.

The headline: filtering before you unnest, by traversing the array directly in the WHERE clause instead of flattening first, took a query from about two minutes to about two seconds. Both produce the same rows, so an optimiser should make them identical. DuckDB currently doesn't.

Parquet vs NDJSON — same query, same time

The uncomfortable result: the same query over a 75 GB NDJSON file took roughly the same two minutes as the Parquet.

On I/O alone that shouldn't happen, which points at either:

• Untuned Parquet (all defaults, no partitioning on the filtered column, row-group sizing)
• An engine that isn't doing the predicate push-down and column pruning that make Parquet worth using

Nobody claimed NDJSON is as good as Parquet — only that this benchmark wasn't measuring what it looked like it was measuring.

The real problem for the minimal-schema design

If a ViewDefinition touches a field that isn't in any of the Parquet files, DuckDB errors rather than returning null — even when the view allows nulls via forEachOrNull.

With NDJSON you can impose a schema at read time built from the view's FHIRPaths, so absent elements come back null; with Parquet the schema is baked into the files and you can't overlay one. The workarounds are hacky, like writing a file of nulls purely to get the field into the union schema.

The coupling argument

If a runner has to introspect every file in a data lake before it can compile a ViewDefinition into SQL, then the compiled query is tied to whatever data happened to be there, which changes constantly. A comprehensive schema removes that coupling.

The counter: new sources arriving is a relatively rare event, so an offline process could rebuild the superset schema then, rather than every query doing it.

Testing across three engine archetypes

Spark handles this by introspecting the schema and conditionally building the query, at almost no cost — but that's one engine and one data point.

The group agreed to test across three archetypes:

• Spark
• The DuckDB-like tier (ClickHouse next)
• Cloud warehouses — BigQuery, Athena, Redshift, Snowflake

The vocabulary of comprehensive versus focused schemas came out of this call.