BigQuery materialized views: when they help, limits and refresh
A materialized view stores the result of an aggregate query and keeps it up to date as the base table changes, so dashboards read a small precomputed table instead of the raw data. This guide explains how it differs from a view and a scheduled table, how refresh and smart tuning work, which SQL it accepts, and reproduces the errors BigQuery returns for the queries it does not.
A materialized view stores the result of a query, usually an aggregation, and BigQuery keeps it current as the base table changes. A dashboard that asks for daily clicks by channel then reads a few thousand precomputed rows instead of scanning the raw table on every load. Google describes them as precomputed tables “for frequently run, predictable queries”, and that last word is the condition: they pay off when the same aggregation is asked for again and again.
| View | Materialized view | Scheduled table | |
|---|---|---|---|
| Stores data | no, runs the query each time | yes, the precomputed result | yes |
| Fresh | always | yes: recent changes are merged in at query time | as of the last run |
| SQL allowed | anything | a restricted set (below) | anything |
| Cost | the query, every time it is read | storage, refresh, and the smaller query | storage and each run |
| Used automatically by other queries | no | yes, smart tuning can reroute them | no |
The facts in this page are from Google’s introduction to materialized views and its create and manage pages, read on 16 October 2026.
Note: Rewritten in October 2026. Every statement below was checked with a BigQuery dry run on 16 October 2026, which validates it without creating anything; the error messages are the ones those dry runs returned.
Reading to get this done?
Create one
CREATE MATERIALIZED VIEW mydataset.daily_clicks_mv
PARTITION BY data_date
OPTIONS (
enable_refresh = true,
refresh_interval_minutes = 60
)
AS
SELECT
data_date,
search_type,
SUM(clicks) AS clicks,
SUM(impressions) AS impressions
FROM searchconsole.searchdata_site_impression
GROUP BY data_date, search_type;This aggregates a Search Console export by day and search type. Partitioning the view by the same date column as the base table lets queries on it read only the days they need. You need the BigQuery Data Editor role, or another role with the permissions to create tables, on the dataset.
How refresh works
- Automatic refresh runs on a best-effort basis. Google says BigQuery tries to start a refresh within 5 minutes of a change to the base table, if the previous refresh was more than 30 minutes ago, and does not guarantee when it starts or finishes.
refresh_interval_minutessets the frequency cap.enable_refresh = falseturns refresh off; you then refresh manually.- Queries stay correct between refreshes. For an incremental view, BigQuery combines the stored result with the changes in the base table since the last refresh. The cost of that is that a view which has not refreshed in a while reads more of the base table, and the query is slower and costs more than expected.
- Refresh jobs appear in the project’s job history, run by
bigquery-adminbot@system.gserviceaccount.com. Filter the job history by that account to see what refresh costs.
Every dashboard load scans the raw table, and you can't tell whether a materialized view would help or just add cost.
Paste this into your AI assistant. It checks the queries behind your dashboards in BigQuery, says which ones a materialized view would speed up, and writes the view or a scheduled table for each.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-materialized-views.md. The task: look at the queries behind my dashboards in BigQuery and tell me which should become a materialized view and which a scheduled table, then write the SQL for each. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Smart tuning: queries that never mention the view
Smart tuning: queries that never mention the view
When a query on the base table can be answered from a materialized view, BigQuery can reroute it to the view on its own. A dashboard query that sums clicks by day from searchdata_site_impression can be served from daily_clicks_mv without anyone changing the dashboard. Only incremental materialized views take part in this. To see whether a view was used, compare the bytes a dry run of the query reports with and without the view in place.
What SQL is allowed
Incremental materialized views accept a restricted set of SQL. The supported aggregate functions, from Google’s list:
ANY_VALUE, APPROX_COUNT_DISTINCT, ARRAY_AGG, AVG, BIT_AND, BIT_OR, BIT_XOR, COUNT, COUNTIF, HLL_COUNT.INIT, LOGICAL_AND, LOGICAL_OR, MAX, MIN, MAX_BY, MIN_BY, SUM, with some restrictions on STRUCT and ARRAY arguments.
What fails, with the errors our dry runs returned:
| Query shape | Error |
|---|---|
a window function, ROW_NUMBER() OVER (...) |
“Incremental materialized views do not support analytic functions or WITH OFFSET.” |
WHERE data_date >= DATE_SUB(CURRENT_DATE(), ...) |
“Materialized view query cannot use functions that depend on current time (example: CURRENT_TIMESTAMP, CURRENT_DATE).” |
SUM(clicks) / SUM(impressions) AS ctr |
“Incremental materialized views with aggregation … must output the result of the aggregation directly, without further transformation” |
Also unsupported, per Google: RIGHT and FULL OUTER JOIN, self-joins, ARRAY subqueries, other non-deterministic functions such as RAND(), user-defined functions, TABLESAMPLE and FOR SYSTEM_TIME AS OF. LEFT OUTER JOIN and UNION ALL were in preview at the time of writing.
The workarounds are simple once you know the rule:
- Ratios: store
SUM(clicks)andSUM(impressions)in the view and divide in the query that reads it. This is the more correct design anyway: a ratio of sums, not an average of ratios. - Rolling windows: store every day and filter by date in the reading query.
- Rankings and other window functions: apply them on top of the view.
Non-incremental materialized views
With allow_non_incremental_definition = true and a max_staleness option, a materialized view accepts most SQL, including outer joins, UNION and analytic functions. In exchange it is not merged with fresh changes at query time: it is as fresh as its staleness setting, and it does not take part in smart tuning. It suits reporting where data that is a few hours old is acceptable.
Limits worth knowing
- Each base table can have at most 100 materialized views in the same project and 500 in the same organization.
- If the base table is deleted and recreated, the materialized view must be recreated too.
- Querying the view needs access to the view and to the base tables.
Do views cost extra?
A logical view stores nothing and costs nothing to keep; each query on it runs the view’s SQL and is billed for what that reads. A materialized view adds storage for its result and the compute of its refreshes, and in return each query on it reads far less. Whether that is a saving depends on how often it is read: a view queried a hundred times a day by a dashboard pays back quickly, one queried weekly may not. Prices are in BigQuery pricing; creating and replacing ordinary views is in BigQuery views.
Materialized view, scheduled table, or both
Materialized view, scheduled table, or both
A materialized view is the right tool for one aggregation that many queries repeat and that must stay current. For logic it cannot express, ratios, rankings and rolling windows, use a scheduled CREATE OR REPLACE TABLE ... AS SELECT (see CREATE TABLE) or put that logic in the query that reads the view.
That reading query is where the definition of a metric lives: what counts as a click, which search types are included, how the ratio is taken. In OWOX Data Marts it is published once as a Data Mart, run as written in your own BigQuery project, and read by every sheet and ; a materialized view underneath keeps it fast. OWOX Data Marts does not transform the data.
Every dashboard load scans the raw table, and you can't tell whether a materialized view would help or just add cost.
Paste this into your AI assistant. It checks the queries behind your dashboards in BigQuery, says which ones a materialized view would speed up, and writes the view or a scheduled table for each.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-materialized-views.md. The task: look at the queries behind my dashboards in BigQuery and tell me which should become a materialized view and which a scheduled table, then write the SQL for each. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
What are the limits of BigQuery materialized views?
Incremental materialized views accept a restricted set of SQL: no window functions, no functions that depend on the current time, no RIGHT or FULL OUTER JOIN, no self-joins, and aggregates must be output directly, so SUM(a) / SUM(b) fails. Each base table can have up to 100 materialized views in one project.
Do materialized views cost extra in BigQuery?
You pay for storing the precomputed result and for the refresh jobs, and each query on the view reads much less than the base table. It saves money when the same aggregation is read often, for example by a dashboard; a view read once a week may cost more than it saves. A logical view costs nothing to keep.
What is the difference between a view and a materialized view in BigQuery?
A view stores only its SQL and runs it on every query. A materialized view stores the result and keeps it current by refreshing and by merging recent changes at query time. Views accept any SQL; materialized views accept a restricted set.
How often does a BigQuery materialized view refresh?
Automatic refresh is best effort: BigQuery tries to start one within 5 minutes of a change to the base table if the last refresh was more than 30 minutes ago. refresh_interval_minutes sets the frequency cap, and enable_refresh = false turns it off. Queries remain correct between refreshes.
What is a materialized view in BigQuery?
A precomputed table that stores the result of a query, usually an aggregation, and is kept up to date as the base table changes. Queries on the base table can be rerouted to it automatically through smart tuning.
