PDE Storing the Data Practice Question
A retail analytics team has a 400 TB BigQuery table partitioned by DATE on a transaction_date column. Analysts almost always filter by a single store_id and a date range, and each store has roughly 12 years of history. Queries currently scan the entire partition for the requested dates. The team wants to reduce bytes billed without changing the table name or rewriting the ingestion pipeline. What should they do?
⚠ Common exam trap
The trap here is assuming that partitioning alone prunes all irrelevant data, when partition pruning only eliminates whole partitions and a within-partition filter still scans the entire partition unless clustering is defined.
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 a clustering specification on store_id to the existing partitioned table.
Clustering a partitioned table on the frequently filtered column lets BigQuery prune blocks within each partition, so a query that filters on both date and store reads far less data than a full partition scan. The optimization applies transparently to the same table name, so ingestion and existing SQL keep working, and bytes billed drop in proportion to how well the clustering column discriminates rows inside each partition.
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 transactions by store_id and date, and point analysts at the view.
Why it's wrong here
A materialized view can accelerate repeated aggregations, but it does not reduce the scan of the base table for arbitrary date ranges and store filters, and it must be refreshed as new data arrives. It also changes the object analysts query, which the team explicitly wants to avoid, and it does not give BigQuery a way to prune within a date partition.
- ✗
Enable partition expiration set to 30 days so older partitions are dropped automatically.
Why it's wrong here
Partition expiration deletes data, which destroys the 12 years of history the analysts query. It reduces bytes billed only by removing rows the business still needs, and it does not help queries that target recent partitions while filtering by a single store. This is a data-loss change, not a performance optimization.
- ✓
Add a clustering specification on store_id to the existing partitioned table.
Why this is correct
BigQuery can co-locate and sort data within each date partition by the clustering columns, so a filter on store_id lets the engine read only the blocks containing that store rather than every block in the partition. This directly cuts bytes billed for the store-plus-date-range pattern and requires no change to the table name or pipeline.
- ✗
Convert the table to use the US multi-region instead of a single region to increase scan parallelism.
Why it's wrong here
Location affects data residency, latency, and pricing tier, not the amount of data a query must read. Moving the dataset to a multi-region does not prune blocks inside a partition, so a store_id filter would still scan the full partition for the selected dates. It also may violate residency requirements and adds migration effort.
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.