Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
A SaaS company uses Cloud Spanner for a multi-tenant analytics platform experiencing high tail latencies, write bottlenecks, and rising infrastructure costs:
Customer and Events rows are frequently read and written together within the same transaction.AuditLog table uses Timestamp as the leading primary key column.Which combination of schema and resource configuration changes should you implement to minimize cross-node communication, eliminate hotspots, and control operational costs?
Events in a separate root table with an auto-incrementing sequence primary key, move AuditLog to a separate database in the same instance, and provision fixed capacity sized for peak load.This configuration keeps Events as an independent root table using a monotonically increasing sequence key, isolates AuditLog into a separate logical Spanner database within the same instance, and allocates a static number of nodes sized for maximum expected traffic.
Events in a separate root table breaks physical co-location with Customer. Transactions accessing both tables are much more likely to span multiple database splits and servers, adding network latency and distributed two-phase commit overhead.Events table, reproducing the same hotspot problem.AuditLog to another database inside the same Spanner instance leaves its timestamp-ordered primary key intact, and databases in the same instance still share underlying compute nodes and suffer from single-split write bottlenecks.The workload had completely steady, predictable 24/7 traffic with no overnight drop-off, Events and Customer were rarely accessed together, and primary keys used bit-reversed sequences or pseudo-random UUIDs instead of sequential values.
AuditLog primary key to (EventType, Timestamp), and purchase Committed Use Discounts for peak capacity.This design applies a table-per-tenant multi-tenancy pattern inside one database, places EventType ahead of Timestamp in the AuditLog primary key without sharding, and uses Committed Use Discounts (CUDs) sized to cover maximum peak traffic.
(EventType, Timestamp) fails to prevent hotspots whenever a high volume of writes shares the same EventType (such as a default or dominant event category), because all writes for that category still land on the end of one key range.ShardId prefix remain vulnerable to skewed traffic across popular attribute values.The platform served a small, bounded number of tenants requiring table-level logical separation, writes were naturally balanced across thousands of EventType values, and traffic remained consistently near peak levels around the clock.
Events inside the Customer parent table, prefix the AuditLog primary key with a hashed ShardId column, and enable Spanner autoscaling with defined minimum and maximum compute capacity limits.Table interleaving in Cloud Spanner physically co-locates child rows (Events) alongside their parent row (Customer) inside the same row tree. Application-level key sharding adds a bounded ShardId column (typically 0 to N-1, generated via hashing) as the leading primary key prefix to distribute sequential writes evenly across splits. Spanner autoscaling dynamically adjusts processing units (PUs) or nodes within configured minimum and maximum boundaries based on workload demand.
Events inside Customer allows Spanner to keep each customer's entire row tree in a single database split. Queries and transactions that read or write a customer and their events together avoid cross-split network RPCs and distributed two-phase commit overhead.AuditLog primary key with a hashed ShardId spreads concurrent timestamped inserts evenly across multiple splits rather than concentrating all writes at the end of a single split.ShardId preserves efficient retrieval for specific keys while preventing lock contention on monotonically increasing timestamps.Unlike separate root tables or unsharded secondary indexes, this design addresses both data locality and write distribution at the schema layer while matching compute capacity to real-time traffic. It delivers predictable low latency for transactional operations without overprovisioning compute resources.
Events as a root table with a STORING secondary index on Timestamp, create a new AuditLog table each day, and run a single-node instance without autoscaling.This configuration leaves Events un-interleaved with an added covering (STORING) secondary index on Timestamp, partitions AuditLog manually by generating daily tables, and restricts the Spanner instance to a fixed single-node deployment.
Timestamp column creates a severe index hotspot on every insert. Adding STORING columns further increases storage footprint and write amplification.AuditLog table per day still funnels all concurrent writes for the current day into a single active table with a timestamp-ordered key. Over time, daily tables also consume the per-database table limit (5,000 tables) and add schema management overhead.Events as a root table misses the cross-split reduction benefits of interleaving with Customer. Furthermore, a single-node instance without autoscaling cannot absorb business-hour traffic spikes and lacks production headroom.STORING clauses accelerate specific read queries at the expense of higher storage usage and slower writes.The environment were a non-production development or functional testing setup with minimal traffic, where minimizing baseline cost with a single-node instance outweighed production throughput and availability needs.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated