PDE Designing Data Processing Systems Practice Question
You are designing a BigQuery data warehouse for a retail company. Queries frequently filter on order_date and customer_id. To optimize query performance and cost, which table design should you use?
⚠ Common exam trap
PDE often tests the partition-then-cluster ordering — candidates reverse the two or choose ingestion_time partitioning, not realizing that the partition column must match the dominant filter predicate to enable pruning.
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 order_date and cluster by customer_id
Partitioning by order_date and clustering by customer_id aligns the physical storage with the query access pattern: partition pruning eliminates irrelevant date ranges, and clustering sorts data within each partition by customer_id so filter and aggregation operations scan fewer blocks. This combination minimizes bytes scanned, which directly reduces both query latency and cost in BigQuery's on-demand pricing model.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Cluster by order_date and partition by customer_id
Why it's wrong here
Partitioning by customer_id creates one partition per customer, producing enormous partition counts and metadata overhead, while clustering by order_date cannot prune on the date filter. The correct design partitions by order_date and clusters by customer_id. Customer partitioning suits low-cardinality, stable keys.
- ✗
Partition by ingestion_time and cluster by order_date
Why it's wrong here
Partitioning by ingestion_time cannot prune on order_date, so date filters scan every ingestion partition; clustering by order_date then adds no benefit because order_date is not the partition column. Ingestion-time partitioning suits streaming pipelines that filter on load time, not analytical queries filtering on business dates.
- ✗
Use a clustered table without partitioning
Why it's wrong here
Clustering alone orders data within the whole table but provides no partition pruning, so date-range filters still scan all partitions, raising bytes billed. Partitioning by order_date is what restricts scanned data. Clustering-only suits tables queried by high-cardinality columns without a reliable date filter.
- ✓
Partition by order_date and cluster by customer_id
Why this is correct
Partitioning by order_date prunes scanned data when queries filter on that column, while clustering by customer_id co-locates rows sharing that value within each partition. This combination satisfies both frequent filter predicates, reducing bytes scanned and query cost compared with clustering or partitioning alone.
Go deeper
Related to this question
About these practice questions
One of 747 original PDE practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.