Google ACE Using BigQuery for analytics Practice Question

A company is using BigQuery for analytics. They notice that queries are slow and expensive. The data is loaded daily into a single table. Which action would most improve performance and reduce cost?

⚠ Common exam trap

Google Cloud often tests the misconception that increasing compute resources (slots or concurrency) is the primary fix for slow queries, when in reality data pruning via partitioning and clustering is the first and most impactful optimization for cost and performance.

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 frequently filtered columns.

Partitioning the table by date allows BigQuery to prune partitions during query execution, scanning only the relevant daily data instead of the entire table. Clustering on frequently filtered columns further reduces the data scanned by sorting data within partitions. This directly reduces both query cost (pay-per-byte) and latency, addressing the core issue of slow, expensive queries on a large daily-loaded table.

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 a flat-rate reservation to improve query concurrency.
Why it's wrong here
A flat-rate reservation ensures a consistent number of slots for your project, which improves concurrency by preventing other workloads from stealing capacity. However, it does not reduce the amount of data scanned or speed up any individual query; a query that scans a massive table will still take just as long, because slots are not the bottleneck when the issue is I/O and data volume.
✗
Denormalize the table to reduce joins.
Why it's wrong here
Denormalizing the table eliminates expensive JOIN operations, which can reduce query execution time. But it does not address the root cause of slow analytics in BigQuery: full table scans. In fact, denormalization often increases storage size and costs, and while it can reduce query time in some cases, partitioning and clustering provide a more principled and cost-effective way to minimize the bytes processed per query.
✗
Increase the number of slots available for the project.
Why it's wrong here
Increasing the number of slots for your project adds more CPU and memory capacity, which helps when many queries contend for resources or when queries hit the per-project slot limit. However, a single query's latency is primarily determined by how much data it must read and process; adding slots will not make a query scan less data, so a query that filters on a non-partitioned table will still read the entire table and remain slow.
✓
Partition the table by date and cluster by frequently filtered columns.
Why this is correct
Partitioning the table by date allows BigQuery to use partition pruning, so queries with date range filters only read the relevant daily partitions instead of the full table. Clustering on frequently filtered columns further organizes data within each partition, enabling block-level pruning based on the cluster columns' values. Together, these features dramatically reduce the bytes scanned and the underlying I/O, directly improving query speed and reducing cost.

Go deeper

Google ACE Using BigQuery for analytics Practice Question

Why each option matters

Option-by-option breakdown

Related to this question