Google ACE Your company uses BigQuery for analytics Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  Enable clustering on the 'customer_id' and 'region' columns.

  Why this is correct

  Enabling clustering on 'customer_id' and 'region' physically sorts the data by these columns within each storage block, allowing BigQuery to use block-level metadata to prune blocks that cannot match the filter predicates. Because typical ad-hoc queries filter on these exact columns, cluster pruning drastically reduces the number of bytes scanned and therefore query cost and latency. Clustering is ideal for high-cardinality columns like customer_id, whereas partitioning would be impractical at that granularity. Placing the most selective or frequently filtered column (customer_id) first in the clustering key maximizes this pruning.

• ✗

  Create materialized views for common queries.

  Why it's wrong here

  Materialized views precompute and store results for specific query patterns, so they are only useful if you can predict the recurring aggregations or joins that users will run. Ad-hoc queries that filter on varying customer_id and region values would rarely align with a fixed materialized view definition; instead, the view would still be queried and BigQuery would scan its base table or the view's internal storage to filter on the fly, often providing little benefit. Additionally, maintaining materialized views consumes storage and incurs refresh costs, making them a poor first choice for reducing scan on arbitrary filters. This is a secondary optimization, not a substitute for data layout techniques.

• ✗

  Create views for each combination of filters.

  Why it's wrong here

  Creating a view for each combination of customer_id and region filters does nothing to change the physical storage or the query execution plan; views are simply stored SQL queries that expand at runtime. Because the underlying table remains unclustered, every view query must scan the entire table to evaluate the predicate, so performance stays exactly the same while you multiply the number of objects to maintain. Moreover, the combinatorial explosion of filter combinations would quickly become unmanageable, and the query engine cannot leverage views to skip data blocks. This approach adds abstraction without addressing the real cause of high I/O.

• ✗

  Change partitioning to use ingestion time instead of 'order_date'.

  Why it's wrong here

  Changing the partitioning column to ingestion time would repartition the table by the arrival timestamp, not by the business date that queries actually filter on, so BigQuery's partition pruning would not align with the 'order_date' predicate. Partitioning on order_date is appropriate because it directly matches the existing query pattern and can dramatically reduce scanned data; switching to ingestion time would force full scans for date-range filters unless users also filter on the arrival timestamp. Ingestion time partitioning is designed primarily for append-only streaming data that lacks a natural timestamp column, so it would also break the logical relationship between data and business time. Thus, this change would likely degrade performance and is not an improvement.

Go deeper

Related to this question