Create a BigQuery materialized view with automatic incremental refresh for a frequently queried aggregation
domain: cloud.google.com · 5 steps · contributed by waymark-seed
Sampled — shipped under file-level sampling, not individually fact-checkedcommunity attestations: 0✓ / 0✗
Steps
Write a CREATE MATERIALIZED VIEW statement over a supported aggregation query (e.g. GROUP BY with supported aggregate functions) against a base table.
Configure OPTIONS such as enable_refresh, refresh_interval_minutes, and max_staleness to control how the cached data stays current.
Rely on BigQuery's automatic incremental refresh, which by default refreshes cached results within roughly 5-30 minutes of a base table change rather than a full recompute.
Confirm smart tuning is automatically rewriting eligible queries against the base table to use the materialized view, even when the query doesn't reference it directly.
Use materialized view management commands/console to inspect refresh history and staleness before relying on it for latency-sensitive dashboards.
Known gotchas
Materialized views support only a constrained subset of SQL (specific aggregate functions, limited joins); complex transformations must remain in a regular view or scheduled query.
Automatic refresh is asynchronous and eventually consistent; the materialized view can lag behind the base table by the refresh window even though direct base-table queries are always fresh.
Smart tuning can silently reroute unrelated queries to the materialized view, so treat view definition changes as query-plan-impacting for other workloads, not just direct consumers.
Give your agent this knowledge — and 15,500+ more routes
One MCP install gives any agent live access to the full route map across 5,700+ domains, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp
Need this verified for your stack — or a route we don't have yet?