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

Question 1

A financial analytics firm is deploying a Cloud Spanner instance for a global transaction ledger. They require that all reads and writes to any region observe the same transaction ordering, and that the system remains available if an entire region fails. They also need to minimize write latency for users in both North America and Europe. Which configuration should they choose?

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

Accepted Answer

A multi-region instance with default leader in North America and read-write replicas in both North America and Europe. A multi-region Spanner instance with read-write replicas in both North America and Europe provides synchronous replication, strong consistency, and low-latency writes for users in both regions. The default leader coordinates writes, and read-write replicas participate in quorum, ensuring that an entire region failure does not cause data loss or unavailability. This configuration directly satisfies the requirements for consistency, availability, and write latency.

Answer

A multi-region instance with default leader in Europe and read-only replicas in North America

Answer

Two separate regional instances in us-central1 and europe-west1 with application-level two-phase commit

Answer

A regional instance in us-central1 with a read-write replica in europe-west1

Question 2

A media streaming company uses Cloud Bigtable to store user viewing history. The row key is currently a combination of user_id and timestamp in reverse chronological order, formatted as user_id#reverse_timestamp. The company wants to optimize for two access patterns: (1) retrieve the most recent 10 viewing events for a given user, and (2) retrieve all viewing events for a given user within a specific date range. They also need to minimize storage costs. What should they do?

Accepted Answer

Maintain two separate tables: one with row key user_id#reverse_timestamp for recent events, and another with row key user_id#timestamp for date-range queries.. Bigtable does not support secondary indexes, so optimizing for both recent-event retrieval and date-range queries requires two tables with different row key orderings. One table uses reverse timestamp to fetch the latest events quickly, and the other uses forward timestamp for efficient range scans. This design trades additional storage for improved query performance across both access patterns.

Answer

Use a row key of user_id#timestamp and create a secondary index on reverse_timestamp for recent events.

Answer

Change the row key to user_id#timestamp and use a filter to retrieve the most recent events.

Answer

Keep the row key as user_id#reverse_timestamp and use a filter to retrieve events within a date range.

Question 3

A media company stores user-generated videos in Cloud Storage and metadata in Firestore. They need to run complex analytical queries on the metadata, such as aggregating views by category and joining with user demographics. The metadata is updated frequently, and they need near-real-time analytics. Which solution should they use?

Accepted Answer

Export Firestore data to BigQuery using the Firestore export to BigQuery feature and run queries there.. Exporting Firestore data to BigQuery enables complex analytical queries with near-real-time updates through the managed export feature. BigQuery is designed for large-scale analytics and supports SQL, joins, and aggregations, while Firestore continues to handle operational workloads efficiently.

Answer

Run analytical queries directly on Firestore using composite indexes.

Answer

Migrate the metadata to Cloud Bigtable and use its analytical capabilities.

Answer

Use Cloud SQL federated queries to query Firestore data.

Question 4

A retail company uses Firestore in Native mode for a mobile app catalog. The catalog data is read-heavy and rarely changes. They want to minimize read latency for users worldwide and reduce costs. What should they do?

Accepted Answer

Use Firestore's multi-region location to replicate data across regions for lower latency and higher availability.. Firestore multi-region locations replicate data across multiple regions within a continent, reducing read latency for users in those regions and increasing availability. This is the most suitable choice for a read-heavy mobile app catalog. Other options either use inappropriate database services, do not provide global low-latency reads, or add unnecessary complexity.

Answer

Set up Cloud SQL read replicas in multiple regions and query them from the mobile app.

Answer

Enable Firestore in Datastore mode to improve read performance.

Answer

Create a Cloud Bigtable instance and replicate the catalog data for fast reads.

Question 5

A healthcare company stores sensitive patient records in Cloud SQL for PostgreSQL. They need to ensure that database backups are encrypted with customer-managed encryption keys (CMEK) and that the keys are rotated regularly. They also need to maintain the ability to restore backups to a point in time within the last 30 days. Which combination of steps should they take?

Accepted Answer

Enable CMEK on the Cloud SQL instance, configure automatic backups with point-in-time recovery (PITR), and use Cloud KMS to rotate the key.. Enabling CMEK on the Cloud SQL instance ensures backups are encrypted with customer-managed keys. Configuring automatic backups with PITR provides the ability to restore to any point within the last 30 days. Cloud KMS key rotation automatically rotates the key and re-encrypts data, satisfying the rotation requirement. Together, these steps meet all specified needs without unnecessary custom scheduling.

Answer

Enable CMEK on the Cloud SQL instance, configure automatic backups without PITR, and manually rotate the key by creating a new key version.

Answer

