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

Question 1

A global e-commerce company uses Cloud SQL for MySQL to store inventory data. They have a single primary instance in us-central1 and two read replicas in us-west1 and europe-west1 for local reads. Recently, the primary instance experienced a hardware failure causing an outage. The failover to a Cloud SQL high availability (HA) instance took 2 minutes. However, during that time, inventory updates were lost because the binary log position was not fully synchronized. The company requires zero data loss for inventory updates. What should the database engineer do?

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

Accepted Answer

Migrate to Cloud Spanner with multi-region configuration.. Cloud Spanner with a multi-region configuration provides synchronous replication across regions, ensuring strong consistency and zero data loss during failover. Unlike Cloud SQL's asynchronous replication, Spanner uses the Paxos protocol to commit writes across multiple regions before acknowledging success, which eliminates the risk of lost inventory updates during a primary failure.

Answer

Use Cloud SQL with external replication and a stand-by instance in another region.

Answer

Implement application-level write-ahead logging and replay on failover.

Answer

Enable point-in-time recovery with a 7-day retention.

Question 2

A financial services company runs a Cloud SQL for PostgreSQL instance for transactional data. They need to conduct regular security audits and compliance checks. The database engineer must ensure that all connections to the database are encrypted and that access is restricted to authorized VMs only. The database is currently accessible from the internet via an authorized network with a public IP. What should the database engineer do to meet these requirements?

Accepted Answer

Enable Cloud SQL private IP and disable public IP. Use VPC Service Controls and Cloud Identity-Aware Proxy for access.. It addresses both requirements: encryption and access restriction. Enabling Cloud SQL private IP ensures that the database is only reachable from within the VPC, eliminating internet exposure. VPC Service Controls provide a security perimeter to prevent data exfiltration, and Cloud Identity-Aware Proxy (IAP) enables fine-grained, identity-based access to the database without requiring a public IP or VPN.

Answer

Create a Cloud SQL proxy instance in the same VPC and force all clients to connect through the proxy.

Answer

Configure SSL/TLS for all connections and use an authorized network with a specific CIDR range.

Answer

Enable Cloud SQL public IP with SSL/TLS and restrict access using Cloud Armor.

Question 3

A company is migrating an on-premises PostgreSQL database to Cloud SQL. They need to minimize downtime during migration. Which TWO steps should they take?

Accepted Answer

Set up a read replica for switchover. (Set up a read replica for switchover) minimizes downtime by allowing a near-instantaneous cutover: you can promote the read replica to primary with minimal interruption. Option E (Use Database Migration Service with continuous replication) synchronizes changes in real time, enabling a controlled switchover with very low downtime. Option B (pg_dump and restore) involves exporting and importing data, which requires taking the source database offline or operating in read-only mode, causing significant downtime. Option C (Enable automatic backups) is a backup strategy that does not directly address migration downtime. Option D (Configure a high-availability instance) is post-migration configuration for resilience, not a step that reduces downtime during the migration.

Answer

Use pg_dump and restore

Answer

Enable automatic backups

Answer

Configure a high-availability instance

Question 4

Refer to the exhibit. A database administrator notices that the Spanner instance has only 3 nodes, but the application experiences high read latency during peak hours. The team needs to improve performance without over-provisioning. What should they do?

Accepted Answer

Create a secondary index. Creating a secondary index on frequently queried columns allows Spanner to avoid full table scans, reducing read latency without increasing nodes. Option A (increase node count) would provision more compute and storage capacity, but if CPU utilization is not high, it constitutes over-provisioning. Option B (interleaved table) improves performance for parent-child joins but does not help general read queries. Option C (enable point-in-time recovery) adds storage costs for versioned data without directly improving read latency. Option D (change to multi-region configuration) increases write latency and cost due to replication across regions, and may not reduce read latency in a single-region setup.

Answer

Increase node count to 6

Answer

Use an interleaved table

Answer

Enable point-in-time recovery

Answer

Change to a multi-region configuration

Question 5

A media company streams video content to users worldwide. Their metadata database on Cloud Spanner must serve low-latency reads from both the US and Europe. The database currently uses a regional instance configuration in us-central1. The team wants to minimize read latency for European users without changing the application's SQL queries. What should they do?

Accepted Answer

Recreate the instance using a multi-region instance configuration such as nam-eur-asia1.. Cloud Spanner instance configurations define where data is stored and replicated. A multi-region configuration automatically replicates data across regions, enabling low-latency reads from multiple geographies without application changes. Regional instances keep data in a single region, so European users experience higher latency. Recreating the instance with a multi-region configuration is the intended solution for global read latency.

Answer

Add a read-only replica in europe-west1 and update the application to use a new connection string.

Answer

Enable Data Boost on the existing instance to offload read traffic to a separate compute pool.

Answer

Create a second regional instance in europe-west1 and use change streams to replicate data between them.

Question 6

