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

Question 1

A logistics company uses Cloud Spanner for a shipment tracking system that spans three continents. Their main workload is point reads and writes keyed by shipment_id, but they also need to run daily reports that aggregate shipments by destination region. The database is experiencing high CPU utilization during peak hours, and the operations team reports that many transactions are aborting. You need to design the schema to minimize hotspots and improve write throughput while keeping the reporting queries efficient. What should you do?

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

Accepted Answer

Use a composite primary key with destination region as the first column and shipment_id as the second, and create a secondary index on shipment_id.. Leading the primary key with destination region distributes writes across multiple key ranges, which minimizes hotspots and improves write throughput. The secondary index on shipment_id ensures that point reads remain efficient. This combination directly addresses the high CPU utilization and transaction aborts while supporting the daily reporting queries that aggregate by destination region.

Answer

Use a composite primary key with shipment_id as the first column and destination region as the second, and create a secondary index on destination region.

Answer

Hash the shipment_id and use the hash as the leading part of the primary key, then create an interleaved table for shipment events.

Answer

Use shipment_id as the primary key and add a secondary index on destination region.

Question 2

A media company stores user-generated video metadata in Cloud Spanner. The table `Videos` has a monotonically increasing `video_id` as the primary key, leading to write hotspots during peak upload periods. The team needs to distribute writes evenly across nodes without changing the application logic. What should they do?

Accepted Answer

Prepend a random shard number to the primary key.. Cloud Spanner hotspots occur when writes concentrate on a narrow key range, often due to monotonically increasing keys. Prepending a random shard number spreads writes across multiple ranges, balancing load across nodes while still allowing reads by video_id with a slight query modification. This approach maintains application logic with minimal changes and is a recommended best practice for high-write workloads.

Answer

Create a secondary index on video_id and use that for writes.

Answer

Enable interleaving of the Videos table with a parent table.

Answer

Use a hash of the video_id as the primary key.

Question 3

A healthcare company is designing a Cloud Spanner schema for a patient records system. The primary access patterns are: (1) retrieve a patient's record by patient_id, and (2) retrieve all patients for a given hospital_id. The system requires strong consistency and high availability across multiple regions. The team wants to optimize for these queries and minimize latency. Which two schema design choices should they make? (Choose two.)

Accepted Answer

Create a secondary index on hospital_id to efficiently retrieve all patients for a hospital.. Using patient_id as the primary key optimizes point lookups, and adding a secondary index on hospital_id enables efficient retrieval of patients by hospital. These two choices directly address the stated access patterns while maintaining strong consistency. They are schema-level optimizations that work within Spanner's distributed architecture.

Answer

Create an interleaved table for patient records with hospital_id as the parent key.

Answer

Enable multi-region configuration with a read-write leader in one region and read-only replicas in others.

Answer

Use a composite primary key of (hospital_id, patient_id) for the Patients table.

Question 4

A company runs a global e-commerce platform on Cloud Spanner. They need to design a schema for orders that allows efficient queries by customer ID and order date, and also supports queries by order status for fulfillment. The orders table will have billions of rows. They want to avoid hotspots and ensure scalability. What should they do?

Accepted Answer

Use a composite primary key with customer ID as the first column and order date as the second, and create a secondary index on order status.. The optimal schema uses a composite primary key starting with customer ID, which is high-cardinality and distributes writes, followed by order date for efficient customer-date queries. A secondary index on order status handles fulfillment queries. This design avoids hotspots and supports the required access patterns at scale.

Answer

Use a randomly generated UUID as the primary key and create secondary indexes on customer ID, order date, and order status.

Answer

Use a composite primary key with customer ID as the first column and order status as the second, and create a secondary index on order date.

Answer

Use a composite primary key with order status as the first column and order date as the second, and create a secondary index on customer ID.

Question 5

A logistics company uses Firestore in Native mode to track shipment locations. They need to query shipments by status and by a range of expected delivery dates. The data is updated frequently. They want to minimize the number of composite indexes while supporting these queries. What should they do?

Accepted Answer

Create a composite index on status (ascending) and expected_delivery_date (descending).. A composite index on status ascending and expected_delivery_date descending supports queries that filter by status and sort by date descending, and it can also serve ascending sorts by scanning in reverse. This single index minimizes the number of composite indexes required while covering the access pattern.

Answer

Create two separate single-field indexes, one on status and one on expected_delivery_date.

Answer

Create a composite index on expected_delivery_date (ascending) and status (ascending).

Answer

Create a composite index on status (ascending) and expected_delivery_date (ascending).

Question 6

