Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Bigtable Row Keys and BigQuery Clustering for Telemetry
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
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, andvehicle_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?
Explanations
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 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.
What This Option Proposes
This option consolidates all operational and analytical workloads into BigQuery and relies on traditional explicit B-tree indexes for point lookups.
Why It Doesn't Meet the Requirements
- No Explicit B-Tree Indexes in BigQuery: BigQuery does not use or support explicit B-tree or unique primary indexes for transactional row lookups. It relies on columnar storage (Capacitor), partitioning, and clustering for scan reduction.
- Latency Profile Unsuitable for Operational Reads: While BigQuery handles high-throughput streaming inserts, analytical query start-up and execution latency (typically several seconds) does not meet operational low-latency requirements (sub-100ms) for real-time fleet operations.
Key Characteristics and Limitations
- BigQuery decouples storage and compute for massively parallel analytical queries, not real-time operational transaction processing.
- Explicit relational database indexes do not exist in BigQuery's columnar storage architecture.
This Answer Would Be Correct If
BigQuery offered low-latency row indexing engines and all queries were strictly ad-hoc business intelligence aggregations with relaxed latency tolerances.
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 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_iddistributes write traffic evenly across cluster tablet splits (preventing write hotspotting caused by monotonically increasing timestamps). Appending areverse_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.
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 by creating a separate table for each vehicle, and store analytical data in BigQuery using date-sharded tables (telemetry_YYYYMMDD).
What This Option Proposes
This option isolates each vehicle's time-series data into its own dedicated Bigtable table and uses date-sharded tables (telemetry_YYYYMMDD) in BigQuery.
Why It Doesn't Meet the Requirements
- Bigtable Instance Table Limits: Bigtable enforces a hard recommendation and limit of no more than 1,000 tables per instance. Creating an individual table for each vehicle across a fleet of millions of vehicles will immediately exhaust instance limits and degrade cluster management.
- BigQuery Sharding Overhead: Table sharding requires BigQuery to maintain separate schema and metadata copies for every daily table, verifying permissions and schema consistency individually. This adds query planning overhead and degrades scan performance compared to native partitioning.
Key Characteristics and Limitations
- Bigtable is optimized for multi-tenant datasets stored in a small number of large tables using structured row keys.
- Date-sharded tables in BigQuery increase metadata overhead and query latency compared to unified partitioned tables.
This Answer Would Be Correct If
The total number of tracked entities was fewer than 100, and BigQuery table partitioning features were unavailable.
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 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.
What This Option Proposes
This option places a monotonically increasing timestamp at the beginning of the Bigtable row key and applies integer-range partitioning to BigQuery based on vehicle_id.
Why It Doesn't Meet the Requirements
- Bigtable Hotspotting: Beginning a row key with a timestamp is a critical anti-pattern in Bigtable. Because Bigtable stores rows in lexicographical order, all concurrent writes for the current timestamp target the exact same tablet and node, bottlenecking ingestion throughput.
- Inefficient Bigtable Reads: Retrieving the historical time series for a single vehicle requires scanning across all timestamp ranges (or performing expensive multi-range lookups) rather than a single contiguous range scan.
- BigQuery Misalignment: BigQuery integer-range partitioning on millions of vehicle IDs either exceeds partition limits (maximum 4,000 partitions per table) or creates millions of tiny, sub-10 GB partitions, degrading metadata cache performance and preventing partition pruning across historical date ranges.
Key Characteristics and Limitations
- Leading timestamps concentrate write load onto a single node at any given moment.
- Range queries cannot target individual devices contiguously.
- Integer-range partitioning is designed for small, discrete integer ranges, not high-cardinality entity identifiers.
This Answer Would Be Correct If
Writes were evenly pre-distributed across time and operational lookups routinely scanned all devices across a single point in time rather than querying a specific device's history.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
Cloud Spanner Columnar Engine
Analyzing data access patterns
AdvancedProfessional Data Engineer
AlloyDB HTAP PostgreSQL Compatibility
Analyzing data access patterns
AdvancedProfessional Data Engineer
BigQuery Reservation Slot Allocation Priority
Capacity management (e.g., BigQuery Editions and reservations)
AdvancedProfessional Data Engineer
Cloud Storage Object Lifecycle Management
Planning for storage costs and performance
AdvancedProfessional Data Engineer
BigQuery VPC Service Controls RLS Dynamic Data Masking
Identity and Access Management (e.g., Cloud IAM and organization policies)
AdvancedProfessional Cloud DevOps Engineer
Predictive Autoscaling with Initialization Period
Performance and latency issues
Last updated Validated