A data engineering team is designing a multi-terabyte dataset in BigQuery to support recurring BigQuery ML model training and batch prediction workloads (ML.PREDICT). The workloads have the following operational characteristics: Model training queries regularly filter transactions over sliding time windows based on a transaction_timestamp column. Batch inference queries frequently filter and aggregate features on a high-cardinality customer_id column within specific time windows. Data engineers need upfront query cost estimates before executing training queries to prevent budget overruns. Which table design strategy in BigQuery should the team implement to optimize query performance and minimize data processing costs?

Partition the table by `transaction_timestamp` (day) and cluster the table by `customer_id`.: ### What Partitioning and Clustering Combined Is Combining **table partitioning** and **table clustering** in BigQuery organizes data physically into segmented partitions and sorts the underlying storage blocks within each partition based on designated high-cardinality keys. ### How It Meets the Requirements - **Sliding time window model training:** Partitioning the table by `transaction_timestamp` allows BigQuery to prune entire physical partitions when date filters are evaluated during training runs, scanning only the necessary time ranges. - **High-cardinality filtering for batch inference:** Clustering by `customer_id` sorts and groups data within each date partition, enabling **block pruning** when querying specific customers during batch prediction jobs. - **Upfront query cost estimation:** Partitioned tables maintain metadata that allows BigQuery to accurately calculate and display the total bytes to be processed before the query executes, satisfying cost control requirements. ### Key Advantages and Capabilities - **Dual-level data pruning:** Eliminates full table scans by pruning irrelevant date partitions and unreferenced clustering blocks simultaneously. - **Accelerated machine learning workflows:** Decreases query latency for feature retrieval during both iterative model training and batch inference. - **Granular cost management:** Restricts the scanned bytes billed under BigQuery on-demand analysis pricing or minimizes slot utilization under capacity-based editions. - **Long-term storage qualification:** Partitions not modified for 90 consecutive days automatically qualify for long-term storage pricing independently of active partitions. ### Why This Is Optimal This architecture optimizes both the temporal filtering inherent in time-series training pipelines and the high-cardinality lookups required during batch inference, while preserving deterministic cost governance before queries execute.

Optimize Bigquery Storage Partitioning Clustering Ml | professional-cloud-data-engineer

Optimize Bigquery Storage Partitioning Clustering Ml

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.

A data engineering team is designing a multi-terabyte dataset in BigQuery to support recurring BigQuery ML model training and batch prediction workloads (ML.PREDICT).

The workloads have the following operational characteristics:

Model training queries regularly filter transactions over sliding time windows based on a transaction_timestamp column.
Batch inference queries frequently filter and aggregate features on a high-cardinality customer_id column within specific time windows.
Data engineers need upfront query cost estimates before executing training queries to prevent budget overruns.

Which table design strategy in BigQuery should the team implement to optimize query performance and minimize data processing costs?

Create separate daily tables using date-sharded table suffixes (e.g., transactions_YYYYMMDD) without partitioning or clustering.

Partition the table by customer_id using integer range partitioning and cluster the table by transaction_timestamp.

Partition the table by transaction_timestamp (day) and cluster the table by customer_id.

Create an unpartitioned table clustered solely on customer_id and append LIMIT clauses to all ML training queries.

Professional Cloud Data Engineer

Optimize Bigquery Storage Partitioning Clustering Ml

Related questions