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.

StudyCerts

Professional Cloud Data Engineer

professional-cloud-data-engineer

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Optimize Bigtable Bigquery Schema Access Paths

Section: Analyzing data access patterns

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 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 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 vehicle_id#reverse_timestamp, and load analytical data into BigQuery partitioned by event_date and clustered by region, fleet_tier, and vehicle_model.

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.

Professional Cloud Data Engineer

professional-cloud-data-engineer

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Optimize Bigtable Bigquery Schema Access Paths

Section: Analyzing data 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 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 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.

Explanations

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.

View source documentation

Professional Cloud Data Engineer

Optimize Bigtable Bigquery Schema Access Paths

Professional Cloud Data Engineer

Optimize Bigtable Bigquery Schema Access Paths

What the Recommended Architecture Is

How It Meets the Requirements

Key Advantages and Capabilities

Why This Is Optimal

Related questions

Looker Interactive Dashboard Filters