Speeding Up BigQuery with Materialized Views: 10 Code-Along Examples

The materialized views article describes them in five words: a cache for a SQL query. A standard view recomputes every time you read it; a materialized view stores the precomputed result and refreshes itself automatically, so dashboards and repeated aggregations stop rescanning the raw table. This workbook builds that up step by step. You will contrast a view with a materialized view, watch BigQuery rewrite queries to use one transparently, tune the refresh, partition and cluster it, inspect its metadata, and finally hit the limitations and the non-incremental escape hatch. Run Example 1 first.

1. Create the sample tables

Materialized views must sit on top of a real managed table, so run this once to build the fact table (plus a small customers table for the join example at the end). The purchases carry a status and a date, which the dashboard-style views use.

-- Run once. Change `scratch` to any dataset you can write to.
CREATE OR REPLACE TABLE scratch.customers AS
SELECT * FROM UNNEST([
  STRUCT(1 AS customer_id, 'Ava' AS name),
  STRUCT(2, 'Ben'), STRUCT(3, 'Chloe'), STRUCT(4, 'Dan')
]);
CREATE OR REPLACE TABLE scratch.purchases AS
SELECT * FROM UNNEST([
  STRUCT(1 AS purchase_id, 1 AS customer_id, 102 AS product_id, 300 AS amount, 'completed' AS status, DATE '2024-05-01' AS purchase_date),
  STRUCT(2, 1, 101, 80,   'completed', DATE '2024-05-01'),
  STRUCT(3, 1, 103, 50,   'refunded',  DATE '2024-06-01'),
  STRUCT(4, 2, 103, 25,   'completed', DATE '2024-05-10'),
  STRUCT(5, 3, 102, 1200, 'completed', DATE '2024-05-12'),
  STRUCT(6, 3, 101, 80,   'completed', DATE '2024-06-15')
]);
SELECT * FROM scratch.purchases ORDER BY purchase_id;

Six purchases across three buyers, one refunded. That is enough to see every materialized view behaviour; the point here is speed at scale, not the size of this toy table.

2. A standard view first, for contrast

A regular view stores no data. It is a saved query that recomputes from scratch every single time you read it, which is exactly the cost a materialized view removes.

CREATE OR REPLACE VIEW scratch.customer_spend_view AS
SELECT customer_id, SUM(amount) AS total_spent
FROM scratch.purchases
GROUP BY customer_id;
SELECT * FROM scratch.customer_spend_view;   -- rescans purchases every call

Every SELECT against this view re-aggregates the whole purchases table. On six rows that is free; on a billion-row fact table read a thousand times a day by a dashboard, it is the problem materialized views exist to solve.

3. Your first materialized view

CREATE MATERIALIZED VIEW runs the query once, stores the result, and from then on serves the stored rows while refreshing them automatically in the background.

CREATE MATERIALIZED VIEW scratch.customer_spend_mv AS
SELECT
  customer_id,
  COUNT(*)    AS orders,
  SUM(amount) AS total_spent
FROM scratch.purchases
GROUP BY customer_id;

The syntax is a normal aggregation query with CREATE MATERIALIZED VIEW ... AS in front. If you rerun this and it complains the view exists, add IF NOT EXISTS or drop it first (Example 10). BigQuery now maintains this result for you, incrementally, whenever purchases changes.

4. Query it like a table

Once created, a materialized view behaves like any table for reading: you can select from it, filter it, order it, and join it. Reads come from the stored result, not the base table.

SELECT *
FROM scratch.customer_spend_mv
WHERE total_spent > 100
ORDER BY total_spent DESC;

Chloe at 1280 and Ava at 430 come back. The filter runs against the tiny precomputed result, so this scans a fraction of the bytes the equivalent query on purchases would. That reduction in bytes scanned is where both the speed and the cost saving come from.

5. Smart tuning: automatic query rewrite

This is the feature that makes materialized views feel like magic. You do not have to query the view by name; when you run an aggregation against the base table, BigQuery can transparently rewrite it to use a matching materialized view instead.

-- note: we query the BASE table, not the materialized view
SELECT customer_id, SUM(amount) AS total_spent
FROM scratch.purchases
GROUP BY customer_id;

Even though this reads scratch.purchases, BigQuery recognises that customer_spend_mv already holds the answer and serves it from there. To confirm, open the job’s Execution Details after it runs and look for the materialized view listed in the plan. This means existing dashboards and reports get faster the moment you create the right view, with no query changes at all.

6. A dashboard-style view with a filter

The classic use case is a daily revenue summary. Materialized views support WHERE, GROUP BY, and the deterministic aggregates COUNT, SUM, MIN, and MAX, which is exactly what a metrics table needs.

CREATE MATERIALIZED VIEW scratch.sales_summary_mv AS
SELECT
  purchase_date AS sale_date,
  product_id,
  SUM(amount) AS daily_revenue,
  COUNT(*)    AS orders
FROM scratch.purchases
WHERE status = 'completed'          -- filter is baked into the cache
GROUP BY sale_date, product_id;
SELECT * FROM scratch.sales_summary_mv ORDER BY sale_date, product_id;

