Fast drilldown dashboards from a single Parquet file

Every period brings a caller eruption of clever uses for entity storage, easy the astir volcanically progressive area of non-AI package infrastructure connected earth. The astir caller lava explosive was Vicent Martí’s writeup of Cursor Origin’s S3 + WAL attack to managing Git repositories astatine scale. It’s a masterpiece of method writing, dissimilar this post. I’ll admit that moreover earlier reference it, I was pensive astir a wholly different benignant of task wherever entity retention astir apt conscionable works, this clip for customer-facing analytics dashboards. A friend of excavation has customer usage information successful Iceberg connected R2, and wants to show his users immoderate basal charts pinch filters. He told maine he didn’t want to adhd immoderate much vendors, which ruled retired MotherDuck, the cloud-hosted DuckDB database institution wherever I presently work.

Well, successful analytics, erstwhile each you person is entity storage, everything looks for illustration a scope request. We could astir apt conscionable rotation this benignant of information up into a Parquet-backed information cube, and capable retired the dashboard pinch very elemental scope queries against it, utilizing Hyparquet, a mini javascript Parquet scholar that runs successful the browser. With that, you tin service a existent drilldown dashboard pinch neither a database nor a query engine. The cube could moreover beryllium tens (or hundreds) of MB, since a correctly laid-out record intends you only ever publication a fewer mini slices of it astatine a time. You conscionable request a information pipeline to nutrient the cubes ~ which is also, it turns out, wherever each the existent money goes erstwhile you do person a existent analytical database.

The heresy was excessively bully to walk up, since these days I presume DuckDB is the lightweight solution to each my information problems. To trial it, I took the well-known NYC 311 work requests dataset I had connected my machine ~ astir 34 cardinal rows astatine the petition level, 15 aliases truthful years ~ and rolled it up into a 40MB Parquet cube pinch filters for metropolis agency, title type, submission type, and borough, positive a azygous creation-time file for the clip series. Then I stuck it connected R2. 40MB is large capable to consciousness the symptom of downloading the full thing.

The demo dashboard beneath sounds straight from that record utilizing Hyparquet. The bytes walk done a mini Cloudflare Worker connected the way, because the free r2.dev URL is rate-limited. The Worker proxies byte ranges and caches them astatine the edge, which is safe because the record is immutable. To beryllium honest, I was amazed really accelerated caller information loads, fixed that it forgoes some a existent database and a powerful query engine. The UI does each of the existent reading, and it is lightweight capable to embed straight successful this station without hurting the page load. The existent complexity is almost wholly offloaded to the information cube layout. Try scrubbing the floor plan aliases clicking connected the rows of the leaderboards.

So, really does this dashboard work?

A dashboard for illustration this 1 is designed to reply a bounded group of analytical questions ~ requests per day, requests per time for 1 agency, all-time totals by borough. Each mobility tin beryllium answered by GROUP BY queries, truthful we tin precompute them each up of clip and prevention each consequence arsenic its ain mini table, called a grouping set. Stack each of the grouping sets successful 1 Parquet file, 1 conception per set, and you person a data cube. A grouping group is only useful if it either enables a mobility to beryllium answered, aliases reduces the latency of pulling the data. This record has some kinds. The all-time totals provender the leaderboards, and a regular grouping group for each operation of filters provides the information for the statement chart. The play and yearly grouping sets trim the number of rows scanned that results from brushing the chart. The aforesaid totals could beryllium summed from regular rows, but location are less rows to fetch if we precompute by weeks and years.

The record now holds the grouping sets that render the dashboard, but the browser still has to propulsion retired conscionable the rows it needs. Two features of the Parquet format make that possible. A Parquet record is divided into row groups of a fewer tens of thousands of rows, and it ends pinch a footer that contains metadata astir the byte ranges of statement groups and the min/max values of each file wrong it. The customer sounds the footer once. Each query past uses the min/max values to prime the statement groups that could match, fetches those byte ranges, and aggregates the rows successful the browser.

The debased latency successful the dashboard requests is owed to really the rows successful the Parquet record are sorted and scanned. If the rows of the record were randomly ordered, each statement group’s min/max values would span astir the afloat scope of each column, and a query would person to publication astir of the record conscionable to fetch a mini percent of rows. Instead, the rows of each grouping group are sorted by the columns its queries select on. The matching rows frankincense usually dress up a contiguous agelong of the file, and the min/max statistic alteration the scholar to disregard the remainder of the statement groups. That is why clicking NYPD successful the agency leaderboard sounds astir 260KB retired of the 40MB record alternatively than the full file. Below is the existent layout of the record successful position of bytes and grouping sets:

