PDE Preparing and Using Data for Analysis Practice Question
Your team maintains a BigQuery dataset where a partitioned table 'transactions' is partitioned by DATE(transaction_time) and clustered by customer_id. Analysts frequently run queries filtering on a specific customer_id and a date range of the last 7 days. They report that these queries scan more data than expected, and you notice that the table was created without a partition filter requirement but with a clustering column. Which action should you take to optimize query performance and reduce bytes scanned?
⚠ Common exam trap
The trap here is assuming that adding a partition filter requirement or additional clustering will automatically improve performance, when the real issue is that queries must filter directly on the existing partition and clustering columns to enable pruning.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Ensure that queries filter on the clustering column customer_id and use the partition column in the WHERE clause to allow partition pruning and cluster pruning.
The table is partitioned by DATE(transaction_time) and clustered by customer_id. To optimize queries that filter on customer_id and a date range, analysts must ensure their WHERE clause includes both the partition column and the clustering column without transformations. This enables partition pruning and cluster pruning, minimizing data scanned. Requiring a partition filter or changing clustering does not fix the root cause if queries are not written to leverage the existing schema.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a partition filter requirement to the table using ALTER TABLE SET OPTIONS(require_partition_filter = true).
Why it's wrong here
Setting require_partition_filter forces queries to include a partition filter, which can prevent accidental full-table scans, but it does not improve performance for queries that already filter on a date range. The scenario states analysts filter on a date range, so the filter is present. This option does not address the root cause of excessive bytes scanned, which is likely due to the clustering not being leveraged effectively or a mismatch in filter usage.
- ✗
Recreate the table with clustering on both customer_id and transaction_time to improve filter effectiveness.
Why it's wrong here
Clustering on transaction_time in addition to customer_id may not help because the table is already partitioned by DATE(transaction_time). BigQuery automatically prunes partitions based on the partition column, so adding transaction_time to clustering is redundant and can increase storage overhead. The primary filter is on customer_id, which is already the clustering column. This change would not reduce bytes scanned for the given query pattern.
- ✗
Change the table to use ingestion-time partitioning instead of column-based partitioning to reduce overhead.
Why it's wrong here
Ingestion-time partitioning partitions data by when it was loaded, not by the transaction_time column. Analysts filter on transaction_time, so switching to ingestion-time partitioning would break partition pruning for those queries and likely increase bytes scanned because the filter would not align with the partition boundaries. This option is counterproductive and does not address the performance issue.
- ✓
Ensure that queries filter on the clustering column customer_id and use the partition column in the WHERE clause to allow partition pruning and cluster pruning.
Why this is correct
BigQuery uses partition pruning when the query filters on the partition column, and cluster pruning when it filters on the clustering columns. If analysts are not filtering on customer_id, or if the filter is not applied correctly (e.g., using a function on the column), clustering benefits are lost. By ensuring queries filter directly on customer_id and the partition column, BigQuery can scan only relevant partitions and blocks, reducing bytes scanned and improving performance. This is the correct approach to optimize the query.
Go deeper
Related to this question
About these practice questions
This PDE question is part of Courseiva's 747-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Google Cloud exam blueprint
This PDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PDE exam.