BigQuery Materialized Views in Practice

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

A BigQuery materialized view sits in an awkward middle ground. It’s not a regular view (which is just stored SQL that re-runs on every query), and it’s not a scheduled query (which is a cron job writing into a normal table). It’s its own thing: a managed, incrementally-maintained, query-rewrite-eligible projection over a base table that BigQuery keeps in sync for you, charges you for storage and refresh, and applies to queries automatically — even ones that don’t mention the view by name.

That hybrid model is exactly what makes materialized views (MVs) useful and exactly what trips teams up. The mental model from Postgres or Snowflake doesn’t transfer cleanly: Postgres MVs are full-refresh-only without manual CONCURRENTLY work, Snowflake’s are Enterprise-edition-and-up only and have very different refresh semantics, and BigQuery’s incremental maintenance has a specific set of SQL features it supports and a specific set it doesn’t. Get the shape right and a dashboard that scanned 4 TB starts scanning 4 GB; get it wrong and you’ve added a refresh bill on top of a query bill.

This post is about what BigQuery MVs actually do, when they pay back, and the production patterns that come up when you’re maintaining a handful of them across a warehouse.

In this post, we’ll cover:

• What a BigQuery materialized view actually is — beyond the docs blurb
• Smart tuning and automatic query rewrite — the feature that does most of the work
• Incremental vs non-incremental refresh — what BigQuery can maintain cheaply and what it can’t
• Partition alignment with the base table — the single most important design rule
• Refresh modes — automatic, manual, and the enable_refresh = false pattern
• Materialized views over external tables — BigLake, Iceberg, and the cache pattern that emerged in 2024-2026
• The cost model — refresh cost + storage cost + query savings
• Limitations that bite in production — outer joins, analytic functions, LIMIT, and the non-deterministic function trap
• INFORMATION_SCHEMA for MVs — refresh history, last-refresh-time, and detecting stale MVs
• Materialized view vs scheduled query vs BI Engine — a decision rule
• Common pitfalls — refresh storms, MV drift, and the “rewrite didn’t fire” debugging loop

What a BigQuery materialized view actually is

A materialized view in BigQuery is a logical object backed by a physical, columnar BigQuery table that stores the pre-computed result of a SELECT over one base table. You declare it with CREATE MATERIALIZED VIEW and a query; BigQuery runs the query, writes the result into a managed table, and from that point on keeps the managed table in sync with the base table.

“In sync” is doing a lot of work in that sentence. BigQuery doesn’t recompute the whole projection on every base-table write. Instead, it tracks the base table’s streaming buffer and partition-level commit metadata and applies just the delta — the new rows, the affected partitions, the modified groups — to the materialized table. This is the incremental refresh, and it’s the entire reason MVs make sense as a managed feature instead of a scheduled query. When you write to the base table, BigQuery flags the affected partitions of the MV as stale, and a background process refreshes them within the configured staleness window (default: 30 minutes, configurable down to 0 minutes or up to 7 days).

The other half of the feature is automatic query rewrite, also called smart tuning. When you write a query against the base table that happens to be answerable from the materialized view — same aggregation, same group-by, same filter shape, or a superset — the BigQuery planner silently rewrites the query plan to read from the MV instead of the base table, and charges you for the bytes scanned in the MV (which is typically a tiny fraction of the base table). The application code never references the MV; it sees only the base table. The cost reduction shows up automatically in the next query.

That combination — incremental maintenance plus transparent rewrite — is the part to internalize. It makes MVs additive: you don’t have to refactor the queries that already exist, just create the projection and watch the scan bytes drop on the queries it covers.

Smart tuning and automatic query rewrite

The smart tuning rule is roughly: if the planner can prove that the user’s query can be answered by reading rows from the MV plus possibly the base-table delta (rows newer than the MV’s last refresh, if maximum staleness allows), it will do so. The set of queries this fires for is wider than people expect, because the planner reasons about the query shape rather than literal equality with the MV’s defining query.

A common pattern: you create an MV that aggregates events by event_date and customer_id:

CREATE MATERIALIZED VIEW analytics.events_daily_by_customer AS
SELECT
  event_date,
  customer_id,
  COUNT(*) AS event_count,
  APPROX_COUNT_DISTINCT(session_id) AS session_count,
  SUM(amount) AS revenue_total
FROM analytics.events
GROUP BY event_date, customer_id;

Note the APPROX_COUNT_DISTINCT — an incremental MV can’t use an exact COUNT(DISTINCT) (BigQuery rejects it with “do not support the DISTINCT clause”); distinct counts have to go through APPROX_COUNT_DISTINCT / HLL sketches, which is also what makes them re-aggregatable.

