PCD Design Scalable and Highly Available Cloud Database Solutions Practice Test 5 — 15 Questions

Question 1

A company needs a globally distributed, strongly consistent relational database with horizontal scaling and a 99.999% SLA for a financial application. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides a globally distributed, strongly consistent relational database with horizontal scaling and a 99.999% SLA. It combines the benefits of relational database structure (ACID transactions and SQL queries) with horizontal scalability across regions, making it ideal for financial applications that require strict consistency and high availability.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 2

A company is designing a Cloud Spanner schema for a global user database. They anticipate high write throughput and want to avoid hotspotting on the primary key. Which primary key design strategy is MOST appropriate?

Accepted Answer

Use a UUID as the primary key. Using a composite key with a hash prefix or a monotonically increasing value as the first part of the key can cause hotspots. UUIDs distribute writes evenly across splits. Using a UUID as the leading part of a composite key avoids hotspotting better than the other options.

Answer

Use an auto-incrementing integer as the primary key

Answer

Use a timestamp as the primary key

Answer

Use a composite key with user_id and timestamp

Question 3

A Cloud Bigtable instance is experiencing high read latency on a table storing time-series data. The row key is a concatenation of metric_type, timestamp, and device_id. Most queries filter by metric_type and a time range. Which row key redesign would MOST improve read performance?

Accepted Answer

Promote metric_type to the first part of the row key. Field promotion moves the most frequently filtered column to the start of the row key. Since queries filter by metric_type and time range, placing metric_type first allows Bigtable to efficiently scan a contiguous range of rows.

Answer

Use a salted key by prepending a hash of the metric_type

Answer

Use a separate column family for the timestamp

Answer

Reverse the timestamp portion of the key to distribute writes

Question 4

A company wants to lift-and-shift an existing on-premises MySQL OLTP application to Google Cloud with minimal changes. They need up to 64 TB of storage and 96 vCPUs. Which database service should they use?

Accepted Answer

Cloud SQL for MySQL. Cloud SQL for MySQL supports up to 64 TB storage and 96 vCPU, and requires minimal changes for lift-and-shift. AlloyDB is PostgreSQL-compatible, not MySQL. Cloud Spanner is not a drop-in replacement for MySQL. BigQuery is for analytics.

Answer

Cloud Spanner

Answer

BigQuery

Answer

AlloyDB

Question 5

A Cloud Spanner database has a table Orders with a child table OrderItems. Queries often join Orders and OrderItems on OrderID. Which schema design optimizes performance for these joins?

Accepted Answer

Make OrderItems an interleaved table under Orders. Interleaved tables store child rows with their parent row in the same split, colocating related data and reducing cross-node communication. This is the correct optimization for parent-child joins.

Answer

Define a secondary index on OrderItems.OrderID

Answer

Use a global index on Orders.OrderID

Answer

Denormalize OrderItems data into the Orders table

Question 6

A mobile app needs to store user preferences and allow offline read-write sync when the device reconnects. The data is simple key-value pairs. Which Google Cloud database is MOST suitable?

Accepted Answer

Firestore. Firestore provides offline persistence and automatic sync, making it ideal for mobile apps. Cloud SQL does not have built-in offline sync. Bigtable is not designed for mobile clients. Memorystore is a cache, not a persistent database.

Answer

Memorystore for Redis

Answer

Cloud Bigtable

Answer

Cloud SQL

Question 7

A company needs to run a PostgreSQL-compatible database with 4x faster OLTP performance than standard PostgreSQL, and also wants to run analytical queries on the same data without extracting to a separate system. Which Google Cloud database should they choose?

Accepted Answer

AlloyDB. AlloyDB is a PostgreSQL-compatible database that delivers up to 4x faster OLTP performance than standard PostgreSQL through a combination of a columnar engine, adaptive indexing, and a disaggregated storage architecture. It also supports running analytical queries on the same data without extraction, using its built-in columnar engine for fast analytics on transactional data.

Answer

Cloud Spanner

Answer

Cloud SQL for PostgreSQL

Answer

BigQuery

Question 8

A global e-commerce application uses Cloud Spanner. Some queries need to read data that is up to 10 seconds stale to reduce latency. How can the application achieve this while minimizing read latency?

Accepted Answer

Use a stale read with a max staleness of 10 seconds. Cloud Spanner supports bounded staleness reads (stale reads) which allow reading data as of a specific timestamp in the past, reducing latency by avoiding strong read barriers. Using a read timestamp 10 seconds in the past achieves this.