This setup useful nether 2 conditions. The combinatorics of your charts and filters person to enactment small, and your pipeline has to rebuild each customer’s record accelerated capable to meet the update cadence. Most usage and billing pages meet both. They are a fixed group of charts ~ events complete time, counts aliases sums by hr aliases by day, a fewer filters aliases leaderboards ~ complete information that updates connected a coarse schedule alternatively than successful realtime, for their liking arsenic overmuch arsenic yours. From the position of latency, the cube size doesn’t matter, but you’ll want it to beryllium somewhat mini anyhow since you’re regenerating 1 per customer connected a schedule.

The clip atom is intelligibly ascendant successful my example, since the regular sections relationship for astir of the bytes of the file. Cardinality is the different multiplier ~ title type has 485 chopped values, and each ample conception successful the sketch contains it. In fact, choosing a regular atom for the statement floor plan made the record astir 7x larger than the play balanced (5.6mb). Still, the regular atom did not meaningfully effect the latency of the scope requests, since immoderate relationship only ever sounds a fewer statement groups. And for this case, it’s bully to spot a large single-day spike, since a large uptick successful work requests tin hap successful a azygous time because of awesome events for illustration hurricanes aliases blizzards.

Dashboards specified arsenic the 1 supra activity good for distributive and algebraic aggregations, which tin beryllium computed successful pieces and past mixed earlier visualizing. Think of sums, counts, maxes, and averages. Making this setup activity for holistic aggregations (ones that require knowledge of the distribution earlier achieving a last filtered aggregate) person some nonstop and approximate solutions. I’ll time off that arsenic an workout to the scholar and their favourite agent.

Range requests complete a cautiously laid-out record person plentifulness of anterior art. PMTiles packs a tileset into 1 record that clients publication via scope requests complete http. It useful because the tiles are laid retired successful the record on a Hilbert curve, truthful the tiles for a fixed representation position beryllium adjacent each different successful the record and tin beryllium fetched successful a fewer coalesced scope requests. And of course, the well-known SQLite-over-HTTP writeup proved the mechanic useful moreover for B-trees.

My favourite portion is that this attack shifts the complexity “left” each the measurement to the information pipeline. The layout is decided beforehand, truthful by the clip a personification clicks connected a leaderboard aliases scrubs a clip bid chart, the customer only has to fetch the correct rows and sum them. As for the pipeline, for astir customer-facing dashboards, a 10mb per-customer cube falls retired of a DuckDB GROUP BY GROUPING SETS statement. For my friend, who’s a information engineer, it’s pitch-perfect déformation professionnelle.

One record per customer besides makes auth refreshingly boring. Access power amounts to a signed URL for that customer’s file, aliases a mini Worker that checks the session.

Given that R2 has free egress, the pipeline is besides the point that costs existent money. Writes costs $4.50 per million (12.5x the value of reads), and you salary 1 constitute per customer per rebuild, sloppy of record size (a 1MB cube and a 40MB cube costs the aforesaid to upload). Take 10,000 customers. Each rebuild replaces the files, truthful retention is flat: 10MB cubes make 100GB, astir $1.50/month; 40MB cubes make 400GB, astir $6/month. Rebuilding each record erstwhile a time is 300k writes a month, aliases astir $1.35; rebuilding hourly is 7.2M writes, astir $32; rebuilding each 5 minutes for a period is 86M writes, astir $389. Thankfully, Iceberg snapshot diffs show you precisely which customers person caller data, truthful it’s easy to only rebuild the cubes pinch caller activity.

Even still, let’s opportunity you update each 5 minutes and each customer has activity successful that model (again, not very likely). For a azygous use-case for illustration this one, $389/mo for 10k customers is astir apt cheaper crossed the committee than opinionated up caller infra, and it is almost surely simpler. And some the economics and the personification acquisition person changed very recently: egress fees wouldn’t person rather killed this idea, but they’ve astir apt discouraged group from experimenting this way. The aforesaid setup connected S3 comes retired only astir 20% much costly wide ~ astir $20 a period of egress astatine a cardinal queries, and a cardinal queries is much postulation than astir customer dashboards will ever see.

My other favourite portion is the radically bladed implementation. An 18kb javascript scholar and a byte layout that does the database activity for you. What a world!