Google Professional Data Engineer

Get started today

Ultimate access to all questions.

Explanation:

The correct data model involves creating a separate metrics table partitioned by timestamp, which allows for efficient querying based on time intervals, ideal for monitoring and analytical purposes. Including a sensorId column in the metrics table to reference the sensors table ensures a clear relationship between the two, facilitating easy joins when necessary. Using an INSERT statement every 30 seconds to append new metrics to the metrics table is the appropriate method for adding new data at regular intervals without impacting existing data.

Other options are less suitable because:

Adding a nested column in the sensors table for metrics is inefficient for analytical queries and monitoring, leading to unnecessary data duplication and complex querying.
Using UPDATE statements to add new metrics every 30 seconds is inadvisable as it updates existing records rather than appending new ones, risking data inconsistency and complicating historical data tracking.
While using an INSERT statement to add new metrics is closer to the correct approach, doing so in the sensors table instead of a separate metrics table can result in data redundancy and complicate querying.

Explanation:

Other options are less suitable because:

Adding a nested column in the sensors table for metrics is inefficient for analytical queries and monitoring, leading to unnecessary data duplication and complex querying.
Using UPDATE statements to add new metrics every 30 seconds is inadvisable as it updates existing records rather than appending new ones, risking data inconsistency and complicating historical data tracking.
While using an INSERT statement to add new metrics is closer to the correct approach, doing so in the sensors table instead of a separate metrics table can result in data redundancy and complicate querying.

Comments (0)

No comments yet.

You are working for a farming company that utilizes a BigQuery table named 'sensors', which is 500 MB in size and contains information on 5000 sensors, including their id, name, and location. The table is updated hourly, and each sensor generates a metric every 30 seconds, complete with a timestamp that needs to be stored in BigQuery. Your task is to choose the most suitable data model for storing these sensor metrics to facilitate weekly analytical queries while minimizing costs. What would be the best approach?

Real Exam

Create a metrics column in the sensors table. 2. Set RECORD type and REPEATED mode for the metrics column. 3. Use an UPDATE statement every 30 seconds to add new metrics.

8.3%

Create a metrics column in the sensors table. 2. Set RECORD type and REPEATED mode for the metrics column. 3. Use an INSERT statement every 30 seconds to add new metrics.

12.5%

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.

Create a metrics table partitioned by timestamp. 2. Create a sensorId column in the metrics table, which points to the id column in the sensors table. 3. Use an UPDATE statement every 30 seconds to append new metrics to the metrics table. 4. Join the two tables, if needed, when running the analytical query.

12.5%