Enable CMEK on the Cloud SQL instance, configure automatic backups with PITR, and schedule a Cloud Scheduler job to call the Cloud KMS API to rotate the key.

Answer

Use default Google-managed encryption, configure automatic backups with PITR, and use Cloud KMS to rotate the key.

Question 6

A retail company runs its order-management system on Cloud SQL for PostgreSQL with a 2 TB database and sustained peak load of 4,000 write transactions per second. The business requires a 99.99% availability SLA, near-zero-downtime maintenance, and the ability to add capacity without taking the database offline. The team wants to minimize application rewrites while moving to a managed service. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner with a PostgreSQL dialect interface, sized with enough nodes to absorb the write load.. Spanner is the only option that simultaneously satisfies a 99.99% SLA, horizontal write scaling, online capacity changes, and near-zero-downtime maintenance while allowing the existing PostgreSQL application to migrate with minimal rewriting. Cloud SQL and AlloyDB both cap writes at a single primary, and Bigtable sacrifices relational transactions and SQL entirely.

Answer

Cloud SQL for PostgreSQL configured with a high-availability (regional) instance and multiple read replicas.

Answer

Bigtable with a row key designed around order ID and a column family for transaction details.

Answer

AlloyDB for PostgreSQL with a read pool of multiple read replicas spread across zones.

Question 7

A healthcare company needs to store patient records in Cloud Spanner with strict data residency requirements. Data for European patients must remain in the EU, and data for US patients must remain in the US. The application must support global queries that join patient data across regions. What should they do?

Accepted Answer

Use two separate Spanner instances, one in europe-west1 and one in us-central1, and use federated queries to join data across instances.. To comply with data residency while enabling global queries, use separate Spanner instances in each region and leverage federated queries. This keeps data within its required region and allows cross-instance joins without data movement. Other options either replicate data across regions or split across incompatible systems, failing residency or global query requirements.

Answer

Use a single regional Spanner instance in us-central1 and store European data in a separate Cloud SQL database in europe-west1.

Answer

Use a single regional Spanner instance in europe-west1 with a read-only replica in us-central1.

Answer

Use a multi-region configuration with leader region in europe-west1 and read-write replicas in us-central1.

Question 8

A media company ingests 500,000 events per second from a global set of IoT devices. The events are written to Bigtable with a row key that concatenates a device type (10 possible values) and a reversed timestamp. The team observes severe hotspotting on a few tablet servers. They need to distribute writes evenly while preserving the ability to query the most recent events for a given device. Which row key design change should they make?

Accepted Answer

Prepend a hash of the device ID to the row key so writes are spread across tablets.. Prepending a hash of the device ID to the row key spreads writes uniformly across tablets because the hash values are evenly distributed. The reversed timestamp remains as a suffix, allowing efficient retrieval of the most recent events for a device via a prefix scan on the hash and device ID, then a range on the reversed timestamp. This balances write load and preserves query patterns.

Answer

Use a sequential timestamp as the first component of the row key to ensure chronological ordering.

Answer

Add a random integer suffix to the row key to spread writes across tablets.

Answer

Change the row key to use the device ID as the only component, dropping the timestamp.

Question 9

A company is designing a highly available database solution on Google Cloud using Cloud SQL for MySQL. They need to ensure that the database remains available during a zone failure and that failover is automatic. They also want to minimize data loss during failover. (Choose two.)

Accepted Answer

Configure the Cloud SQL instance with a regional (high availability) configuration.. A regional Cloud SQL instance provides automatic failover to a standby in another zone, and synchronous replication ensures that no data is lost during failover. These two features together deliver high availability and minimal data loss, meeting the company's requirements.

Answer

Create a read replica in a different region and configure automatic failover to it.

Answer

Enable point-in-time recovery (PITR) to minimize data loss during failover.

Answer

Enable binary logging and set the sync_binlog flag to 1.

Question 10

A healthcare company must design a database tier for a patient-records application that stores protected health information. Requirements: strong external consistency across three continents, 99.999% availability, fine-grained access control per table and column, and the ability to prove who accessed which record. They plan to use Cloud Spanner. Which two design choices should they make? (Choose two.)

Accepted Answer

Enable Cloud Audit Logs for Spanner data access and export them to a log sink for retention and analysis.. The multi-region instance configuration provides cross-continent strong consistency and the 99.999% SLA, while IAM database roles deliver table- and column-level access control. Data Access audit logs exported to a durable sink create the evidence trail needed to prove who accessed which record, which is the only combination that satisfies all four requirements.

Answer

Create a separate Spanner database per continent and use application-level conflict resolution with last-write-wins timestamps.

Answer

Use interleaved tables to co-locate patient records with their audit entries so that audit queries avoid distributed joins.

