GCP Professional Data Engineer Practice Question

Your team stores 2 TB of clickstream data in a BigQuery table partitioned on the event_date column. Analysts frequently query the most recent 90 days for a single user_id, yet each query still scans entire daily partitions, driving up on-demand query costs. You need to reduce the bytes read without changing existing SQL, scheduling extra maintenance jobs, or incurring additional storage cost. Which approach best meets the requirement?

Add a clustering specification on user_id for the existing partitioned table.
Export the last 90 days of data to Cloud Storage and query it through external tables.
Create materialized daily tables per user_id and have analysts query only their specific table.
Re-partition the table using an integer-range partition on user_id instead of event_date.

Report Issue

Answer Description

Adding a clustering specification on user_id reorganizes the data inside every event_date partition so that rows with similar user_id values reside in the same data blocks. When a query includes a filter on user_id, BigQuery can prune blocks that do not match, dramatically lowering bytes scanned and therefore cost. Clustering has no extra storage charge and is maintained automatically, so it satisfies the constraints.

Using integer-range partitioning on user_id would require rebuilding the table, rewriting queries, and could create many thousands of partitions that exceed limits. Creating user-specific tables or exporting to Cloud Storage both add operational overhead and extra storage cost while not guaranteeing lower scanned bytes for existing queries.

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.