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

Question 1

A financial services firm runs a mission-critical Cloud SQL for SQL Server Enterprise edition instance in the us-east1 region. Their compliance team mandates that the database must remain available even if an entire Google Cloud zone fails, and they require a recovery point objective (RPO) of less than one minute. The database is write-heavy and approximately 1.5 TB in size. Which configuration should you choose to meet these requirements?

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

Accepted Answer

Enable high availability by configuring a regional instance with a primary and standby in different zones, and rely on synchronous replication to the standby.. For Cloud SQL for SQL Server, high availability is achieved by configuring a regional instance with a primary and standby in different zones. The primary and standby use synchronous replication, ensuring zero data loss and automatic failover if a zone fails. This meets the compliance team's requirement for availability during a zone failure and an RPO of less than one minute. Asynchronous replication or read replicas would not provide the same guarantees.

Answer

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

Answer

Deploy the instance in a single zone and enable automated backups with point-in-time recovery (PITR).

Answer

Enable high availability by configuring a regional instance with a primary and standby in different zones, and rely on asynchronous replication to the standby.

Question 2

A financial services company runs a Cloud SQL for PostgreSQL instance in a single zone. Their compliance team requires that the database survive a zonal outage without manual intervention and with no data loss. They also want to minimize cost while meeting this requirement. What should they do?

Accepted Answer

Enable high availability (HA) on the instance, which creates a standby in a different zone and performs automatic failover.. High availability in Cloud SQL for PostgreSQL provisions a standby instance in a different zone and replicates data synchronously. If the primary zone becomes unavailable, Cloud SQL automatically fails over to the standby, ensuring no data loss and no manual intervention. This is the standard solution for zonal resilience with minimal cost, as it only requires one additional instance.

Answer

Take regular automated backups and restore them to a new instance in a different zone when a failure occurs.

Answer

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

Answer

Configure the instance to be regional by setting the availability type to REGIONAL, which automatically creates a failover replica.

Question 3

A company runs a Cloud SQL for PostgreSQL instance with a primary and two read replicas. They need to perform a read-heavy analytics workload without impacting the primary's performance. The analytics queries are long-running and resource-intensive. They want to isolate the analytics workload to a dedicated replica that can be resized independently. What should they do?

Accepted Answer

Create a dedicated read replica with a different machine type and configure it as a cascading replica.. To isolate analytics workload, a dedicated read replica that replicates from another replica (cascading replica) is ideal. It offloads replication from the primary and can be resized independently. Cascading replicas allow you to create a replica that does not directly impact the primary's replication load, and long-running queries on it do not affect other replicas. This meets the requirement for isolation and independent resizing.

Answer

Use the existing read replicas for analytics and enable query parallelism.

Answer

Create a new read replica with a larger machine type and direct analytics queries to it.

Answer

Promote one of the existing read replicas to a standalone instance and use it for analytics.

Question 4

A company runs a Cloud SQL for PostgreSQL instance that supports a customer-facing application. Compliance requires that the database be encrypted with a customer-managed encryption key (CMEK) that the security team can rotate and revoke independently of Google. The instance was created with Google-managed encryption. What should the database engineer do?

Accepted Answer

Create a Cloud KMS key in the same region as the instance, then update the instance to use that key as its CMEK.. Cloud SQL for PostgreSQL supports customer-managed encryption keys backed by Cloud KMS. An existing instance can be updated to use a CMEK as long as the key is in the same region as the instance and the service account has the necessary permissions. This lets the security team rotate or revoke the key independently, meeting the compliance requirement without a full migration or instance recreation.

Answer

Use the gcloud sql instances patch command with the --disk-encryption-key flag to assign a Cloud KMS key to the running instance.

Answer

Enable CMEK by creating a Cloud External Key Manager (EKM) key and attaching it to the instance.

Answer

Create a new instance with a CMEK and migrate the data using Database Migration Service, then decommission the old instance.

Question 5

A company runs a Cloud SQL for MySQL instance that is used by a reporting application. The instance has a single primary and no replicas. The reporting team wants to run heavy analytical queries without affecting the performance of the primary for the transactional application. They also want to ensure that if the primary fails, the reporting queries can continue with minimal disruption. Which configuration should they implement?

Accepted Answer

Create a read replica in a different zone and direct reporting queries to it; configure the primary for high availability.. To isolate heavy reporting from the transactional primary, a read replica is the correct tool. To ensure the reporting source remains available if the primary fails, the primary should be configured for high availability. The replica itself does not provide failover, and running analytics on the primary defeats the purpose of isolation.

Answer

Create a cross-region read replica and direct reporting queries to it; configure the primary for high availability.

Answer

Create a read replica and direct reporting queries to it; do not configure high availability because the replica provides redundancy.

Answer

