GCP Professional Data Engineer Practice Question 3: Partition Cluster Query Cost
Google Cloud Professional Data Engineer · Test 1 · question 3 of 10
An events table has grown to 40 TB in a single unpartitioned BigQuery table with an event_ts TIMESTAMP column and a high-cardinality customer_id column. The project uses on-demand pricing, and the bill is dominated by bytes billed. Analysts almost always restrict their queries to the last seven days and to one customer, but queries that omit the date predicate scan the whole table and have twice caused billing spikes.
Which change reduces the bytes billed the most while preventing accidental full-table scans?
Show answer and explanation
Answer:
Daily partitioning on event_ts lets BigQuery prune: a query filtering on the partitioning column scans only the matching partitions, so a seven-day query reads only those seven days of partitions instead of all 40 TB. Clustering by customer_id sorts the data inside each partition into blocks, so the per-customer predicate then eliminates most blocks within those partitions; the documented pattern is to combine partitioning with clustering for fine-grained pruning. require_partition_filter closes the remaining hole by rejecting any query that does not filter on the partitioning column, which is what stops the runaway scans.
Option B gets part of the benefit but none of the guarantee. Block pruning from clustering is best-effort and cannot be enforced, there is no equivalent of require_partition_filter for clustered columns, and a dry run cannot tell an analyst in advance how much a clustered-only query will cost.
Option C is the pattern partitioning was introduced to replace. Date sharding pushes the date into table names, multiplies table metadata, and a wildcard query with a loose _TABLE_SUFFIX predicate still reads everything, with no way to require the filter.
Option D does not bound the scan. A materialized view accelerates a specific, repeated aggregation; it does not make arbitrary ad hoc queries cheaper, it carries its own storage and refresh cost, and queries that miss the view fall back to the base table at full price.
Reference: Introduction to partitioned tables