That MV doesn’t just speed up the exact same query. It also covers:

• SELECT event_date, SUM(event_count) FROM analytics.events GROUP BY event_date — the planner re-aggregates the MV’s event_count by event_date, dropping the customer_id axis. The base table is never read.
• SELECT customer_id, SUM(revenue_total) FROM analytics.events WHERE event_date >= '2026-01-01' GROUP BY customer_id — the filter is pushed down into the MV’s event_date column, and the re-aggregation runs over the matching partition.
• SELECT COUNT(*) FROM analytics.events WHERE event_date BETWEEN '2026-01-01' AND '2026-01-31' — SUM(event_count) over the MV partitions in that range.

What the rewrite doesn’t cover is queries that need a column the MV didn’t keep. SELECT MAX(timestamp) FROM analytics.events GROUP BY event_date can’t be answered from the MV because timestamp isn’t in the projection. Neither can COUNT(DISTINCT user_id) — the MV kept a distinct-count sketch of session_id, not user_id, and one can’t be derived from the other. The planner falls back to the base table silently and you pay the full scan.

The way to confirm a rewrite fired is to look at the query plan. In the BigQuery console, after running the query, the “Execution details” tab shows the table the planner actually scanned. If you see your MV’s name there, the rewrite worked; if you see the base table, it didn’t. Programmatically, INFORMATION_SCHEMA.JOBS_BY_PROJECT.referenced_tables lists exactly what the job read, and JOBS_BY_PROJECT.materialized_view_statistics gives you per-MV usage counts.

A small but important detail: smart tuning is enabled by default, but only fires when the staleness budget allows. If your MV is configured with max_staleness = 0 MINUTE (forces fresh reads), the planner can still use the MV but must also read the base table delta to satisfy the freshness requirement — which often means the rewrite degrades into “read MV + read recent base partition” rather than “read MV only”. A staleness window of 15-60 minutes is the right default for most analytics workloads.

Incremental vs non-incremental refresh

The incremental refresh is what makes BigQuery MVs cheap. The non-incremental refresh is what makes them expensive. The line between them is the SQL constructs your MV’s defining query uses.

Incrementally refreshable MVs support these aggregations: SUM, COUNT, COUNT(DISTINCT) (with HLL_COUNT.INIT), MIN, MAX, AVG (rewritten as SUM/COUNT), BIT_AND/BIT_OR/BIT_XOR, LOGICAL_AND/LOGICAL_OR, ANY_VALUE, and APPROX_COUNT_DISTINCT (rewritten through HLL sketches). The query can include WHERE, GROUP BY, HAVING (in limited form), and joins between the base table and small dimension tables. It cannot include ORDER BY, LIMIT, analytic / window functions, RIGHT/FULL OUTER JOIN, non-deterministic functions like CURRENT_TIMESTAMP(), or sub-queries (other than the trivial case of CTEs that fold into the main query). LEFT OUTER JOIN and UNION ALL are supported (in Preview as of 2025) but make the MV ineligible for smart-tuning rewrite.

By default, a defining query that uses any of those unsupported constructs simply fails at CREATE MATERIALIZED VIEW — BigQuery returns an error like “Materialized views do not support the Sort operation” rather than silently building something slow. To use a broader (but still limited) set of SQL you have to opt in with OPTIONS(allow_non_incremental_definition = true) plus a max_staleness. That gives you a non-incremental MV, with two big caveats: every refresh recomputes the whole projection from scratch, and the MV is not eligible for smart tuning — the planner will not auto-rewrite base-table queries to it, so you must reference it by name. For a 10 TB base table refreshed every 30 minutes, the full recompute is 480 TB of scans per day, roughly $2,400 in on-demand compute. Non-incremental MVs on large tables are usually a billing accident waiting to happen.

The trap is the opt-in. Once you set allow_non_incremental_definition = true to get a tricky query to build, BigQuery stops pushing back — it just full-refreshes every cycle. There is no per-refresh refresh_type flag and no MATERIALIZED_VIEW_REFRESH_HISTORY view (despite what some older write-ups claim), so the cost is easy to miss until the billing console jumps. The signal you can actually query is the size of the automatic refresh jobs: a non-incremental refresh scans roughly the whole base table every time, an incremental one only the changed partitions.

The defensive pattern is to check those refresh jobs right after creating the MV. Automatic refresh jobs land in INFORMATION_SCHEMA.JOBS_BY_PROJECT with a job_id that starts with materialized_view_refresh:

