You are collecting IoT sensor data from millions of devices across the world and storing the data in BigQuery. Your access pattern is based on recent data, filtered by location_id and device_version…

Question

Exhibit

Options

• APartition table data by create_date, location_id, and device_version.
• BPartition table data by create_date, cluster table data by location_id, and device_version.
• CCluster table data by create_date, location_id, and device_version.
• DCluster table data by create_date, partition by location_id, and device_version.

• A
  6% (3)
• B
  81% (39)
• C
  10% (5)
• D
  2% (1)

Explanation

Option B is correct because BigQuery only supports partitioning on a single column, and create_date (a time-based field) is the ideal partition key since it enables partition pruning on the most selective filter - recent data. Clustering on location_id and device_version then further narrows data scanned within each partition, reducing cost and improving performance.

Why the distractors fail:

• A is invalid - BigQuery does not support multi-column partitioning; you can only partition on one column.
• C skips partitioning entirely, which means BigQuery cannot prune whole partitions of old data before applying clustering, losing the biggest cost/performance win.
• D reverses the relationship - you cannot cluster by a date column and then partition by two other columns; partitioning is limited to one column and clustering follows partitioning, not the other way around.

Memory tip: Think "Partition first, cluster second" - partition on your primary time/range filter (one column only), then cluster on the next 1–4 columns your WHERE clause filters. This matches the "coarse filter → fine filter" pattern BigQuery uses internally.

No community discussion yet for this question.