Is every aggregate a materialized view?

No. A materialized view is one implementation option. A managed summary table or an analytical cube can also hold aggregates.

Can an aggregate be updated continuously?

Some designs maintain summaries incrementally as data changes. The supported operations and consistency behavior depend on the system and the measure.

Should detailed records be deleted after creating aggregates?

That is a separate retention decision. Deleting detail can remove the ability to audit, drill down or answer future questions that the summary does not support.

AI Glossary / Data engineering and architecture / Intermediate

Aggregate (data warehouse)

Definition

An aggregate in a data warehouse is a summary of more detailed records, usually stored to answer common analytical queries efficiently. It groups data at a coarser level, such as daily sales by store instead of individual transaction lines.

What it means

The level represented by one row is its grain. A detailed table might hold one row per sold item on a receipt. An aggregate could hold one row per store, product and day, combining all relevant item lines into totals.

This can reduce the amount of data a report must read and combine repeatedly. The summary is useful only for questions supported by its grain and retained measures. A daily store total cannot explain which individual customer bought a particular item if customer detail was discarded during aggregation.

An aggregate is therefore normally a performance aid alongside detailed data, not a complete replacement for it. It also differs from an ordinary view that merely stores a query definition: a persisted summary stores results that may need maintenance when the underlying records change.

How it works

Design begins with recurring queries and a declared summary grain. Engineers select grouping dimensions and define measures such as total quantity, total sales and transaction counts. They then compute the summary and make it available to the reporting layer.

Some systems can direct a suitable query to an aggregate automatically. Others require explicit queries or semantic-layer configuration. Either way, the result must have the same meaning as a correct calculation over the relevant detailed records.

Maintenance may rebuild the summary or apply changes incrementally. Late-arriving transactions, corrections and deletions must be reflected according to a stated freshness policy. A summary refreshed last night does not necessarily include this morning's activity.

Not every number can simply be summed. Ratios often require summing their numerators and denominators separately before division. Inventory balances can be combined across locations at one time, but summing successive daily balances does not produce a meaningful month-end balance. Distinct counts also need special treatment when groups overlap.

Three practical examples

A retail dashboard reads daily store totals instead of scanning every transaction line for each chart refresh.
A service team summarizes completed jobs by week and region, retaining total duration and job count for weighted averages.
A demand-forecasting pipeline produces product-day quantities while preserving detailed records for investigating unusual spikes.

Think of it this way

Think of a meeting's summary sheet alongside its full notes. The sheet answers recurring high-level questions quickly, while the notes retain details needed for follow-up.

The analogy stops where calculation rules begin. A warehouse aggregate must follow precise grouping and measure definitions; it is not an editor's interpretation. A concise result can still be wrong if it mixes incompatible groups or combines measures incorrectly.

Worked example

Why an average of averages can fail

Suppose an invented support dataset has two regional summaries. Region A completed two jobs with a total duration of 20 minutes, so its average is 10 minutes. Region B completed eight jobs with a total duration of 240 minutes, so its average is 30 minutes.

Taking the simple average of those regional averages gives (10 + 30)/2 = 20 minutes. That weights each region equally rather than each job.

The correct overall job average uses the additive components: total duration 20 + 240 = 260 minutes, divided by total count 2 + 8 = 10 jobs. The result is 26 minutes.

An aggregate that retains only each region's average cannot reliably answer the combined question unless the corresponding counts are available elsewhere. Retaining sum and count allows correct recombination for this measure.

Now suppose the question asks for the median job duration. Neither the two averages nor the sum and count is enough to calculate an exact median. The retained statistics must match the intended question; summaries cannot preserve every analysis.

Illustrative case study

A fictional wholesaler builds a monthly category-sales aggregate to speed management reports. Later, analysts request customer-level comparisons and find that the summary does not retain the customer dimension.

Rather than distributing category totals across customers by guesswork, the team uses the detailed sales table for that question. It also notices that a returned order posted after the monthly refresh has not yet changed the summary.

The team documents which queries the aggregate supports and how corrections propagate. It displays the relevant refresh time and reconciles summary totals against detail. The case illustrates scope and maintenance requirements.

Common misconceptions and mistakes

An aggregate cannot answer every question supported by the original records.
Averaging group averages is usually wrong when group sizes differ.
A count of rows and a count of distinct customers are different measures.
Combining overlapping summaries can double-count the same underlying events.
A stored summary may be stale even when the detailed table is current.

Business use

Choose aggregates around repeated, expensive questions whose meaning is stable. Balance faster reads against storage, refresh work and the complexity of maintaining consistent definitions.

For AI applications, record the time window and data availability rules used to create aggregate features. A historical prediction must not receive a summary containing events that occurred after its prediction time. Also check whether grouping hides important variation: an overall improvement can coexist with worse outcomes in a particular subgroup.

Check your understanding

Two stores report 100 and 80 distinct customers, and 20 customers visited both stores. Is the combined distinct count 180?

Answer: No. The overlapping customers would be counted twice. The combined count is 100 + 80 − 20 = 160, assuming those figures describe the same reporting period.

Related terms

Data Warehouse
Fact table
Materialized view
OLAP cube
grain
dimension
additive measure
aggregate navigation

Learn next

Define a daily sales summary and list five questions it can answer. Then list questions that require discarded dimensions or nonretained statistics. Add a rule for late corrections and a reconciliation check against the detailed data.

Sources

Sources: Kimball Group, Aggregate Fact Tables or Cubes: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/aggregate-fact-table-cube/ . Kimball Group, Additive, Semi-Additive, and Non-Additive Facts: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/additive-semi-additive-non-additive-fact/ . PostgreSQL materialized-view documentation: https://www.postgresql.org/docs/current/rules-materializedviews.html . Relevant summary, measure and refresh explanations were reviewed; the examples are original.

Previous: Agglomerative clustering Next: Aggregate Query