SELECT
  job_id,
  creation_time,
  total_bytes_processed,
  error_result.reason AS error_reason
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND job_id LIKE 'materialized_view_refresh%'
ORDER BY creation_time DESC;

If total_bytes_processed on each refresh is close to the full size of the base table, the MV is doing full recomputes (non-incremental, or its partitioning is misaligned — see the next section). To check whether the last refresh failed, read last_refresh_status from INFORMATION_SCHEMA.MATERIALIZED_VIEWS: a non-NULL value means the last automatic refresh errored.

Partition alignment with the base table

The single most important design rule for incremental BigQuery MVs is that the MV must inherit the partitioning of the base table. If the base table is partitioned by DATE(created_at), the MV’s GROUP BY should include DATE(created_at) so that the MV can also be partitioned by it, and BigQuery’s refresh process can apply per-partition deltas instead of recomputing the whole MV.

When you create an MV, BigQuery automatically partitions the MV’s storage on the same column as the base table, provided the defining query carries that column through the projection. If you drop the partitioning column from the GROUP BY, the MV becomes an unpartitioned table, and every base-table partition write forces a refresh of the entire MV. This is one of the most expensive accidents in the feature: a perfectly innocent GROUP BY customer_id, region over a DATE(event_date)-partitioned base table produces an unpartitioned MV, and now every event ingested into yesterday’s partition triggers a refresh that recomputes the projection over all of history.

The fix is almost always to add the partition column to the GROUP BY:

-- Bad: unpartitioned MV, full refresh on every base write
CREATE MATERIALIZED VIEW analytics.events_by_customer_bad AS
SELECT
  customer_id,
  region,
  COUNT(*) AS event_count
FROM analytics.events
GROUP BY customer_id, region;

-- Good: inherits DATE(event_date) partitioning from base table
CREATE MATERIALIZED VIEW analytics.events_by_customer_good AS
SELECT
  DATE(event_date) AS event_date,
  customer_id,
  region,
  COUNT(*) AS event_count
FROM analytics.events
GROUP BY DATE(event_date), customer_id, region;

The second MV is partitioned by event_date, so when yesterday’s partition gets a new write, only yesterday’s MV partition needs to refresh. The first MV refreshes the entire projection. The query shape SELECT customer_id, SUM(event_count) FROM analytics.events GROUP BY customer_id is still answerable from the second MV — the planner re-aggregates across the partitions — so you lose nothing on the rewrite side.

For tables with time-unit column partitioning (hour / day / month / year), BigQuery inherits the same partitioning granularity. For ingestion-time partitioning, the _PARTITIONTIME or _PARTITIONDATE pseudo-column must be projected explicitly. For integer range partitioning, the integer column must be in the GROUP BY. Clustering keys are inherited automatically if the columns are kept in the projection — and clustering matters a lot for MVs because most MV queries filter on the same columns the base table is clustered by.

Refresh modes — automatic, manual, and enable_refresh = false

BigQuery offers three refresh modes for an MV, configured at create time and changeable later via ALTER MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW analytics.events_daily
OPTIONS (
  enable_refresh = true,
  refresh_interval_minutes = 30,
  max_staleness = INTERVAL "1" HOUR
)
AS
SELECT DATE(event_date) AS event_date, COUNT(*) AS n
FROM analytics.events
GROUP BY DATE(event_date);

enable_refresh = true (the default) is the automatic mode: BigQuery refreshes the MV every refresh_interval_minutes and serves stale reads up to max_staleness. This is the “set it and forget it” mode and the right default for most production MVs.

refresh_interval_minutes controls how often the refresh runs. The minimum is 1 minute and the maximum is 7 days. Setting it lower than 5 minutes is usually unnecessary for analytics workloads and starts to cost more in refresh-job overhead than it saves on query freshness.

max_staleness controls what the planner can serve from the MV without triggering a refresh first. If max_staleness = INTERVAL "30" MINUTE and the MV’s last refresh was 25 minutes ago, queries can read from the MV directly. At 35 minutes, the planner either triggers a synchronous refresh of the affected partitions before serving the query, or falls back to the base table — depending on the size of the delta. Setting max_staleness = INTERVAL "0" MINUTE forces every query to consult fresh data, which usually means reading the base-table delta in addition to the MV. This is occasionally what you want for reporting on the last few minutes of activity, but it costs more per query.

