PDE Storing the Data Practice Question
A data engineer is designing a BigQuery table to store customer order records. Queries frequently filter by order_date and customer_id, and the dataset grows by about 5 TB per day. The engineer wants to minimize query cost and improve performance for these filtered queries. What should the engineer do?
⚠ Common exam trap
The trap here is assuming that a materialized view or BI Engine can replace partitioning and clustering for reducing scan cost on large base tables.
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_date and cluster by customer_id.
Partitioning by order_date enables partition pruning, so queries with date filters scan only relevant partitions, cutting cost and improving speed. Clustering by customer_id sorts data within partitions, making customer-filtered queries more efficient. Together they match the described query patterns and scale well with daily 5 TB loads, unlike materialized views, wildcard tables, or BI Engine, which do not address base-table scan efficiency.
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 orders by customer_id and order_date.
Why it's wrong here
A materialized view can accelerate specific pre-aggregated queries, but it adds storage cost and must be refreshed as new data arrives. It does not reduce the cost of arbitrary filtered queries that scan raw order records, and it cannot replace the need for partitioning and clustering on the base table. For a 5 TB/day ingest, the base table still needs efficient pruning.
- ✗
Enable BigQuery BI Engine and reserve memory for the dataset.
Why it's wrong here
BI Engine accelerates interactive dashboards and BI tools by caching data in memory, but it is not a substitute for partitioning and clustering on large base tables. It has capacity limits and does not reduce the cost of ad-hoc SQL queries that scan the full table. It also does not help with the 5 TB/day ingest efficiency.
- ✓
Partition the table by order_date and cluster by customer_id.
Why this is correct
Partitioning by order_date allows BigQuery to prune partitions when queries filter on that column, reducing bytes scanned and cost. Clustering by customer_id further sorts data within each partition, improving filter and aggregation performance for customer-specific queries. This combination directly addresses the access patterns described and is the recommended approach for large, time-series-like datasets with common filter columns.
- ✗
Use a wildcard table over daily sharded tables named orders_YYYYMMDD.
Why it's wrong here
Wildcard tables over date-sharded tables require querying many tables and do not provide partition pruning benefits comparable to native partitioning. They also complicate schema management and have higher query planning overhead. This approach was common before native partitioning, but it is not the best practice for new large tables with date filters.
Go deeper
Related to this question
About these practice questions
This PDE question is part of Courseiva's 747-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.