GCP Professional Data Engineer Practice Question

Your utility company streams 20 TB of smart-meter readings into BigQuery every day. Each record has meter_id (INT64), reading_time (TIMESTAMP), and consumption_kwh. Two dominant query patterns exist: (1) a near-real-time dashboard that aggregates all meters' data for the last 48 hours, and (2) ad-hoc support queries that fetch several weeks of history for a single meter_id. To cut scan costs yet keep the dashboard under 5 seconds, which table design should you deploy?

Use ingestion-time partitioning on _PARTITIONTIME with no clustering.
Partition the table by DATE(reading_time) on a daily basis and cluster by meter_id.
Partition the table by an integer range on meter_id (for example, 10,000-row buckets) and cluster by reading_time.
Keep the table unpartitioned and rely on automatic column pruning to reduce scanned bytes.

Report Issue

Answer Description

Partitioning a table on a DATE-typed timestamp column means queries that filter on that column can prune entire partitions and read only the relevant days. Because the dashboard always restricts results to the most recent 48 hours, daily partitions on DATE(reading_time) limit the scan to two partitions, sharply reducing processed bytes and latency. Adding clustering on meter_id orders data within each daily partition, so a query that specifies a single meter_id touches only the small set of blocks containing that value, lowering cost and improving speed. Partitioning by meter_id would force the dashboard to scan every partition for the last two days; ingestion-time partitioning may mis-align when late data arrives; leaving the table unpartitioned forfeits any pruning. Therefore, daily partitioning on DATE(reading_time) combined with clustering on meter_id best satisfies both cost and performance requirements.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.