enable_refresh = false is the manual mode: BigQuery stops auto-refreshing the MV but the smart-tuning rewrite still works for queries inside the existing staleness window. This is the pattern teams use for two cases. First, when the MV is used only by a daily batch job and you’d rather refresh once after the upstream ETL completes — you set enable_refresh = false and then run CALL BQ.REFRESH_MATERIALIZED_VIEW("analytics.events_daily") from your orchestrator after the upstream job lands. Second, when the MV exists primarily as a cache of an expensive query over a slow-changing dimension table — enable_refresh = false plus periodic manual refresh gives you full control over when the refresh cost is incurred.

A subtle behavior: max_staleness interacts with the planner’s willingness to use the MV. A high max_staleness (say, 24 hours) means the planner uses the MV freely without triggering refreshes for in-flight queries — the refresh runs on its own schedule. A low max_staleness (under an hour) means the planner is sometimes forced to refresh before serving, which can show up as occasional latency spikes on queries that happen to land just past the staleness window.

Materialized views over external tables

Materialized views over BigLake and Iceberg external tables — particularly tables backed by Parquet in Cloud Storage or Apache Iceberg — became generally available across 2024-2025 and saw the staleness model and incremental support fill in over 2025-2026. They cover a use case that no other feature in BigQuery does well: an on-demand-priced query path over data living outside BigQuery’s native storage that still gets sub-second response times via a pre-computed projection.

The mental model is the same as native-storage MVs (same CREATE MATERIALIZED VIEW syntax, same smart tuning, same INFORMATION_SCHEMA views), with two practical differences. First, the MV is stored in BigQuery’s managed storage even when the base table is external — you pay the standard storage rate on the MV’s bytes. Second, the staleness model is calibrated against the external table’s metadata snapshot rather than against streaming-buffer commits, because external tables don’t have a streaming buffer. For Iceberg tables, BigQuery uses the Iceberg manifest’s snapshot ID as the freshness watermark; for Hive-partitioned Parquet, it uses file-mtime metadata at the directory level.

For incremental refresh on an Iceberg-backed MV, BigQuery reads the manifest deltas between the MV’s last-known snapshot and the current snapshot and applies just the affected file changes to the MV. This makes Iceberg MVs cheap to maintain: a daily-updated Iceberg fact table sees one small refresh per day, and the MV’s smart tuning eliminates the per-query manifest planning cost that’s otherwise the slowest part of external-table reads.

The pattern that emerged for 2025-2026 lakehouse setups is to leave the raw partitioned Parquet / Iceberg files in Cloud Storage (so they’re shareable with Spark and Databricks), expose them in BigQuery as a BigLake table, and create a small handful of MVs over the BigLake table for the queries that dominate the BI workload. The MV serves the dashboards from BigQuery managed storage at native speed; the underlying Parquet stays accessible to the rest of the data platform. The total cost is the storage of the MVs (typically 5-15% of the base table size) plus the per-snapshot refresh, which on an Iceberg base table is usually a few cents per day.

The one rough edge: not every operation that’s incremental against a native-storage table is incremental against an external table. Compactions in Iceberg that rewrite files without changing logical rows still register as a snapshot change and can force a partial refresh of the MV; the Iceberg expiration policy for old snapshots interacts with the MV’s last-known snapshot in subtle ways. Both are manageable — set the MV’s max_staleness higher than your compaction frequency and configure the snapshot retention to keep at least the MV’s watermark — but neither is documented prominently. The Iceberg tables in practice post covers some of the same compaction issues from the Snowflake side.

The cost model

There are three line items to track on a BigQuery MV: the one-time build cost when the MV is created, the ongoing refresh cost, and the storage cost.

The build cost is a single full scan of the base table at MV-create time. For a 10 TB base table on on-demand pricing, that’s roughly $50. This shows up as the first materialized_view_refresh% job for the MV in INFORMATION_SCHEMA.JOBS_BY_PROJECT. It’s a sunk cost; the question is whether the steady-state savings justify it.

The refresh cost depends entirely on whether the MV is incremental and how often the base table is written. An incremental MV refresh scans only the affected base-table partitions (plus a small overhead for refresh metadata). For a base table partitioned by day, with hourly writes to today’s partition only, an incremental refresh on a 30-minute schedule scans roughly the size of one day’s partition × the number of refreshes per day, but in practice BigQuery deduplicates the refresh work — if two writes happen in the same 30-minute window, only one refresh runs. The cost typically lands at 5-15% of the base table’s daily scan cost.

