GoKawiilFast drilldown dashboards from a single Parquet file One 40MB Parquet data cube, an 18KB reader, an R2 bucket, and a few unassuming http range requests.
Every month brings a new eruption of clever uses for object storage, easily the most volcanically active corner of non-AI software infrastructure on earth. The most recent lava bomb was Vicent Martí’s writeup of Cursor Origin’s S3 + WAL approach to managing Git repositories at scale. It’s a masterpiece of technical writing, unlike this post. I’ll admit that even before reading it, I was daydreaming about a totally different kind of task where object storage probably just works, this time for customer-facing analytics dashboards. A friend of mine has customer usage data in Iceberg on R2, and wants to show his users some basic charts with filters. He told me he didn’t want to add any more vendors, which ruled out MotherDuck, the cloud-hosted DuckDB database company where I currently work.
Well, in analytics, when all you have is object storage, everything looks like a range request. We could probably just roll this kind of data up into a Parquet-backed data cube, and fill out the dashboard with very simple range queries against it, using Hyparquet, a small javascript Parquet reader that runs in the browser. With that, you can serve a real drilldown dashboard with neither a database nor a query engine. The cube could even be tens (or hundreds) of MB, since a correctly laid-out file means you only ever read a few small slices of it at a time. You just need a data pipeline to produce the cubes ~ which is also, it turns out, where all the actual money goes when you do have a real analytical database.
The heresy was too good to pass up, since these days I assume DuckDB is the lightweight solution to all my data problems. To test it, I took the well-known NYC 311 service requests dataset I had on my computer ~ about 34 million rows at the request level, 15 or so years ~ and rolled it up into a 40MB Parquet cube with filters for city agency, complaint type, submission type, and borough, plus a single creation-time column for the time series. Then I stuck it on R2. 40MB is big enough to feel the pain of downloading the whole thing.
The demo dashboard below reads directly from that file using Hyparquet. The bytes pass through a small Cloudflare Worker on the way, because the free r2.dev URL is rate-limited. The Worker proxies byte ranges and caches them at the edge, which is safe because the file is immutable. To be honest, I was surprised how fast new data loads, given that it forgoes both a real database and a powerful query engine. The UI does all of the actual reading, and it is lightweight enough to embed directly in this post without hurting the page load. The real complexity is almost entirely offloaded to the data cube layout. Try scrubbing the chart or clicking on the rows of the leaderboards.
nyc 311 ~ daily requests all time ~ 0 requests in view 0 range request s · 0 KB fetched · 0.0 % of the cube so far no filters ~ click a leaderboard row, or brush the chart (click the chart to clear) no filters ~ tap a row or brush the chart by agency by complaint type by borough by channel
So, how does this dashboard work?
A dashboard like this one is designed to answer a bounded set of analytical questions ~ requests per day, requests per day for one agency, all-time totals by borough. Each question can be answered by GROUP BY queries, so we can precompute them all ahead of time and save each result as its own small table, called a grouping set. Stack all of the grouping sets in one Parquet file, one section per set, and you have a data cube. A grouping set is only useful if it either enables a question to be answered, or reduces the latency of pulling the data. This file has both kinds. The all-time totals feed the leaderboards, and a daily grouping set for every combination of filters provides the data for the line chart. The weekly and yearly grouping sets reduce the number of rows scanned that results from brushing the chart. The same totals could be summed from daily rows, but there are fewer rows to fetch if we precompute by weeks and years.
The file now holds the grouping sets that render the dashboard, but the browser still has to pull out just the rows it needs. Two features of the Parquet format make that possible. A Parquet file is divided into row groups of a few tens of thousands of rows, and it ends with a footer that contains metadata about the byte ranges of row groups and the min/max values of each column inside it. The client reads the footer once. Each query then uses the min/max values to pick the row groups that could match, fetches those byte ranges, and aggregates the rows in the browser.
The low latency in the dashboard requests is due to how the rows in the Parquet file are sorted and scanned. If the rows of the file were randomly ordered, each row group’s min/max values would span nearly the full range of each column, and a query would have to read most of the file just to fetch a small percentage of rows. Instead, the rows of each grouping set are sorted by the columns its queries filter on. The matching rows thus usually make up a contiguous stretch of the file, and the min/max statistics enable the reader to ignore the rest of the row groups. That is why clicking NYPD in the agency leaderboard reads about 260KB out of the 40MB file rather than the whole file. Below is the actual layout of the file in terms of bytes and grouping sets:
... continue reading