# Pre-aggregation gets you 100× — until someone applies a filter

You have a dashboard over a large fact table. Tens of millions of new rows a month, thirty tiles on the page, and every tile is an aggregate: orders by channel, revenue by region, units by device. Each one scans a lot of rows to produce a handful of numbers.

The fix is obvious and correct: stop computing the same answers over and over. Precompute them. Store the answers instead of the rows.

Done well this is one of the largest wins available in analytics — two orders of magnitude, routinely. It also has a limit that is easy to miss at design time and impossible to miss once a user clicks a filter. The limit is the interesting part, because it determines the architecture of everything downstream.

## The generic cube

The naive version of pre-aggregation is one materialized view per tile. It works and it doesn't scale organizationally: every new tile is a schema change, a migration, and a backfill.

So you generalise. Instead of a table per question, store aggregates in one long-format table where the *dimension itself* is data:

```sql
-- synthetic pre-aggregation cube
day          Date          -- bucket
dim_name     LowCardinality(String)   -- 'channel' | 'region' | 'device'
dim_value    LowCardinality(String)   -- 'web' | 'north' | 'mobile'
orders       UInt64
revenue      Decimal(18, 2)
units        UInt64
-- engine: summing / aggregating, keyed on (day, dim_name, dim_value)
```

The ETL writes one row per day per dimension per value. A summing engine collapses rows sharing that key during merges, so ingest can be dumb and append-only.

Now any single-dimension tile is the same trivial query:

```sql
SELECT dim_value, sum(orders) AS orders
FROM cube
WHERE day BETWEEN ? AND ?
  AND dim_name = 'channel'
GROUP BY dim_value;
```

Row counts tell you why it's fast. The fact table grows with events. The cube grows with `days × dimensions × distinct values` — thousands of rows where the facts have hundreds of millions. Nothing is scanned that isn't an answer.

## The limit: one slice per row

Read a cube row as a sentence: *on this day, for channel = web, here are the metrics.*

Notice what it does not say. It says nothing about region. Nothing about device. Those dimensions weren't compressed or deferred — they were **summed away**. That row is a total across every region and every device.

So:

*   *"orders by channel"* — the cube answers it instantly.
    
*   *"orders by channel, where region = north"* — the cube **cannot answer it at all.** Not slowly. Not approximately. The channel rows have no idea which regions contributed.
    

This is the property to internalise: **a long-format cube supports one-dimensional slicing only.** The speed comes precisely from having thrown away the cross-dimensional detail. You can't get it back from the cube because it isn't in there.

And the failure is asymmetric in a nasty way. Unfiltered, every tile is instant. Apply one filter and every tile that can't express it has to go somewhere else — so the page doesn't degrade gracefully, it falls off a cliff at the exact moment the user starts exploring.

## Three responses

| Option | What it buys | What it costs |
| --- | --- | --- |
| Composite dimensions | Store `channel|region` as its own `dim_name`. The filtered tile stays fast. | Combinatorial. Every pair is a new dimension; `n` dimensions have `2^n` subsets. Row count and ETL cost explode. |
| Raw fallback | Always correct, works for any filter. | You lose the 100× exactly when the user is most engaged. Applied page-wide, one filter makes thirty tiles slow. |
| Capability-aware routing | Per-tile decision: cube when it can answer, raw when it can't. | You now own an explicit model of what the cube supports, and it must not drift from what the ETL writes. |

Composite dimensions are worth it for two or three known-hot pairs and worth nothing as a general strategy — the moment you're enumerating combinations, the cube has become a worse version of the fact table.

Raw fallback is the honest baseline, and its real flaw is granularity: it's usually implemented as a page-level decision when it should be a tile-level one. A filter on `region` doesn't stop the *region* tile from being expressible.

## Teach the planner what the cube can honour

The version that holds up: keep an explicit registry of which dimensions exist in the cube, and route each tile independently.

```python
# dimensions the cube physically materialises
CUBE_DIMENSIONS = {"channel", "region", "device"}

def plan_tile(group_by: str, filters: dict) -> str:
    if group_by not in CUBE_DIMENSIONS:
        return "raw"                      # can't even group on it

    unsupported = set(filters) - CUBE_DIMENSIONS
    if unsupported:
        return "raw"                      # filter not expressible in the cube

    if set(filters) - {group_by}:
        return "raw"                      # cross-dimensional: cube summed it away
    return "cube"
```

Three checks, and the third is the one people forget: a filter on a dimension the cube *has* still forces raw data if it isn't the dimension being grouped by. `group_by=channel, filters={region}` is a cross-dimensional question, and no amount of the cube containing a `region` dimension makes it answerable.

Two properties matter more than the code:

**Allowlist, not heuristic.** `CUBE_DIMENSIONS` enumerates what the ETL actually writes. Don't infer capability from the filter's shape or from whether a lookup returns rows — an empty cube result is indistinguishable from a genuine zero.

**Fail safe toward slow.** An unknown dimension routes to raw data. The failure mode of guessing wrong in that direction is a slow tile. The failure mode of guessing wrong in the other direction is a confidently wrong number, which in analytics is a broken product rather than a performance issue.

## When not to build a cube

Pre-aggregation is a bet that your read patterns are predictable. It pays enormously when users mostly slice one dimension at a time over closed date ranges — which describes most dashboards most of the time.

It pays badly when users compose arbitrary filters, because then you're routing to raw data on most requests and maintaining a cube for the minority case. If that's your traffic, the honest move is to skip the cube and spend the effort on the raw path instead: sort key choice, projections, skip indexes. Measure the shape of real queries before committing, because the cube's value is entirely a function of that shape.

## The principle

Pre-aggregation doesn't compress your data. It **discards the dimensions you didn't group by**, and the speed is the discarding. Every cube is a set of questions made instant and a larger set made unanswerable.

Which means the design question isn't *how do I make the cube faster*. It's *which questions am I willing to make unanswerable* — and then building a system that knows the difference, instead of one that finds out at query time.