A non-incremental MV refresh, by contrast, scans the entire base table on every refresh. A 10 TB table refreshed every 30 minutes is 480 TB scanned per day, or ~$2,400/day on on-demand. This is where the “set up an MV, forget about it, get a surprise bill” story comes from. The defense is policy plus telemetry: only set allow_non_incremental_definition = true deliberately, and add a CI / merge gate that flags any new MV created with that option (or whose materialized_view_refresh% jobs in INFORMATION_SCHEMA.JOBS_BY_PROJECT scan close to the full base table). That catches the entire class of accidents before the billing report does.

The storage cost is the MV’s bytes-on-disk billed at standard BigQuery active storage rates (about $0.02/GB/month for active storage in us-central1 in 2026, long-term storage automatically kicks in for unmodified MV partitions after 90 days). MVs over heavily-aggregated queries are tiny — a COUNT(*) GROUP BY date, customer_id projection over a 10 TB base table is typically a few hundred megabytes. MVs that retain row-level columns (a WHERE-filtered projection without aggregation) can be a significant fraction of the base.

The query savings — the thing that has to justify all of the above — show up as a reduction in the bytes-scanned column of INFORMATION_SCHEMA.JOBS for queries the rewrite covers. A useful exercise is to capture a candidate dashboard query’s total_bytes_processed with the MV in place, then compare it against the same query where no MV covers it — BigQuery has no per-query “disable rewrite” hint, so the clean A/B is with-vs-without the MV present (e.g. in a copy of the dataset, or the figure from before the MV existed). The ratio of those two numbers is the per-query savings, and multiplied by query frequency gives you the monthly savings that has to exceed the refresh + storage cost for the MV to be worth keeping.

Limitations that bite in production

Beyond the incremental / non-incremental split covered earlier, a few specific limitations come up over and over once teams have a handful of MVs in production.

Outer joins are limited (and changing). LEFT OUTER JOIN and UNION ALL are now supported in incremental MVs (in Preview as of 2025), with two caveats: the MV no longer gets smart-tuning automatic rewrite, and it stays incrementally maintained only while the left (row-preserving) table is appended to — a change on the right side forces a full refresh. RIGHT JOIN and FULL OUTER JOIN are still unsupported. The reason is the delta model: an outer join only has a clean per-partition delta when the preserving side changes. So a LEFT JOIN to a small, slow-changing dimension is workable, but plan for full refreshes whenever the dimension changes — and remember you give up automatic rewrite, so queries have to reference the MV by name.

No analytic / window functions. ROW_NUMBER(), LAG(), LEAD(), RANK(), and the rest of the window-function family are blocked. The reason: windows depend on row ordering, and the delta-application model doesn’t have a single “next row” to reason about. If the projection you want is “top N per group”, you can’t build it as an MV. Pre-aggregate to a smaller table with a scheduled query and read from that.

No LIMIT and no ORDER BY. Same root cause as window functions — the result depends on row ordering that the delta-application model can’t preserve incrementally.

Non-deterministic functions. CURRENT_TIMESTAMP(), CURRENT_DATE(), SESSION_USER(), RAND(), and other functions whose value depends on when (or where) they’re evaluated are all blocked from incremental MVs. The most common surprise is using CURRENT_DATE() in a WHERE clause to filter to “last 7 days” — the MV definition would change meaning every time it’s refreshed, so the planner refuses to build an incremental MV for it. The fix is to compute the filter relative to a column in the base table (WHERE event_date >= DATE_SUB(MAX(event_date) OVER (), INTERVAL 7 DAY) won’t work either because windows are blocked) or to filter at query time and let the MV cover the whole history.

One base table per MV. Incremental MVs read from a single base table. Joins to dimension tables are technically supported but only as broadcast joins to small static tables, and changes to the dimension table won’t trigger MV refreshes — you need to manage that with enable_refresh = false and explicit refreshes after dimension changes.

No nested or repeated columns in the GROUP BY. If your base table has a STRUCT or ARRAY column you want to group by, you need to flatten it into scalar columns in the projection first. The MV can produce nested output, but its grouping has to be over scalar values.

No CREATE MATERIALIZED VIEW … OR REPLACE. Changing the defining query of an existing MV requires DROP plus CREATE plus a full rebuild — there’s no incremental ALTER for the query shape. This is the largest difference from Postgres MVs in practice; rebuilds on a 10 TB base table aren’t cheap. The pattern is to plan for MV definitions that are stable for months at a time and accept the rebuild when the schema changes underneath.

INFORMATION_SCHEMA for MVs

