GCP Professional Data Engineer Practice Question

A 5-TB BigQuery table stores raw sales transactions. A Looker Studio dashboard refreshes every hour by running an unchanged SQL query that calculates total revenue per product category for the last seven days. Each refresh now scans the full table, costs several dollars, and takes about 15 seconds. You need to bring query latency under two seconds and cut per-refresh cost, while requiring little or no modification to the existing dashboard query. Which solution best meets these goals?

Partition the sales table by date and cluster it on product category, then instruct report authors to include the date filter so only the last seven days are scanned.
Purchase a 20 GB BigQuery BI Engine reservation so the dashboard queries are cached in memory without adjusting the underlying data model.
Nightly export the most recent seven days of transactions to Cloud Bigtable, create an external table on top of it, and repoint the dashboard to query Bigtable instead of BigQuery.
Create a materialized view that pre-aggregates last-seven-day revenue by product category and rely on BigQuery's automatic query rewrite so the dashboard continues to run its existing SQL against the cached results.

Report Issue

Answer Description

A BigQuery materialized view can store a pre-aggregated result set (for example, revenue summed per product category for the rolling seven-day window). Because the materialized view is incrementally refreshed, each dashboard run touches only the small, cached result instead of the 5 TB base table, typically reducing both latency and the number of bytes billed. When a user query is semantically compatible, BigQuery's optimizer automatically rewrites it to read from the materialized view, so the existing dashboard SQL does not need to change. Simply partitioning and clustering the base table lowers scanned bytes but still requires processing raw records and forces SQL changes. Reserving BI Engine memory may speed execution but does not eliminate full-table scan costs and might exceed the memory budget. Exporting data to Cloud Bigtable adds complexity, requires query rewrites, and provides no built-in aggregation benefits for SQL dashboards.

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.