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:

-- 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:

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

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.

# 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.