GCP Professional Data Engineer Practice Question

You manage a 4 TB BigQuery table that records click-stream events (about 500 million new rows per day). Business-intelligence dashboards built in Looker Studio always apply two mandatory filters: a date range selection (last 1-14 days) and the visitor's country_code. You plan to turn on BigQuery BI Engine with the default 10 GB memory reservation and want queries from the dashboards to be accelerated without increasing that reservation size. Which table redesign will best help BI Engine stay within the memory limit while still answering the dashboards quickly?

Create an unpartitioned materialized view that selects all event columns and point dashboards to the view.
Move the data to an external Parquet table on Cloud Storage and query it through BigQuery.
Keep the table as-is but disable partition pruning so BI Engine caches the entire dataset up front.
Partition the table by DATE(event_timestamp) and cluster it by country_code so queries can prune both dimensions.

Report Issue

Answer Description

BI Engine works by caching columnar blocks of data in memory. When a query includes filters that match the table's partitioning or clustering keys, BigQuery can prune the scan to only the needed partitions or clusters, so BI Engine loads far less data. Partitioning the events table by event date lets the engine ignore all but the partitions that fall in the user-selected date range, and clustering by country_code allows additional pruning on the clustered blocks that match the chosen country. This combination minimizes the in-memory footprint and keeps the workload inside the 10 GB reservation.

The other choices do not help BI Engine's memory efficiency:

A materialized view that contains the full, unpartitioned dataset still requires BI Engine to cache all data.
Converting the table to an external Parquet table disables BI Engine acceleration because only native BigQuery storage is supported.
Disabling partition pruning forces BI Engine to cache the whole 4 TB table, quickly exceeding the reservation.

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.