What determines how much a pre-aggregated rollup table reduces the data an analytical query scans?

## What a rollup table is A rollup (or aggregate table) physically stores the result of a `GROUP BY` over a fact table so that repeated queries read pre-computed rows instead of re-scanning the raw facts. It does not matter for the economics whether a scheduled pipeline maintains it or you declare it to the engine as a materialized view: you pay compute once per refresh and save on every read. The whole value proposition is a ratio, and a candidate who cannot estimate that ratio before building the table is guessing. ## The arithmetic of the win The first-order reduction is: ``` reduction ≈ base_row_count / distinct_combinations_of_grouping_keys ``` The denominator is the number of key tuples that **actually occur**, not the product of the individual cardinalities. Real fact data is sparse: not every product sells in every country every day. That is why the honest way to size a rollup is to run the probe rather than multiply cardinalities on a whiteboard. Example: a 4-billion-row event fact grouped by `(day, country, product_id)` might produce 9 million rows. That is roughly a 400x collapse, and since analytical engines bill by bytes scanned or by compute time proportional to bytes read, that is close to a 400x cost cut for every query that can be answered at that grain. ## Grain is the entire decision Grain is the set of grouping keys. Coarser grain means fewer rows, and each dimension you add multiplies the count. Two rules follow: - **Choose the coarsest grain that still serves your queries.** Hourly instead of per-event, day instead of hour, country instead of city — each step is often an order of magnitude. - **One high-cardinality key destroys the win.** Adding `user_id`, `session_id` or `order_id` to the grouping keys makes the aggregate approach the row count of the base table, because those keys are close to unique per fact row. At that point you have built a copy of the fact table with extra refresh cost. If a query genuinely needs per-user detail, the aggregate is the wrong tool for it — serve that query from the well-clustered base table and keep the rollup for the coarse questions. ## Why 'fewer columns' is a weaker argument than it sounds People often justify a rollup by saying it carries only a handful of columns while the fact table has eighty. In a **columnar** engine that argument is mostly already priced in: a scan of the fact table reads only the column chunks the query references, so the eighty unread columns cost nothing. The row-count collapse is the real win; the column reduction matters only for the columns the query would have read anyway. What does add on top of the row collapse is **encoding and layout**. Aggregate rows are dominated by repeated low-cardinality keys and numeric measures, which dictionary- and run-length-encode extremely well, so bytes often shrink faster than rows. And because a rollup is small, you can afford to sort or cluster it on the dimension that dashboards filter on, so the engine prunes most of it away before reading. ## When a rollup does not pay - **The query filters on a dimension the rollup does not carry.** There is no way to select the right subset of pre-aggregated rows, so the engine falls back to the fact table and you get zero benefit. - **The measure is not re-aggregatable.** Distinct counts and medians cannot be recombined from group-level results. - **Refresh cost exceeds query savings.** A rollup rebuilt every fifteen minutes to serve a weekly report is a net loss. - **The base table already prunes to the same bytes.** A highly selective query against a table partitioned and clustered on the same columns may read no more than the rollup would. ## Measure before you build Run a distinct-count probe over the proposed key tuple, compare it with the base row count, and then compare actual bytes scanned for your top few dashboard queries with and without the aggregate. After it ships, track how often it is actually used — the estimate is a prediction, the hit rate is the truth.