GCP Professional Data Engineer Practice Question

A streaming pipeline writes 2 TB of structured click-stream data into BigQuery each day. Analysts run interactive dashboards that usually filter by event_date (last 60 days) and customer_id, and occasionally also filter by country. Query latency must stay under 5 seconds while storage and analysis costs should be minimized. Which table design best satisfies these requirements?

Create an unpartitioned table and add clustering on event_date, customer_id, and country
Use ingestion-time partitioning and rely on materialized views to accelerate the dashboards
Partition by customer_id and cluster by event_date
Partition by event_date and cluster by customer_id, with an optional secondary cluster column on country

Report Issue

Answer Description

Partitioning the table on event_date limits each query to 60 daily partitions instead of the full table, drastically reducing bytes scanned. Clustering on customer_id (and optionally country) further co-locates rows with similar keys, so filter predicates eliminate additional blocks inside the selected partitions. This combination yields the lowest scan cost and fastest performance. Partitioning on customer_id is impractical because of the very high cardinality, and clustering alone or ingestion-time partitioning would still require full-table scans across many blocks.

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.