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

Question 1

A retail company runs a Cloud SQL for PostgreSQL instance that serves a customer-facing order processing application. The database must remain available for writes even if the primary zone fails, and the recovery time objective (RTO) is under 60 seconds. They also want to minimize cost while meeting these requirements. What should they do?

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

Accepted Answer

Enable high availability (HA) on the instance by configuring a regional instance with a standby in a different zone.. For Cloud SQL for PostgreSQL, high availability is achieved by configuring a regional instance with a standby in a different zone. This provides automatic failover within seconds, satisfying the RTO. Read replicas, backups, or changing the engine do not deliver the required automatic failover and low RTO for a write-intensive application.

Answer

Migrate the database to a Cloud SQL for MySQL instance with binary logging enabled.

Answer

Create a read replica in a different region and manually promote it if the primary fails.

Answer

Use a single-zone instance and rely on automated backups with point-in-time recovery (PITR).

Question 2

A healthcare analytics team is designing a BigQuery solution for a dataset that ingests streaming vitals from hospital devices and must support both near-real-time dashboards and long-term historical cohort analysis. They need to control storage costs while keeping recent data fast to query. Which two design choices should they make? (Choose two.)

Accepted Answer

Use time-partitioned tables with partition expiration on the raw streaming table. Time-partitioned tables with partition expiration keep recent data fast to query while automatically removing old raw partitions to bound storage cost, and BigQuery long-term storage pricing automatically lowers the cost of retained historical data that is not modified for 90 days. Together these choices support near-real-time dashboards and long-term cohort analysis without manual data lifecycle management.

Answer

Load streaming data into Bigtable and query it with BigQuery federated queries for all dashboards

Answer

Configure the dataset default table expiration to 30 days and write all historical data into that same dataset

Answer

Store all historical data in a single unpartitioned table and rely on clustered columns only

Question 3

A healthcare company uses Cloud SQL for PostgreSQL to store patient records. The database is critical, and they need to ensure that in the event of a zone failure, the database remains available with minimal data loss and automatic failover. They also want to minimize downtime during planned maintenance. Which feature should they implement?

Accepted Answer

Enable high availability (HA) configuration for the Cloud SQL instance.. Enabling high availability in Cloud SQL for PostgreSQL provides a synchronous standby in a different zone. This ensures automatic failover with no data loss if the primary zone fails, and it allows for minimal downtime during maintenance. The other options involve manual processes or asynchronous replication, which do not meet the requirements.

Answer

Configure a read replica in a different zone and manually promote it if the primary fails.

Answer

Use Cloud SQL's built-in backup and restore feature to recover from failures.

Answer

Set up a cross-region read replica and use it for disaster recovery.

Question 4

A financial institution is designing a Cloud Spanner schema for a transaction ledger. The primary access pattern is to retrieve all transactions for a given account within a date range, and they need to optimize for both read and write performance. The table will have columns: account_id (string), transaction_date (date), transaction_id (string), amount (numeric), and details (string). Which schema design should they use?

Accepted Answer

Primary key: (account_id, transaction_date, transaction_id). No interleaving.. The primary key (account_id, transaction_date, transaction_id) ensures that all transactions for an account are stored together and sorted by date, enabling efficient range scans. This design avoids hotspots because account_id is not monotonically increasing. Interleaving is unnecessary because the access pattern is fully served by the primary key, and interleaving could introduce additional constraints without performance gains.

Answer

Primary key: (transaction_id, account_id, transaction_date). No interleaving.

Answer

Primary key: (transaction_date, account_id, transaction_id). No interleaving.

Answer

Primary key: (account_id, transaction_date, transaction_id). Interleave the table in a parent Accounts table.

Question 5

A media company uses Cloud Bigtable to store user interaction events. The row key is currently a random UUID, which ensures even distribution but makes it impossible to query events for a specific user efficiently. They need to retrieve all events for a given user, sorted by time descending. What should they change the row key to?

Accepted Answer

Use the user ID as the row key prefix, followed by a reversed timestamp.. To efficiently retrieve events for a specific user sorted by time descending, the row key should start with the user ID to group all events for that user. Appending a reversed timestamp ensures that the most recent events appear first in the sorted order, allowing a simple range scan with a row limit.

Answer

Use a hash of the user ID as the row key prefix, followed by the timestamp.

Answer

Use a reversed timestamp as the row key prefix, followed by the user ID.

Answer

Use the timestamp as the row key prefix, followed by the user ID.

Question 6

