Section 9: Counting Users You Never Stored - Deep Dive: The Query Engine — Cubes, Sketches & Sampling

Executive Summary: Section 8 made the numbers correct. This section makes them fast. A report's p95 budget is 2 seconds, and the query engine meets it by refusing to touch raw events whenever it can. A cube router sends each request to the smallest pre-aggregated cube that covers it. HyperLogLog++ sketches answer "how many distinct users" in about 12 KB per row with ~0.81% relative standard error, and they merge across any date range without double-counting. Counters stay additive, and ratios are computed only at query time. Questions no cube covers fall through to explorations over raw events, sampled by hash(client_id) above 10M events. A version-keyed cache and per-property quotas finish the job. The engine is fast because the hard work happened at write time, and honest because the response names every approximation.

The interviewer circles the runReport box from Section 4: "Dana asks how many users came from the newsletter last week and how many bought. You have 2 seconds. Walk me through it."

You: "The answer is mostly already computed. My job at query time is to find the right precomputed pieces, combine them in ways that are mathematically allowed, and say which approximations I used. There are four moves: route, merge, divide, label."

Move 1: Route to the Smallest Cube That Covers the Question

Each of the ~30 cubes declares its dimension set, say (source, medium) for traffic sources, (page path) for pages, (country, device) for audience. A request is covered when every dimension it groups by or filters on is in the cube's set, and every metric it asks for is a column there. Among covering cubes the router picks the one with the fewest rows for that property, because fewer rows means fewer bytes read. Dana's request (dimension sessionSource, filter = newsletter, 7 days) is covered by the traffic-source cube for users, key events and revenue; distinct purchasers come from the source × event-name cube described below. The engine reads 7 daily rows for P-40213, one per Denver day, from each day's newest version, which is final for older days and possibly provisional for yesterday.

The alternative, scanning raw for every widget, would read Trailhead's ~3M hits a day × 7 = ~21M rows to produce what the cube answers in 7. Raw scans are kept for questions nobody predicted, and a question the logs show running constantly becomes a new cube.