A media company runs a Cloud SQL for PostgreSQL instance in us-central1 for its video metadata catalog. The database is 3 TB and the company needs a read replica in europe-west1 to serve European users with low latency. The company's security policy requires that all data in transit between the primary and the replica be encrypted with a customer-managed encryption key (CMEK) and that the replica use the same key. Which configuration should the database engineer use?

Accepted Answer

Create a cross-region read replica with the same CMEK key as the primary instance.. Cross-region read replicas in Cloud SQL for PostgreSQL can be created with the same CMEK key as the primary. This provides encryption at rest with the customer-managed key and ensures replication traffic is encrypted in transit by default. The replica in europe-west1 serves European users with lower latency. The other options either violate the CMEK requirement or do not address latency.

Answer

Create a read replica in the same region and use a VPN to route European traffic to it.

Answer

Create a cross-region read replica and enable SSL/TLS encryption with a self-signed certificate.

Answer

Create a cross-region read replica with a different CMEK key in the replica's region.

Question 7

A financial services company uses Cloud Spanner for a global ledger application. The database has a table with a monotonically increasing primary key (timestamp-based) and experiences hot spotting on writes. The company needs to reduce write latency and improve throughput without changing the application's write pattern. Which approach should the database engineer take?

Accepted Answer

Use a UUID or bit-reversed sequence for the primary key.. Cloud Spanner hot spotting occurs when writes concentrate on a narrow key range, such as with monotonically increasing keys. Using a UUID or bit-reversed sequence distributes writes across the key space, improving throughput and reducing latency. This change does not alter the application's write pattern because the key generation can be modified independently. The other options either require application changes or do not address the root cause.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Hash the primary key values and store the hash as the primary key.

Answer

Enable optimistic concurrency control for writes.

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 need high availability or read replicas. The database will be used only during business hours and can tolerate downtime. Which Cloud SQL edition should they choose?

Accepted Answer

Sandbox edition. Cloud SQL offers three editions: Enterprise, Enterprise Plus, and Sandbox. Sandbox is designed for development and testing with minimal cost, no SLA, and limited features. It is the best fit for a startup that needs a temporary, low-cost PostgreSQL instance without high availability or read replicas. Enterprise and Enterprise Plus are for production workloads with higher costs.

Answer

Developer edition

Answer

Enterprise Plus edition

Answer

Enterprise edition

Question 9

You are designing a new Cloud SQL for PostgreSQL instance for a financial application that requires a recovery point objective (RPO) of less than 5 minutes. The instance will be in a single region. You need to minimize data loss in the event of a zonal failure. What should you configure?

Accepted Answer

Enable high availability (HA) with a regional persistent disk and set the transaction log retention period to 7 days.. High availability (HA) in Cloud SQL for PostgreSQL provides a synchronous standby in a different zone, ensuring near-zero data loss on zonal failure. The regional persistent disk replicates data synchronously, so the RPO is seconds, not minutes. This meets the requirement without manual intervention. Other options involve asynchronous replication or manual recovery, which cannot guarantee an RPO under 5 minutes.

Answer

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

Answer

Enable point-in-time recovery (PITR) and set the transaction log retention period to 7 days.

Answer

Create a Cloud SQL instance with a cross-region replica and use it for disaster recovery.

Question 10

A multinational corporation runs a mission-critical Cloud Spanner database with a multi-region configuration in nam3 (us-east1, us-central1, us-west1). The database has a table with a monotonically increasing primary key, causing hotspots. The engineering team wants to optimize write performance without changing the application's write pattern. They decide to use a hash-based sharding technique by adding a new column and using it as the primary key prefix. Which approach should they take to minimize hotspots while maintaining query performance?

Accepted Answer

Create a new column `shard_id` of type INT64, compute it as `MOD(original_key, 1000)`, and make the primary key `(shard_id, original_key)`. Update all queries to filter on `shard_id` when possible.. To reduce hotspots caused by a monotonically increasing primary key, introduce a shard column and use it as the primary key prefix. A deterministic hash like MOD(original_key, 1000) distributes writes across many splits while allowing queries to filter on the shard_id if needed. This maintains read performance for queries that include the shard_id and minimizes write hotspots.

Answer

Create a new column `shard_id` of type STRING, compute it as a random UUID, and make the primary key `(shard_id, original_key)`. Update all queries to use the new primary key.

Answer

Create a new column `shard_id` of type INT64, compute it as `MOD(original_key, 1000)`, and add a secondary index on `original_key`. Keep the original primary key unchanged.

Answer

Create a new column `shard_id` of type INT64, compute it as `MOD(original_key, 100)`, and make the primary key `(original_key, shard_id)`. Update all queries to filter on `original_key`.

Question 11

A financial services firm runs a 4 TB Cloud SQL for PostgreSQL instance in us-east1. The security team requires that all data at rest be encrypted with a customer-managed encryption key (CMEK) and that the key be rotated every 90 days. The database administrator has already created a Cloud KMS key ring and key in the same region. Which configuration should the administrator perform to meet these requirements?