A healthcare company is deploying a global patient records system on Cloud Spanner. The system must provide strong consistency and low-latency reads for patients in North America, Europe, and Asia. The primary access pattern is reading a patient's record by patient_id, and writes occur during patient registration and updates. The company wants to minimize read latency for users in all regions and ensure that reads reflect the latest writes. They also need to optimize for cost. What configuration should they choose?

Accepted Answer

Use a multi-region instance with read-write replicas in all three regions and set the leader region to North America.. A multi-region instance with read-write replicas in all regions ensures strong consistency because all replicas are voting members and reads from any replica return the latest committed data. Placing the leader in North America optimizes write latency for the primary user base, while users in Europe and Asia can read from local replicas with low latency. This configuration balances performance, consistency, and cost by using read-write replicas that serve both reads and writes.

Answer

Use a regional instance in North America with read-only replicas in Europe and Asia, and perform all reads from the nearest read-only replica.

Answer

Use a multi-region instance with read-write replicas in North America and Europe, and witness replicas in Asia, with the leader in North America.

Answer

Use a regional instance in each region and implement application-level replication with eventual consistency.

Question 7

A healthcare company stores patient records in Cloud Firestore in Native mode. They need to design a data model to support queries that retrieve all patients for a given doctor, sorted by last name, and also retrieve all appointments for a patient sorted by date. They want to minimize composite indexes and ensure strong consistency. What should they do?

Accepted Answer

Use a root collection 'patients' with a subcollection 'appointments' under each patient document. Store doctor references in the patient document and create a composite index on doctorId and lastName.. The subcollection model for appointments under each patient avoids the need for a composite index for the patient-appointment query. The composite index on doctorId and lastName supports the doctor-patient query. This design minimizes composite indexes and leverages Firestore's strong consistency for single-document reads and queries within a collection.

Answer

Use a root collection 'appointments' with a field 'patientId' and a root collection 'patients'. Create a composite index on patientId and date for appointments, and on doctorId and lastName for patients.

Answer

Use a root collection 'doctors' with a subcollection 'patients' under each doctor. Store appointments as a subcollection under each patient. Create a composite index on lastName within the subcollection.

Answer

Use a root collection 'patients' with an array field 'appointments' containing appointment objects. Create a composite index on doctorId and lastName.

Question 8

A team is designing a Firestore in Native mode database for a mobile app with millions of users. They need to support queries that filter on multiple fields and sort by a timestamp, and they want to minimize read costs and latency. Which two design practices should they follow? (Choose two.)

Accepted Answer

Create composite indexes for combinations of fields used together in queries.. Firestore requires composite indexes for multi-field queries and does not support joins, so denormalizing data to keep related fields in one document minimizes reads. These two practices directly reduce latency and cost. Collection group queries, storing large binaries, and offset pagination either do not address the cost goal or actively increase reads.

Answer

Use offset-based pagination to skip documents and reduce the number of documents read.

Answer

Store large binary objects directly in documents to avoid separate storage calls.

Answer

Use collection group queries to query across all subcollections with the same name.

Question 9

A financial analytics firm runs a Cloud SQL for PostgreSQL instance that supports a trading dashboard. The dashboard requires complex analytical queries involving multiple joins and aggregations over the last 30 days of trading data. These queries currently run on the primary instance during market hours and cause contention with the trading application's OLTP workload. The firm wants to offload the analytical queries to a read replica without modifying the application's connection logic. What should they do?

Accepted Answer

Create a read replica and use a database proxy such as PgBouncer with query routing rules to send analytical queries to the replica.. To offload analytical queries without changing application connection logic, a database proxy that performs read/write splitting is required. PgBouncer, when configured with routing rules or combined with a load balancer, can direct read-only queries to a read replica while sending writes to the primary. This maintains a single connection endpoint for the application and achieves workload isolation.

Answer

Configure the Cloud SQL instance to automatically route queries with multiple joins to a read replica using the built-in query optimizer.

Answer

Enable the Cloud SQL Auth Proxy and use its built-in read/write splitting feature to route analytical queries to a read replica.

Answer

Create a read replica and configure the application to connect to the read replica's IP address for analytical queries.

Question 10

A financial institution runs a global trading platform on Cloud Spanner. They need to ensure that read-write transactions across multiple regions have low latency and strong consistency. The application performs reads and writes to the same rows frequently. Which configuration should they use?

Accepted Answer

Use a multi-region configuration with leader region in us-central1 and read-write replicas in other regions.. For a global application requiring strong consistency and low latency for read-write transactions, Cloud Spanner's multi-region configuration with read-write replicas in multiple regions is the best choice. Read-write replicas allow read-only transactions to be served locally, reducing latency for reads. While writes are routed to the leader region, this configuration provides the highest availability and data durability. It balances performance and consistency for global workloads.

