PDE Storing the Data Practice Question
A retail analytics team stores transaction data in a BigQuery table partitioned by day on a DATE column named transaction_date. Analysts repeatedly run queries that filter on transaction_date and also on a high-cardinality STRING column named store_id. These queries scan far more data than expected, and slot usage is high. The team wants to reduce bytes processed without changing query results. What should they do?
⚠ Common exam trap
The trap here is assuming that partitioning alone handles every filter predicate, when partitioning only prunes by the partition column and a second high-cardinality filter needs clustering.
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
✓
Create a clustered table on store_id, so BigQuery can prune blocks within each transaction_date partition.
Clustering a partitioned table on the frequently filtered high-cardinality column lets BigQuery prune storage blocks within each partition. Partition pruning on transaction_date still happens, and cluster pruning further limits scanned data. This directly reduces bytes processed and slot consumption for the described queries without altering results, making it the appropriate storage optimization.
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 clustered table on store_id, so BigQuery can prune blocks within each transaction_date partition.
Why this is correct
Clustering on store_id sorts data within each partition into storage blocks, and BigQuery uses the cluster column to skip blocks that cannot match the store_id filter. Because queries already filter on transaction_date, partition pruning still applies, and adding clustering further reduces scanned bytes. This matches the requirement of lowering bytes processed without changing results, and clustering can be added to an existing partitioned table.
- ✗
Create a materialized view that precomputes all transactions grouped by store_id and transaction_date.
Why it's wrong here
A materialized view can help specific aggregations, but it does not reduce scanned bytes for arbitrary queries that select detailed transaction rows. It also introduces refresh and storage costs and may not be usable for queries that need non-aggregated columns. The team wants broad filtering improvements, not a single precomputed aggregate, so this does not address the root cause.
- ✗
Export the table to Cloud Storage as Avro and query it with an external table that applies store_id predicates.
Why it's wrong here
External tables over Cloud Storage do not benefit from BigQuery's columnar storage and block pruning in the same way, and they typically scan more data. Moving data out also adds latency and egress considerations. This approach would likely increase bytes processed and operational complexity, so it does not satisfy the goal of reducing scanned data while keeping query results unchanged.
- ✗
Change the table to require a partition filter, forcing analysts to always specify transaction_date.
Why it's wrong here
Requiring partition filters prevents full-table scans, but the analysts already filter on transaction_date. The problem is the additional store_id filter still scans all blocks inside matching partitions. This setting adds friction and can break existing pipelines that omit the filter, yet it does not reduce bytes for the queries described, so it is not the right optimization.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PDE question from scratch — 747 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.