Accepted Answer

Create the Cloud SQL instance with the --disk-encryption-key flag pointing to the Cloud KMS key version, and rely on Cloud KMS automatic rotation for the 90-day requirement.. Cloud SQL for PostgreSQL supports customer-managed encryption keys (CMEK) by specifying a Cloud KMS key at instance creation. The key can be rotated automatically by Cloud KMS, and the instance will use the latest version. Enabling CMEK after creation is not supported, and CSEK is not available. Therefore, the correct configuration is to create the instance with the CMEK key and rely on Cloud KMS rotation.

Answer

Use a customer-supplied encryption key (CSEK) stored in Cloud KMS, and configure a cron job to re-encrypt the database every 90 days.

Answer

Set the instance's encryption to Google-managed and create a Cloud KMS key to encrypt backups, ensuring the key rotates every 90 days.

Answer

Enable CMEK by updating the instance with gcloud sql instances patch --disk-encryption-key, then manually rotate the key version every 90 days.

Question 12

A financial services company runs a Cloud SQL for MySQL instance with a single primary and two read replicas. During a quarterly audit, they need to generate a consistent snapshot of the database for analysis without impacting the performance of the primary. The snapshot must be point-in-time consistent across all tables. What should they do?

Accepted Answer

Create an on-demand backup of the primary instance and restore it to a new instance.. Cloud SQL on-demand backups create a consistent snapshot of the entire instance. Restoring that backup to a new instance yields a point-in-time consistent copy of all tables, ideal for analysis. This method does not affect the primary's performance because the backup is taken from the instance's storage layer, and the restore is to a separate instance. Other methods either impact the primary or do not guarantee consistency.

Answer

Configure a federated query from BigQuery to the Cloud SQL instance.

Answer

Use the mysqlbinlog utility to replay binary logs from the primary to a new instance.

Answer

Run mysqldump on the primary instance during off-peak hours.

Question 13

A financial analytics team runs a 4 TB Cloud SQL for PostgreSQL instance that ingests trade records continuously. The database must remain available for reads even if the primary zone fails, and the recovery point objective (RPO) is under one minute. The team also wants to minimize cost by not running an idle standby that serves no traffic. Which configuration should they choose?

Accepted Answer

Enable high availability with a regional persistent disk and automatic failover to the standby in another zone.. High availability in Cloud SQL maintains a synchronized standby in a second zone and fails over automatically, which satisfies both the sub-minute RPO and read availability without paying for a serving replica. Cross-region replicas serve reads but require manual promotion, and backups cannot meet the recovery time objective for a 4 TB database.

Answer

Create a Cloud SQL backup schedule every 15 minutes and restore to a new instance during an outage.

Answer

Configure a cross-region read replica and promote it automatically during an outage.

Answer

Enable high availability on the instance and place a read replica in a different region.

Question 14

Your company runs a Cloud Spanner database with a multi-region configuration in nam3 (us-east1, us-central1, us-west1). The database has a table with a monotonically increasing primary key, and you notice hotspots on write operations. You need to redesign the primary key to distribute writes evenly. What should you do?

Accepted Answer

Hash the primary key value and use the hash as the primary key.. Hashing the primary key value distributes writes uniformly across the key space, preventing hotspots from sequential inserts. Cloud Spanner splits data by key ranges, so a monotonically increasing key causes all writes to go to the last range. Hashing ensures that new keys are scattered, allowing Spanner to distribute load across multiple servers. Storing the original key separately preserves query capability. This is the recommended practice for high-write workloads with sequential keys.

Answer

Enable automatic sharding on the table to distribute the load across multiple nodes.

Answer

Use a composite primary key with the first column being a random integer and the second column being the original key.

Answer

Use a UUID (Universally Unique Identifier) as the primary key and let Cloud Spanner generate it automatically.

Question 15

A global gaming company uses Cloud Bigtable to store player session data. They need to ensure that reads and writes are strongly consistent and that the cluster can tolerate a zone failure without data loss. They also want to minimize latency for users in North America and Europe. Which configuration should they choose?

Accepted Answer

Create a Bigtable instance with clusters in us-central1 and europe-west1, and use a multi-cluster routing policy that ensures writes are acknowledged by both clusters before returning success.. Bigtable replication is asynchronous, so strong consistency across clusters is not guaranteed. However, to tolerate a zone failure without data loss and minimize latency, a multi-cluster instance with clusters in both regions is necessary. The routing policy should be configured to route requests to the nearest cluster, but writes must be replicated to both clusters for durability. The option that describes acknowledging writes in both clusters provides the highest level of consistency available.

Answer

Create a Bigtable instance with a single cluster in us-central1 and configure it with SSD storage and a high number of nodes.

Answer

Create a Bigtable instance with a single cluster in us-central1 and enable replication to europe-west1.

Answer

Create a Bigtable instance with a multi-cluster routing policy set to 'multi_cluster_routing_use_single' and clusters in us-central1 and europe-west1.