An IoT platform stores sensor readings in BigQuery. Every dashboard query specifies a reading time window, and many queries also filter by sensor_id. What table design fits this access pattern best?
Show answer and explanation
Answer: C
Partitioning on the reading timestamp lets BigQuery prune every partition outside the requested window, so the scan covers only the relevant days. Clustering on sensor_id narrows the scan further within each partition. An integer epoch loses native time semantics and time-unit partitioning, string timestamps cannot be partitioned as true time values, and per-day tables multiply the objects that must be managed and queried.
Why the other options are wrong
- A. An integer epoch has no calendar meaning for date functions and cannot use native time-unit partitioning, so pruning by a date window becomes awkward and error-prone.
- B. Daily sharded tables create many objects to manage, and every window query needs a wildcard or UNION. That is harder to maintain than one partitioned table with clustering.
- D. Lexical sorting of strings is fragile when timestamp formats vary, and string columns cannot use native time partitioning, so partition pruning on the window is lost.
Exam tip. For time-bounded queries in BigQuery, partition on the DATE, TIMESTAMP or DATETIME column the filters use.