The answer is to add clustering on transaction_date in addition to customer_id. This is correct because BigQuery partitioning by transaction_date already prunes partitions to the last 30 days, but clustering on customer_id alone does not physically sort rows by date within those partitions; adding clustering on transaction_date ensures that within each daily partition, rows for the same date range are colocated, allowing BigQuery to skip irrelevant blocks during the scan. On the Google Professional Cloud Database Engineer exam, this tests your understanding that clustering complements partitioning by further narrowing data reads, especially when filters span both a partition column and a non-partition column—a common trap is assuming partitioning alone is sufficient for all filter patterns. Remember the memory tip: “Partition prunes partitions, cluster sorts blocks; for date-range filters, cluster the date too.”
PCDE Practice Question: Define data structures and implement SQL for Business Intelligence
This PCDE practice question tests your understanding of define data structures and implement sql for business intelligence. Read the scenario carefully and evaluate each option against the stated constraints before committing to an answer. After answering, compare your reasoning against the explanation and wrong-answer breakdown below. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
A financial institution uses BigQuery for BI reporting. They have a table 'transactions' (10 TB) partitioned by transaction_date and clustered by customer_id. A common report filters on customer_id and last 30 days. The report is slow. Which change would most improve query performance for this specific report?
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Add clustering on transaction_date in addition to customer_id
Option C is correct because adding clustering on transaction_date alongside customer_id improves query performance for the specific report that filters on both customer_id and the last 30 days. BigQuery uses clustering to sort data within partitions, so clustering by transaction_date ensures that within each partition, the rows for the last 30 days are colocated, reducing the amount of data scanned. This complements the existing partition pruning by further narrowing the scan to relevant blocks.
Key principle: Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
✗
Change partition column to customer_id
Why it's wrong here
Partitioning by customer_id would create a huge number of partitions, likely exceeding limits and hurting performance.
✗
Remove clustering and rely only on partitioning
Why it's wrong here
Removing clustering would increase scan size for customer-specific queries, worsening performance.
✓
Add clustering on transaction_date in addition to customer_id
Why this is correct
Clustering on the partition column can further optimize queries that filter on both customer_id and date range.
Related concept
Read the scenario before looking for a memorised answer.
✗
Manually recluster the table daily
Why it's wrong here
BigQuery automatically reclusters; manual reclustering adds unnecessary cost and complexity.
Common exam traps
Common exam trap: answer the scenario, not the keyword
Google Cloud often tests the misconception that partitioning alone is sufficient for all filter patterns, but the trap here is that clustering on the filter column (transaction_date) is needed to optimize queries that filter on both partition and clustering columns, especially when the partition column is not the primary filter.
Detailed technical explanation
How to think about this question
BigQuery clustering physically reorders data within each partition based on the clustering columns, and the metadata tracks the min and max values of clustering columns per block. When a query filters on both partition and clustering columns, BigQuery can skip entire blocks that don't contain matching customer_ids or dates, achieving block-level pruning. In practice, for a 10 TB table partitioned by day, clustering by transaction_date ensures that the last 30 days' data is stored contiguously, minimizing I/O for the time-range filter.
KKey Concepts to Remember
Read the scenario before looking for a memorised answer.
Find the constraint that changes the correct option.
Eliminate answers that are true in general but not in this case.
TExam Day Tips
→Watch for words such as best, first, most likely and least administrative effort.
→Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Real-world example
How this comes up in practice
A cloud solutions architect for a retail company is evaluating services for a new workload. The correct answer here reflects best practice for the specific scenario described — not a general cloud recommendation. Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option. Cloud exam questions reward reading the constraint carefully: the same technology can be right or wrong depending on the use case.
What to study next
Got this wrong? Here's your next step.
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
Define data structures and implement SQL for Business Intelligence — This question tests Define data structures and implement SQL for Business Intelligence — Read the scenario before looking for a memorised answer..
What is the correct answer to this question?
The correct answer is: Add clustering on transaction_date in addition to customer_id — Option C is correct because adding clustering on transaction_date alongside customer_id improves query performance for the specific report that filters on both customer_id and the last 30 days. BigQuery uses clustering to sort data within partitions, so clustering by transaction_date ensures that within each partition, the rows for the last 30 days are colocated, reducing the amount of data scanned. This complements the existing partition pruning by further narrowing the scan to relevant blocks.
What should I do if I get this PCDE question wrong?
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
What is the key concept behind this question?
Read the scenario before looking for a memorised answer.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. The user runs a BigQuery query on a non-partitioned table and receives the error shown. Which optimization should be applied first to resolve the issue?
medium
✓ A.Partition the table by the event_date column
B.Increase the BigQuery reservation slot count
C.Create a materialized view that pre-aggregates the data
D.Cluster the table by event_date
Why A: The error indicates that the query is scanning too much data, likely exceeding the free tier or slot quota. Partitioning the non-partitioned table by `event_date` allows BigQuery to perform partition pruning, scanning only the relevant date range instead of the entire table. This directly reduces the data processed, which is the most effective first optimization for cost and performance.
Variation 2. Refer to the exhibit. What is the likely cause of this error?
medium
A.The table is a view
✓ B.The query does not include WHERE clause with partition column
C.The table is not partitioned
D.The user does not have permission to query the table
Why B: The error occurs because the query attempts to access a partitioned table without specifying the partition column in the WHERE clause. In Snowflake (the platform implied by PCDE context), querying a large partitioned table without a partition filter forces a full scan of all partitions, which can exceed resource limits or time out. The correct approach is to include the partition column in the WHERE clause to enable partition pruning.
Variation 3. A BI team queries this table with a WHERE clause that filters on product_id but does not include a sale_date filter. What is the outcome?
easy
✓ A.The query fails with an error.
B.The query runs successfully and only scans partitions containing product_id values.
C.The query runs successfully and scans only the latest partition.
D.The query runs successfully but scans all partitions.
Why A: In a partitioned table (e.g., using Hive-style partitioning or a similar system like BigQuery or Snowflake), a WHERE clause that filters only on `product_id` without including the partition key `sale_date` forces a full scan of all partitions. However, if the table is defined with a strict partition pruning requirement (e.g., in Databricks or Spark SQL with dynamic partition pruning disabled, or in a system that requires the partition column in the filter), the query may fail with an error because the engine cannot determine which partitions to read without the partition key. The correct answer is A because the scenario implies a system (like certain SQL-on-Hadoop engines or strict partitioning rules) where omitting the partition column in the WHERE clause results in a query error, not a successful scan.
Last reviewed: Jun 30, 2026
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
This PCDE 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 PCDE exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.