A data engineering team at an enterprise IoT company is architecting a high-volume BigQuery data warehouse table that ingests 2 TB of telemetry data daily. The data contains an event_timestamp, a high-cardinality device_id, a low-cardinality device_type, and sensor metric payloads. The analytical workload has the following characteristics: Most analytical queries filter data within specific date ranges (e.g., the last 7 to 30 days). Routine operational queries filter heavily on specific device_id values. Aggregation dashboards frequently group and calculate metrics by device_type. The organization operates under BigQuery on-demand compute pricing and must strictly minimize query execution time and total bytes scanned. Which table design strategy should the data engineer implement to achieve optimal query performance and cost efficiency?

Partition the table daily by `event_timestamp` and cluster the table by `device_id` and `device_type`.: ### What Partitioning and Clustering in BigQuery Is **Table partitioning** divides a large table into smaller, manageable segments based on a date, timestamp, or integer range column. **Clustering** sorts the underlying columnar data based on one or more user-specified columns (up to four) within each partition, colocating related data into granular storage blocks. ### How It Meets the Requirements - **Date range filtering**: Partitioning by `event_timestamp` ensures that queries filtering on recent date windows (e.g., past 7–30 days) perform **partition pruning**, eliminating unnecessary partition scans entirely. - **High-cardinality filtering (`device_id`)**: Clustering by `device_id` sorts and organizes records into distinct storage blocks, enabling BigQuery to skip scanning irrelevant data blocks (block pruning). - **Aggregation performance (`device_type`)**: Including `device_type` in the clustering specification colocates identical device types together within the blocks, speeding up aggregate calculations and group-by operations. - **Cost minimization under on-demand pricing**: Because on-demand pricing charges per byte scanned, combining partition pruning and clustered block elimination drastically cuts both byte consumption and query latency. ### Key Advantages - **Automatic reclustering**: BigQuery manages block maintenance and background reclustering automatically without consuming user reservation slots or impacting active queries. - **Multi-column optimization**: Enables simultaneous multi-dimensional filtering across time and categorical attributes. - **Granular block skipping**: Scans only the specific Capacitor columnar blocks needed for the filtered IDs. ### Why This Is Optimal Combining time-based partitioning with multi-column clustering leverages BigQuery's two primary storage organization mechanisms simultaneously. This provides the lowest possible bytes scanned for time-series analytical workloads.

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

Bigquery Partitioning Clustering Telemetry Optimization

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 at an enterprise IoT company is architecting a high-volume BigQuery data warehouse table that ingests 2 TB of telemetry data daily. The data contains an event_timestamp, a high-cardinality device_id, a low-cardinality device_type, and sensor metric payloads.

The analytical workload has the following characteristics:

Most analytical queries filter data within specific date ranges (e.g., the last 7 to 30 days).
Routine operational queries filter heavily on specific device_id values.
Aggregation dashboards frequently group and calculate metrics by device_type.
The organization operates under BigQuery on-demand compute pricing and must strictly minimize query execution time and total bytes scanned.

Which table design strategy should the data engineer implement to achieve optimal query performance and cost efficiency?

Store the telemetry data in Cloud Storage using CSV files and query them via BigLake external tables partitioned by date.

Cluster the table by event_timestamp, device_id, and device_type without configuring table partitioning.

Partition the table daily by event_timestamp and cluster the table by device_id and device_type.

Partition the table by device_type and cluster the table by event_timestamp and device_id.

Professional Cloud Data Engineer

Bigquery Partitioning Clustering Telemetry Optimization

Related questions