PCDE Plan and manage database infrastructure Practice Test 15 — 15 Questions

Question 1

A healthcare company runs a Cloud SQL for MySQL instance with a read replica in another region for disaster recovery. They need to ensure that the read replica can be promoted to a standalone primary with minimal data loss in the event of a regional failure. They also want to monitor replication lag. What should they do?

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

Accepted Answer

Create a cross-region read replica, monitor replication lag using Cloud Monitoring metrics, and manually promote the replica if the primary fails.. Cross-region read replicas in Cloud SQL are intended for disaster recovery and require manual promotion. Monitoring replication lag is essential to estimate potential data loss. Automatic failover is only supported for same-region failover replicas, so the manual promotion approach is correct here.

Answer

Use Cloud SQL's built-in automatic failover to a cross-region replica by enabling the 'cross-region failover' option in the instance settings.

Answer

Set up a Cloud SQL replica in the same region as the primary and use it for failover, while using the cross-region replica only for read offloading.

Answer

Configure the read replica as a cross-region replica and enable automatic failover by setting the replica as a failover replica.

Question 2

A company is planning to deploy a Cloud Spanner instance for a global application that requires strong consistency and high availability. The database engineer must choose a configuration that minimizes latency for users in North America, Europe, and Asia. The application has a read-heavy workload with some writes. Which two factors should the engineer consider when selecting the instance configuration? (Choose two.)

Accepted Answer

The number of nodes or processing units must be sufficient to handle the read and write throughput.. When selecting a Cloud Spanner instance configuration for a global application, the engineer must consider the number of nodes or processing units to handle the workload, and the instance configuration (regional or multi-region) to place replicas close to users. These factors directly impact performance, latency, and availability. The SQL dialect, node count for failover, and secondary indexes do not determine global latency or availability in the same way.

Answer

Enabling automatic failover requires a minimum of three nodes in each region.

Answer

The use of secondary indexes can reduce write latency by distributing writes across multiple replicas.

Answer

The choice of SQL dialect (GoogleSQL or PostgreSQL) affects the replication strategy and consistency guarantees.

Question 3

You are managing a Cloud Bigtable instance with a single cluster in us-central1. The instance is used for a real-time analytics application that requires high availability across zones. You need to ensure that the cluster can survive a zonal failure without downtime. What should you do?

Accepted Answer

Enable replication by adding a second cluster in a different zone within the same region.. Adding a second cluster in a different zone within the same region enables Bigtable replication, which provides automatic failover if one zone fails. This ensures high availability without downtime and maintains low latency because the clusters are in the same region. Other options do not provide automatic failover or are designed for disaster recovery rather than zonal high availability.

Answer

Create a backup of the Bigtable instance and restore it to a new instance in a different zone if a failure occurs.

Answer

Configure the existing cluster to use SSD storage instead of HDD for faster failover.

Answer

Use a multi-region instance with clusters in different regions to ensure high availability.

Question 4

A media company stores user-generated video metadata in Cloud Spanner. The table uses a monotonically increasing integer as the primary key, and writes arrive at a sustained rate of 50,000 rows per second. Performance degrades as the table grows, with hot spots on a single split. The team needs to eliminate the hot spot without changing the application's write pattern. What should they do?

Accepted Answer

Hash the primary key or prepend a randomly distributed value to the key.. Cloud Spanner divides data into splits by key range. A monotonically increasing key sends every new insert to the same trailing split, so one server absorbs the write burst while others idle. Hashing the key or prefixing a random value scatters inserts across the key space, letting many splits and servers share the load and removing the hot spot.

Answer

Create a secondary index on the primary key column to improve write throughput.

Answer

Enable optimistic concurrency control and retry failed writes with exponential backoff.

Answer

Increase the number of nodes in the instance to distribute the write load.

Question 5

A company is planning to deploy a Cloud SQL for MySQL instance that will handle a high-traffic web application. They need to ensure high availability and the ability to scale read operations. Which two configurations should they implement? (Choose two.)

Accepted Answer

Enable high availability by creating a regional instance with a standby replica in a different zone.. High availability with a regional instance and standby replica ensures automatic failover during zone outages, while read replicas distribute read traffic to improve performance. Together, these configurations provide both uptime and scalability for a high-traffic web application. Other options address security, backup, or maintenance but do not fulfill the core requirements.

Answer

Configure the instance with a public IP address and restrict access using authorized networks.

Answer

Set the instance's maintenance window to occur during off-peak hours.

Answer

