Why SQL on Zarr? The architecture behind zarr-datafusion
Zarr stores array data the way scientists think about it: chunked, N-dimensional, indexed by coordinates like time, lat, lon. SQL engines think in rows and columns. zarr-datafusion exists to close that gap — without copying the array into a database first. Here’s how it actually works.
Arrays aren’t tables, but they can look like one
A weather dataset in Zarr typically has coordinate arrays (time, lat, lon, each 1D) and data variables (temperature, humidity, each shaped to the Cartesian product of those coordinates — e.g. [time, lat, lon]). zarr-datafusion’s core move is presenting that structure as a 2D table, one row per grid cell, without materializing it:
Zarr Store (3D) SQL Table (2D)
─────────────────────────────────────────────────────────
temperature[t, lat, lon] → | time | lat | lon | temperature |
humidity[t, lat, lon] → | 0 | 0 | 0 | 43 |
| 0 | 0 | 1 | 51 |
| ... | ... | ... | ... |That mapping has a documented contract, not a hidden one: arrays with a single dimension are treated as coordinates, arrays with multiple dimensions are data variables, and a data variable’s shape must equal the Cartesian product of all coordinates, dimension-ordered alphabetically. It’s a real constraint — it won’t (yet) handle arbitrary ragged or non-Cartesian layouts — but it’s the constraint that makes the rest of the engine tractable, and it covers the common case for gridded climate, satellite, and sensor data.
Plugging into DataFusion, not replacing it
zarr-datafusion isn’t a SQL parser bolted onto Zarr — it’s a DataFusion TableProvider. CREATE EXTERNAL TABLE era5 STORED AS ZARR LOCATION '...' resolves through a TableProviderFactory straight into DataFusion’s own planner. Schema inference reads Zarr v2 (.zarray) or v3 (zarr.json) metadata directly — the Zarr store is its own catalog, there’s no separate schema registry to keep in sync. From there, DataFusion’s logical planner, optimizer, and Arrow-native execution model all just work, because the only thing zarr-datafusion had to build was the ExecutionPlan that turns a scan into Arrow RecordBatches read from chunks.
That’s the leverage: getting joins, GROUP BY, HAVING, window functions, and a real cost-based optimizer for free by building into an existing Arrow-native engine instead of writing a query engine from scratch.
The optimizations that make it not just correct, but cheap
Flattening arrays into rows is the easy 80%. The part worth showing rather than telling:
- Projection pushdown — a query that only selects
temperaturenever reads thehumidityarray off disk. - Filter pushdown on coordinates —
WHERE lat = 5skips chunks outright rather than filtering after a full read. - Statistics-based constant folding —
SELECT MIN(lat), MAX(lat) FROM era5doesn’t scan a single chunk. A custom DataFusionOptimizerRulerewrites it directly into the literal values pulled from Zarr’s own coordinate metadata, the same way a database uses a B-tree’s bounds instead of a table scan. - Dictionary-encoded coordinates — repeated coordinate values (every
lat/lonpair repeats across everytimestep) are stored as ArrowDictionaryArrayrather than expanded in memory, which is roughly a 75% memory reduction on coordinate columns for a typical gridded dataset.
None of this is exotic database theory. It’s the same pushdown/statistics playbook every columnar query engine uses — applied to chunked array metadata instead of Parquet row groups.
I/O you can actually see
Every query in the CLI prints what it touched: 5 rows · 3 arrays · 6.70 KB disk · 13.92 KB mem · 0.013s. That’s deliberate. The entire pitch of “query in place” only means something if you can verify, query by query, that it isn’t quietly pulling more than it needs.
What’s still ahead
The honest version of the roadmap: filter pushdown currently handles coordinate equality well; coordinate ranges and chunk-level partition pruning are next. Aggregate pushdown (SUM/AVG computed at the chunk level, not just MIN/MAX) isn’t there yet. Chunk reads aren’t parallelized within a query yet, either. Further out: integrating Icechunk for transactional, versioned reads, and Kerchunk/VirtualiZarr support so NetCDF and HDF5 archives can be queried without a conversion step first.
We’d rather list what’s not done than imply it all is — the architecture is built to take these on incrementally, not to be rewritten for them.
To see all of this working on a real dataset, walk through computing the El Niño index from ERA5 with SQL — the coordinate filter pushdown, in-place read, and Arrow execution above, applied end-to-end in a single query with no download.