GCP Professional Data Engineer Practice Question

Your analytics team has built an executive dashboard in Looker Studio that issues complex SQL queries against a 5-TB BigQuery fact table that is both date-partitioned and clustered by customer_id. Query results are currently returned in 8-10 seconds, which stakeholders find too slow. You need to reduce latency to under 2 seconds while avoiding any changes to the existing Looker Studio reports or their underlying SQL. Which approach best meets these requirements?

Create a BI Engine in-memory reservation for the project and allow Looker Studio to use it to transparently accelerate the existing queries.
Build materialized views for each dashboard widget and rely on them to accelerate the underlying queries.
Migrate the fact table to Bigtable and query it through a BigQuery external connection to leverage Bigtable's low-latency reads.
Ask analysts to add the query hint "--cache=true" to every Looker Studio SQL block so dashboards always hit the BigQuery query cache.

Report Issue

Answer Description

Creating a BigQuery BI Engine reservation allocates dedicated in-memory capacity that the BigQuery engine uses to cache and process frequently accessed data. Because BI Engine operates underneath the BigQuery API, tools such as Looker Studio automatically benefit from this acceleration without any modifications to dashboards or SQL. Migrating to Bigtable would require redesigning the data model and introducing an external table, adding complexity and breaking native SQL compatibility. Forcing the BigQuery result cache with manual hints obliges analysts to edit every query and still depends on the cache already being warm. Materialized views can accelerate some aggregate queries, but you would have to create and manage a set of views that exactly match (or are automatically matched to) each dashboard pattern, and parameterized filters could prevent reuse-so they do not provide the same drop-in, broad performance improvement as BI Engine.

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.