Enable point-in-time recovery (PITR) to allow restoration to any moment within the retention period.

Question 6

A startup runs a small Cloud SQL for MySQL instance for its staging environment. The instance is idle most of the day, and the team wants to reduce cost while keeping the instance available for occasional tests. They do not want to lose data or reconfigure connection strings. Which action should they take?

Accepted Answer

Stop the instance and start it when needed.. Stopping a Cloud SQL instance halts compute billing while retaining data, configuration, and the IP address, so the team can start it again for tests without reconfiguring applications. Deleting and restoring changes the IP and introduces risk, shrinking disks is unsupported, and converting to a read replica does not fit a standalone staging instance.

Answer

Reduce the instance's disk size to lower storage costs.

Answer

Convert the instance to a read replica and promote it when tests are needed.

Answer

Delete the instance and restore it from a backup when tests are needed.

Question 7

A company runs a Cloud SQL for SQL Server 2019 Enterprise instance with 8 vCPUs and 52 GB of memory in us-east1. Their 3 TB database must be available with less than 30 seconds of downtime for a planned regional maintenance event that will restart the instance. They require the application to remain read-write throughout. Which configuration should they implement?

Accepted Answer

Enable high availability by adding a standby in a different zone, and rely on the automatic failover during the maintenance event.. High availability in Cloud SQL provisions a synchronous standby in a different zone of the same region. During a planned maintenance restart, the primary is failed over to the standby automatically, the instance keeps the same connection endpoint, and clients reconnect within seconds. This is the only listed option that preserves read-write access with sub-minute disruption, whereas replicas, backups, and exports either require manual steps or cause prolonged outages.

Answer

Configure the instance with a cross-region read replica in us-west1 and manually promote it during the maintenance window.

Answer

Export the database to a Cloud Storage bucket and import it into a new instance in another zone before the maintenance window.

Answer

Schedule the maintenance window during off-peak hours and take a manual backup immediately before the window begins.

Question 8

A startup is deploying a new Cloud SQL for PostgreSQL instance for a development environment. They want to minimize cost and do not require high availability or read replicas. Which machine type and configuration should they choose?

Accepted Answer

A shared-core machine type (e.g., db-f1-micro) with a single zone and no failover replica.. For a development environment with minimal cost and no HA requirement, a shared-core machine type in a single zone without a failover replica is the most economical choice. Dedicated-core types and high availability add cost without providing value for this use case. The startup should select the shared-core instance to keep expenses low while still having a functional PostgreSQL database.

Answer

A shared-core machine type (e.g., db-f1-micro) with high availability enabled across two zones.

Answer

A dedicated-core machine type (e.g., db-n1-standard-1) with a single zone and no failover replica.

Answer

A dedicated-core machine type (e.g., db-n1-standard-1) with high availability enabled across two zones.

Question 9

A healthcare company stores sensitive patient data in Cloud Firestore. They need to ensure that the data is encrypted at rest with keys they manage and can revoke if necessary. They also want to minimize operational overhead. What should they do?

Accepted Answer

Enable Customer-Managed Encryption Keys (CMEK) using Cloud KMS and create a key ring and key in the same region as the Firestore database.. Customer-Managed Encryption Keys (CMEK) with Cloud KMS provide the required control over encryption keys, including the ability to revoke access by disabling the key. This approach integrates natively with Firestore and requires less operational effort than client-side or external key management. Creating the key in the same region as the database ensures data residency and performance.

Answer

Implement client-side encryption before writing data to Firestore, managing keys in an on-premises HSM.

Answer

Use Google-managed encryption keys and rely on Google's default encryption at rest.

Answer

Use Cloud External Key Manager (Cloud EKM) to store keys in a third-party key management system.

Question 10

A healthcare company stores patient records in a Cloud Spanner database with a multi-region configuration (nam3) to ensure low latency and high availability across the United States. The database has a table with a monotonically increasing primary key based on a timestamp. The operations team notices that write latency has increased significantly and that CPU utilization on the leader region is consistently above 90%. They need to improve write performance without changing the application's data model. What should they do?

Accepted Answer

Change the primary key to use a UUID or a hash of the timestamp to distribute writes evenly across splits.. Cloud Spanner distributes data based on primary key ranges. A monotonically increasing key causes all writes to go to the last range, creating a hotspot on a single server. This leads to high CPU on the leader and increased write latency. Changing the key to a UUID or a hash of the timestamp distributes writes randomly, allowing Spanner to balance the load across multiple servers. Adding nodes or replicas does not fix the hotspot because the issue is key distribution, not overall capacity.

