Associate Data Practitioner · Free Practice Question Medium
Question 18
A data engineer needs to load time-series data from IoT sensors into BigQuery for analysis. The data includes device ID, timestamp, and multiple sensor readings. Which schema design would be most efficient for querying by time ranges and device?
-
A
Create a table with separate columns for each sensor reading type
-
B
Create a table with a repeated field containing all sensor readings
-
C
Create separate tables for each device type
-
D
Create a partitioned table with clustering on device ID
Reveal correct answer
Correct answer: A
Explanation
Option D is CORRECT. Creating a partitioned table (by date) with clustering on device ID provides optimal query performance for time-series IoT data. Partitioning by date allows BigQuery to prune partitions when querying specific time ranges, significantly reducing the amount of data scanned. Clustering on device ID co-locates data from the same device, improving query performance when filtering on specific devices. This approach efficiently supports the common query pattern of analyzing data for specific devices over time ranges.
Option A is INCORRECT. While using separate columns for each sensor type is a valid approach for the sensor readings, this option doesn't address the optimization needs for querying by time ranges and device ID. Without partitioning and clustering, queries would still need to scan the entire table when filtering by date or device.
Option B is INCORRECT. Using a repeated field for sensor readings introduces complexity for time-series analysis. Querying individual reading types would require UNNEST operations, and this approach still doesn't address the optimization for querying by time ranges and device ID without partitioning and clustering.
Option C is INCORRECT. Creating separate tables for each device type would fragment the data, making it difficult to perform analysis across multiple device types. This approach creates management overhead as new device types are added and complicates queries that need to compare or aggregate across different device types.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
