hardMultiple ChoiceObjective-mapped
PCDE A company stores sensor data in BigQuery Practice Question
A company stores sensor data in BigQuery. They have a table 'sensor_readings' with columns: sensor_id, reading_time, value. The table is partitioned by reading_time (hourly) and clustered by sensor_id. A BI query aggregates average value per sensor for the last week. The query still scans many bytes. What is the most likely cause?
⚠ Common exam trap
Google Cloud often tests the misconception that clustering alone solves all performance issues, but the trap here is that clustering only helps when the query filters or aggregates on the clustered column—without such a filter, clustering does not reduce bytes scanned, and overly fine partitioning is the real culprit.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Partition granularity is too fine for the query range
The query scans a full week of data (168 hourly partitions), and each partition must be read entirely even though only a subset of sensors may be active. Hourly partitioning over a 7-day range means the query engine must scan all 168 partitions, which can result in a large number of bytes being processed. Clustering on sensor_id helps within each partition but does not reduce the number of partitions scanned; the fine granularity of hourly partitioning is the primary cause of excessive bytes scanned.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The query uses SELECT * instead of specific columns
Why it's wrong here
Selecting * increases bytes but the question implies aggregation on value only; still, the main issue is partition count.
- ✗
Clustering on sensor_id is ineffective
Why it's wrong here
Clustering on sensor_id is appropriate for grouping.
- ✗
The table is not using columnar storage
Why it's wrong here
BigQuery is columnar by default.
- ✓
Partition granularity is too fine for the query range
Why this is correct
Hourly partitions for a week means 168 partitions scanned; coarser partitioning (daily) would scan 7 partitions, reducing bytes.
Go deeper
Related to this question
About these practice questions
This PCDE question is part of Courseiva's 1,446-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
5 more ways this is tested on PCDE
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are designing a Cloud Bigtable schema for a time-series application that stores temperature readings from sensors. Each reading has a sensor ID (string), a timestamp (microseconds), and a temperature value. Queries always filter by sensor ID and a time range. Which row key design is optimal?
easy- A.[sensor_id]#[timestamp]
- ✓ B.[salted_hash]#[sensor_id]#[reversed_timestamp]
- C.[timestamp]#[sensor_id]
- D.[sensor_id]#[reversed_timestamp]
Why B: Optimal because it uses a salted hash to distribute writes across Bigtable tablets, avoiding hot-spotting on a single node for high-write sensors. The sensor_id ensures all data for a sensor is co-located for efficient range scans, and the reversed timestamp allows queries for the most recent data to be served from the start of the row range, leveraging Bigtable's lexicographic ordering.
Variation 2. A company is designing a schema for time-series sensor data in Cloud Spanner. They need to efficiently query the latest reading for each sensor. Which schema design is most appropriate?
easy- A.Use a single table with columns for each sensor and wide rows
- B.Use Cloud SQL with a normalized schema
- ✓ C.Create a Sensors table and an interleaved Readings table with primary key (SensorId, Timestamp DESC)
- D.Use Cloud Bigtable with row keys (SensorId#Timestamp)
Why C: The most appropriate because it uses an interleaved Readings table under the Sensors table with the primary key (SensorId, Timestamp DESC). This allows efficient retrieval of the latest reading for each sensor by taking the first row per SensorId using the descending timestamp order. Interleaving ensures that rows for the same sensor are stored together, minimizing cross-node reads. Option A (single table with wide rows) leads to large rows and poor scalability. Option B (Cloud SQL) is not designed for high-throughput time-series data at scale. Option D (Bigtable) is a good choice for time-series but the question specifically requires using Cloud Spanner.
Variation 3. A team is designing a schema for a time-series database in Bigtable to store IoT sensor readings. Each sensor sends a reading every minute. The team needs to create a row key that supports efficient queries for a specific sensor's readings over a time range. Which row key design is most appropriate?
easy- A.timestamp#sensor_id
- B.hash(sensor_id)#timestamp
- ✓ C.sensor_id#reverse_timestamp
- D.random_UUID
Why C: Bigtable stores rows sorted lexicographically by row key. By placing the sensor_id first, all readings for a given sensor are co-located in contiguous rows. Using reverse_timestamp (e.g., 9999-12-31 minus actual timestamp) ensures that the most recent readings appear first within that sensor's row range, which optimizes scans for the latest data and allows efficient range queries over a time window.
Variation 4. An engineer is designing a Bigtable schema for a weather data application. The data is written by thousands of sensors, each generating a reading every minute. Queries typically retrieve all readings for a sensor in a time range. The row key should be designed to avoid hotspots and support these queries. Which two row key components are recommended? (Choose two.)
easy- A.Sensor ID (raw) as the only key
- B.Sensor location as a column
- ✓ C.Reversed timestamp
- D.Timestamp in natural order
- ✓ E.Hash of sensor ID as prefix
Why C: Using a reversed timestamp (e.g., Long.MAX_VALUE - timestamp) as part of the row key spreads writes across Bigtable tablets, avoiding hotspots that occur when sensors write sequentially in natural time order. This design also supports efficient range scans for a sensor's data over a time range when combined with a sensor ID prefix.
Variation 5. You need to design a Bigtable row key for a time-series application that records temperature readings from thousands of sensors. The most common query is 'get all readings for a specific sensor in the last hour'. Which row key design is optimal?
medium- A.timestamp#sensorID
- B.sensorID#timestamp
- ✓ C.sensorID#reverse_timestamp
- D.hash(sensorID)#timestamp
Why C: Optimal because it groups all readings for a sensor together (via sensorID as the row key prefix) while using reverse timestamps to ensure the most recent data appears first within each row. This design allows Bigtable to efficiently scan a single row for the last hour's readings using a prefix scan on sensorID with a timestamp range filter, minimizing the number of rows accessed.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.