PCDE Design innovative, scalable, and highly available cloud database solutions Practice Test 3 — 15 Questions

Question 1

A company needs to migrate its on-premises Oracle database to a fully managed relational database on Google Cloud. The application uses stored procedures and requires high availability across multiple zones. Which migration path is MOST appropriate?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Migrate to Cloud SQL for PostgreSQL and configure cross-zone high availability. Cloud SQL for PostgreSQL supports high availability across zones and can use the PostgreSQL dialect with some migration effort. Bare Metal Solution is for lifting and shifting Oracle workloads but is not fully managed. AlloyDB is PostgreSQL-compatible and provides high availability. The best option is Cloud SQL for PostgreSQL due to its managed nature and cross-zone HA.

Answer

Use Database Migration Service to migrate to Cloud SQL for SQL Server

Answer

Use Bare Metal Solution to run Oracle Database on dedicated hardware

Answer

Migrate to AlloyDB for PostgreSQL with cross-region replication

Question 2

A team is designing a Spanner schema for an online gaming leaderboard. The leaderboard stores player scores and requires high write throughput. Which primary key design is BEST to avoid write hotspots?

Accepted Answer

Primary key: (HashOfPlayerId, Timestamp). Monotonically increasing keys (like score or timestamp) cause hotspotting. Using a random prefix (e.g., hash of player ID) ensures writes are distributed across splits. Player ID alone might also cause hotspotting if many players write simultaneously.

Answer

Primary key: (PlayerId, Timestamp)

Answer

Primary key: (Score, PlayerId)

Answer

Primary key: (Timestamp, PlayerId)

Question 3

A financial services firm needs a database for real-time fraud detection that requires single-digit millisecond latency for lookups on a precomputed feature set. The dataset is 5 TB and grows by 1 TB per month. Which database service BEST meets these requirements?

Accepted Answer

Cloud Bigtable. Bigtable is designed for high-throughput, low-latency access to large datasets. It can handle 5 TB (or more) with SSD nodes. Cloud SQL and Spanner have lower throughput limits. BigQuery is not real-time.

Answer

BigQuery

Answer

Cloud Spanner

Answer

Cloud SQL (MySQL)

Question 4

You need to estimate the number of Bigtable nodes required for a workload of 50,000 reads per second (QPS) and 20,000 writes per second. Each node can handle 10,000 QPS for reads or writes. Storage is not a constraint. What is the minimum number of nodes required?

Accepted Answer

5 nodes. Reads require 50,000/10,000 = 5 nodes. Writes require 20,000/10,000 = 2 nodes. The higher value is the bottleneck, so 5 nodes are needed.

Answer

2 nodes

Answer

7 nodes

Answer

10 nodes

Question 5

Which of the following is a benefit of using parent-child interleaved tables in Cloud Spanner?

Accepted Answer

Improved read performance for queries joining parent and child. Interleaving stores child rows physically close to their parent row, enabling fast joins and reducing read latency. It does not improve write throughput or eliminate the need for secondary indexes.

Answer

Increased write throughput by distributing writes

Answer

Automatic sharding across regions

Answer

Eliminates the need for secondary indexes

Question 6

A team is migrating a MongoDB application to Firestore. The data model includes embedded documents and references between collections. Which approach should they follow to maintain similar query performance?

Accepted Answer

Use subcollections for embedded data and references for relationships. Firestore supports subcollections (similar to embedded documents) and references. Denormalization can reduce the need for joins. Avoiding transactions for every write is not ideal for consistency. The recommended pattern is to use subcollections for embedded data and references for related entities.

Answer

Flatten all data into a single document per entity

Answer

Replicate data across multiple collections to avoid reads

Answer

Use a single collection for all documents and rely on client-side joins

Question 7

A company is designing a database solution for a global social media application that requires strong consistency, high write throughput, and complex relational queries. Which TWO Google Cloud databases should they consider? (Choose 2)

Accepted Answer

Cloud Spanner. Cloud Spanner provides global strong consistency and relational support. AlloyDB offers strong consistency and high performance for relational workloads. Bigtable is eventually consistent. BigQuery is analytical. Firestore is not globally consistent.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Firestore

Question 8

A gaming company is designing a leaderboard using Cloud Spanner. The leaderboard updates scores in real time and supports queries for top players. Which THREE strategies should they implement to achieve high performance and avoid hotspots? (Choose 3)

Accepted Answer

Use global secondary indexes with STORING clause to avoid back-to-base lookups. Salting distributes writes, storing top scores in a separate table reduces contention, and using a STORING clause in indexes prevents data lookup. Global secondary indexes without STORING cause extra round trips.

Answer

Use a monotonically increasing primary key for scores

Answer

Use a single table with all data and rely on Spanner auto-scaling

Question 9

A startup is building an IoT analytics platform that ingests sensor data at high velocity and needs to run real-time dashboards and ad-hoc queries on the data. Which TWO Google Cloud databases should they use together? (Choose 2)

Accepted Answer