A media analytics company ingests 200,000 JSON events per second into BigQuery. Each event has a globally unique `event_id` (UUID string) and a `timestamp`. Queries frequently filter by `event_id` for deduplication and by `timestamp` for time-range reports. The table is append-only, and storage cost is a concern because old data is rarely queried after 90 days. Which design meets the query patterns and cost requirements?

Accepted Answer

Partition the table by `timestamp` (day granularity) and cluster by `event_id`.. Partitioning by `timestamp` enables partition pruning for time-range reports and supports partition expiration to automatically delete data after 90 days, directly addressing storage cost. Clustering by `event_id` improves the deduplication lookups that filter on that column. The other designs either create too many partitions, lack expiration, or fail to optimize both access paths.

Answer

Partition the table by `event_id` and cluster by `timestamp`.

Answer

Cluster the table by `event_id` and `timestamp` without partitioning.

Answer

Create a materialized view that selects only the last 90 days of data and query the view instead.

Question 7

A logistics company runs a Cloud SQL for MySQL instance that supports a route-planning application. The database is 2 TB, and the business requires a recovery point objective of 5 minutes and a recovery time objective of 1 hour after an accidental table drop. The team wants the least operational overhead while meeting these objectives. What should they do?

Accepted Answer

Enable automated backups and binary logging, and use point-in-time recovery to restore to a time before the drop. Automated backups plus binary logging enable point-in-time recovery in Cloud SQL, allowing restoration to a timestamp just before the accidental drop. This satisfies the five-minute recovery point objective and, for a 2 TB instance, can plausibly meet the one-hour recovery time objective with minimal operational overhead. Read replicas and high availability replicas replicate the logical error, and nightly dumps are too coarse.

Answer

Enable high availability with a regional failover replica and rely on automatic failover after the drop

Answer

Configure a cross-region read replica and promote it after the table drop

Answer

Schedule a nightly mysqldump export to Cloud Storage and restore the full dump after the drop

Question 8

A media company is designing a Firestore in Native mode database for a mobile app that stores user-generated content. They need to ensure that queries are efficient and scalable. The app frequently queries for the most recent posts by a specific user, ordered by timestamp descending. They also need to retrieve a single post by its unique post ID. Which two actions should they take to optimize the Firestore data model? (Choose two.)

Accepted Answer

Use the post ID as the document ID in a root-level collection.. To optimize the Firestore data model, create a composite index on userId and timestamp descending to support the frequent query for recent posts by user. Additionally, use the post ID as the document ID in a root-level collection to enable fast direct lookups by post ID. These two actions ensure efficient and scalable queries.

Answer

Denormalize the user's most recent posts into the user document as an array.

Answer

Store each post as a subcollection under the user document.

Answer

Enable Firestore's automatic indexing for all fields to speed up queries.

Question 9

A media company uses Cloud Bigtable to store user interaction events. They need to query events for a specific user within the last 7 days, sorted by timestamp descending, and also retrieve the most recent event for each user. The row key currently is a reverse timestamp followed by user_id. What change should they make to optimize for these access patterns?

Accepted Answer

Change the row key to user_id followed by reverse timestamp.. The optimal row key for these access patterns is user_id followed by reverse timestamp. This groups events by user and orders them from newest to oldest, enabling efficient retrieval of recent events and the latest event. Bigtable does not support secondary indexes, so the row key must be designed to support the queries directly.

Answer

Use a composite row key of user_id and event_type, and store timestamp as a column.

Answer

Change the row key to user_id followed by timestamp, and reverse the timestamp in the application.

Answer

Keep the row key as reverse timestamp followed by user_id and add a secondary index on user_id.

Question 10

A logistics company uses Bigtable to store real-time shipment tracking events. The row key is currently 'shipment_id#timestamp', where shipment_id is a random UUID and timestamp is the event time in milliseconds. The team needs to efficiently retrieve the latest event for a given shipment_id. However, they notice that reads for the latest event are slow because Bigtable must scan many versions. What should they do to improve read performance for the latest event?

Accepted Answer

Use a row key of 'shipment_id#reverse_timestamp' where reverse_timestamp is Long.MAX_VALUE minus the event timestamp.. The correct approach is to use a row key of 'shipment_id#reverse_timestamp'. This inverts the timestamp order so that the latest event has the smallest suffix and appears first in a range scan for that shipment. Reading with a limit of 1 then retrieves the latest event efficiently. Bigtable does not support secondary indexes, so row key design is critical.

Answer

Enable Bigtable's versioning feature to automatically return the latest version.

Answer

Create a secondary index on shipment_id and timestamp.

Answer

Change the row key to 'timestamp#shipment_id' to store events in time order.

Question 11

