An analytics team in an enterprise retail organization is designing a BigQuery analytical table for sales transactions. The table ingests approximately 40 GB of new data daily and will store several years of historical transactions. The team identified the following query access patterns and operational constraints: Most queries filter on transaction time windows ranging from a single day to multiple weeks Queries frequently filter by store_id (medium cardinality), followed by customer_id (high cardinality), and occasionally include order_status Data analysts require accurate upfront byte and cost estimates before executing their queries The solution must maximize block pruning and minimize slot consumption during scan and filter operations How should the table be partitioned and clustered to optimize query performance and cost?

Partition the table by `DATE(transaction_timestamp)` and cluster the table by `store_id`, `customer_id`, and `order_status`.: ### What the Solution Is This solution combines **time-unit table partitioning** on `DATE(transaction_timestamp)` with **multi-column clustering** on `store_id`, `customer_id`, and `order_status`. ### How It Meets the Requirements - **Accurate Upfront Cost Estimates & Time Filtering:** Partitioning by `DATE(transaction_timestamp)` creates distinct physical segments per day. Because partition metadata is known prior to query execution, BigQuery provides granular and deterministic query cost estimates in the query dry-run/preview before running. - **Maximizing Block Pruning:** Within each daily partition, BigQuery collocates and sorts data into adaptively sized storage blocks using the specified cluster sort order (`store_id`, `customer_id`, `order_status`). Queries filtering by `store_id` alone or `store_id` combined with `customer_id` follow the left-to-right sort order, enabling BigQuery to skip non-matching blocks completely. - **Minimizing Slot Consumption:** Pruning entire partitions and underlying storage blocks reduces the total bytes scanned from persistent storage, drastically decreasing the number of computational slots required for the scan stage. ### Key Advantages and Capabilities - **Granular Partition Pruning:** Scans only the physical partitions containing the requested date intervals. - **Adaptive Block Colocation:** High-cardinality fields like `customer_id` and medium-cardinality fields like `store_id` benefit from block metadata min/max pruning. - **Automatic Maintenance:** BigQuery automatically performs background reclustering as new data is loaded without requiring manual defragmentation. - **Partition Size Efficiency:** Ingesting 40 GB daily ensures that each partition easily exceeds the recommended minimum threshold of 10 GB per partition, avoiding metadata bottlenecks. ### Why This Is Optimal Combining partitioning on the primary temporal filter with clustering ordered by the most frequent filter columns (`store_id` first, then `customer_id`, then `order_status`) strictly follows BigQuery design best practices for query pruning and cost optimization.

Bigquery Partitioning Clustering Optimization 3 | professional-cloud-data-engineer

Bigquery Partitioning Clustering Optimization 3

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

An analytics team in an enterprise retail organization is designing a BigQuery analytical table for sales transactions. The table ingests approximately 40 GB of new data daily and will store several years of historical transactions.

The team identified the following query access patterns and operational constraints:

Most queries filter on transaction time windows ranging from a single day to multiple weeks
Queries frequently filter by store_id (medium cardinality), followed by customer_id (high cardinality), and occasionally include order_status
Data analysts require accurate upfront byte and cost estimates before executing their queries
The solution must maximize block pruning and minimize slot consumption during scan and filter operations

How should the table be partitioned and clustered to optimize query performance and cost?

Partition the table by an integer range on store_id and cluster the table by transaction_timestamp and customer_id.

Partition the table by DATE(transaction_timestamp) and cluster the table by store_id, customer_id, and order_status.

Cluster the table by transaction_timestamp, store_id, and customer_id without configuring table partitioning.

Partition the table by DATE(transaction_timestamp) and cluster the table by customer_id, order_status, and store_id.

Professional Cloud Data Engineer

Bigquery Partitioning Clustering Optimization 3

Related questions