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

Question 1

A company uses Cloud Spanner with a multi-region configuration (nam3) for a global application. They notice that write latency has increased significantly during peak hours. After investigation, they find that the number of splits has increased from 10 to 50, and the CPU utilization on most nodes is below 10%. However, writes are being throttled due to excessive hot spots on a few nodes. What should they do?

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

Accepted Answer

Redesign the primary key to avoid monotonic increases. The symptom — many splits, low CPU, and write throttling from hot spots — is the classic signature of a monotonically increasing primary key (e.g., timestamp or sequential ID) that concentrates writes on the last split. Redesigning the primary key to distribute writes (e.g., using a hash prefix or UUID) eliminates the hot spot and restores write throughput.

Answer

Enable interleaved tables

Answer

Increase the number of nodes

Answer

Use a read replica

Question 2

A financial services company is deploying a new Cloud SQL for PostgreSQL instance that will handle sensitive customer data. The compliance team requires that all data at rest be encrypted with a customer-managed encryption key (CMEK) that the company can rotate and revoke independently. The company uses Cloud KMS and wants to ensure that the Cloud SQL instance uses the CMEK for all data, including backups and replicas. Which configuration should the database engineer implement?

Accepted Answer

Create a Cloud KMS key ring and key in the same region as the Cloud SQL instance, grant the Cloud SQL service account the Cloud KMS CryptoKey Encrypter/Decrypter role, and specify the key when creating the instance.. To use CMEK with Cloud SQL, the KMS key must be in the same region as the instance, and the Cloud SQL service account must have the CryptoKey Encrypter/Decrypter role. Specifying the key at instance creation ensures that all data, including backups and replicas, is encrypted with the CMEK. This meets the compliance requirement for customer-managed encryption.

Answer

Grant the Cloud SQL service account the Cloud KMS Admin role and specify the key during instance creation.

Answer

Enable CMEK by setting the `--disk-encryption-key` flag to the Cloud KMS key resource name when creating the instance using gcloud.

Answer

Create the Cloud SQL instance with default Google-managed encryption, then use Cloud KMS to encrypt the backups separately after creation.

Question 3

A startup is setting up a new Cloud SQL for MySQL instance for a development environment. They want to minimize costs and do not require high availability or read replicas. The instance will be used intermittently during business hours. Which configuration should they choose to optimize cost?

Accepted Answer

Use a single-zone instance with a shared-core machine type, set a fixed storage size, and schedule the instance to stop during non-business hours.. For a cost-sensitive development environment with intermittent usage, a single-zone instance with a shared-core machine type minimizes compute costs. Setting a fixed storage size prevents unexpected storage charges. Scheduling the instance to stop during non-business hours further reduces costs by eliminating charges when the instance is not needed. This meets the requirements without over-provisioning.

Answer

Use a single-zone instance with a shared-core machine type and enable automatic storage increase.

Answer

Use a single-zone instance with a dedicated-core machine type and disable automatic backups.

Answer

Use a regional instance with a shared-core machine type and enable high availability.

Question 4

A retail company runs a Cloud Spanner instance in a single region with 3 nodes. During a seasonal sale, CPU utilization on the instance exceeds 90% for several hours, and query latency increases significantly. The operations team needs to reduce CPU utilization without changing the application's SQL queries or schema. What should they do?

Accepted Answer

Increase the number of nodes in the Cloud Spanner instance.. The instance is CPU-bound, and the team cannot modify queries or schema. Scaling the Cloud Spanner instance by adding nodes increases compute capacity, which directly alleviates CPU pressure and improves latency. Other options either do not address the root cause, add overhead, or violate the constraint of not changing queries or schema.

Answer

Create a secondary index on the most frequently queried column.

Answer

Enable query statistics and optimize the slowest queries.

Answer

Change the instance configuration to a multi-region configuration.

Question 5

A financial services company runs a mission-critical Cloud SQL for PostgreSQL instance in a single zone. The database team must guarantee that the instance can automatically recover from a zone-level outage with no manual intervention and minimal data loss. The instance currently has no high availability configured. Which action should the team take?

Accepted Answer

Enable high availability on the instance by configuring a regional instance with a standby in a different zone.. High availability for Cloud SQL for PostgreSQL is achieved by configuring the instance as regional, which creates a synchronous standby in a different zone. Failover is automatic and data loss is minimal because replication is synchronous. Read replicas are asynchronous and require manual promotion, backups are for recovery not failover, and the Auth Proxy is only a connectivity tool.