Enable high availability on the primary and run reporting queries directly on the primary, relying on the standby for performance.

Question 6

A retail company runs a Cloud Spanner instance with a multi-region configuration (nam3) to serve users in North America. They have a table with a monotonically increasing primary key (e.g., sequential order IDs). They are experiencing high write latency and hotspots. They want to optimize the schema to improve write performance without changing the application's query patterns, which frequently retrieve orders by order ID. What should they do?

Accepted Answer

Use a composite primary key with a hash prefix of the order ID followed by the order ID itself.. A monotonically increasing primary key causes all writes to go to the last split, creating a hotspot. Using a composite key with a hash prefix of the order ID distributes writes across multiple splits while preserving the order ID for queries. This avoids hotspots without changing query patterns, as the order ID is still part of the key and can be used for lookups.

Answer

Hash the order ID and use the hash as the primary key, then create a secondary index on the original order ID.

Answer

Use a UUID instead of a sequential integer for the primary key.

Answer

Enable a higher number of nodes to increase write throughput.

Question 7

A financial services company uses Cloud Spanner for a global ledger application. They need to ensure that reads and writes are strongly consistent across all regions and that the database can survive a regional failure with zero data loss. They also want to minimize read latency for users in Europe and Asia. The application performs both read-write transactions and read-only queries. Which configuration should they use?

Accepted Answer

A multi-region instance configuration with three read-write regions in Europe, Asia, and North America.. Cloud Spanner multi-region configurations with read-write regions in multiple geographic locations provide strong consistency, zero data loss, and low latency for reads and writes in those regions. Placing read-write regions in Europe, Asia, and North America ensures that users in those areas experience minimal latency while maintaining global consistency and regional failure resilience. Other options either do not provide local read-write capability or use invalid configurations.

Answer

A multi-region instance configuration with two read-write regions in North America and one read-only region in Asia.

Answer

A multi-region instance configuration with two read-write regions and one witness region.

Answer

A regional instance configuration in Europe with a read-only replica in Asia.

Question 8

A healthcare company stores sensitive patient data in Cloud Spanner. They need to ensure that data is encrypted with a customer-managed encryption key (CMEK) and that they can control key rotation and revocation. They also need to minimize latency for global reads. Which combination of steps should they take?

Accepted Answer

Create a Cloud KMS key in the same region as the Spanner instance, grant the Spanner service account the cloudkms.cryptoKeyEncrypterDecrypter role, and specify the key when creating the database.. Cloud Spanner CMEK requires a Cloud KMS key in the same region as the instance, IAM permissions for the Spanner service account, and specifying the key during database creation. This enables customer control over key lifecycle. The key must be at the database level, not instance level, and region alignment ensures performance and compliance.

Answer

Create a Cloud KMS key in the same region, grant the Spanner service account the cloudkms.cryptoKeyEncrypterDecrypter role, and specify the key when creating the instance.

Answer

Create a Cloud KMS key in a different region, grant the Spanner service account the cloudkms.cryptoKeyEncrypterDecrypter role, and specify the key when creating the instance.

Answer

Use Google-managed encryption keys and enable customer-managed encryption by setting a default key in the project metadata.

Question 9

A financial services company is deploying a new Cloud Spanner instance to support a global trading application. The application requires strong consistency and must survive a full regional outage without data loss. The company needs the lowest possible write latency for users in both North America and Europe. Which configuration should the database engineer choose?

Accepted Answer

A multi-region instance configuration such as nam3 or eur3.. A multi-region instance configuration spanning the required continents (e.g., nam3 or eur3) provides synchronous replication and regional failover with strong consistency. It also minimizes write latency by keeping the quorum within the necessary regions. Other options either use invalid replica types or add unnecessary geographic distance, increasing latency without benefit.

Answer

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

Answer

A multi-region instance configuration such as nam-eur-asia1.

Answer

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

Question 10

A company is planning to deploy a new Cloud Bigtable instance for a high-throughput analytics workload. They need to choose an appropriate storage type and node scaling strategy to optimize performance and cost. The workload is expected to grow steadily over time. Which two actions should they take? (Choose two.)

Accepted Answer

Monitor CPU utilization and add or remove nodes manually to match the workload's throughput requirements.. For a high-throughput analytics workload on Cloud Bigtable, choosing SSD storage ensures low latency and high throughput. Monitoring CPU utilization and manually scaling nodes allows the company to match capacity to demand as the workload grows, optimizing both performance and cost. These two actions directly address the performance and cost requirements.

Answer

Configure the Bigtable instance with a single node and enable automatic scaling based on CPU utilization.

Answer

Use HDD storage to reduce costs, since the workload is analytics-oriented and does not require low latency.

Answer

Enable automatic scaling with a minimum of one node and a maximum of ten nodes to handle peak loads.

