hardMultiple SelectObjective-mapped
PCDE Practice Question: A multinational corporation uses BigQuery to…
A multinational corporation uses BigQuery to combine sales data from multiple regions. Each region stores data in separate tables with identical schemas. The BI team needs to create a unified view for a dashboard that queries data by region and product. Which TWO strategies should the data engineer implement to optimize query performance and reduce costs?
⚠ Common exam trap
Google Cloud often tests the misconception that a UNION ALL view alone provides performance benefits, when in fact it does not reduce data scanned unless combined with table-level filters like _TABLE_SUFFIX or underlying partitioned/clustered 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 date and cluster by region and product
Partitioning the table by date and clustering by region and product enables partition pruning and clustering block elimination, reducing data scanned and costs. Option B is correct because wildcard tables with a filter on _TABLE_SUFFIX allow BigQuery to query only the required region tables, minimizing data read and cost. The other options are not optimal: C (UNION ALL view) does not reduce data scanned unless underlying tables are partitioned/clustered; D (materialized views for each region) adds complexity and cost; E (single table with region column) may still scan all data if not partitioned/clustered properly, and does not leverage region-specific tables.
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 date and cluster by region and product
Why this is correct
Reduces data scanned for common filter conditions.
- ✓
Use a wildcard table with a filter on _TABLE_SUFFIX to query only required region tables
Why this is correct
Enables table pruning to scan only relevant tables.
- ✗
Create a view with UNION ALL of all region tables
Why it's wrong here
Does not prune tables; scans all tables on every query.
- ✗
Create materialized views for each region
Why it's wrong here
Materialized views are pre-computed and may not support dynamic region selection.
- ✗
Store all data in a single table with region as a column
Why it's wrong here
Increases storage and may violate data locality requirements.
Go deeper
Related to this question
About these practice questions
One of 1,446 original PCDE 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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCDE 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 PCDE exam.