Answer

Schedule automated backups with point-in-time recovery enabled and restore to a new instance during an outage.

Answer

Configure a Cloud SQL Auth Proxy with multiple connections to the instance to reroute traffic during an outage.

Answer

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

Question 6

A financial services company needs to deploy a Cloud SQL for PostgreSQL instance that must be highly available across two zones within the same region. They want automatic failover with minimal data loss. Which configuration should they choose?

Accepted Answer

A high availability (HA) configuration with a standby instance in a different zone.. For high availability with automatic failover and minimal data loss in Cloud SQL, the correct configuration is a high availability setup with a standby in a different zone. This uses synchronous replication and automatic failover. Other options either lack automatic failover, use asynchronous replication, or are not valid configurations.

Answer

A multi-zone instance with cross-region replication.

Answer

A single-zone instance with automated backups enabled.

Answer

A read replica in a different region.

Question 7

A startup is deploying a new Cloud SQL for MySQL instance for a development environment. They want to minimize costs while ensuring that the instance can be automatically patched during a maintenance window. They do not need high availability or read replicas. Which configuration should they choose?

Accepted Answer

A single-zone instance with automated backups enabled and a maintenance window set.. For a development environment with no high availability requirement, a single-zone instance is the most cost-effective. Automated backups provide basic data protection, and setting a maintenance window ensures that automatic patching occurs during a controlled period. Other options either add unnecessary cost or fail to address the maintenance window requirement.

Answer

A single-zone instance with point-in-time recovery enabled and no maintenance window.

Answer

A single-zone instance with binary logging enabled and no maintenance window.

Answer

A regional instance with high availability enabled and a maintenance window set.

Question 8

A healthcare company stores sensitive patient data in Cloud Firestore. They need to ensure that data is encrypted with a customer-managed encryption key (CMEK) that they control and can rotate independently. They also need to be able to revoke access to the data by disabling the key. What should they do?

Accepted Answer

Configure Cloud Firestore to use a CMEK from Cloud KMS.. Cloud Firestore supports customer-managed encryption keys (CMEK) through Cloud KMS. Configuring the Firestore database to use a CMEK allows the company to control key rotation and revoke access by disabling the key. Other options either do not provide customer control or add unnecessary complexity.

Answer

Use Cloud External Key Manager (EKM) to store keys outside Google Cloud.

Answer

Encrypt the data at the application level before writing to Firestore.

Answer

Use Google-managed encryption keys and rely on Google's key rotation.

Question 9

A financial analytics firm runs a mission-critical Cloud Spanner database in a single region. The compliance team mandates that the database must tolerate a full regional outage with zero data loss and no application changes. Which configuration should you implement?

Accepted Answer

Create a new Cloud Spanner instance with a multi-region configuration that includes the primary region and a second region.. A multi-region Cloud Spanner configuration replicates data synchronously across regions and automatically fails over to a healthy region, ensuring zero data loss and continuous availability. It requires no application changes because the same instance endpoint remains valid. Other options either do not provide automatic failover, introduce data loss, or require manual intervention.

Answer

Enable point-in-time recovery (PITR) on the existing instance and set the retention period to 7 days.

Answer

Export the database to Cloud Storage every hour and restore it to a new instance in a different region if an outage occurs.

Answer

Add a read-only replica in a second region and configure the application to redirect writes to the replica during a failure.

Question 10

A media company uses Bigtable for storing user interaction events. The table has a row key that starts with a timestamp, causing hot spotting and poor performance. They need to redesign the row key to distribute writes evenly while still allowing efficient queries for the most recent events for a given user. What should they do?

Accepted Answer

Use a row key that starts with the user ID, followed by a reversed timestamp.. To avoid hot spotting and allow efficient per-user recent-event queries, the row key should start with the user ID to group a user's events, and then use a reversed timestamp to sort recent events first. This design distributes writes across different users and supports efficient range scans for a user's latest events.

Answer

Reverse the timestamp and prepend it to the row key.

Answer

Use a row key that starts with a hash of the user ID, followed by the timestamp.

Answer

Use a row key that starts with a random number, followed by the user ID and timestamp.

Question 11