Question 11

A healthcare company runs a Cloud Spanner instance with a multi-region configuration in nam3 (us-east1, us-east4, us-central1). They need to add a new region, us-west1, to the instance to improve read latency for users on the West Coast. What should they do?

Accepted Answer

Create a new Cloud Spanner instance with a multi-region configuration that includes us-west1 and migrate the data.. Cloud Spanner instance configurations are immutable; you cannot add or remove regions from an existing instance. To include us-west1, you must create a new instance with a configuration that covers that region and migrate the data. This requires planning and possibly downtime, but it is the supported method.

Answer

Modify the instance configuration to a custom multi-region configuration that includes us-west1.

Answer

Create a new Cloud Spanner instance in us-west1 and migrate the data using the Cloud Spanner export/import feature.

Answer

Add a read-only replica in us-west1 to the existing instance.

Question 12

A company is planning a Cloud Spanner deployment for a global inventory system. They need to minimize write latency for users in North America and ensure that the database survives the loss of an entire region. They also want to avoid manual intervention during a regional outage. Which two instance configuration choices meet these requirements? (Choose two.)

Accepted Answer

Ensure the instance configuration includes at least three read-write regions to provide quorum-based durability.. To minimize write latency for North American users, the leader replicas should be in North America, which a nam3-based multi-region configuration provides. To survive a regional outage automatically, the configuration must place replicas in multiple regions with quorum-based replication, such as a three-region read-write configuration. Together these choices satisfy both the latency and the availability requirements without manual failover.

Answer

Use a multi-region instance configuration with nam3 as the base region and rely on Spanner's automatic failover to a replica in another region.

Answer

Use a regional instance in us-central1 with a read-only replica in us-east1.

Answer

Use a multi-region instance configuration with nam3 as the base region and configure the application to retry on transient errors.

Question 13

A media company uses Bigtable to store user interaction events. The table has a row key that starts with a timestamp in reverse chronological order, followed by a user ID. As the dataset grows, the company notices that all writes are going to a single node, causing hot spotting and high latency. The database engineer needs to redesign the row key to distribute writes evenly across all nodes while still allowing efficient queries for the most recent events for a given user. Which row key design should the engineer choose?

Accepted Answer

Use a row key that starts with a salted hash of the user ID, followed by the reversed timestamp.. Salting with a hash of the user ID distributes writes across nodes because the hash breaks the sequential pattern. Appending the reversed timestamp allows efficient retrieval of the most recent events for a user by scanning the beginning of that user's key range. This design addresses hot spotting while preserving query performance for recent events.

Answer

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

Answer

Use a row key that starts with the user ID, followed by the timestamp in reverse chronological order.

Answer

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

Question 14

A healthcare company is deploying a new Cloud Spanner instance to store patient records with strong global consistency. They must choose a configuration that minimizes write latency for users in both the United States and Europe while meeting data residency requirements that keep data within those regions. They also need to ensure the instance can survive a single region failure. Which two actions should they take? (Choose two.)

Accepted Answer

Verify that the chosen instance configuration has a quorum of voting replicas that can tolerate the loss of one region without losing write availability.. A multi-region instance with read-write regions in both the US and Europe allows writes to be served locally on each continent, minimizing write latency, and it keeps data within the selected regions for residency. Such configurations are built with a quorum that can tolerate a single region failure, ensuring write availability. Regional or read-only-replica designs cannot meet both latency and failover goals.

Answer

Create two separate regional instances, one in us-central1 and one in europe-west1, and use application-level dual writes.

Answer

Create a regional instance in us-central1 and add a read-only replica in europe-west1.

Answer

Use a multi-region instance configuration that includes us-central1 and europe-west1 as read-only regions, with a single read-write region in us-central1.

Question 15

A company is deploying a Cloud Spanner instance for a global application. They need to ensure high availability and low latency for users in North America, Europe, and Asia. The application requires strong consistency and 99.999% availability. They also want to minimize cost while meeting these requirements. Which two configurations should they choose? (Choose two.)

Accepted Answer

Use a multi-region instance configuration with read-write replicas in North America and Asia, and a read-only replica in Europe.. To achieve low latency across North America, Europe, and Asia with strong consistency and 99.999% availability, a multi-region configuration with two read-write replicas and one read-only replica is optimal. The read-write replicas ensure strong consistency and high availability, while the read-only replica provides low-latency reads in the third region at lower cost than a full read-write replica. Both configurations with two read-write and one read-only replica meet the requirements, but the placement of the read-only replica can vary based on read patterns.

Answer

Enable automatic failover and use a single read-write replica in North America.

Answer

Use a multi-region instance configuration with read-write replicas in all three regions.

Answer

Use a regional instance configuration in a single region.