GCP Professional Data Engineer Practice Question

Your organization maintains a 4-TB BigQuery fact table that records billions of daily clickstream events. Dashboards in Looker Studio always filter on the event_date column and often add a WHERE customer_id = ? predicate. During interactive analysis the BI Engine reservation frequently runs out of memory, causing queries to spill to slot-based execution and slow down. Which table design change is most likely to improve BI Engine efficiency without increasing the reservation size?

Disable clustering and switch to ingestion-time partitioning without changing the schema.
Partition the table by event_date and cluster it by customer_id.
Partition the table by customer_id and cluster it by event_timestamp.
Leave the table unpartitioned and cluster it only by event_timestamp.

Report Issue

Answer Description

BI Engine only loads the partitions and column chunks that are needed in memory. When a table is partitioned on the mandatory date filter, the engine can prune entire partitions that fall outside the selected date range, sharply reducing the data that must be held in memory. Clustering the remaining rows on customer_id further narrows the blocks that BI Engine reads when the dashboard applies that predicate. Partitioning on a high-cardinality field such as customer_id would create millions of tiny partitions and add overhead, while clustering or using ingestion-time partitions without an appropriate date partition provides far less pruning. Therefore, partitioning by event_date and clustering by customer_id offers the greatest memory savings and performance benefit without enlarging the BI Engine 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.