Skip to content

Summary or Rollup Table

Precomputed aggregate table — instantiates Access-Optimized Redundant Representation

Precomputes and stores grouped aggregates — counts, sums, and rollups at a chosen grain — so repeated dashboard queries read a small answer table instead of rescanning and re-aggregating the raw rows every time.

A Summary or Rollup Table stores the pre-computed results of an aggregation — totals, counts, averages, or rollups grouped at a chosen grain — so that a query which would otherwise scan and aggregate millions of raw rows instead reads a handful of pre-summarized ones. Its defining feature is that it is row-reducing: unlike a prejoined table, which reproduces the same rows pre-stitched, a rollup collapses many source rows into one summary row per group (per day, per region, per customer), trading away the detail to make the aggregate a cheap lookup. The whole justification is economic — an aggregation that is computed once and read a thousand times is worth precomputing; one read once is not — so the mechanism lives or dies on the ratio of read frequency to source churn.

Example

A SaaS platform bills on API usage and shows customers a "calls this month, by day" chart on every dashboard load. Computing it live means scanning a request-log table of billions of rows and grouping by customer and day on every page view — slow and ruinously expensive at the platform's scale. The read is also enormously repetitive: the same customers reload the same chart constantly.

The team maintains a rollup at the grain of (customer, day): one row holding that day's call count and total billable units, updated as new usage lands. The dashboard query now reads at most thirty-one small rows per customer instead of aggregating billions — sub-millisecond, and cheap enough to serve on every load. The rollup is worth it precisely because of the read-to-write ratio: usage is written once and the daily total is read thousands of times across dashboards, billing, and alerts. A metric read only once a quarter would never justify the storage and maintenance, and the team explicitly declines to roll those up.

How it works

  • Justify the rollup economically. Precompute only aggregates whose read frequency dwarfs their update churn; the read-to-write ratio, not mere query slowness, decides whether a rollup earns its keep.
  • Fix the grouping grain. Choose the group keys and level (per day, per region) the workload actually queries; the grain sets both the table's size and which questions it can answer without touching raw data.
  • Maintain the aggregate, not the rows. As source rows change, update the affected group's summary — incrementing a count, adjusting a sum — rather than the individual detail, so the summary stays a small governed derivation of the raw data.

Tuning parameters

  • Aggregation grain — coarse (monthly per region) versus fine (hourly per customer). Finer grains answer more questions and re-roll into coarser ones, but multiply row count and maintenance cost.
  • Materialization eagerness — update the rollup on every source write, on a schedule, or lazily on first read. Eager keeps it always current at write-time cost; scheduled and lazy are cheaper but admit staleness.
  • Additivity handling — whether the measures are additive (sums, counts roll up trivially) or non-additive (distinct counts, medians, ratios can't simply be summed across groups). Non-additive measures constrain how coarse grains can be derived from finer ones.
  • Pre-aggregation breadth — how many group-by combinations to precompute. Materializing every combination makes every query instant but explodes storage and maintenance combinatorially; too few leaves common queries un-served.

When it helps, and when it misleads

Its strength is enormous read leverage for repetitive aggregate queries: dashboards, scorecards, and billing views that ask the same grouped question over and over become trivial lookups, and the raw scan is paid once at write or refresh time instead of on every read. When reads vastly outnumber changes, few mechanisms save more.

It misleads when the pre-aggregation is over-built. Materialize every possible group-by combination and you hit write amplification[1] — a single source row must now update many rollups — plus combinatorial storage growth, so the maintenance cost swamps the read savings the table was meant to deliver. Rollups also silently lose the detail: a summary can answer "how many per day" but not "which ones," and a naive rollup of a non-additive measure (a distinct-user count summed across regions) is simply wrong. And a stale rollup understates today's still-accumulating totals, so it must never be presented as final when its window is open. The discipline is to roll up only what the read-to-write ratio justifies, keep grains additive where they will be re-aggregated, expose the rollup's freshness, and preserve a path back to raw detail for the questions a summary cannot answer.

How it implements the components

Summary or Rollup Table supplies the economics and derivation of a precomputed aggregate — the why-and-what, not the freshness or read-contract machinery:

  • performance_benefit_and_cost_model — its whole existence is a benefit/cost judgement: precompute an aggregate when read frequency dwarfs update churn, and decline when it does not.
  • derivation_and_duplication_mapping — it defines the aggregation that derives each summary row from source detail — the group keys, measures, and how updates fold in.
  • access_workload_profile — the chosen grain and group keys come straight from the repetitive aggregate queries the workload issues; the table is shaped to that read pattern.

It does not eliminate joins or preserve rows (redundant_representation_specification for a flat detail copy) — that is Prejoined Read Table — and it does not keep itself current (freshness_rule, synchronization_rule), which is Scheduled Incremental Refresh.

References

[1] Write amplification is the multiplication of downstream write work triggered by a single source change — here, one source row updating many precomputed rollups. It is the natural ceiling on how much pre-aggregation is worth materializing, and the reason "roll up everything" is a trap rather than an optimization. ↩