A financial technology firm is designing a Cloud Spanner schema for a payments ledger. The ledger table will contain hundreds of billions of rows and must support high-throughput inserts of new transactions and efficient lookups by account ID for the most recent transactions. The team wants to avoid hotspots and minimize storage cost for older records. Which schema design should they choose?

Accepted Answer

Use a composite primary key of account ID followed by a reversed or hashed transaction timestamp. A composite primary key that starts with account ID spreads inserts across many key ranges, avoiding the hotspot caused by a sequential key, while ordering the timestamp portion in reverse or hashed form keeps recent transactions for each account physically close for fast lookups. This balances high insert throughput with efficient per-account recent-transaction queries and avoids secondary index overhead.

Answer

Use a monotonically increasing transaction ID as the primary key and rely on Spanner load balancing

Answer

Use a random UUID as the primary key and create a secondary index on account ID and timestamp

Answer

Use a composite primary key of transaction timestamp followed by account ID with a descending timestamp

Question 12

A media company stores user-generated video metadata in Firestore in Native mode. The application reads individual documents by document ID millions of times per day and rarely runs queries across collections. The team wants the lowest-latency, most cost-effective reads for this access pattern. What should they do?

Accepted Answer

Perform direct document reads using the document ID path. Firestore direct document reads by document ID use the primary key path, which is the fastest and least expensive way to retrieve a single document. Since the application almost always reads by document ID and rarely queries across collections, adding composite indexes or routing reads through BigQuery would increase cost and latency without improving the dominant access pattern.

Answer

Use collection group queries with a filter on a metadata field

Answer

Export Firestore data to BigQuery and serve reads from BigQuery

Answer

Create a composite index on the metadata fields and query by those fields

Question 13

A financial services company is designing a highly available database solution on Google Cloud for a trading application that requires strong consistency and low latency across multiple regions. They are evaluating Cloud Spanner and Cloud SQL. Which two statements are true regarding the capabilities of Cloud Spanner compared to Cloud SQL? (Choose two.)

Accepted Answer

Cloud Spanner offers multi-region replication with strong consistency, while Cloud SQL supports cross-region read replicas but not multi-region strong consistency for writes.. Cloud Spanner is designed for horizontal scalability and multi-region strong consistency, using automatic sharding and synchronous replication. Cloud SQL is a regional, vertically scalable relational database that supports cross-region read replicas but not multi-region strong consistency for writes. These fundamental architectural differences make Cloud Spanner the choice for globally distributed, strongly consistent, high-scale transactional workloads.

Answer

Cloud Spanner requires manual indexing for all queries, whereas Cloud SQL automatically creates indexes for primary keys and foreign keys.

Answer

Cloud Spanner supports standard SQL with ACID transactions, while Cloud SQL only supports eventual consistency for transactions.

Answer

Cloud Spanner is limited to a single region, while Cloud SQL can be deployed across multiple regions with strong consistency.

Question 14

A global e-commerce platform uses Cloud Spanner for its order management system. The orders table is defined with a monotonically increasing order_id as the primary key. During peak shopping periods, the team observes severe write latency and hotspots concentrated on a few Spanner servers. They need to redesign the primary key to distribute writes evenly while still supporting efficient lookups by order_id. What should they do?

Accepted Answer

Prepend a random integer (e.g., a shard number) to the order_id to form a composite primary key, and use a secondary index on the original order_id for lookups.. Monotonically increasing primary keys cause hotspots in Spanner because writes concentrate on the last key range. Prepending a random shard number to the key distributes writes across multiple key ranges, while a secondary index on the original order_id preserves efficient lookups. This pattern maintains write scalability and read performance without sacrificing the business identifier.

Answer

Switch the primary key to a UUID generated by the application, and remove the order_id column entirely.

Answer

Use a hash of the order_id as the primary key and store the original order_id as a secondary indexed column.

Answer

Partition the orders table by order_id range and distribute partitions across multiple Spanner instances.

Question 15

A financial services firm runs a Cloud SQL for PostgreSQL instance that must be highly available across two zones. They need automatic failover with minimal data loss and want to test failover without impacting production. Which configuration and action should they use?

Accepted Answer

Enable high availability and use the `gcloud sql instances failover` command to trigger a failover.. Enabling high availability on a Cloud SQL instance creates a synchronous standby in another zone, enabling automatic failover with no data loss. The `gcloud sql instances failover` command is the supported method to manually trigger a failover for testing. Read replicas use asynchronous replication and manual promotion, and restart does not exercise the failover path.

Answer

Create a read replica in another zone and promote it manually during an outage.

Answer

Use a Cloud SQL replica with automatic failover enabled via the `--failover` flag.

Answer

Enable high availability and use the `gcloud sql instances restart` command to test failover.