Answer

Use a regional configuration with all nodes in a single region.

Answer

Use a multi-region configuration with leader region in us-central1 and no replicas in other regions.

Answer

Use a multi-region configuration with leader region in us-central1 and read-only replicas in other regions.

Question 11

A startup is deploying a new web application on Google Cloud. The application uses a MongoDB-compatible document store for user profiles. The team expects rapid growth and wants a fully managed, scalable database service that automatically handles replication and failover. They also want to minimize operational overhead. Which Google Cloud service should they choose?

Accepted Answer

MongoDB Atlas on Google Cloud. MongoDB Atlas on Google Cloud is the only option that provides MongoDB compatibility while being fully managed. It handles replication, failover, and scaling automatically, reducing operational burden. The other options are either not MongoDB-compatible or require significant application changes, making them unsuitable for this startup's needs.

Answer

Cloud Bigtable

Answer

Firestore in Datastore mode

Answer

Cloud SQL for PostgreSQL

Question 12

A healthcare company stores patient records in Cloud Firestore. They need to ensure that queries for patient data by last name and date of birth return results sorted by last name in ascending order, and they require strong consistency for these queries. The data is updated frequently. What should they do?

Accepted Answer

Create a composite index on date_of_birth (ascending) and last_name (ascending), and query using an equality filter on date_of_birth and an orderBy on last_name.. Firestore composite indexes must be defined with fields in the order of equality filters first, then the sort field. For an equality filter on date_of_birth and an orderBy on last_name, the index should have date_of_birth ascending, then last_name ascending. Firestore queries are strongly consistent, so the results will reflect the latest writes.

Answer

Use a single-field index on last_name and rely on Firestore's automatic merging of results from multiple single-field indexes.

Answer

Create a composite index on last_name (ascending) and date_of_birth (ascending), and query using an equality filter on date_of_birth and an orderBy on last_name.

Answer

Store the patient records in a subcollection keyed by date_of_birth, and query each subcollection with an orderBy on last_name.

Question 13

A retail company is deploying a new Cloud SQL for MySQL instance for an order management system. They expect unpredictable traffic spikes during flash sales and want the database to automatically add read capacity without manual intervention. They also want to keep costs low during idle periods. What should they do?

Accepted Answer

Migrate to Cloud Spanner, which automatically scales read capacity based on load.. Cloud Spanner provides automatic scaling of compute capacity, including read throughput, and can scale down when idle, meeting both the automatic scaling and cost goals. Cloud SQL read replicas are manual and do not autoscale, high availability does not serve reads, and there is no CPU-based autoscaling for Cloud SQL replicas.

Answer

Enable high availability on the primary instance to handle read traffic.

Answer

Use Cloud SQL read replicas with automatic scaling based on CPU utilization.

Answer

Configure a read replica and manually add more replicas when traffic increases.

Question 14

A retail company is designing a Bigtable schema for storing product inventory updates. The row key is currently `product_id#timestamp`, where `product_id` is a fixed-length string and `timestamp` is in milliseconds. They need to optimize for two access patterns: (1) retrieve the latest inventory for a specific product, and (2) retrieve all inventory updates for a product within a time range. Which two changes should they make to the row key design? (Choose two.)

Accepted Answer

Keep the timestamp in ascending order to support time-range scans.. To optimize for both access patterns, keep the timestamp in ascending order for efficient time-range scans, and create a separate row for the latest inventory value to enable fast point reads. This design leverages Bigtable's lexicographic sorting for range queries and provides a dedicated row for the most recent update, avoiding full scans. It balances read performance for both historical and latest-data queries.

Answer

Reverse the timestamp to store the latest data first.

Answer

Use a salt prefix to distribute writes evenly.

Answer

Use a separate column family for the latest inventory value.

Question 15

A company is designing a Bigtable schema for a time-series application that stores sensor readings. The row key currently is (sensorId, timestamp) where timestamp is in milliseconds. They need to optimize for queries that retrieve the latest readings for a specific sensor and for queries that retrieve all readings for a sensor within a time range. They also want to avoid hotspots. Which two changes should they make? (Choose two.)

Accepted Answer

Use a composite row key of (sensorId, reversed timestamp) and keep the column family for readings.. Reversing the timestamp and using it as part of the row key after sensorId optimizes for latest-reading queries and time-range scans. It also avoids hotspots because writes for a single sensor are sequential but distributed across many sensors. The other options either break query patterns or do not address the hotspot issue effectively.

Answer

Add a hash prefix to the sensorId to distribute writes across multiple nodes.

Answer

Create a separate table for each sensor to isolate writes.

Answer

Store each reading as a separate row with a random row key to distribute writes.