An aggregate fact table pre-summarizes a base fact table at a coarser grain so that common queries scan far less data. Where a transaction fact table holds one row per sale, an aggregate might hold one row per product per day - the same measures, rolled up. It's a performance optimization, not a new source of truth, and that distinction governs how it's built and maintained.

Why pre-aggregate

Analytic queries routinely ask for totals at a grain much coarser than the base events: monthly revenue by region, daily active users by plan. Answering those from a billion-row transaction table means scanning and summing the whole thing every time. An aggregate computed once - say sales_by_product_day rolled up from per-transaction rows - turns that repeated full scan into a small lookup. For dashboards that run the same handful of rollups thousands of times a day, the read savings dwarf the cost of maintaining the summary.

-- base: one row per sale
fact_sales (sale_id, date_key, product_key, store_key, amount, quantity)

-- aggregate: one row per product per day
agg_sales_product_day (date_key, product_key, total_amount, total_quantity, txn_count)

Only additive measures aggregate cleanly

Aggregates are safe to build from fully additive measures - amounts, counts, quantities that sum across every dimension. Semi-additive measures (balances, levels) and non-additive ones (ratios, distinct counts) don't roll up by simple summation: you can't add a distinct-user count across days to get a monthly distinct count, and averaging an average is wrong. Store the additive components (sum and count) in the aggregate and compute the ratio at query time, and treat distinct counts as needing their own grain rather than a rollup.

The consistency contract

An aggregate must reconcile exactly with the base table it summarizes. If a late-arriving fact or a correction updates the base and the aggregate isn't refreshed in step, two reports that should agree will drift - one hitting the base, one hitting the summary. This is the core operational burden: every load that touches the base must update the matching aggregate rows, and the two should be reconcilable by a checksum query at any time. The application or BI layer chooses the aggregate transparently (aggregate navigation), so users never query it directly and never see which one answered.

Frequently asked questions

How is an aggregate different from a materialized view? Conceptually they're the same idea - a stored, pre-computed rollup. "Aggregate fact table" is the dimensional-modeling term and is usually maintained by the ETL alongside the base; a materialized view is the database engine's built-in mechanism for the same result, refreshed by the engine.

What grain should an aggregate use? The grain of your most common, most expensive queries - often a date rolled up to day or month and the highest-cardinality dimension reduced to its reporting level. Build aggregates for proven query patterns, not speculatively.

Why not just aggregate every combination? The number of possible rollups explodes combinatorially, and each one is storage plus maintenance cost. Pick the few that pay for themselves in read savings.

Do aggregates ever return wrong numbers? Only when they fall out of sync with the base or when a non-additive measure was summed. Keep the refresh in lockstep with base loads and restrict aggregates to additive measures.