GCP Professional Data Engineer Practice Question

You are troubleshooting a Looker Studio dashboard backed by a BigQuery view. Execution details for the slowest query show: Stage 1 scans table sales_raw (5.2 TB), no partition pruning; a filter on event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() is present. Later stages repartition for joins and aggregate only 50 GB. The table is an unpartitioned, unclustered BigQuery managed table that receives daily appends. Without changing business logic, which action will most effectively lower both query latency and cost?

Convert the existing query into an unpartitioned materialized view that refreshes daily to avoid recomputation.
Purchase a large dedicated slot reservation to increase parallelism for all queries against the dataset.
Load sales_raw into a new daily partitioned table and cluster it by the frequently filtered region column, then update the view to read from this table.
Keep the table unchanged but force query result caching by adding the clause OPTIONS(use_cache=true).

Report Issue

Answer Description

Because the filter restricts rows to the last seven days, most of the 5 TB scan is wasted work. Converting sales_raw to a date-partitioned table lets BigQuery prune partitions so that only seven daily partitions are read. Clustering by region further reduces shuffle during aggregations, but even partitioning alone removes the dominant bottleneck, shrinking bytes scanned and slot-time consumed. Merely enabling query cache does not help when data changes daily; buying extra slots speeds execution only after the excessive scan is finished; creating an unpartitioned materialized view would still read the full underlying table, so cost and latency remain high.

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.