Three INFORMATION_SCHEMA views, plus JOBS_BY_PROJECT, give you the production observability you need on MVs. Note there is no MATERIALIZED_VIEW_REFRESH_HISTORY view — BigQuery exposes current-state fields and the refresh jobs, not a dedicated refresh-history table.

INFORMATION_SCHEMA.MATERIALIZED_VIEWS lists every MV with its last_refresh_time, refresh_watermark, and last_refresh_status. A non-NULL last_refresh_status means the last automatic refresh failed. This is the health-check query:

SELECT
  table_schema,
  table_name,
  last_refresh_time,
  refresh_watermark,
  last_refresh_status      -- NULL = last refresh OK; non-NULL = it failed
FROM `region-us`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE table_schema = 'analytics'
ORDER BY last_refresh_time DESC;

The refresh options (enable_refresh, refresh_interval_minutes, max_staleness) are not on this view — they live in INFORMATION_SCHEMA.TABLE_OPTIONS as option_name/option_value rows (or in the table resource from bq show / the DDL).

For refresh cost and history, query INFORMATION_SCHEMA.JOBS_BY_PROJECT for jobs whose job_id starts with materialized_view_refresh: total_bytes_processed per refresh tells you whether refreshes are incremental (small deltas) or full (≈ base-table size), and error_result flags failures. That is the closest thing to a refresh-history view.

INFORMATION_SCHEMA.JOBS_BY_PROJECT — already mentioned for query rewrite detection — exposes materialized_view_statistics per query, including which MVs were considered, which were rejected, and why. The “considered but rejected” data is the most useful debugging signal when you expected a rewrite to fire and it didn’t: it tells you whether the MV was rejected for staleness, missing columns, or an unsupported operator.

INFORMATION_SCHEMA.TABLE_STORAGE reports the MV’s storage bytes (the MV appears as a row of type MATERIALIZED_VIEW). Useful for the storage-cost half of the ROI calculation.

A pattern that works well: a daily scheduled query that joins refresh history, table storage, and a sample of jobs that used (or could have used) each MV, and writes a per-MV summary row into an mv_health table. The summary row has columns for “refresh type”, “refresh cost (24h)”, “storage cost (current)”, “queries served (24h)”, and “estimated query savings (24h)”. The MVs whose savings are below their cost get flagged in the next sprint review.

Materialized view vs scheduled query vs BI Engine

The most common architectural question that comes up with MVs is “should this be an MV, a scheduled query writing into a normal table, or do I just need BI Engine?”

A scheduled query is the right answer when the projection involves SQL constructs MVs can’t handle: outer joins, window functions, top-N-per-group, multi-base-table aggregations, or anything that needs to fan out across more than one source table. A scheduled query also gives you full control over the output schema, can write into a partitioned + clustered table that the downstream queries can use exactly like a hand-built fact table, and is easy to debug because it’s just SQL. The downside is that the output table doesn’t participate in smart-tuning rewrite — queries that could use it have to mention it by name — and you’re responsible for the freshness contract (every team has at one point shipped a stale dashboard because the scheduled query was failing silently for three days).

An MV is the right answer when the projection fits within the supported SQL surface, the base table is large enough that the refresh-vs-query-savings ratio works out, and you want smart tuning to apply the projection to queries that reference the base table directly. The biggest practical wins are on aggregation-heavy dashboards over partitioned fact tables where every dashboard panel computes a SUM or COUNT over a billion rows; the MV cuts those queries’ scan cost by 95-99% with zero changes to the dashboard SQL.

BI Engine is a different layer: an in-memory cache for query results, billed by reserved capacity. It’s complementary to MVs, not a substitute. The combination — MVs for the projection, BI Engine for the in-memory cache of MV reads — gives you both the scan-bytes reduction (MVs) and the sub-100ms dashboard latency (BI Engine) with no code changes. The right time to add BI Engine is after MVs have already cut the scan cost; adding it first usually means you’re caching scans that didn’t need to happen.

The decision rule that’s worked in practice: if the projection’s SQL fits within the incremental MV surface and the base table is over ~100 GB, start with an MV. If you need anything MVs can’t express, write a scheduled query. Layer BI Engine on top only when the queries are already cheap and you need them fast.

Common pitfalls

Refresh storms after a bulk load. When the base table receives a write that touches many partitions (a backfill, a partition-replace, a bulk import), the MV’s affected-partitions list explodes and the next refresh becomes large. On a low max_staleness setting, this can also cause synchronous refresh delays for in-flight queries. The pattern is to either temporarily set enable_refresh = false before the backfill and re-enable afterwards, or to spread the backfill into smaller chunks that the refresh job can absorb one at a time.

