TopolearnDashboards that hit the same aggregation repeatedly can become many times cheaper and faster.
A logical view (CREATE VIEW) stores a query, not data. Every query on it runs the underlying SQL and is billed for the base tables' bytes. Views give reuse, a stable interface and, as authorized views, controlled access (next lesson).
A materialized view stores precomputed results and keeps them up to date:
- BigQuery refreshes it automatically in the background, by default within minutes of base-table changes but no more often than every 30 minutes (
refresh_interval_minutes).enable_refresh = falseand manualCALL BQ.REFRESH_MATERIALIZED_VIEW(...)are options. - Queries on an incremental MV always return fresh results: BigQuery combines the stored result with only the base-table changes since the last refresh.
- Smart tuning: queries against the base table can be rewritten automatically to read the MV when it can answer them.
- Incremental MVs support a restricted SQL subset: mainly aggregations such as
SUM,COUNT,AVGandAPPROX_COUNT_DISTINCT, filters, and limited joins. Non-incremental MVs (allow_non_incremental_definition = true) support any SQL but needmax_stalenessand full refreshes. max_stalenesslets queries accept slightly stale data in exchange for skipping the delta computation.
MVs cost storage plus refresh compute. They pay off for repeated aggregations over large, append-mostly tables, such as a dashboard counting events per day and country. Partition the MV like the base table to keep refreshes small. For complex multi-step transformations, scheduled queries or tools like Dataform and dbt are usually a better fit.
Example
CREATE MATERIALIZED VIEW web.daily_country_counts
PARTITION BY day
CLUSTER BY country
OPTIONS (enable_refresh = true, refresh_interval_minutes = 30)
AS
SELECT DATE(event_ts) AS day, country, event_name, COUNT(*) AS n
FROM web.events
GROUP BY day, country, event_name;
-- Query the MV directly: reads the small precomputed table
SELECT day, SUM(n) AS purchases
FROM web.daily_country_counts
WHERE day >= '2025-06-01' AND event_name = 'purchase'
GROUP BY day;
-- This base-table query may be answered from the MV automatically (smart tuning)
SELECT DATE(event_ts) AS day, COUNT(*)
FROM web.events
WHERE DATE(event_ts) >= '2025-06-01' AND event_name = 'purchase'
GROUP BY day;Remember
- Logical views store SQL; materialized views store refreshed results
- Incremental MVs return fresh data by combining stored results with recent changes
- Smart tuning can route base-table queries to an MV automatically
Watch out for
- Creating MVs on tables that are rewritten wholesale every hour, so every refresh recomputes everything.