How to Create Materialized Views in BigQuery

How do I create and refresh a materialized view in BigQuery?

CREATE MATERIALIZED VIEW stores pre-computed query results in BigQuery, giving sub-second reads while Google handles automatic, incremental refreshes.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why choose a materialized view over a standard view?

Materialized views persist query results on disk, so downstream dashboards read from stored data instead of re-running the base query. You gain faster response times and lower on-demand query costs, at the price of extra storage.

What is the basic syntax?

Use CREATE MATERIALIZED VIEW followed by optional refresh options and the query definition. BigQuery refreshes incrementally whenever base tables change, or on the schedule you set.

How do I create a materialized view that aggregates orders by customer?

The example below pre-computes each customer’s lifetime spend, perfect for customer-profile APIs and embedded analytics.

CREATE MATERIALIZED VIEW IF NOT EXISTS ecommerce.mv_customer_spend OPTIONS ( enable_refresh = TRUE, refresh_interval_minutes = 60 ) AS SELECT c.id AS customer_id, c.name AS customer_name, SUM(o.total_amount) AS lifetime_value, MAX(o.order_date) AS last_order FROM `ecommerce.Customers` c JOIN `ecommerce.Orders` o ON o.customer_id = c.id GROUP BY customer_id, customer_name;

How often is the data refreshed?

By default, BigQuery performs automatic incremental refreshes every 30 minutes when new data arrives. Override with refresh_interval_minutes or disable automatic refresh and trigger ALTER MATERIALIZED VIEW ... REFRESH manually.

Can I add filters or parameters?

Yes. Use deterministic expressions only. Non-deterministic functions such as RAND() or CURRENT_TIMESTAMP() are not allowed in a materialized view definition.

Best practice: store in a dedicated dataset

Keep materialized views in a mv_* or derived dataset to separate them from raw tables. This simplifies permissioning and cost tracking.

Best practice: monitor freshness and usage

Expose INFORMATION_SCHEMA.MATERIALIZED_VIEWS for ops dashboards or set up Cloud Monitoring alerts on bigquery.googleapis.com/storage_bytes and query/avg_latency.

How do I force a refresh on demand?

ALTER MATERIALIZED VIEW ecommerce.mv_customer_spend REFRESH;

Use this after backfilling historical data to make the view immediately consistent.

What limitations should I know?

Materialized views cannot include ORDER BY, LIMIT, HAVING, or subqueries in the SELECT list. The query must reference only a single project.

Why How to Create Materialized Views in BigQuery is important

How to Create Materialized Views in BigQuery Example Usage


-- Top-5 high value customers using the materialized view
SELECT customer_id, customer_name, lifetime_value
FROM   ecommerce.mv_customer_spend
ORDER  BY lifetime_value DESC
LIMIT  5;

How to Create Materialized Views in BigQuery Syntax


CREATE MATERIALIZED VIEW [IF NOT EXISTS] project_id.dataset_id.view_name
[OPTIONS (
    enable_refresh = { TRUE | FALSE },
    refresh_interval_minutes = integer,
    max_staleness = INTERVAL expr
)] AS
SELECT columns
FROM   project.dataset.table
[WHERE condition]
[GROUP BY columns];

-- Ecommerce example
gcloud bq query --use_legacy_sql=false """
CREATE MATERIALIZED VIEW IF NOT EXISTS ecommerce.mv_daily_product_sales
OPTIONS ( enable_refresh = TRUE, refresh_interval_minutes = 15 ) AS
SELECT
  oi.product_id,
  DATE(o.order_date) AS sales_day,
  SUM(oi.quantity)   AS units_sold,
  SUM(oi.quantity * p.price) AS revenue
FROM `ecommerce.OrderItems` oi
JOIN `ecommerce.Orders`     o ON o.id = oi.order_id
JOIN `ecommerce.Products`   p ON p.id = oi.product_id
GROUP BY product_id, sales_day;"""

Common Mistakes

Mistake: Including non-deterministic functions (e.g., CURRENT_TIMESTAMP) in the view definition. Why wrong: BigQuery can’t guarantee repeatable results for incremental refresh. Fix: Move such logic to the consuming query, not the materialized view.
Mistake: Forgetting to grant SELECT on base tables. Why wrong: Users can select the materialized view but get errors because underlying tables are blocked. Fix: Create authorized views or grant the same permissions on all referenced tables.