hardMultiple Choice
PDE Practice Question: Optimizing a BigQuery query that runs on a large…
You are optimizing a BigQuery query that runs on a large table (hundreds of TB). The table is partitioned by date and frequently queried with filters on a specific customer_id column and date range. Queries are slow even after partitioning. Which optimization should you apply?
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
✓
Columnar clustering on customer_id
Clustering on customer_id within the partition improves query performance because BigQuery can prune blocks based on clustered columns. Partitioning alone doesn't help with non-date filters. Materialized views may help pre-aggregated queries but not ad-hoc customer_id filters. Denormalization is not an optimization. Increasing slots is expensive and doesn't address data structure.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase the number of BigQuery slots
Why it's wrong here
Adding slots raises compute capacity, but the query still scans every partition matching the date filter because no clustering exists on customer_id. Slot increases help when workloads are genuinely compute-bound or concurrent queries queue; here the bottleneck is bytes scanned, so clustering on customer_id is required.
- ✓
Columnar clustering on customer_id
Why this is correct
Clustering sorts and co-locates data by customer_id within each date partition, so filtered scans read far fewer blocks. Partitioning alone cannot prune on customer_id, which is why queries stay slow; clustering on that column satisfies the filter constraint.
- ✗
Create materialized views for each customer
Why it's wrong here
A materialised view per customer multiplies storage and refresh cost across potentially millions of customers, and BigQuery cannot maintain that many. Materialised views suit a small number of recurring, predictable aggregate queries; here clustering on customer_id lets every ad-hoc customer filter prune blocks directly.
- ✗
Denormalize the table to reduce joins
Why it's wrong here
Denormalisation removes joins, yet the stem describes a single large table already filtered by customer_id and date — no join is the bottleneck. Denormalising suits star-schema analytics where joins dominate; here it adds bytes scanned without pruning, whereas clustering on customer_id reduces data read.
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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.