Best BigQuery Data Model for Streaming Sensor Metrics with Cost Minimization?

You work for a farming company. You have one BigQuery table named sensors, which is about 500 MB and contains the list of your 5000 sensors, with columns for id, name, and location. This table is updated every hour. Each sensor generates one metric every 30 seconds along with a timestamp, which you want to store in BigQuery. You want to run an analytical query on the data once a week for monitoring purposes. You also want to minimize costs. What data model should you use?

1. Create a metrics column in the sensors table.
1. Create a metrics column in the sensors table.
1. Create a metrics table partitioned by timestamp. Source Reference Answer
1. Create a metrics table partitioned by timestamp.

Community Votes

65%

20%

15%

65% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The exam tests whether you recognize time-series metrics should be in an append-only partitioned table, not in a column on the existing sensors table; the trap is confusing BigQuery's nested/repeated fields or partition limits with best practice.

For high-frequency sensor data, create a separate BigQuery metrics table partitioned by timestamp and join to the sensors metadata table only for weekly queries. This minimizes cost by enabling partition pruning and keeping the small sensors table stable.

Choosing B (or A), which attempts to store metrics in a column on the sensors table, because it avoids a join but creates severe write amplification: every 30-second insert would require rewriting the sensor row, and a weekly analytical query would process the entire table.

Community Discussion (11 comments)

raaad 👍 10

Partitioned Metrics Table: Creating a separate metrics table partitioned by timestamp is a standard practice for time-series data like sensor readings. Partitioning by timestamp allows for more efficient querying, especially when you're only interested in a specific time range (like weekly monitoring). Reference to Sensors Table: Including a sensorId column that references the id column in the sensors table allows you to maintain a relationship between the metrics and the sensors without duplicating sensor information. INSERT Every 30 Seconds: Using an INSERT statement every 30 seconds to the partitioned metrics table is a standard approach for time-series data ingestion in BigQuery. It allows for efficient data storage and querying. Join for Analysis: When you need to analyze the data, you can join the metrics table with the sensors table based on the sensorId, allowing for comprehensive analysis with sensor details.

plum21 👍 1 Selected: C

C. B is not feasible – update on the metrics column will be required in such a case or an insert with all sensor data with one-element array of metrics which does not make any sense.

Pime13 👍 2 Selected: C

This approach offers several advantages: Cost Efficiency: Partitioning the metrics table by timestamp helps reduce query costs by allowing BigQuery to scan only the relevant partitions. Data Organization: Keeping metrics in a separate table maintains a clear separation between sensor metadata and sensor metrics, making it easier to manage and query the data2. Performance: Using INSERT statements to append new metrics ensures efficient data ingestion without the overhead of frequent updates

7787de3 👍 2 Selected: C

Because "Minimize costs" was requested, i would go for C. Storage cost will be lower for partitions where no writes took place for a certain amount of time, see https://cloud.google.com/bigquery/pricing#storage Partitioning by timestamp can be configured to use daily, hourly, monthly, or yearly partitioning - so if you choose daily partitioning, the number of partitions should not be an issue. Working with RECORDS (A,B) would be an option if performance was in focus.

dac9215 👍 3

Option C will not violate partitioning limit of 4000 as the lowest grain of partitioning is hourly

vbrege 👍 4 Selected: B

Here's my logic (some people have already said same thing) Cannot be C and D - Total 5000 sensors are sending new timestamp every 30 seconds. If you partition this table with timestamp, you are getting partitions above 4000 (single job) or 10000 (partition limit) so option C and D don't look correct - For C and D, also need to consider that BigQuery best practices advise to avoid JOINs and use STRUCT and RECORD types to solve the parent-child join issue. Now coming back to A and B, we will be adding sensor readings for every sensor. I don't think this is a transactional type database where you need to update data. You will add new data for more accurate analysis later so A is discarded. BigQuery best practices also advise to avoid UPDATE statements since its an Analytical columnar database B is the correct option.

Gloups 👍 3 Selected: A

Since BigQuery tables are limited to 4000 partitions, options C & D are discarded. Option B is wrong as insertion is invalid too. So option A.

anushree09 👍 4

I'm in favor of Option B Reason: BQ has nested columns feature specifically to address these scenarios where a join would be needed in a traditional/ relational data model. Nesting field will reduce the need to join tables, performance will be high and design will be simple

96f3bfa 👍 1 Selected: C

Option C

Matt_108 👍 2 Selected: C

Option C

scaenruy 👍 1 Selected: C

C. 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.

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Option C separates metadata from time-series data, which is the standard BigQuery data model for sensor events.
Partitioning by timestamp means the weekly monitoring query can prune to the relevant date range and scan far less data, lowering cost.
The sensors table remains small and is updated hourly; the metrics table grows append-only with INSERT statements, making ingestion simple and efficient.

Why the Other Options Are Wrong

Options A and B add metrics to the sensors table. This forces either UPDATE rewrites for every 30-second metric or storing an array that must be unnest/rewritten, and it bloats the small table causing high query cost. Option B's nested/repeated field is often cited, but it is not suited for continuous row-level inserts.
Option D is similar to C but puts the timestamp in the sensors table and still appends metrics to the sensors table, incorrectly storing many rows per sensor in a metadata table. It also fails to create a proper metric partition strategy.
Partition-limit objections (comment 2) miss that timestamp partitioning can be daily/hourly, so 5000 sensors do not create 4000+ partitions.

Community Comment Notes

Comment 6 and 9 correctly explain that partitioning by timestamp lowers cost and lets you separate metadata from metrics; comment 9 gives a clear step-by-step model matching option C.
Comment 8 points out that B is infeasible because updating a metrics column with every insert is not a workable pattern.
Comment 2 argues C/D exceed partition limits, but comment 4 corrects this by noting BigQuery partition granularity is configurable (hourly/daily/monthly/yearly), so C remains valid.

Official Reference

Exam Strategy

Always separate low-cardinality metadata from high-cardinality event data when the events are append-only and queried by time range. Look for 'partitioned by timestamp' in the correct option and be wary of options that store metrics in the metadata table; mention partition pruning when 'minimize costs' is required.

Related Analysis

← Back to PDE Study Guide