A logistics enterprise is designing a data storage architecture on Google Cloud for a real-time fleet management platform. The system ingests high-frequency telemetry metrics from millions of connected vehicles and must support two primary access patterns: Operational path: High-throughput, low-latency writes and frequent sequential range reads retrieving recent metrics for a specific vehicle. Analytical path: Large-scale ad-hoc batch queries analyzing historical fleet performance across date ranges, filtered and grouped by attributes such as region, fleet_tier, and vehicle_model. You need to design the schema, row keys, and optimization strategies to maximize read/write performance, prevent hotspotting, and optimize query costs. Which storage and schema configuration should you recommend?

Store operational telemetry in Cloud Bigtable using a row key formatted as `vehicle_id#reverse_timestamp`, and load analytical data into BigQuery partitioned by `event_date` and clustered by `region`, `fleet_tier`, and `vehicle_model`.: ### What the Recommended Architecture Is This design pairs **Cloud Bigtable** as a high-throughput NoSQL wide-column database for point and range operational access with **BigQuery** as an enterprise columnar data warehouse optimized for large-scale analytical scans, leveraging native **table partitioning** and **multi-column clustering**. ### How It Meets the Requirements - **Operational Path (Cloud Bigtable)**: Designing the row key prefix as `vehicle_id` distributes write traffic evenly across cluster tablet splits (preventing write hotspotting caused by monotonically increasing timestamps). Appending a `reverse_timestamp` (e.g., `Long.MAX_VALUE - timestamp`) ensures that the most recent metric events for a specific vehicle appear first in lexicographical order, enabling low-latency sequential range scans. - **Analytical Path (BigQuery)**: Partitioning by a date/timestamp column (`event_date`) enables partition pruning, eliminating scanned bytes outside the query's time boundary. Clustering by high-cardinality filtering columns (`region`, `fleet_tier`, `vehicle_model`) sorts data within partitions into collocated storage blocks, accelerating multi-column filtering and aggregations. ### Key Advantages and Capabilities - **Eliminates Write Hotspotting**: Distributes incoming operational writes across Bigtable nodes based on high-cardinality vehicle identifiers rather than global timestamps. - **Optimizes Operational Range Scans**: Collocates historical data per vehicle, allowing single-row or narrow scan retrievals with sub-millisecond latencies. - **Cost-Effective Analytical Pruning**: BigQuery only scans physical storage blocks matching the date filter and cluster predicates, significantly lowering on-demand query costs and execution slot contention. - **Scalable Separation of Concerns**: Isolates real-time ingestion and transactional reads from heavy analytical query workloads. ### Why This Is Optimal Cloud Bigtable is purpose-built for single-row and sequential range queries at massive write throughput, whereas BigQuery excels at analytical scans. Combining partitioning with multi-column clustering in BigQuery offers finer-grained sorting than partitioning alone, outperforming unclustered, sharded, or row-based alternatives.

Optimize Bigtable Bigquery Schema Access Paths | professional-cloud-data-engineer

Optimize Bigtable Bigquery Schema Access Paths

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

A logistics enterprise is designing a data storage architecture on Google Cloud for a real-time fleet management platform. The system ingests high-frequency telemetry metrics from millions of connected vehicles and must support two primary access patterns:

Operational path: High-throughput, low-latency writes and frequent sequential range reads retrieving recent metrics for a specific vehicle.
Analytical path: Large-scale ad-hoc batch queries analyzing historical fleet performance across date ranges, filtered and grouped by attributes such as region, fleet_tier, and vehicle_model.

You need to design the schema, row keys, and optimization strategies to maximize read/write performance, prevent hotspotting, and optimize query costs.

Which storage and schema configuration should you recommend?

Store operational telemetry in Cloud Bigtable by creating a separate table for each vehicle, and store analytical data in BigQuery using date-sharded tables (telemetry_YYYYMMDD).

Store operational telemetry in Cloud Bigtable using a row key formatted as timestamp#vehicle_id, and load analytical data into BigQuery partitioned by vehicle_id with integer-range partitioning.

Store both operational and analytical telemetry in BigQuery using date partitioning, and create explicit B-tree primary indexes on vehicle_id to handle low-latency single-vehicle queries.

Store operational telemetry in Cloud Bigtable using a row key formatted as vehicle_id#reverse_timestamp, and load analytical data into BigQuery partitioned by event_date and clustered by region, fleet_tier, and vehicle_model.

Professional Cloud Data Engineer

Optimize Bigtable Bigquery Schema Access Paths

Related questions