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

Question 1

A startup is building a mobile app that needs offline support and real-time synchronization across devices. The data is primarily user profiles and activity logs. Which Google Cloud database would best meet these requirements?

Accepted Answer

Firestore. Firestore provides offline data persistence and real-time sync, making it ideal for mobile apps. Cloud SQL and Spanner lack built-in offline support, and Bigtable is not suitable for complex document data.

Answer

Cloud Spanner

Answer

Cloud SQL

Answer

Cloud Bigtable

Question 2

A global e-commerce company needs a database for its order processing system that can handle high write throughput across multiple regions, with strong consistency and 99.999% availability. The data model is relational with joins. Which database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it is a globally distributed, horizontally scalable relational database service that provides strong consistency across regions, 99.999% availability (five nines), and supports SQL joins. It uses synchronous replication and the TrueTime API to deliver ACID transactions at global scale, meeting the high write throughput and consistency requirements.

Answer

Firestore

Answer

Cloud SQL (PostgreSQL)

Answer

Cloud Bigtable

Question 3

You are designing a Cloud Spanner schema for a global user database. Users are identified by a UUID. You notice hotspotting on a specific node during writes. What is the most effective way to resolve this?

Accepted Answer

Use a composite primary key with a hash prefix of the UUID. Hotspotting in Cloud Spanner occurs when writes are concentrated on a single node due to a monotonically increasing primary key (like a UUID without a hash prefix). By using a composite primary key with a hash prefix of the UUID, you distribute writes evenly across all nodes because the hash prefix ensures that successive writes land on different splits. This prevents a single node from becoming a bottleneck, thereby resolving the hotspotting issue.

Answer

Enable interleaved tables to store child rows with parent

Answer

Change the primary key to a monotonically increasing timestamp

Answer

Add a secondary index on the UUID column

Question 4

A company runs a high-traffic web application that reads session data frequently. They want to reduce latency and offload database load. Which Google Cloud service should they use as a caching layer?

Accepted Answer

Memorystore for Redis. Memorystore (Redis) is the managed caching service on GCP, ideal for session stores and reducing database load. Cloud SQL, Bigtable, and Firestore are not suitable as low-latency caches.

Answer

Cloud SQL

Answer

Firestore

Answer

Cloud Bigtable

Question 5

Your Bigtable cluster is experiencing high latency for a table that stores IoT sensor data. The row key format is deviceID#timestamp. You discover that most reads query the last hour of data for a few devices. How can you optimize row key design to improve read performance?

Accepted Answer

