A data analytics team uses BigQuery for large-scale queries. They notice that queries are scanning more data than necessary, leading to high costs. Which feature should they implement to reduce the amount of data scanned per query?
Partitioning divides a table into separate storage segments based on a specified column, commonly a date or timestamp, and BigQuery performs partition pruning during query execution. When a query's WHERE clause filters on the partition column, the engine avoids scanning partitions that fall outside the filter, directly reducing the bytes processed and thereby lowering query cost. This is a native, deterministic way to limit scan size and is especially effective for large, time-series tables.
Why this answer
Partitioning divides a table into segments based on a column (e.g., date), allowing BigQuery to prune partitions during query execution. When a query includes a filter on the partitioning column, BigQuery scans only the relevant partitions, significantly reducing the bytes processed and lowering costs.
Exam trap
Google Cloud often tests the distinction between partitioning (which reduces data scanned by pruning entire segments) and clustering (which only reorganizes data within partitions for better compression and filtering, but does not reduce the total data scanned unless combined with partitioning).
How to eliminate wrong answers
Option A is wrong because materialized views precompute and cache query results for faster performance, but they do not reduce the amount of raw data scanned per query; they may even increase storage costs. Option B is wrong because streaming inserts are used for real-time data ingestion into BigQuery, not for controlling data scan volume during queries. Option D is wrong because clustering sorts data within partitions based on column values, improving query performance and reducing costs only after partitioning is applied; without partitioning, clustering alone does not limit the total data scanned.