GCP Professional Data Engineer Practice Question

A retail analytics team is troubleshooting a Looker Studio dashboard that runs SQL against a 10-TB sales fact table in BigQuery. They have purchased a 20-GB BI Engine reservation, but most charts still take about 25 seconds to load. Query plans show full table scans even though analysts usually filter the dashboard to the last 90 days of data. The team wants faster, sub-second response times without rewriting any of the existing Looker SQL. Which action will let BI Engine accelerate the dashboard with the least additional effort?

Increase the project-wide shuffle parallelism setting so query stages finish faster when BI Engine is used.
Create materialized views for the last 90 days of sales data because BI Engine can accelerate only materialized views.
Export historical partitions to Bigtable and leave only the most recent 90 days in BigQuery so the table fits entirely in the BI Engine cache.
Partition the sales table by transaction_date and cluster it on frequently filtered dimensions so BI Engine only caches the relevant partitions.

Report Issue

Answer Description

BI Engine keeps frequently accessed columns in an in-memory cache, but it can only load as much data as the reservation allows. When a table is partitioned and clustered, the BigQuery optimizer prunes unneeded partitions and blocks before BI Engine is invoked. That selective pruning means only the partitions that match the 90-day filter have to be loaded into the 20-GB cache, giving BI Engine a dataset small enough for sub-second queries without changing the SQL generated by Looker Studio.

Creating materialized views could help, but it would require new objects and maintenance; BI Engine is not limited to materialized views. Moving data to Bigtable would break existing queries, and tuning shuffle parallelism affects execution, not BI Engine's in-memory cache.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.