Optimize Bi Engine Reservation Materialized Views

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

A business intelligence team uses Looker Studio to query a 600 GB partitioned and clustered sales dataset stored in BigQuery. Users report that interactive dashboard filters on recent summary metrics (last 30 days, totaling ~20 GB) perform with sub-second response times, but ad-hoc multi-year queries spanning the full 600 GB dataset experience significant latency due to queries partially falling back to standard BigQuery slot execution.

You have a 30 GB BigQuery BI Engine reservation provisioned in the project. You need to ensure consistent, sub-second query performance across both dashboard filters and broader historical aggregation queries while minimizing BI Engine reservation costs.

What should you do?

Export the dataset to Cloud Bigtable and query it from Looker Studio using BigQuery federated external tables

Create partitioned materialized views that pre-aggregate historical sales metrics, allowing BI Engine to accelerate queries via smart tuning within the 30 GB memory reservation

Disable BI Engine reservation and configure BigQuery scheduled queries to write hourly results to separate destination tables

Increase the BI Engine reservation capacity to 600 GB to fit the entire uncompressed sales dataset in memory

Professional Cloud Data Engineer

Optimize Bi Engine Reservation Materialized Views

Related questions