Google PCA Practice Question: Analysing and Optimising Technical and Business Processes
A development team uses BigQuery for analytical queries. They want to reduce query costs for a large table that is frequently filtered by a date column and a customer_id column. Which TWO table design strategies will reduce the amount of data scanned? (Choose 2)
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.
Option A is correct because partitioning the table by the date column means BigQuery only scans the partitions that match the query's date filter, dramatically reducing bytes processed for date-filtered queries. Option E is correct because clustering on customer_id physically sorts and co-locates data by that column, so filters on customer_id prune blocks within partitions and further cut data scanned. Together, partitioning by date and clustering by customer_id directly address the two frequent filter columns in this scenario. Option B is incorrect because BigQuery does not support traditional secondary indexes on columns like customer_id; clustering is the equivalent mechanism. Option C is incorrect because wildcard tables with date suffixes are a query-time convenience for sharding, not a table design that reduces scanned data by itself. Option D is incorrect because normalizing into multiple tables does not inherently reduce bytes scanned and may even require more joins and data reads.
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.
Why this is correct
Partitioning by date divides the table into segments, so queries filtering on the date column prune irrelevant partitions and scan only matching ones. This directly reduces bytes scanned, satisfying the cost-reduction goal for date-filtered analytical queries.
- ✗
Create an index on customer_id.
Why it's wrong here
BigQuery tables are columnar and cannot use traditional indexes; clustering on customer_id is the mechanism that prunes scanned data. An index is tempting because it is the standard relational tuning tool, and would be correct on row-store systems such as Cloud SQL or SQL Server.
- ✗
Use wildcard tables with date suffixes.
Why it's wrong here
Wildcard tables with date suffixes still scan every matching table unless the query filters on the _TABLE_SUFFIX pseudo-column, so they do not themselves prune data. They are tempting because they organise date-sharded datasets, and would be correct for querying across many legacy daily export tables.
- ✗
Normalize the table into multiple tables.
Why it's wrong here
Normalisation splits columns across tables but does not reduce bytes scanned for date and customer_id filters; partitioning and clustering do. It is tempting because normalisation is a classic modelling practise, and it would be correct when the goal is eliminating update anomalies rather than lowering query cost.
- ✓
Cluster the table on customer_id.
Why this is correct
Clustering on customer_id physically co-locates rows sharing that value, so filters on customer_id prune blocks before scanning. This directly reduces bytes read for the frequently filtered column, satisfying the cost-reduction constraint. Pair it with date partitioning to cover both filter predicates.
Go deeper
Related to this question
Learn chapter
Cloud SQL and Managed Data Stores
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
BigQuery
BigQuery is a fully managed, serverless data warehouse on Google Cloud that lets you run fast SQL queries on massive datasets without managing any infrastructure.
About these practice questions
This PCA question is part of Courseiva's 807-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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCA 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 PCA exam.