Answer

Rely on Spanner's automatic replication and IAM basic roles at the project level to control access to patient data.

Question 11

A financial institution runs a Cloud Spanner database for a global ledger. They need to design the schema to minimize read latency for point lookups by account ID and to avoid hotspots during heavy insert load. The application performs millions of point reads per second and also inserts new transactions at a steady rate. Which two schema design choices should they make? (Choose two.)

Accepted Answer

Interleave the Transactions table in the Accounts table using account ID as the common prefix.. Making account ID the first part of the primary key ensures point lookups are fast and predictable. Interleaving the Transactions table under Accounts co-locates related rows, reducing latency for reads that fetch transactions per account, and distributes write load across splits because inserts for different accounts go to different locations. Together, these choices optimize for both read latency and write scalability.

Answer

Use a monotonically increasing timestamp as the first part of the primary key for the Transactions table.

Answer

Add a randomly generated UUID as the first column of the primary key for the Transactions table.

Answer

Use a composite primary key with account ID and a reversed transaction timestamp for the Transactions table.

Question 12

A company is designing a global e-commerce platform using Cloud Spanner. They need to support strong consistency for transactions, horizontal scaling for writes, and low-latency reads for users worldwide. They also want to minimize cross-region latency for writes. Which two design choices should they make? (Choose two.)

Accepted Answer

Use a regional Cloud Spanner instance configuration and place read-only replicas in other regions for low-latency reads.. To minimize cross-region write latency while supporting global reads, a regional instance with read-only replicas in other regions is ideal. Additionally, designing primary keys to avoid hotspots ensures write scalability. Multi-region configurations increase write latency, and other options do not address the core requirements.

Answer

Enable Cloud Spanner's optimistic concurrency control to reduce write conflicts.

Answer

Use interleaved tables to co-locate related data and improve join performance.

Answer

Use a multi-region Cloud Spanner instance configuration to replicate data across continents.

Question 13

A startup runs an e-commerce catalog on Firestore in Native mode. Product documents are updated by an admin console, and a mobile app reads individual product documents by product ID with occasional queries filtered by category. The team needs the lowest possible read latency for the mobile app and wants to avoid maintaining servers. Which Firestore capability should they use to keep mobile reads fast as the catalog grows to millions of documents?

Accepted Answer

Create composite indexes for the category queries and rely on automatic single-field indexes for product ID lookups.. Firestore automatically maintains single-field indexes, so document reads by product ID are fast with no configuration. Queries that filter on category alongside other fields need composite indexes, and defining them keeps those queries index-backed. This combination delivers low-latency mobile reads without provisioning or managing any servers.

Answer

Use a collection group query across all product subcollections to reduce the number of indexes needed.

Answer

Add a Redis Memorystore cache in front of Firestore and invalidate entries whenever an admin updates a product.

Answer

Enable Datastore mode on the same database so that category queries can use ancestor queries for locality.

Question 14

A retail company runs a Cloud SQL for PostgreSQL instance that supports a web application. The database is experiencing high read traffic during peak hours, causing slow response times. The company wants to offload read queries to a separate instance without changing the application's connection string. Which Google Cloud feature should they use?

Accepted Answer

Configure a read pool in Cloud SQL for PostgreSQL to automatically distribute read queries.. Read pools in Cloud SQL for PostgreSQL provide a single endpoint that load-balances read-only queries across multiple read replicas. The application can use the same connection string, so no code changes are needed. This feature is designed to offload read traffic and improve performance during peak hours without manual routing.

Answer

Enable the Cloud SQL Auth Proxy and use it to route read queries to a replica automatically.

Answer

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

Answer

Use Cloud SQL's built-in query cache to serve read queries from memory.

Question 15

A logistics company uses Cloud Bigtable to store shipment tracking events. They need to design the row key to support two access patterns: (1) retrieve all events for a specific shipment ID, and (2) retrieve all events for a specific customer ID across all their shipments. The workload is write-heavy, and they want to avoid hotspots. Which row key design should they use?

Accepted Answer

Use a composite row key of shipment ID and reversed timestamp, and create a separate table with customer ID and shipment ID as the row key.. The primary table with shipment ID and reversed timestamp supports efficient retrieval of events per shipment. The secondary table with customer ID and shipment ID enables querying all shipments for a customer. This denormalization is common in Bigtable to support multiple access patterns, and it avoids hotspots because shipment IDs are high-cardinality and not monotonically increasing.

Answer

Use shipment ID as the row key, and create a separate table for customer ID lookups.

Answer

Use a composite row key of customer ID followed by reversed timestamp, and store shipment ID as a column.

Answer

Use a composite row key of customer ID, shipment ID, and reversed timestamp.