Cloud Bigtable. Bigtable is ideal for real-time ingestion and retrieval of sensor data with low latency. BigQuery is used for analytical queries and dashboards. Cloud SQL and Spanner are not optimized for high-velocity IoT ingestion. Firestore is more suited for mobile apps.

Answer

Cloud Spanner

Answer

Firestore

Answer

Cloud SQL

Question 10

A financial services company runs a high-frequency trading application that requires strong consistency, horizontal scalability, and low-latency transactions across multiple regions. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, strongly consistent, horizontally scalable relational database service designed for mission-critical applications like trading. Cloud SQL is not multi-region, Bigtable does not support SQL/ACID, and Firestore is not relational.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 11

You are designing a Cloud SQL for PostgreSQL instance for an OLTP application. The application typically handles 500 concurrent connections and the working set is 8 GB. You estimate a buffer pool of 4 GB. What minimum memory allocation should you choose?

Accepted Answer

15 GB. Cloud SQL PostgreSQL recommends max_connections = RAM_MB/16, but this is a soft limit. More importantly, memory must accommodate the working set and buffer pool. With 500 connections, 8 GB working set + 4 GB buffer pool = 12 GB, but PostgreSQL also needs overhead. A safe minimum is 15 GB, but the smallest Cloud SQL tier with >12 GB is 15 GB (e.g., db-custom-2-15360). However, among the options, 15 GB is the only viable choice. Note: max_connections formula suggests 15GB RAM gives ~960 connections, which covers 500.

Answer

12 GB

Answer

8 GB

Answer

26 GB

Question 12

An e-commerce platform uses Cloud Spanner with a table Orders and a child table OrderItems. The primary key of Orders is (CustomerId, OrderId) where OrderId is a UUID. The primary key of OrderItems is (CustomerId, OrderId, ItemId). However, writes to OrderItems are creating hotspots. What is the most likely cause?

Accepted Answer

The leading key (CustomerId) is monotonically increasing. Hotspots occur when writes are concentrated on a small range of keys. Since OrderId is a UUID, it's already random. However, using CustomerId as the first part of the primary key can cause hotspots if certain customers place many orders. But more commonly, if OrderItems uses the same CustomerId and OrderId, and many items are inserted for the same order, they will be interleaved and written sequentially. Still, the hotspot is due to the leading key CustomerId being monotonically increasing if customers are assigned IDs sequentially. The best answer is that the primary key design leads to concentrated writes because CustomerId is not distributed well. However, the question likely expects that the primary key design is correct (UUID) but the hotspot might be due to not using a hash prefix. Actually, in Spanner, the first key part should be distributed. If CustomerId is sequential (e.g., auto-increment), it causes hotspots. So the cause is a monotonically increasing leading key. The correct answer should point to the leading key being monotonically increasing.

Answer

Using UUID for OrderId causes random writes

Answer

The primary key is too long

Answer

The parent-child interleaving is not defined correctly

Question 13

A team is migrating a MySQL OLTP database to Cloud Spanner. The existing schema uses auto-increment primary keys. They plan to convert them to STRING columns with UUIDs. However, the application also relies on ORDER BY on the original integer key. How should they preserve ordering while avoiding hotspots in Spanner?

Accepted Answer

Use the UUID as the primary key and create a secondary index on the original integer. Using UUID as the primary key avoids hotspotting because UUIDs are random and distribute writes evenly across splits. Creating a secondary index on the original integer key allows efficient ORDER BY queries on that column. While the secondary index may experience some hotspotting due to monotonically increasing values, Spanner's automatic split management can mitigate this, and the approach meets both requirements: hotspot avoidance on the primary key and ordering capability on the original integer.

Answer

Keep the auto-increment key but use bit-reversal

Answer

Use a composite primary key with a hash prefix followed by the UUID

Answer

Use a monotonically increasing custom ID and rely on Spanner's split management

Question 14

A startup needs a database for a global user base with low-latency reads and writes, strong consistency, and the ability to scale horizontally without downtime. They anticipate variable traffic. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner provides global distribution, strong consistency, horizontal scaling, and no downtime for schema changes or scaling. Cloud SQL is not global, Bigtable does not have strong consistency, and Firestore is not global with strong consistency for multi-region.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 15

You are running Cloud Bigtable for time-series analytics. Each row represents a metric and uses a row key of format 'metricID#timestamp' (e.g., 'cpu_usage#2023-08-01T00:00:00Z'). You notice that writes are concentrated on a small number of nodes. What is the most effective way to distribute writes more evenly?

Accepted Answer

Add a hash prefix of the metricID to the row key. The row key design is poor because metricID is a limited set (e.g., cpu_usage) and timestamp is increasing, so all writes for a metric go to a single tablet. Salting by prepending a hash of the metricID (or using a field promotion with a hash prefix) distributes writes across tablets. Reversing timestamp helps with reads but not write distribution. Using a different column family does not affect row key distribution. Increasing nodes only helps if data is distributed, but the hotspot will remain.

Answer

Use a different column family for each metric

Answer

Increase the number of nodes

Answer

Reverse the timestamp in the row key