The WHERE status = 'completed' lives inside the view, so the refunded purchase never reaches the cache and every dashboard read gets clean, pre-filtered totals. A dashboard hitting this on each page load reprocesses nothing; it just reads the small stored summary.

7. Tuning the refresh

By default a materialized view refreshes automatically, roughly every 30 minutes. OPTIONS lets you control that: whether auto-refresh is on, how often, and how stale a read is allowed to be before BigQuery refreshes on demand.

CREATE MATERIALIZED VIEW scratch.orders_by_status_mv
OPTIONS (
  enable_refresh = true,                 -- auto-refresh on (the default)
  refresh_interval_minutes = 30,         -- how often the background refresh runs
  max_staleness = INTERVAL "0:30:0" HOUR TO SECOND   -- serve cache up to 30 min old
)
AS
SELECT status, COUNT(*) AS orders, SUM(amount) AS revenue
FROM scratch.purchases
GROUP BY status;

max_staleness is the important dial: it lets a query be answered from the cache even if the base table changed moments ago, trading a little freshness for guaranteed low latency and cost. This is the knob you reach for when a dashboard must be fast and “a few minutes old” is acceptable, which describes most dashboards.

8. Partitioning and clustering

A materialized view can be partitioned and clustered just like a table, which speeds up reads that filter on those columns. Partition by the date and cluster by the dimension your dashboard filters on most.

CREATE MATERIALIZED VIEW scratch.daily_revenue_mv
PARTITION BY sale_date
CLUSTER BY product_id
AS
SELECT
  purchase_date AS sale_date,
  product_id,
  SUM(amount) AS daily_revenue
FROM scratch.purchases
GROUP BY sale_date, product_id;
SELECT * FROM scratch.daily_revenue_mv
WHERE sale_date = DATE '2024-05-01';   -- prunes to one partition

Filtering on sale_date now prunes to a single partition instead of scanning the whole view, and clustering on product_id co-locates rows so per-product reads touch less data. The partition and cluster columns must appear in the view’s SELECT, which they do here as sale_date and product_id.

9. Inspecting the view’s metadata

INFORMATION_SCHEMA.MATERIALIZED_VIEWS reports the state of your views: when each last refreshed and how far its cached data has caught up to the base table. This is how you check a view is healthy and current.

SELECT
  table_name,
  last_refresh_time,
  refresh_watermark      -- base-table data up to here is reflected in the cache
FROM `scratch.INFORMATION_SCHEMA.MATERIALIZED_VIEWS`
ORDER BY table_name;

last_refresh_time tells you when the background job last ran, and refresh_watermark tells you the point in the base table’s history the cache currently reflects. If the watermark lags far behind, the view is stale and a read may trigger a refresh. This view is your monitoring dashboard for the materialized views themselves.

10. Limitations, the non-incremental escape hatch, and cleanup

Classic materialized views are deliberately restricted: single table only, no JOIN, UNION, LIMIT, or DISTINCT. Those restrictions are what allow cheap incremental refresh. When you need more, a non-incremental materialized view lifts them, at the cost of full recomputes.

-- This would FAIL: classic materialized views cannot use JOINs
-- CREATE MATERIALIZED VIEW scratch.bad_mv AS
-- SELECT c.name, SUM(p.amount) AS total
-- FROM scratch.purchases p JOIN scratch.customers c USING (customer_id)
-- GROUP BY c.name;
-- The escape hatch: a NON-INCREMENTAL view allows joins,
-- but must declare max_staleness and refreshes by full recompute
CREATE MATERIALIZED VIEW scratch.spend_by_name_mv
OPTIONS (
  enable_refresh = true,
  max_staleness = INTERVAL "1:0:0" HOUR TO SECOND,
  allow_non_incremental_definition = true
)
AS
SELECT c.name, SUM(p.amount) AS total_spent
FROM scratch.purchases AS p
JOIN scratch.customers AS c USING (customer_id)
GROUP BY c.name;
-- Clean up the objects created in this workbook when you are done
DROP MATERIALIZED VIEW IF EXISTS scratch.customer_spend_mv;
DROP MATERIALIZED VIEW IF EXISTS scratch.sales_summary_mv;
DROP MATERIALIZED VIEW IF EXISTS scratch.orders_by_status_mv;
DROP MATERIALIZED VIEW IF EXISTS scratch.daily_revenue_mv;
DROP MATERIALIZED VIEW IF EXISTS scratch.spend_by_name_mv;

allow_non_incremental_definition = true unlocks joins and a wider SQL surface, and it requires max_staleness because BigQuery can no longer refresh a piece at a time. Choose it when the query genuinely needs a join; prefer the classic form otherwise, since incremental refresh is far cheaper. The DROP statements at the end remove everything so you leave no billable objects behind.

Work through these and you have the whole article in practice: the view-versus-materialized-view distinction, automatic and smart-tuned query rewriting, the supported aggregations and the dashboard pattern, refresh and staleness tuning, partitioning and clustering, metadata inspection, and the limitations with their non-incremental workaround. The judgement to carry: reach for a materialized view when the same aggregation is read over and over against a large table and a few minutes of staleness is fine, which is the exact shape of nearly every dashboard.

See you soon.