Fast drilldown dashboards from a single Parquet file | Hacker News Reader

Fast drilldown dashboards from a single Parquet file

hamiltonulmer.com

173 pointsv3gas23 comments

> The bytes pass through a small Cloudflare Worker on the way, because the free r2.dev URL is rate-limited.

For a 40MB file I suggest hosting it directly on GitHub Pages - that's effectively a free CORS-enabled CDN and supports HTTP range requests, so you should be able to get that demo working without needing to involve Cloudflare Workers at all.

Agreed, for a public demo like this one, GitHub Pages would work great (or any host that speaks HTTP range requests with CORS). I used R2 partly because I wanted to see how it behaved, and partly because the real use-case doesn't fit Pages. The source data already lives on R2 as Iceberg, the files are per-customer and would probably need auth (signed URLs or a session-checking Worker), and obviously 10k customer cubes on a schedule works better with object PUTs rather than git deploys

> Agreed, for a public demo like this one, GitHub Pages would work great

If you are okay with it being down regularly

Edit: Ironically, that would be the case now: https://www.githubstatus.com/incidents/hcbtzksccj2f

A clever repurposing of technologies but realistically only worthwhile for static datasets with range payloads small enough to fit into a web response.

> your pipeline has to rebuild each customer’s file fast enough to meet the update cadence. ... data that updates on a coarse schedule rather than in realtime

It doesn't have to all live in the same Parquet file. you can have a Parquet file for all your historical data, plus one for the current week which is updated often cheaply, and then when the week is over you merge that into your big parquet file.

You're making it seem like there's hard limits to what can be done but while there definitely is, you can do incredible stuff.

And then you eventually just take the weekly files and combine them and you've reinvented data lakes with worse (no) metadata management

"static datasets with range payloads small enough to fit into a web response" fits a lot of workloads.

I expect that if your overall data is less than a GB this trick will work really well for you.

Most reporting I've ever worked on is based on live data and users expect updates. Range requests over parquet cubes are a cool party trick, but you outgrow it quickly once you need to start regularly updating the dataset such as to avoid full recompuation.

The next step in this journey is Iceberg (and a proper incremental pipeline), which can also be read directly in the browser via WASM either via DuckDB or without. This is from the same author as the parquet library mentioned in the OP https://github.com/hyparam/icebird

I think it depends on (1) customer expectations for freshness and (2) scale (both for the cubes and for the customer data in toto). There are many types of customer facing dashboards where giving "live data" is a bad idea for them and for you. And recomputation is indeed a problem, but if the volume of data isn't that high to begin with, it's probably easier than setting up an incremental pipeline architecture vs. a grouping set query in DuckDB. But I am not really a data engineer, so perhaps this is naive.

I work at a household name tech company and pretty much all our "data" dashboards are running SQL queries against a Hive/Presto environment that's at least 24 hours behind. Real-time dashboards are limited to the service metrics time series database (so combinations of categorical variables, no individual records) or rare expensive Pinot tables.

I have seen this on the business side too. A lot of we need live data requests don’t really need live data. If the decision is made once a day, hourly data is more than enough. Yet we still build dashboards that refresh every few minutes just because people asked for it.

In this case, we do actually need live data! We use wide events for monitoring business events and it lets us understand our platform with utmost flexibility, which is important for us as a fintech company.

Traditional observability is ill-suited for observability around business events. What if you forget to instrument a counter or gauge for something? In my experience it's far easier to log wide events with as much context as possible instead of agonizing over anticipating the dimensionality of metrics upfront (you're going to miss something).

Perhaps changing is a better word.

Yes this was also my experience working at a large tech co. I work in fintech now and data volumes are low enough to maintain 2-3 minute up to a few hour data freshness.

> 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.

What's the benefit of "data cubes" over caching?

It's a good question. In a sense, the cube is caching, just materialized ahead of time instead of memoized on demand. A result cache still needs a live database behind it for misses; the cube has no misses, since every question the dashboard is designed to answer has data in the cube already. And for this experiment, the goal was to forgo a database to serve the data in the first place.

I provide caveats for when this would work vs. when it doesn't in the post. For a lot of customer-facing dashboards, I think it's probably pretty good.

Interesting how noise complaints dwarf any other type of complaint in NYC.

On the surface (I haven't tested it) it looks like a great cost and runtime saver, but only for data sets that need a cadence above 5 or so minutes. You wouldn't be able to have a refresh button to get the "latest" data outside this window, depending on size and build time? Wondering if you can apply a hybrid approach, combining the historical parquet file with a live query.

Cloudflare Pipelines has a configurable interval to write to parquet files that's as low as 10s: https://developers.cloudflare.com/pipelines/sinks/available-... so you could have a pretty fast refresh

It will cost (slightly) more to write this frequently to R2 since you are charged per-write, but this is something you can tune.

I’d loved to have seen more detail on how the Parquet file was actually built — are there any good resources that cover this?

With the GROUPING SETS or CUBE features in DuckDB, I think.

https://duckdb.org/docs/current/sql/query_syntax/grouping_se...

How is this any different from the old OLAP cubes like IRL / Express? The cube is basically an array in memory and super fast, the dashboards based on Group By as well. I was an Express consultant at Oracle, great to see old concepts come back to life if it’s what I think it is.

Indeed, OLAP cubes keep finding new life. The critical difference here is that there isn't a heavy query engine (just a Parquet reader) and a few http requests to pull from the cube. So it's light on the backend and light on the frontend.

Great read!

Reply on news.ycombinator.com