A week of CDN logs in DuckDB and Parquet

A production storefront was billed roughly 4.84 million CDN API requests a month against a 1 million limit. Eight days of usage logs — 3.2 million requests as 3 GB of NDJSON — became a 113 MB Parquet file I queried locally in DuckDB, and the cause fell out: per-render queries cached for 60 seconds or not at all.

Turning 3 GB of NDJSON into a 113 MB Parquet file

The storefront is a client’s Shopify Hydrogen app with content from Sanity, and Sanity’s usage-log export arrives as newline-delimited JSON — 3 GB for eight days, 3,247,141 requests from 58,904 distinct IPs. The toolkit is deliberately small: one conversion script that has DuckDB read the NDJSON directly and write an enriched Parquet file (typed columns, derived fields like client class and endpoint), and a runner that executes numbered SQL files — overview, performance, attribution, cost, anomalies, and a targeted CDN-reduction pack. brew install duckdb is the entire infrastructure; the 27× smaller Parquet file makes every query in the library return in seconds on a laptop. The property I care most about: every number in the findings report is reproducible by running one named query, which means every claim in the report has a verification path.

The first finding was fake: connection lifetime is not latency

The raw average request duration was 1,596 ms, which reads like a meltdown. The true picture: p50 latency 5.6 ms, p95 157 ms, p99 467 ms — healthy. The gap exists because two endpoint families in these logs (listen and socket, the CMS’s realtime machinery) record connection lifetime in the same duration field; the longest was 5.7 hours, and the hourly averages spiked wherever long-lived editor connections happened to close. The toolkit now excludes those endpoints from every latency query, and the lesson generalises: before aggregating a column, know what it measures for each row type, because a single overloaded field can manufacture a regression that isn’t there.

Where 3.2 million requests in a week actually go

Images were 52.7% of requests but only 30% of egress. Queries were the inverse: 36.7% of requests carrying 68.8% of the 25.6 GB egress — bandwidth lives in query responses, not assets. Writes barely register: 3,343 mutation calls all week. The waste was more interesting: 7.5% of all traffic — 244,984 requests — were auth failures, and 96% of the 175,788 401s came from just two IP addresses, the signature of editor sessions with expired tokens reconnecting in a tight loop. Fixing two stale clients removes ~7% of the entire request volume. Two smaller surprises: in-app social browsers (Instagram and Facebook webviews) were 23% of all requests, and bots pulled 21.6% of image bandwidth from only 4.7% of requests. Both splits come from user-agent strings, which are a claim rather than evidence — enough to size a problem, not to bill anyone for it.

The overage was caching policy, not traffic

The billed metric counts every request to the CDN endpoint, cache hits included, so the only lever is firing fewer queries. The logs attributed the 4.84M run-rate to specific lines of storefront code. The single worst: a one-boolean feature flag fetched on every request with caching explicitly disabled — 0.96 million requests a month for a value that lives on a settings document the same loader already fetches. Next: a global 60-second cache TTL on content that changes a few times a week, re-paid at every edge location because the sub-request cache is per-PoP. And the root loader read the same settings singleton four to five times per request through separate queries. The fix ladder is three stages — fold the flag into the existing settings query and correct one mis-set cache policy (−1.3M/month for two small diffs), consolidate the root loader’s six reads into one projection, then raise TTLs with a publish-webhook purge for the per-page content bucket that is 53% of the total. Projected trajectory: 4.84M → 3.5M → 2.0M → under the 1M limit.

What a local toolkit buys over a dashboard

The vendor dashboard showed the overage existed; it couldn’t say which line of code caused it. The raw logs could, but only once they were queryable — nobody finds a stale-token loop by scrolling 3 GB of JSON. The honest limits: this is one project and one week, and 87% of the traffic was untagged, which capped how precisely the biggest request bucket could be attributed — the follow-up recommendation is to tag every query so the next export is fully traceable. That’s also the point of keeping the toolkit in the repo rather than treating this as a one-off: the same conversion and the same CDN-reduction query run against the next export, and the projected drop becomes a measured one.