Reverse the timestamp in the row key (e.g., deviceID#MAXTIME - timestamp). Option A is correct because reversing the timestamp (e.g., deviceID#MAXTIME - timestamp) converts the row key from monotonically increasing to more evenly distributed. In Bigtable, rows are sorted lexicographically by row key; with deviceID#timestamp, all writes for a device go to the same tablet server, causing a hotspot. By reversing the timestamp, recent data for a device is spread across multiple tablets, reducing write contention and improving read latency for the last hour of data.

Answer

Separate frequently accessed data into a different column family

Answer

Use a single table with a composite key including device type

Answer

Add a salt prefix to the row key

Question 6

You need a managed database service for a lift-and-shift migration of an on-premises SQL Server 2019 OLTP application with up to 64 TB of data. Which Google Cloud database should you choose?

Accepted Answer

Cloud SQL. Cloud SQL is the correct choice because it supports SQL Server 2019, provides managed database services with up to 64 TB of storage (via the Enterprise Plus tier with 64 TB SSD capacity), and is ideal for lift-and-shift migrations of OLTP workloads without requiring application changes. It offers high availability, automated backups, and compatibility with SQL Server features like T-SQL and linked servers, making it a direct replacement for on-premises SQL Server.

Answer

Cloud Bigtable

Answer

AlloyDB

Answer

Cloud Spanner

Question 7

A financial services firm needs a database for real-time fraud detection. The workload requires sub-millisecond read latency on transactions and the ability to run complex SQL analytics on the same data. Which database should they choose?

Accepted Answer

AlloyDB. AlloyDB is correct because it combines PostgreSQL-compatible SQL analytics with sub-millisecond read latency for transactional workloads, using a columnar engine for analytical queries and a memory-optimized row store for real-time transactions. This dual-engine architecture allows the same data to serve both OLTP and OLAP requirements without data movement or latency trade-offs.

Answer

BigQuery

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 8

Your Cloud Spanner database runs a query that uses a secondary index. The query plan shows an 'Index Join' operation. What can you do to eliminate this join and improve query performance?

Accepted Answer

Use the STORING clause to include the needed columns in the index. The 'Index Join' operation in Cloud Spanner occurs when a secondary index does not contain all the columns needed by the query, forcing Spanner to join the index with the base table to retrieve the missing data. By using the STORING clause (also known as covering index), you include the required columns directly in the index, allowing Spanner to satisfy the query entirely from the index without accessing the base table, thus eliminating the join and improving performance.

Answer

Change the index to a local index

Answer

Create a new interleaved table

Answer

Rewrite the query as a join

Question 9

Which Google Cloud database service provides automatic failover with synchronous replication and 0 RPO within a single region?

Accepted Answer

Cloud SQL (HA configuration). Cloud SQL with HA configuration provides synchronous replication to a standby instance in the same zone (or different zone) and automatic failover with 0 RPO. Spanner replicates across regions, Firestore multi-region replication is not synchronous, Bigtable cluster replication is asynchronous.

Answer

Cloud Spanner

Answer

Cloud Bigtable

Answer

Firestore

Question 10

A company uses Cloud Bigtable for AdTech clickstream data. They notice that some tables have many deleted rows and the storage cost is increasing. What should they configure to automatically remove deleted data and reduce storage?

Accepted Answer

Configure a garbage collection policy based on time or number of versions. Cloud Bigtable automatically compacts data in the background, but deleted rows are not immediately removed; they remain as tombstones until a garbage collection policy is applied. By configuring a garbage collection policy based on time or number of versions, you instruct Bigtable to permanently delete expired data and reclaim storage space, reducing costs.

Answer

Set up cluster replication to a secondary cluster

Answer

Enable compaction on the table

Answer

Use IAM conditions to restrict write access

Question 11

Your application uses Cloud Spanner with strong reads, but you are experiencing high latency for read requests. You don't need absolute consistency for every read; stale data up to 5 seconds is acceptable. How can you reduce read latency?

Accepted Answer

Use bounded staleness reads with a max staleness of 5 seconds. Using bounded staleness allows reads to be served from replicas that may be slightly stale (up to 5 seconds), reducing latency. Read timestamps and DML are not relevant, and global indexes would not help.

Answer

Use the read timestamp to read the latest data

Answer

Create a global secondary index on frequently read columns

Answer

Use DML instead of mutations for writes

Question 12

A company needs to analyze terabytes of log data for business intelligence. The queries are complex SQL aggregations and run periodically. The data is read-only after ingestion. Which Google Cloud database is most suitable?

Accepted Answer

BigQuery. BigQuery is the correct choice because it is a serverless, highly scalable data warehouse designed for petabyte-scale analytics using SQL. It excels at complex aggregations over terabytes of read-only log data, with automatic partitioning and columnar storage that optimize query performance for periodic analytical workloads.

Answer

Cloud SQL

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 13

A gaming company wants to store player profiles and game state data that require strong consistency and the ability to run SQL queries. They also need to support real-time leaderboards with high write throughput. Which two Google Cloud databases should they consider? (Choose 2)

Accepted Answer

Memorystore for Redis. Cloud Spanner provides strong consistency and SQL, while Memorystore for Redis can be used for real-time leaderboards with high throughput. Bigtable and Firestore do not have SQL, and AlloyDB is not ideal for high-throughput leaderboards.

Answer

Cloud Bigtable

Answer

AlloyDB

Answer

Firestore

Question 14

You are designing a Cloud Bigtable schema for a time-series application that stores metrics from millions of devices. The row key is currently deviceID#timestamp. You want to avoid hotspotting on writes and optimize scan performance for reading all data from a specific device within a time range. Which two row key design strategies should you apply? (Choose 2)

Accepted Answer

Add a salt prefix to the row key (e.g., hash of deviceID). Option A is correct because adding a salt prefix (e.g., a hash of the deviceID) distributes write load across multiple tablet servers, preventing hotspotting on a single node when many devices write concurrently. This ensures that sequential timestamps for the same device are not all written to the same tablet, which would otherwise cause a bottleneck.

Answer

Store data in multiple tables per device

Answer

Use a single column family for all metrics

Answer

Promote the device type to the start of the row key

Question 15

Which three of the following are managed database services offered by Google Cloud that support SQL or SQL-like query languages? (Choose 3)

Accepted Answer

AlloyDB. Cloud SQL, Cloud Spanner, and AlloyDB all support SQL. Bigtable uses HBase API, not SQL. Memorystore is a cache, not a database with SQL.

Answer

Memorystore

Answer

Cloud Bigtable