Query Performance Optimization

Understand materialized views in BigQuery, a powerful feature for optimizing query performance and controlling costs. Materialized views store precomputed query results, making them ideal for dashboards and reports that run frequently but do not require real-time updates.

What Is a Materialized View

A materialized view stores the result of a query as a physical table:

• It refreshes automatically based on changes in the underlying data;
• Refresh intervals can be configured, such as every 60 minutes;
• Queries against a materialized view are faster and cheaper than querying the base table.

Why Use Materialized Views

Materialized views provide several key benefits:

• Improved performance by avoiding repeated computation;
• Lower query costs by reducing scanned data;
• Automatic query optimization, where BigQuery may transparently use the materialized view instead of the base table when possible.

Creating a Materialized View

Materialized views are created using CREATE MATERIALIZED VIEW and a standard SELECT statement. Refresh behavior is controlled through configuration options.

-- 1. Create a materialized view for sales analysis by user loyalty status
CREATE MATERIALIZED VIEW `codefinity-442016.codeFinity_test.mv_sales_by_loyalty` 
OPTIONS(
  enable_refresh = true,
  refresh_interval_minutes = 60
) AS
SELECT 
  up.loyalty_status,
  COUNT(DISTINCT ps.user_id) AS user_count,
  COUNT(ps.sale_id) AS transaction_count,
  SUM(ps.quantity) AS total_items_sold,
  SUM(ps.quantity * ps.sale_price) AS total_revenue,
  AVG(ps.sale_price) AS avg_price_per_unit,
  MAX(PARSE_DATE('%Y-%m-%d', ps.sale_date)) AS last_sale_date
FROM 
  `codefinity-442016.codeFinity_test.product_sales_section4` ps
JOIN 
  `codefinity-442016.codeFinity_test.user_profile` up
  ON ps.user_id = up.user_id
GROUP BY 
  up.loyalty_status;

-- 2. Query the materialized view for much faster results
SELECT 
  loyalty_status,
  user_count,
  transaction_count,
  total_revenue,
  total_revenue / transaction_count AS avg_transaction_value,
  total_revenue / user_count AS revenue_per_user
FROM 
  `codefinity-442016.codeFinity_test.mv_sales_by_loyalty`
ORDER BY 
  total_revenue DESC;

Limitations to Be Aware Of

Materialized views have important constraints:

• Certain stateful functions (like CURRENT_TIMESTAMP) are not allowed;
• Supported joins and aggregations are limited to deterministic patterns;
• The base tables must be in the same dataset as the materialized view.

These restrictions are important to consider during design to ensure the view remains eligible for automatic refresh.

Example

A dashboard that tracks weekly sales metrics can query a materialized view instead of the raw transaction table. This significantly improves load times while keeping data reasonably fresh.

Key Takeaway

Materialized views are an effective way to scale BigQuery workloads and manage costs. They are best suited for recurring queries, dashboards, and reporting scenarios where near-real-time updates are sufficient.