Answer

Use a global secondary index

Answer

Use a strong read with a read timestamp

Answer

Use the Mutations API to read data

Question 9

A Cloud Bigtable cluster is under heavy write load, and you notice write latency spikes. The row key pattern is domain_reversed_timestamp (e.g., com.example.2024-01-15T10:00:00). Which row key modification would BEST reduce write hotspots?

Accepted Answer

Add a random salting prefix to the row key. The row key pattern `domain_reversed_timestamp` causes all writes for a given domain to hit the same tablet server, creating a hotspot under heavy load. Adding a random salting prefix (e.g., a hash of the domain or a random integer) distributes writes evenly across all nodes in the Bigtable cluster, eliminating the sequential write bottleneck. This is the standard anti-hotspot pattern for Bigtable row key design.

Answer

Promote the timestamp to the beginning of the key

Answer

Remove the domain reversal and use just timestamp

Answer

Use a single table with multiple column families

Question 10

A company wants to achieve 0 RPO for a Cloud SQL for MySQL instance. Which configuration should they use?

Accepted Answer

Configure a Cloud SQL HA instance. Option D is correct because Cloud SQL HA (high availability) instances use synchronous replication to a standby VM in a different zone within the same region, providing automatic failover with zero data loss (0 RPO). This configuration ensures that committed transactions are immediately replicated to the standby before acknowledging the commit, guaranteeing that no committed data is lost even if the primary zone fails.

Answer

Enable cross-region replication to a replica in another region

Answer

Enable automatic backups with point-in-time recovery

Answer

Use a read replica in the same zone

Question 11

A company needs a managed Redis instance for caching and pub/sub messaging in their application. Which Google Cloud service should they use?

Accepted Answer

Memorystore for Redis. Memorystore for Redis is a managed Redis service that supports caching and pub/sub. Memorystore for Memcached only supports caching. Cloud SQL and Firestore are not caching services.

Answer

Firestore

Answer

Cloud SQL

Answer

Memorystore for Memcached

Question 12

A Cloud Spanner query that uses a secondary index is taking longer than expected. The index includes a large payload column. How can the query performance be improved?

Accepted Answer

Add the payload column to the index using the STORING clause. Using the STORING clause in a secondary index stores the specified columns in the index, allowing queries to be satisfied from the index alone without a join back to the base table. This reduces read latency.

Answer

Use a composite key instead of an index

Answer

Change the index to a global index

Answer

Remove the index and use a full table scan

Question 13

A company is designing a Cloud Bigtable schema for a time-series application. The application writes data from millions of devices and reads recent data by device ID and time range. Which TWO design patterns should they implement to optimize performance? (Select TWO.)

Accepted Answer

Use a single table with a row key that combines device_id and reversed timestamp. To optimize reads by device ID and time range, promote device_id to the start of the row key for efficient scans. Reverse the timestamp to avoid hotspotting on recent writes. Column families separate access patterns but don't affect read efficiency directly. A single table is fine.

Answer

Use multiple tables, one per device

Answer

Use a salting prefix to distribute writes

Answer

Store frequently accessed columns in a separate column family from rarely accessed ones

Question 14

A financial services company is migrating a globally distributed trading application to Cloud Spanner. They need strong consistency and low-latency reads across regions. Which THREE configurations should they choose? (Select THREE.)

Accepted Answer

Use interleaved tables for related data. Interleaved tables physically co-locate parent and child rows in Cloud Spanner, enabling efficient joins and low-latency reads for related data. This is critical for a globally distributed trading application that requires strong consistency and fast access to hierarchical data, such as orders and their line items, without cross-node coordination.

Answer

Use stale reads with bounded staleness

Answer

Regional instance configuration to reduce latency

Question 15

A company uses Firestore in Native mode for a mobile app. They need to enforce security rules to allow users to read and write only their own data. Which TWO steps are required? (Select TWO.)

Accepted Answer

Write a security rule that checks if request.auth.uid == resource.data.user_id. Firestore Security Rules use the request.auth object to identify the user. Use resource.data.user_id to match the document's owner field. The rules must check request.auth.uid against the document's field. Configuring IAM roles at the project level is not granular enough. Enabling Datastore mode changes the API.

Answer

Enable Datastore mode instead of Native mode

Answer

Assign the roles/datastore.user IAM role to each user

Answer

Create a composite index on user_id and timestamp