PDE Designing Data Processing Systems Practice Question
An organization is using BigQuery for analytics. They have a table that is 500 GB and is frequently queried by 'date' and 'region'. They want to optimize query performance and reduce costs. Which TWO actions should they take?
⚠ Common exam trap
PDE often tests the confusion between access control features (authorized views) and performance features (partitioning, clustering); candidates pick materialized views or wildcard tables without recognizing that partitioning and clustering directly address the query patterns.
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
✓
Cluster the table by region
Option E is correct because partitioning the table by date means BigQuery only scans the partitions that match the query's date filter, drastically reducing bytes processed and cost for a 500 GB table frequently filtered by date. Option D is correct because clustering the table by region physically sorts data within each partition by region, so queries filtering on region benefit from block pruning and scan less data. Together, partitioning by date and clustering by region match the two most common query predicates and are the standard BigQuery optimization pattern. Option A (authorized view) only controls access to data and does not improve query performance or reduce scan costs. Option B (wildcard table) is used to query multiple similarly named tables with a UNION-like syntax and does not optimize a single table's scans. Option C (materialized views) can help some workloads but is not the primary optimization for a frequently filtered base table and adds storage and refresh overhead, so it is not one of the two best actions here.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use an authorized view
Why it's wrong here
An authorized view grants query access to underlying data; it neither partitions the table nor clusters it, so scanned bytes stay at 500 GB. It is tempting because it does control row-level access for specific principals, which would be the right choice when the requirement is restricting which users can read which rows.
- ✗
Use a wildcard table
Why it's wrong here
A wildcard table queries a set of similarly named tables as one; it cannot partition or cluster a single 500 GB table, so pruning by date and region never occurs. It is tempting because it genuinely helps when data is already split across date-sharded tables named with a common prefix.
- ✗
Use materialized views
Why it's wrong here
Materialized views accelerate repeated aggregate queries but cannot reduce bytes scanned for arbitrary date-and-region filters, and they add refresh and storage cost. They are correct for caching stable, expensive aggregation patterns rather than raw scan reduction.
- ✓
Cluster the table by region
Why this is correct
Clustering by region sorts data within partitions by that column, so filters on region read only relevant blocks rather than scanning everything. This satisfies the stem's region-filtering access pattern, reducing bytes billed and improving performance.
- ✓
Partition the table by date
Why this is correct
Partitioning by date splits the 500 GB table into segments, so queries filtering on date scan only matching partitions instead of the whole table. This satisfies the stem's date-filtering access pattern, cutting bytes processed and cost.
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.