Answer

Enable Data Boost to offload read traffic from the leader region, freeing up CPU for writes.

Answer

Increase the number of replicas in the multi-region configuration to spread the write load.

Answer

Add more nodes to the Cloud Spanner instance to increase write throughput.

Question 11

A startup is deploying a new web application on Google Cloud. They need a fully managed relational database for their MySQL-compatible application. The database will store user profiles and session data, with moderate traffic and a single region. They want minimal operational overhead and automatic backups. Which Google Cloud service should they use?

Accepted Answer

Cloud SQL for MySQL. Cloud SQL for MySQL is a fully managed service that supports MySQL, making it the best fit for a MySQL-compatible application with moderate traffic. It provides automated backups, replication, and patching, minimizing operational overhead. Other options are either non-relational, lack MySQL compatibility, or are overkill for the stated scale and region.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 12

A company runs a Cloud SQL for PostgreSQL instance with a 1 TB database. They need to enable point-in-time recovery (PITR) to be able to restore to any moment within the last 7 days. They also want to minimize the performance impact on the primary instance. What should they do?

Accepted Answer

Enable point-in-time recovery on the instance and configure the transaction log retention period to 7 days.. Point-in-time recovery in Cloud SQL for PostgreSQL is enabled by turning on PITR and setting the transaction log retention period. This allows restoration to any point within that period, up to 7 days. The feature uses asynchronous log archiving, so the performance impact on the primary is minimal. Other options either do not provide PITR or introduce unnecessary performance overhead.

Answer

Enable automated backups and set the retention period to 7 days.

Answer

Schedule a cron job to export the database every hour to Cloud Storage.

Answer

Create a read replica and enable binary logging on the primary.

Question 13

A media company stores user activity events in Cloud Bigtable. The table uses a single column family and a row key that is the reverse of a timestamp. Read latency is low, but a nightly batch scan of the full table is extremely slow because it must read every row. They want to add a second column family that stores a pre-aggregated daily count per user, updated on each write, so the batch job can read only the aggregate column family. Which change should they make?

Accepted Answer

Create a separate aggregate table keyed by user ID and day, and increment the counter using an atomic increment operation in a single row mutation.. The batch job is slow because it scans the entire event table. Moving the aggregate to a separate table keyed by user and day allows the job to read only the small aggregate table. Atomic increments via ReadModifyWriteRow keep counts accurate under concurrent writers without a read-before-write, which would otherwise add latency and race conditions.

Answer

Add a second column family to the existing table and use a conditional mutation that reads the current value in the same row before writing.

Answer

Create a new column family and write the aggregate value using a read-modify-write on every event, accepting higher write latency.

Answer

Enable a Cloud Bigtable replication cluster in a second region and direct the batch job to the replica to offload reads.

Question 14

A startup is using Firestore in Native mode for a mobile application. They need to ensure that the database can scale automatically to handle sudden spikes in traffic without manual intervention. They also want to minimize costs during periods of low usage. Which Firestore feature should they rely on?

Accepted Answer

Automatic scaling. Firestore in Native mode is a serverless, fully managed database that automatically scales to handle traffic spikes and scales down during low usage. This automatic scaling ensures that the application can handle sudden increases in load without manual intervention, and the pay-as-you-go pricing model minimizes costs when usage is low. Other options like multi-region replication or manual indexing do not provide these benefits.

Answer

Multi-region replication

Answer

Manual index configuration

Answer

Datastore mode

Question 15

A team is deploying a Cloud Spanner instance for a new financial application. Their legal team requires that all data be stored within the European Union. The application will be accessed by users in Frankfurt and Paris, and the team wants the lowest possible read latency for both cities while meeting the data residency requirement. Which instance configuration should they choose?

Accepted Answer

A multi-region instance with the base region europe-west3 (Frankfurt) and the second region europe-west1 (Belgium).. Cloud Spanner instance configurations determine where replicas are placed. To keep data inside the European Union and serve Frankfurt and Paris with low read latency, the team needs a multi-region configuration whose regions are both within the EU, such as europe-west3 and europe-west1. A single-region instance would satisfy residency but not the latency goal, while any configuration including a non-EU region breaks the legal requirement.

Answer

A multi-region instance with the base region europe-west3 (Frankfurt) and the second region us-central1 (Iowa).

Answer

A regional instance in europe-west3 (Frankfurt).

Answer

A regional instance in europe-west1 (Belgium) with a read-only replica in europe-west3 (Frankfurt).