Introduction
As organizations handle ever-growing volumes of data, optimizing query performance and managing costs becomes crucial. Big Query’s materialized views offer a powerful way to boost efficiency by precomputing and storing query results, reducing the need for repeated execution of complex SQL. This guide is tailored for beginners to understand how materialized views work, how to create them, and how to verify their output in BigQuery.
What is a Materialized View?
A materialized view in BigQuery is a precomputed query result stored for fast access. Unlike a standard view, which recalculates data on every query, a materialized view stores the output and updates it incrementally as the underlying data changes. This leads to faster response times and reduced processing costs.
Benefits of Materialized Views
- Faster query execution
- Reduced cost due to fewer data scans
- Automatic refresh when base tables are updated
- Seamless integration into existing BI tools
When Should You Use Materialized Views?
Materialized views are best used in scenarios such as:
- Dashboards that run the same aggregation queries repeatedly.
- Reporting pipelines with repetitive filters and joins.
- Large datasets requiring time-based summaries.
- Incremental update pipelines that benefit from precomputed results.
Creating and Querying Materialized Views Step-by-Step
Step 1: Prepare Sample Data in BigQuery
Before creating a materialized view, create a base table named transactions in BigQuery with the following schema, data and commands:
Then we have a transaction table with below data
Step 2: Create a Materialized View
So here we are creating aggregate daily revenue from the transactions table view run the following query in the BigQuery SQL editor:
Create SQL:-
CREATE MATERIALIZED VIEW ` randomtrees-462004.sales_data.daily_revenue_mv` AS SELECT DATE(transaction_time) AS transaction_date, SUM(amount) AS total_revenue FROM `randomtrees-462004.sales_data.transactions` GROUP BY transaction_date;
Once click the go to materialized view it is look like below
In details section it will shows details like below
And, in bottom of details section It will shows the query which we are used of creating the materialized view.
Step 3: Query the Materialized View
Once the materialized view is created, you can retrieve records from it just like a regular table:
Step 4: Verify Automatic Refresh
BigQuery automatically refreshes materialized views when the base table (transactions) is updated. For example, if a new transaction is added:
SQL:-
INSERT INTO `randomtrees-462004.sales_data.transactions`
VALUES (4, ‘2025-05-01 14:45:00 UTC’, 50.25, 104);
The materialized view will reflect this change within 30 minutes. To manually refresh the view, use:
SQL:-
CALL BQ.REFRESH_MATERIALIZED_VIEW(randomtrees-462004.sales_data.daily_revenue_mv);
After refresh we can observe in below screen shot latest refresh time is current time
GCP Looker Studio Dashboard: Visualizing BigQuery View Data
Objective:
Build a dashboard in Looker Studio using the ‘daily_revenue_mv’ BigQuery materialized view to monitor daily revenue metrics.
Prerequisites:
- A Google Cloud Platform (GCP) account with access to BigQuery.
- ‘daily_revenue_mv’ view already created in a dataset.
- Access to Looker Studio: https://lookerstudio.google.com
- BigQuery billing enabled.
Step-by-Step Instructions
Step 1: Open Looker Studio
Go to: https://lookerstudio.google.com
Click “+ Blank Report”.
Step 2: Connect to BigQuery
Click on the use my own data
Select BigQuery as the connector.
Click “Authorize” to give Looker Studio access to your BigQuery project.
Navigate to your dataset and select the view ‘daily_revenue_mv‘.
Click Add to Report”.
Step 3: Add Visualizations
Click “Add a chart” and choose from:
– Time Series Chart
– Bar Chart
– Scorecard
Step 4: Format the Dashboard
Make it presentable:
– Rename charts and fields for clarity.
– Use the ‘Text’ tool to add titles.
– Apply layout adjustments using Freeform or Responsive modes.
– Optionally, add a logo or branding elements.
Step 5: Share the Dashboard
When ready:
– Click “Share” > “Invite people” or generate a shareable link.
– You can also embed the report in a website or internal dashboard.
Limitations
- Only SELECT queries supported
- Must use deterministic functions
- No support for window functions or subqueries
- No DML operations allowed on materialized views
- One base table per MV
2025 Enhancements to BigQuery Materialized Views
BigQuery has introduced several noteworthy enhancements to materialized views in 2025, building upon the solid foundation established in 2024. These updates add more flexibility, performance boosts, and better observability.
- Materialized View Replicas (GA)
BigQuery now supports materialized view replicas. This helps distribute query load and improve availability across regions. It’s especially beneficial for global analytics workloads.
- Iceberg-Compatible Materialized Views
To align with BigLake and Iceberg table formats, BigQuery introduced materialized views compatible with Iceberg tables. This is crucial for customers to modernize their data lakes while maintaining fast access.
- INFORMATION_SCHEMA Monitoring
You can now monitor materialized views through INFORMATION_SCHEMA views. This includes metadata such as last refresh time, staleness, and refresh status — allowing for proactive maintenance and optimization.
- Enhanced Client Library Parameters
The BigQuery client libraries now expose parameters like allowNonIncrementalDefinition and maxStaleness programmatically. This is useful for automating materialized view creation with customized trade-offs.
- Recommender for Materialized Views (Preview)
BigQuery now features a recommender system that analyzes your query patterns and suggests optimal materialized views to improve performance and reduce costs.
Best Practices
- Use for aggregation-heavy queries with stable patterns
- Pair with partitioned and clustered base tables
- Monitor costs and performance over time
- Use INFORMATION_SCHEMA to track metadata and usage
- Validate results using EXPLAIN or dry-run options
Conclusion
Materialized views provide a simple yet effective way to optimize your BigQuery workloads. They improve query speed, lower costs, and simplify analytics pipelines. By following best practices and understanding their limitations, you can integrate them into your reporting and dashboard infrastructure to deliver timely insights with minimal overhead.