PDE Designing Data Processing Systems Practice Question
A company uses BigQuery for analytics. They have a table that is queried frequently by date range. To reduce costs, they want to ensure queries only scan the relevant partitions. They also want to improve performance for queries filtering on a specific customer_id. Which table design should they use?
⚠ Common exam trap
PDE often tests the distinction between partitioning and clustering — candidates may reverse them or choose ingestion-time partitioning when a business date column is the filter, leading to unnecessary data scans.
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
✓
Partition by date column and cluster by customer_id
To reduce costs by ensuring queries only scan relevant partitions, you should partition by the date column (so date-range filters prune partitions). To improve performance for queries filtering on customer_id, you should cluster by customer_id (so BigQuery can skip blocks within partitions). Therefore, partition by date column and cluster by customer_id is the correct design.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Partition by ingestion time and cluster by customer_id
Why it's wrong here
Ingestion-time partitioning cannot prune on a business date column, so date-range filters still scan all partitions. It suits pipelines loading data continuously where arrival time is the query predicate, not tables filtered by a stored date field.
- ✗
Use a materialized view that filters by date and customer_id
Why it's wrong here
A materialised view stores precomputed results and adds refresh cost; it does not partition the base table, so date-range scans still read full data. Materialised views suit repeated identical aggregations, not ad hoc range filtering needing partition pruning.
- ✗
Cluster by date column and partition by customer_id
Why it's wrong here
Partitioning by customer_id forces date-range queries to scan every partition, defeating the cost requirement, since pruning only occurs on the partitioned column. Clustering by date is the correct pattern when date is the primary filter and customer_id the secondary one.
- ✓
Partition by date column and cluster by customer_id
Why this is correct
Partitioning on the date column restricts each query to the relevant date range, cutting bytes scanned and cost. Clustering by customer_id then sorts data within each partition, so filters on that column skip irrelevant blocks, satisfying both the cost and performance constraints.
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.