GCP Professional Data Engineer Practice Question

A fintech company ingests about 20 GB of new transaction records into a 5 TB BigQuery table each day. The table is partitioned by an ingestion_date column. Their Looker Studio dashboards repeatedly run the same ad-hoc SQL that aggregates total transaction_amount by customer_id and transaction_date, usually filtered to the last 90 days. Analysts complain that charts load slowly, and the BI team wants an acceleration method that (1) requires no changes to existing Looker queries, (2) keeps results less than five minutes behind new ingests, and (3) minimizes additional cost. Which approach best meets these requirements?

Export the last 90 days of data to Cloud Storage each night, define an external table over the exported files, and point Looker dashboards to that external table.
Enable BigQuery BI Engine with a memory reservation large enough to hold the most recent 90 days of data; let Looker dashboards automatically benefit from the in-memory cache.
Schedule a query every four hours to write the 90-day customer-level aggregates into a new table, then update all Looker reports to use this table instead of the original.
Create a partitioned materialized view that groups by customer_id and transaction_date, turn on automatic refresh, and rely on BigQuery's query-rewrite to make Looker dashboards hit the view instead of the base table.

Report Issue

Answer Description

Creating a partitioned materialized view that pre-aggregates by customer_id and transaction_date satisfies all three constraints. BigQuery materialized views cache the results of a defined aggregation query and are incrementally and automatically refreshed within minutes as new partitions are appended, so the data stays nearly real-time. Because the BigQuery optimizer can transparently rewrite compatible queries to use the materialized view, no changes are needed in Looker. Storage charges apply only to the materialized results (much smaller than the base table), and there is no extra in-memory reservation fee.

Enabling BI Engine could improve performance without query changes, but to cover 90 days of data the company would need a large in-memory reservation, increasing cost and possibly exceeding regional memory limits. Exporting to Cloud Storage and querying an external table would still force full scans because external tables do not support materialized views or in-memory caching. Scheduling a batch job to write an aggregate table would work, but it would either introduce up to four hours of staleness or require complex orchestration and dashboard query changes to use the new table.

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.