MV drift after a base-table schema change. Adding a column to the base table is fine — the MV keeps working and ignores the new column. Dropping a column or changing its type that the MV references invalidates the MV and refreshes start to fail with a schema-mismatch error. The fix is to drop and recreate the MV with the updated schema — and to put a check in your migration tooling so that any column changes to a table with dependent MVs trigger an MV refresh-validation step.

Smart tuning silently choosing the base table. The most common “the MV isn’t working” issue: a query that should match the MV’s shape ends up reading the base table because of a small detail. The detail is usually one of: a column referenced in the query that isn’t in the MV’s projection (planner can’t answer it from the MV), a COUNT(DISTINCT) on a column the MV stored only the count for (not re-aggregatable), or a join with a dimension table whose freshness exceeds the MV’s staleness window. The debugging path is INFORMATION_SCHEMA.JOBS_BY_PROJECT.materialized_view_statistics on the offending query — the rejection reason is always there.

max_staleness set too aggressively. A max_staleness = INTERVAL "0" MINUTE MV forces the planner to read the base-table delta on every query. On a write-heavy base table that’s busy enough for the delta to be material, this can mean the MV is barely helping at all — every query reads the MV plus a substantial chunk of recent base-table partition. Bumping max_staleness to 15-30 minutes typically restores most of the benefit.

Forgetting to clean up unused MVs. MVs continue to consume storage and refresh budget even if nothing queries them. A scheduled query against INFORMATION_SCHEMA.JOBS_BY_PROJECT — joining the materialized_view_refresh% jobs (refresh cost) with materialized_view_statistics from query jobs (which MVs actually served reads) — can produce a “used in last 30 days” report; MVs that haven’t been hit by a query in that window are usually safe to drop.

Using CREATE MATERIALIZED VIEW IF NOT EXISTS in a deployment pipeline. It does what it says — if the MV already exists with a different defining query, it doesn’t update it. The MV silently diverges from the SQL in your repo. The pattern is to either use DROP MATERIALIZED VIEW IF EXISTS followed by CREATE MATERIALIZED VIEW, accepting the rebuild cost on every deploy, or to detect schema mismatches in CI and fail the deploy until someone manually replaces the MV.

Region mismatches with INFORMATION_SCHEMA. Every INFORMATION_SCHEMA view is region-scoped (region-us, region-eu, etc.). A monitoring query written for region-us won’t see MVs in region-eu and will silently return nothing for them. For multi-region projects, the monitoring infrastructure has to iterate over regions explicitly.

Wrapping up

BigQuery materialized views are one of the highest-leverage features in the warehouse, but only if the defining query lands inside the incremental-refresh sweet spot and the MV’s partitioning aligns with the base table’s. Get those two right and dashboards that were scanning terabytes drop to gigabytes with no code changes; get them wrong and the refresh bill silently exceeds whatever the MV was saving.

The patterns that show up across production rollouts — building MVs only over single base tables with incremental-friendly aggregations, always projecting the partition column so the MV inherits partitioning, keeping max_staleness at 15-30 minutes for most analytics workloads, watching the materialized_view_refresh% jobs in INFORMATION_SCHEMA.JOBS_BY_PROJECT for refreshes that scan the whole base table, and treating MVs over external Iceberg / BigLake tables as a separate (and increasingly mature) capability — are stable enough to bake into a team standard. The riskiest moment is the day you ship a new MV; that’s when you want a CI gate against allow_non_incremental_definition and the first-24-hours of total_bytes_processed monitoring.

If you’re inspecting BigQuery materialized views, debugging which dashboard queries are eligible for rewrite, or building internal tools and dashboards on top of BigQuery, QueryPlane is a SQL editor and app builder that connects to your BigQuery project and lets you build interactive apps over those tables — including dashboards that surface MV refresh-job cost and per-MV usage stats across your warehouse without writing your own observability layer. For a broader BigQuery GUI comparison, our roundup of the best BigQuery GUI tools covers the alternatives. For the upstream partitioning + clustering decisions that determine whether an MV will even be incrementally refreshable, the BigQuery Partitioning and Clustering in Practice post is the prerequisite. And for the broader cost-and-performance picture — slot sizing, query plans, the INFORMATION_SCHEMA.JOBS tour — the BigQuery Query and Cost Optimization in Practice post covers the rest of the surface area.

Frequently asked questions

