professional-cloud-data-engineer
Prepare and test your skills
Prepare and test your skills
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:
StudyCerts
StudyCertsstore_id (medium cardinality), followed by customer_id (high cardinality), and occasionally include order_statusHow should the table be partitioned and clustered to optimize query performance and cost?
This solution combines time-unit table partitioning on DATE(transaction_timestamp) with multi-column clustering on store_id, customer_id, and order_status.
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.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.customer_id and medium-cardinality fields like store_id benefit from block metadata min/max pruning.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.
Keep the momentum going with these hand-picked practice scenarios
Want more questions like this?
Get a free certification question every week.