GCP Professional Data Engineer Practice Question

Your company ingests millions of clickstream rows per hour into a partitioned, clustered BigQuery table that now holds more than 10 billion records. A Looker Studio dashboard repeatedly issues SUM and COUNT aggregations grouped by event_date and campaign_id. Although partitioning reduces some I/O, the dashboard still scans terabytes each day and regularly exceeds the cost budget. You must accelerate the dashboard while:

keeping data no more than a few minutes behind the source
avoiding any changes to existing dashboard SQL
minimizing repeated full-table scans and overall cost
Which BigQuery feature should you implement?

Create a materialized view that pre-aggregates metrics by event_date and campaign_id and let BigQuery automatically rewrite dashboard queries to use it.
Rely on BigQuery's query results cache and configure the dashboard to always return cached results when available.
Enable BigQuery BI Engine with on-demand capacity so that frequently accessed data is cached in memory.
Schedule a batch job that writes daily aggregate tables and point the dashboard at the new tables each morning.

Report Issue

Answer Description

A materialized view satisfies all stated requirements. It stores a pre-computed, pre-aggregated result set and is incrementally refreshed when new rows arrive, so data is typically only minutes behind the base table. Because BigQuery's optimizer can transparently rewrite incoming queries to use a materialized view, the Looker Studio SQL does not need to change. Subsequent dashboard queries scan only the much smaller materialized view, sharply reducing I/O and cost.
BI Engine can accelerate many interactive queries, but it still scans the base table for uncached data and does not pre-aggregate it. The standard query results cache serves only identical queries and is cleared after 24 hours; it cannot satisfy similar but not identical dashboard queries. A daily scheduled aggregate table would meet cost goals but would not refresh within minutes, violating the freshness requirement.

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.