Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
BigQuery Date Partitioning with Ordered Clustering
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
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 bycustomer_id(high cardinality), and occasionally includeorder_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?
Explanations
Cluster the table by transaction_timestamp, store_id, and customer_id without configuring table partitioning.
Cluster the table by transaction_timestamp, store_id, and customer_id without configuring table partitioning.
What the Option Is
This approach relies entirely on table clustering across timestamp and entity attributes on an unpartitioned table without creating distinct physical partitions.
Why It Doesn't Meet the Requirement
- Fails Upfront Cost Estimation: When querying a clustered-only table, BigQuery cannot provide an accurate query cost estimate before execution because the exact number of storage blocks to be scanned is determined dynamically at runtime.
- Suboptimal Time Pruning: While clustering on
transaction_timestampsorts storage blocks, it does not physically segment data into independent partition files, resulting in less deterministic partition pruning compared to date partitioning.
Key Characteristics and Limitations
- Runtime-only Cost Discovery: The final bytes processed can only be calculated after the query finishes scanning blocks.
- No Per-Partition Lifecycle Management: Unpartitioned tables cannot take advantage of separate long-term storage pricing transitions or partition expiration policies per date.
This Answer Would Be Correct If
This strategy would be appropriate if individual date partitions would contain less than 10 GB of data, or if the table exceeded the maximum limit of 4,000 partitions across its lifecycle.
Additional Context
Clustering alone is ideal for tables requiring sorting across many high-cardinality dimensions where strict pre-execution cost estimation is not a business requirement.
Partition the table by DATE(transaction_timestamp) and cluster the table by customer_id, order_status, and store_id.
Partition the table by DATE(transaction_timestamp) and cluster the table by customer_id, order_status, and store_id.
What the Option Is
This design correctly partitions by DATE(transaction_timestamp), but clusters columns in the order of customer_id, order_status, and store_id.
Why It Doesn't Meet the Requirement
- Violates Clustering Sort Order Best Practices: BigQuery uses the column order specified in the
CLUSTER BYclause to organize and sort storage blocks from left to right. Becausestore_idis placed at the end of the clustering definition, queries filtering only bystore_id(orstore_idandcustomer_id) cannot effectively prune blocks. - Increased Slot Consumption: Skipping the leading clustering column (
customer_id) in queries that filter primarily bystore_idprevents optimal block pruning, forcing workers to scan unnecessary blocks and consume more slot time.
Key Characteristics and Limitations
- Strict Left-to-Right Precedence: The first clustered column is the most effective in block pruning, followed by the second, and then the third.
- Reduced Pruning Efficiency: Queries that do not filter on the leading clustering key cannot take full advantage of BigQuery block metadata.
This Answer Would Be Correct If
This column ordering would be optimal if queries predominantly filtered on customer_id as the primary filter before narrowing down by order_status and store_id.
Additional Context
To maximize block pruning, always order clustering columns in the CLUSTER BY clause according to the frequency and hierarchy of how columns appear in query WHERE clauses.
Partition the table by an integer range on store_id and cluster the table by transaction_timestamp and customer_id.
Partition the table by an integer range on store_id and cluster the table by transaction_timestamp and customer_id.
What the Option Is
This design uses integer-range partitioning on store_id to physically segment data into numeric buckets, while clustering the data inside each bucket by timestamp and customer identifier.
Why It Doesn't Meet the Requirement
- Misaligned Partitioning Key: The scenario states that most queries filter primarily on date/time ranges. Partitioning by
store_idforces queries filtering on time windows to scan across all store partitions, resulting in massive partition scans. - Inefficient Time Window Filtering: Queries filtering on date ranges would have to rely on block pruning across every store partition rather than pruning whole partitions upfront, leading to higher slot usage.
Key Characteristics and Limitations
- Integer Range Constraints: Integer-range partitioning requires predefined start, end, and interval parameters that cannot easily adapt if store IDs are sparse or non-sequential.
- Degraded Time Query Performance: Without time-unit partitioning, calculating upfront costs for temporal queries is impossible.
This Answer Would Be Correct If
This pattern would be correct if queries exclusively filtered on numeric store ranges without temporal filters, or in multi-tenant systems where tenant ID is an integer and queries never span tenants.
Additional Context
Always choose the primary query filter dimension (most commonly date/time in append-heavy transaction datasets) as the table partitioning key.
Partition the table by DATE(transaction_timestamp) and cluster the table by store_id, customer_id, and order_status.
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 bystore_idalone orstore_idcombined withcustomer_idfollow 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_idand medium-cardinality fields likestore_idbenefit 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.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
BigQuery SCD Denormalization with Nested STRUCTs
Designing the data model
AdvancedProfessional Data Engineer
BigQuery Nested and Repeated Fields
Designing the data model
AdvancedProfessional Data Engineer
Private Service Connect NAT Subnet Firewall
Networking fundamentals
AdvancedProfessional Data Engineer
Google Cloud Data Platform Architecture
Building a data platform based on requirements by using Google Cloud tools (e.g., Dataplex, Dataplex Catalog, BigQuery, Cloud Storage)
AdvancedProfessional Data Engineer
Eventarc Asynchronous Event-Driven Architecture
Designing system for fault tolerance and managing restarts
FundamentalAssociate Data Practitioner
BigQuery SQL Data Validation
Assess data quality
Last updated Validated