PDE Storing the Data Practice Question
A retail analytics team uses BigQuery to store point-of-sale transactions in a partitioned table. They need to optimize query performance for dashboards that filter on store_id and product_category, and they want to minimize the amount of data scanned. The table is partitioned by transaction_date and has a clustered column of store_id. Queries frequently filter on product_category as well. Which change should the data engineer make to improve performance?
⚠ Common exam trap
The trap here is thinking that clustering columns must be unique or that you can only cluster on one column, when BigQuery actually supports up to four clustering columns and order affects pruning efficiency.
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
✓
Add product_category as a second clustering column after store_id
BigQuery clustering sorts data within partitions by the specified columns, and queries that filter on those columns can skip blocks. Adding product_category as a second clustering column after store_id lets the engine prune more effectively when both columns appear in filters. The partitioning column remains transaction_date, which is ideal for date-range dashboards. This change reduces bytes scanned and improves latency without altering the partitioning scheme.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create a materialized view that pre-aggregates sales by store_id and product_category
Why it's wrong here
A materialized view can improve performance for recurring aggregations, but it does not help ad-hoc queries that filter on other dimensions or require raw transaction detail. It also adds storage and refresh costs. The scenario asks for a change to the table itself to optimize filtering on store_id and product_category, so clustering is more directly applicable and simpler to manage.
- ✓
Add product_category as a second clustering column after store_id
Why this is correct
BigQuery supports up to four clustering columns, and the order matters: data is sorted by the first column, then the second, and so on. Adding product_category as a second clustering column allows BigQuery to prune blocks more effectively when queries filter on both store_id and product_category. This reduces the amount of data scanned and improves dashboard performance without changing the partitioning strategy, which remains optimal for date filters.
- ✗
Change the partitioning column to product_category and keep store_id as the clustered column
Why it's wrong here
Partitioning by product_category would create a partition per category, which is a low-cardinality column and would not reduce data scanned effectively for date-range queries. Dashboards typically filter on date ranges, so removing date partitioning would increase scanned data. Clustering on store_id remains useful, but the loss of date partitioning is a net negative for the stated workload.
- ✗
Convert the table to use time-unit partitioning on transaction_date with a daily granularity and remove clustering
Why it's wrong here
Removing clustering would eliminate the benefit for store_id and product_category filters, increasing data scanned for those queries. Time-unit partitioning on transaction_date is already likely in place; changing granularity does not address the need to filter on the two columns. This option does not improve performance for the described dashboards and may worsen it.
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.