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

Question 1

A company plans to migrate an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB in size and requires minimal downtime. Which migration approach should they use?

Accepted Answer

Use Database Migration Service with a continuous migration job.. Database Migration Service (DMS) with a continuous migration job is the correct approach because it supports minimal-downtime migrations from on-premises PostgreSQL to Cloud SQL. DMS uses logical replication (via PostgreSQL's pgoutput plugin) to continuously sync changes from the source to the target, allowing a short cutover window. For a 2 TB database, this avoids the lengthy downtime required by a full dump-and-load method.

Answer

Export the database using pg_dump and import into Cloud SQL using psql.

Answer

Use Datastream to stream data into Cloud SQL.

Answer

Copy the data files to Cloud Storage and use gcloud to load into Bigtable.

Question 2

An e-commerce application uses Cloud Spanner for its global inventory database. The application experiences high write latency during peak hours. After reviewing the schema, the database engineer notices that the primary key is an auto-incrementing integer. What is the most likely cause of the high write latency?

Accepted Answer

The monotonically increasing primary key creates a hotspot.. Cloud Spanner distributes data across splits based on the primary key range. A monotonically increasing integer primary key, such as an auto-incrementing ID, causes all new writes to target the same split (the highest key range), creating a hotspot. This single split becomes a bottleneck, leading to high write latency during peak hours, as Spanner cannot parallelize the writes across multiple nodes.

Answer

The application is using read-only transactions instead of read-write transactions.

Answer

The database is configured with a backup retention period of 2 seconds.

Answer

The application is using read replicas that are out of date.

Question 3

A company uses Cloud SQL for MySQL with a failover replica. The primary instance is in us-central1 and the replica is in us-east1. During a regional outage in us-central1, the database engineer executes an emergency failover to the replica. After the failover, applications experience high latency when writing to the new primary. What is the most likely cause?

Accepted Answer

The new primary is in a different region, causing higher network round-trip times for applications that are still in us-central1.. After a cross-region failover, the new primary resides in us-east1 while the applications remain in us-central1. This geographic distance increases network round-trip time (RTT) for write operations, as each write must traverse the WAN between regions. Cloud SQL for MySQL does not automatically relocate compute resources, so latency-sensitive applications will experience higher write latency until they are migrated or configured to connect to the new region.

Answer

Cross-region replication triggers a mandatory 1-hour delay before writes are allowed.

Answer

The failover did not complete successfully; the replica is still in read-only mode.

Answer

The replica had a replication lag of 5 minutes, causing data inconsistency.

Question 4

A startup is building a mobile app that requires a highly available, globally distributed, low-latency NoSQL database. The data model is key-value with occasional queries on a secondary field. Which database service should they choose?

Accepted Answer

Cloud Firestore. Cloud Firestore is a fully managed, globally distributed NoSQL document database that provides strong consistency, automatic multi-region replication, and low-latency queries on key-value pairs as well as secondary fields (via composite indexes). It is ideal for mobile apps requiring high availability and real-time data synchronization across the globe, directly matching the requirements of a key-value model with occasional secondary queries.

Answer

Cloud SQL (PostgreSQL)

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 5

A company needs to store session data for a web application that runs on Google Kubernetes Engine (GKE). The data is temporary and high-availability is required. Which database service is most appropriate?

Accepted Answer

Memorystore for Redis with replication. Memorystore for Redis with replication is the most appropriate choice because session data is temporary, requires high availability, and benefits from Redis's in-memory, low-latency key-value store. Replication provides failover capability, ensuring session continuity if a primary node fails, while Redis's built-in expiry (TTL) handles temporary data cleanup automatically.

Answer

Cloud Spanner

Answer

Cloud SQL for MySQL

Answer

Cloud Bigtable

Question 6

A database engineer is reviewing the configuration of a Cloud SQL for MySQL instance. The backup configuration shows binaryLogEnabled and pointInTimeRecoveryEnabled set to true. However, the engineer is unable to perform a point-in-time recovery (PITR) to a specific second within the last 30 days. What is the most likely reason?

Accepted Answer

The transaction log retention period is not set, defaulting to 7 days, so logs older than that are purged.. By default, Cloud SQL for MySQL sets the transaction log retention period to 7 days when binary logging and point-in-time recovery are enabled. This means that binary logs older than 7 days are automatically purged, making it impossible to perform a PITR to a specific second beyond that window, even if the backup retention is set to 30 days. To recover to any point within the last 30 days, the transaction log retention period must be explicitly configured to match the backup retention period.

Answer

Binary logging is not enabled for all databases on the instance.

Answer

The instance is not in a runnable state.

Answer

The database version is MySQL 8.0, which does not support PITR.

Question 7

A company runs a critical application on Cloud SQL for PostgreSQL with a primary instance in us-central1 and a cross-region read replica in us-west1 for disaster recovery. The database engineer is responsible for ensuring that in the event of a regional outage in us-central1, the application can continue with minimal data loss and within 15 minutes of downtime. The application writes about 1000 transactions per second. The current setup has automated backups enabled with point-in-time recovery (7-day retention) and the cross-region replica is configured with asynchronous replication. Which action should the database engineer take to meet the recovery objectives?

Accepted Answer

Promote the cross-region read replica to a new primary and redirect application traffic.. Promoting the cross-region read replica to a primary is the correct DR action for Cloud SQL for PostgreSQL. Promotion converts the asynchronous replica into a standalone primary in us-west1, allowing the application to resume writes there. With asynchronous replication, some data loss (RPO) is possible, but the promotion itself can be completed in minutes, satisfying the 15-minute RTO.

Answer

Change the cross-region replica to synchronous replication and enable automatic failover.

Answer

Create a new instance from the latest backup in us-west1 and redirect traffic.

Answer

Increase automated backup frequency to every hour and ensure binary logging is enabled.

Question 8

A company is migrating an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and has a high write workload. They need minimal downtime. Which migration approach is best?

Accepted Answer

Use Database Migration Service with continuous replication. Database Migration Service (DMS) with continuous replication is the best approach because it supports minimal-downtime migrations from on-premises PostgreSQL to Cloud SQL. DMS uses change data capture (CDC) to replicate ongoing writes while the initial data load completes, then performs a cutover with only seconds of downtime. This handles the 2 TB size and high write workload without requiring manual export/import or schema changes.

Answer

Use Cloud Spanner

Answer

Use Cloud SQL for MySQL instead

Answer

Export using pg_dump and import via psql

Question 9

A financial services company uses Cloud Spanner for transaction processing. They notice increased latency during peak hours. They suspect a hot spot. What is the best way to diagnose the issue?

Accepted Answer

Review Key Visualizer to identify hot keys. Key Visualizer is a Cloud Spanner tool that provides a heatmap of access patterns across keys and time ranges, directly revealing hot spots (e.g., monotonically increasing keys or skewed read/write distributions). This allows you to identify the specific keys causing contention without guesswork, making it the most targeted diagnostic approach for hot spot issues.

Answer

Add more nodes to the instance

Answer

Switch to Cloud SQL

Answer

Check Cloud Spanner's CPU utilization metrics

Question 10

A company wants to migrate a 100 GB MySQL database to Cloud SQL with minimal application changes. Which migration tool should they use?

Accepted Answer

Database Migration Service. Database Migration Service (DMS) is the correct tool because it is designed specifically for migrating databases to Cloud SQL with minimal downtime and minimal application changes. DMS uses a combination of initial snapshot and continuous change data capture (CDC) to replicate the source MySQL database to Cloud SQL, allowing the application to point to the new database with only a connection string update.

Answer

mysqldump

Answer

BigQuery Data Transfer Service

Answer

Storage Transfer Service

Question 11

An e-commerce site uses Cloud SQL for MySQL. They need read scalability for product catalog queries. What should they do?

Accepted Answer

Add read replicas. Adding read replicas in Cloud SQL for MySQL offloads read traffic from the primary instance, providing horizontal read scalability for product catalog queries. Replicas asynchronously replicate data using MySQL's native binary log replication, allowing the primary to focus on writes while replicas handle read-heavy workloads.

Answer

Partition tables

Answer

Use Cloud Spanner

Answer

Enable automatic storage increase

Question 12

Refer to the exhibit. A company has a Cloud Spanner instance with the backup configuration shown. They need to improve disaster recovery. Which THREE strategies should they implement?

Accepted Answer

Enable point-in-time recovery (PITR). Option B is correct because enabling point-in-time recovery (PITR) on the Cloud Spanner instance lets the company restore the database to any timestamp within the configurable retention window (up to 7 days), which directly strengthens disaster recovery against accidental data corruption or deletion. Option C is correct because using a multi-region instance configuration replicates data synchronously across multiple regions with a 99.999% SLA, providing resilience if an entire region becomes unavailable. Option D is correct because configuring cross-region backups stores backup copies in a different region than the source instance, protecting recovery data even if the instance's home region is lost. Option A is not the best fit because exports to Cloud Storage are manual or scheduled logical dumps that are not automatically managed as Spanner backups and have slower, less granular restore capabilities. Option E is not correct because read replicas in another region improve read latency and read availability but do not by themselves provide a recovery mechanism for restoring lost or corrupted data.

Answer

Schedule regular exports to Cloud Storage

Answer

Use read replicas in another region

Question 13

A news website uses Cloud SQL for MySQL for content management. They experience slow reads during breaking news events. They have a single primary instance in us-east1. They need to improve read scalability globally. They also want to ensure data is backed up in another region. What should they do?

Accepted Answer

Add cross-region read replicas in multiple regions and use replica read for queries.. Adding cross-region read replicas in multiple regions allows the website to offload read queries to replicas located closer to global users, reducing latency during traffic spikes. Cloud SQL for MySQL supports cross-region replicas, which also provide a backup copy of data in another region for disaster recovery, meeting both scalability and backup requirements without changing the database engine.

Answer

Use Cloud Spanner with multi-region configuration.

Answer

Enable automatic failover to a standby instance in another region.

Answer

Use Bigtable for content storage.

Question 14

A financial firm uses Cloud Spanner with a single-region configuration. They must meet regulatory requirements for disaster recovery across continents. They need to recover within 1 hour RTO and RPO of 5 minutes. Current workload: 50k writes/sec. What should they do?

Accepted Answer

Use multi-region configuration with synchronous replication across two continents.. Cloud Spanner's multi-region configuration uses synchronous replication across continents, providing strong consistency and automatic failover. This meets the 1-hour RTO and 5-minute RPO requirements for disaster recovery, as synchronous replication ensures data is durable across regions with minimal lag, and Spanner handles failover transparently without manual intervention.

Answer

Use cross-region backups with 5-minute retention.

Answer

Use Cloud SQL for MySQL with cross-region replicas.

Answer

Use export to Cloud Storage every 5 minutes.

Question 15

A healthcare company uses Cloud SQL for MySQL for patient records. They need to export data for a compliance audit. They must ensure the export includes all changes within a specific time window (e.g., last 24 hours). They have binary logging enabled. What is the best method to obtain a consistent snapshot of the data as of the audit time?

Accepted Answer

Use Cloud SQL clone to create a new instance from a point-in-time, then export from clone.. Cloud SQL's clone feature can create a new instance from a specific point-in-time using binary logs, providing a consistent snapshot of the database as of the audit time. This ensures all changes within the last 24 hours are captured without impacting the production instance, and the export can then be performed from the clone.

Answer

Use Database Migration Service's continuous export.

Answer

Use mysqldump to export at the audit time.

Answer

Use Cloud SQL's export feature with a specific backup-id.