A healthcare company uses Cloud Bigtable to store patient monitoring data. They need to ensure that the data is replicated across multiple regions for disaster recovery and that reads can be served from the nearest region. They also want to minimize write latency for applications in both the US and Europe. Which Bigtable configuration should they use?

Accepted Answer

A multi-cluster instance with clusters in us-central1 and europe-west1, using a multi-region replication configuration.. Cloud Bigtable multi-cluster instances allow you to create clusters in multiple regions within the same instance. Replication is automatic, providing disaster recovery and enabling reads from the nearest cluster. Writes can be sent to the nearest cluster, reducing latency. Other options either use unsupported features or require manual replication, which is not ideal.

Answer

A single-cluster instance in us-central1 with a read replica in europe-west1.

Answer

Two separate Bigtable instances, one in us-central1 and one in europe-west1, with application-level replication.

Answer

A single-cluster instance in us-central1 with a Cloud Storage bucket in europe-west1 for backup.

Question 12

A financial services firm runs a Cloud SQL for PostgreSQL instance in us-central1. Their compliance team mandates that all data at rest be encrypted with a customer-managed encryption key (CMEK) that the firm can rotate and revoke independently. The database currently uses Google-managed encryption. They want to enable CMEK with minimal disruption. What should they do first?

Accepted Answer

Create a Cloud KMS key ring and crypto key in the same region as the Cloud SQL instance, grant the Cloud SQL service account the Cloud KMS CryptoKey Encrypter/Decrypter role, then reconfigure the instance to use the CMEK.. To enable CMEK on an existing Cloud SQL instance, you must first create a Cloud KMS key in the same region, grant the Cloud SQL service account the CryptoKey Encrypter/Decrypter role, and then reconfigure the instance to use that key. This approach avoids data migration and minimizes disruption.

Answer

Export the database to a Cloud Storage bucket encrypted with a CMEK, then import it into a new Cloud SQL instance configured with that key.

Answer

Enable CMEK by editing the instance and pasting the key resource name; Cloud SQL automatically grants the required IAM permissions to its service account.

Answer

Create a new Cloud SQL instance with CMEK enabled and use Cloud SQL replica promotion to switch over, then delete the old instance.

Question 13

A company is running a Cloud SQL for PostgreSQL instance that is used for both OLTP and reporting queries. The reporting queries are causing performance degradation for the OLTP workload. The database engineer wants to offload reporting queries to a read replica without affecting the primary instance. The reporting queries require near-real-time data. Which configuration should the engineer implement?

Accepted Answer

Create a read replica in the same region as the primary instance and configure the reporting application to connect to the read replica's IP address.. A same-region read replica provides low replication lag, ensuring near-real-time data for reporting. By directing reporting queries to the read replica, the primary instance's resources are freed for OLTP workloads. This improves overall performance without affecting data freshness significantly.

Answer

Enable high availability on the primary instance and direct reporting queries to the standby instance.

Answer

Create a read replica and enable cascading replication to further distribute the load.

Answer

Create a cross-region read replica and configure the reporting application to connect to it.

Question 14

A startup is deploying a new Cloud SQL for PostgreSQL instance for a development environment. The team wants to minimize cost while ensuring that the instance can be quickly scaled up for production later. They also need to ensure that the instance is not publicly accessible. What should they do?

Accepted Answer

Create the instance with a shared-core machine type, disable public IP, and configure a private IP address.. For a cost-sensitive development environment, a shared-core machine type is the most economical choice. Disabling the public IP and configuring a private IP address ensures the instance is not exposed to the internet, which is a security best practice. The instance can be easily resized to a larger machine type when moving to production, providing scalability.

Answer

Configure the instance with a private IP address and enable the Cloud SQL Admin API.

Answer

Deploy the instance with a public IP address but restrict access using Cloud SQL Auth Proxy.

Answer

Use a dedicated-core machine type with high memory and enable automatic storage increase.

Question 15

A startup is deploying a new application on Google Cloud. They need a fully managed, relational database that supports global transactions with strong consistency and horizontal scaling. The database must automatically handle sharding and replication without manual intervention. They expect rapid growth in users and data volume. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that offers a fully managed, relational, globally distributed system with strong consistency and automatic horizontal scaling. It handles sharding and replication transparently, supporting global transactions without manual overhead. The other options lack either relational capabilities, global strong consistency, or automatic scaling.

Answer

Firestore

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Bigtable