What is a materialized view in BigQuery? A materialized view (MV) is a managed, pre-computed projection over a base table. It stores the result of a SELECT in a managed BigQuery table that’s kept incrementally in sync with the base table, and the planner automatically rewrites queries against the base table to read from the MV when the shapes match.

How does smart tuning work? Smart tuning is BigQuery’s automatic query-rewrite feature. When you run a query that the planner can prove is answerable from an MV (with possible re-aggregation), it rewrites the query to read from the MV instead of the base table, dropping scan bytes by 95-99% on aggregation-heavy queries. The application code never references the MV.

What’s the difference between an incremental and a non-incremental refresh? Incremental refresh applies just the base-table delta to the MV — the partitions that changed get their corresponding MV partitions updated. Non-incremental refresh recomputes the entire MV on every refresh. Whether you get one or the other depends entirely on the SQL constructs in the defining query: aggregations like SUM, COUNT, MIN, MAX, and AVG are incremental-friendly; window functions, RIGHT/FULL OUTER JOIN, ORDER BY, LIMIT, and non-deterministic functions force non-incremental (a LEFT OUTER JOIN or UNION ALL is allowed but disables smart-tuning rewrite).

Does the MV’s partitioning have to match the base table? Strongly recommended. If the MV’s GROUP BY includes the base table’s partitioning column, the MV is partitioned the same way and refreshes per-partition. If you drop the partitioning column, the MV becomes unpartitioned and every base-table write triggers a refresh of the whole MV — which is the most expensive accident in the feature.

How does max_staleness interact with refresh? max_staleness controls how stale the data the planner will serve from the MV can be. If the MV’s last refresh is within max_staleness, queries read from the MV directly. If it’s older, the planner either forces a synchronous refresh of the affected partitions or falls back to the base table. A staleness of 15-60 minutes is the right default for most analytics workloads.

Can a materialized view join multiple base tables? Only in a limited form. An incremental MV reads from a single base table, optionally joined to small static dimension tables. Multi-base-table aggregations are not incrementally refreshable. If you need to project across two large fact tables, a scheduled query writing into a regular table is the better option.

What’s the difference between a materialized view and a scheduled query? A scheduled query is a SQL job that runs on a cron schedule and writes its output into a regular table. A materialized view is a managed projection that the planner automatically uses to rewrite queries against the base table. Scheduled queries can express anything in SQL but require you to mention them by name; MVs are constrained to a subset of SQL but apply automatically. The decision rule: if the projection’s SQL fits the MV surface, prefer MV; if not, scheduled query.

Can I create materialized views over external (BigLake / Iceberg) tables? Yes — this is the maturing-fast feature across 2024-2026. MVs over BigLake or Iceberg tables are stored in BigQuery managed storage and use the external table’s snapshot or file metadata as the freshness watermark. The pattern is to leave the raw data in Cloud Storage as Iceberg / Parquet (shareable with other tools), expose it through BigLake, and create MVs for the queries that dominate the BI workload.

Why isn’t my query being rewritten to use the MV? Almost always one of: a column in your query isn’t in the MV’s projection (planner can’t answer the query from the MV), a COUNT(DISTINCT) that the MV stored only as a count (not re-aggregatable), a max_staleness = 0 setting that forces base-table reads, or a join the MV doesn’t cover. INFORMATION_SCHEMA.JOBS_BY_PROJECT.materialized_view_statistics for the offending query lists the rejection reason.

How do I detect a non-incremental MV in production? There is no refresh_type flag and no MATERIALIZED_VIEW_REFRESH_HISTORY view. You know an MV is non-incremental if it was created with allow_non_incremental_definition = true. To catch it from telemetry, look at the automatic refresh jobs in INFORMATION_SCHEMA.JOBS_BY_PROJECT (job_id starting with materialized_view_refresh): if total_bytes_processed is close to the full base-table size on every refresh, the MV is doing full recomputes. A CI gate that flags new MVs using allow_non_incremental_definition catches it at create time.

How do I change the defining query of an existing MV? You can’t. CREATE MATERIALIZED VIEW doesn’t support OR REPLACE for the defining query, only for the options. To change the SQL, you have to DROP MATERIALIZED VIEW and recreate it, which triggers a full rebuild. Plan for MV definitions to be stable; rebuild costs aren’t trivial on large base tables.

When should I use BI Engine instead of (or with) materialized views? BI Engine is an in-memory cache for query results; MVs are pre-computed projections. They’re complementary. The typical pattern is to add MVs first to reduce scan bytes, then layer BI Engine on top for sub-100ms latency on the MV reads. Adding BI Engine before MVs usually means you’re caching scans that didn’t need to happen.