GCP Professional Data Engineer Practice Question

A media company stores 5 TB of clickstream events in a date-partitioned, user_id and campaign_id-clustered BigQuery table. Analysts explore the data with Looker Studio dashboards that join this fact table to two small dimension tables and apply flexible filters such as "last 7 / 30 / 90 days," specific campaigns, and ad formats. Each dashboard widget currently takes about 8-10 seconds to render. You need to achieve sub-second latency without changing existing SQL or rebuilding the dashboards, while keeping incremental cost low and remaining entirely within BigQuery. Which approach best meets these requirements?

Build a scheduled Dataflow job that writes daily aggregated click metrics to a new partitioned table and have analysts query it with table decorators.
Create a materialized view that pre-aggregates daily clicks by user_id and campaign_id, then modify dashboards to query the materialized view instead of the base table.
Purchase a BigQuery BI Engine reservation large enough to hold the most-queried partitions of the fact table and the two dimension tables, letting Looker Studio queries be automatically accelerated in memory.
Export the fact table to Cloud Storage, load it into Cloud Bigtable, and connect Looker Studio through a Bigtable connector for low-latency lookups.

Report Issue

Answer Description

BI Engine is designed to accelerate interactive BI workloads by caching frequently accessed table and join results in an in-memory layer inside BigQuery. It works transparently with any tool that uses the BigQuery API, including Looker Studio, so no report or query changes are required. You simply purchase a BI Engine capacity reservation sized to contain the hottest partitions of the fact table plus the small dimensions.

Materialized views or scheduled aggregate tables would require analysts to query different objects (or rely on optimizer rewrites that are limited when joins are involved) and add storage overhead while still forcing periodic recomputation. Exporting data to Dataproc moves the workload out of BigQuery, adds significant complexity, and does not guarantee sub-second latency for interactive BI. Authorized views control access, not performance, and offer no caching or acceleration. Therefore, enabling BI Engine with an appropriately sized reservation is the most effective and minimally invasive solution.

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.