Data Warehousing
Intermediate 14 min read Data Warehousing

Materialized Views and Aggregation Layers

Pay for the heavy math once, serve it forever

Precomputed aggregates turn repeated heavy scans into reads of tiny result tables. Materialized views, aggregate tables, and incremental refresh trade freshness and maintenance for query speed.

materialized viewaggregate tableprecomputeincremental refreshrollup
Prerequisites: Warehouse Performance Aggregations and GROUP BY
Before we start

What you will learn

  • Design an aggregate layer from the hottest query shapes
  • Choose between materialized views and hand-maintained aggregate tables
  • Plan refreshes toward freshness SLOs
Introduction

What is this?

A materialized view stores the RESULT of a query as a table the engine can read directly instead of recomputing. Aggregate tables do the same by hand for chosen grains. Incremental refresh updates only changed partitions; choosing grain and refresh cadence trades warehouse cost against freshness.

Motivation

Why do you need it?

  • Dashboards that 'should be fast' are usually the same aggregate over the same big fact.
  • Precomputation is the same economics as caching the query, but queryable and governed.
  • Refresh management is your freshness contract — it belongs in the pipeline and SLOs.
Think about it

Mental model

Imagine

A cached total on a receipt vs re-adding every item

Think of

Materialized aggregate

The receipt's running total is already summed; you read the total unless you really need the item list. A refill (refresh) re-sums when new items arrive — that's the cadence question.

Mechanics

How it works

  • Materialized views store rows of a SELECT, refreshed by engine policy or schedule.
  • Aggregate tables store chosen grains (day×channel, month×region) updated by the pipeline.
  • Incremental strategies: partition-level refresh for appended data, watermark delta for changed rows.
  • Rollups let queries climb dimensions (day→month) without re-scanning the base fact.
  • Cold vs hot: the aggregate layer serves 95% of dashboard reads; the base fact serves ad-hoc depth.

From base facts to an aggregate tier.

fact_sales
agg_day_channel
agg_month_region
BI reads
Refresh cadence
Hands-on

Examples

A dashboard's query, made precomputed

A dashboard's query, made precomputed
-- The slow version (re-scans the whole fact):
SELECT d.country, p.category, sum(f.amount)
FROM fact_sales f
JOIN dim_product p USING (product_key)
JOIN dim_geo d USING (geo_key)
WHERE f.day >= '2026-01-01' GROUP BY 1,2;
 
-- Faster: an aggregate table at day x country x category grain:
CREATE MATERIALIZED VIEW agg_sales AS
  SELECT day, country_code, category, sum(amount) AS amount
  FROM fact_sales JOIN dim_product USING (product_key) JOIN dim_geo USING (geo_key)
  GROUP BY 1, 2, 3;
-- then READ FROM the aggregate and roll up for months/regions.

Same numbers, typically two orders of magnitude less scanned data per query.

Real world

In production

  • An executive KPI dashboard reads a daily aggregate table refreshed hourly, never touching the base fact.
  • A BI tool auto-rewrites queries to an aggregate tier (materialized-views/Rewrite) instead of the base.
  • Nightly batch rolls month-level aggregates so month-end reporting is instant.
Guidelines

Best practices

  • Define the aggregate grain from measured top-10 query shapes, not from intuition.
  • Keep refresh incremental and partition-bound; whole-table refresh defeats the economics.
  • Set freshness expectations (RPO per dashboard) that the refresh cadence can honor.
  • Expose the aggregate layer intentionally, with lineage to the base fact.
Watch out

Common mistakes

  • Materializing a view nobody queries, paying storage+refresh for zero speedup.
  • Refreshing a big aggregate by full rebuild every 5 minutes instead of partition-delta.
  • Letting dashboards diverge: some read the aggregate, some the fact, and numbers differ.
When things break

Debugging tips

  • When aggregate numbers differ from base facts, check the refresh watermark and late-arriving rows.
  • A refresh that grew 10x means the delta detection (watermark/partition) regressed — verify it.
  • Profile by dashboard: if any dashboard still scans the base fact, it skipped the aggregate layer.
Interviews

Interview checkpoint

Practice

Hands-on lab

Turn a Slow Query Into a Precomputed Aggregate
35 min · intermediate
In progress
Objectives
  • Create a moderately large fact and roleplay a hot dashboard query
  • Build a materialized aggregate at the dashboard's grain
  • Compare timing of base-scans vs aggregate reads

Turn a Slow Query Into a Precomputed Aggregate

35 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

The main benefit of an aggregate tier is…

2

Whole-table rebuilds are usually the cheapest way to refresh a large aggregate.

Go deeper

Official documentation

Snowflake materialized views
docs
BigQuery materialized views
docs
dbt incremental models
docs