PDE Storing the Data Practice Question
A data engineer is designing a BigQuery table to store e-commerce order events. Each row contains an order_id (STRING, high cardinality), customer_id (STRING, high cardinality), order_timestamp (TIMESTAMP), and order_amount (NUMERIC). Queries frequently filter on order_id for lookups and also scan by order_timestamp ranges for daily reporting. The engineer wants to minimize bytes scanned by both query patterns. What should the engineer do?
⚠ Common exam trap
The trap here is assuming clustering alone can replace partitioning for timestamp range filters, when clustering only helps when the filter columns are a prefix of the cluster keys and does not prune whole partitions.
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 the table by order_timestamp and cluster by order_id.
Partitioning by order_timestamp restricts daily range scans to the relevant date partitions, while clustering by order_id sorts rows within each partition so that point lookups read only matching blocks. This design targets both query patterns and minimizes bytes scanned without adding a separate pipeline or storage layer.
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 the table by order_timestamp and cluster by order_id.
Why this is correct
Partitioning by order_timestamp limits daily range scans to the relevant date partitions, and clustering by order_id sorts data within each partition so point lookups on order_id read only the matching blocks. This combination addresses both access patterns without duplicating data or adding maintenance overhead.
- ✗
Partition the table by order_id and cluster by order_timestamp.
Why it's wrong here
Partitioning by order_id creates one partition per order, which exceeds BigQuery's limit of 4,000 partitions per table and causes metadata overhead and query planning failures. It also does not help timestamp range scans because each partition holds a single order, forcing a full scan of all partitions for daily reporting.
- ✗
Create a materialized view that pre-aggregates daily order totals and query the view instead.
Why it's wrong here
A materialized view helps only if queries match the view's aggregation pattern. Here, queries also include point lookups by order_id that do not benefit from daily aggregates, and the view adds storage and refresh cost without reducing bytes scanned for the lookup pattern.
- ✗
Enable BigQuery BI Engine on the table and rely on in-memory caching for all queries.
Why it's wrong here
BI Engine accelerates interactive dashboards and subqueries but does not reduce bytes scanned for arbitrary ad-hoc lookups or range scans, and its memory capacity is limited. It is not a storage-layout solution and does not address partitioning or clustering of the underlying table.
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.