Answer-first summary for fast verification
Answer: 1. Create a metrics table partitioned by timestamp. 2. Create a sensorId column in the metrics table, that points to the id column in the sensors table. 3. Use an INSERT statement every 30 seconds to append new metrics to the metrics table. 4. Join the two tables, if needed, when running the analytical query.
Option C is the correct answer. Creating a separate metrics table partitioned by timestamp is the standard practice for time-series data like sensor readings. This allows more efficient querying, especially when you're interested in a specific time range, such as weekly monitoring. Including a sensorId column referencing the id column in the sensors table helps maintain the relationship between metrics and sensors without duplicating sensor information. Using an INSERT statement every 30 seconds to append new metrics to the partitioned metrics table is efficient for time-series data ingestion in BigQuery. When analysis is needed, joining the metrics table with the sensors table based on sensorId allows comprehensive analysis with sensor details. Partitioning by timestamp and appending new metrics using INSERT reduces costs and complexity.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
As a data engineer at a farming company, you manage a BigQuery table named "sensors," which is approximately 500 MB in size. This table includes 5000 sensors with columns for id, name, and location and is updated every hour. Each sensor produces a metric every 30 seconds, accompanied by a timestamp, and you need to store these metrics in BigQuery for analysis. You plan to run an analytical query on the collected data once a week for monitoring purposes. Given these requirements and the goal of minimizing costs, what data model should you implement?
A
B
C
D