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

Question 1

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.. Option D is correct because using Database Migration Service (DMS) for continuous migration is not needed; instead, promoting the cross-region read replica is the fastest failover method. However, with asynchronous replication, some data loss may occur. To minimize data loss, they should set replication lag alerts and automate failover procedures. Option A is wrong because backup-based recovery takes longer than 15 minutes for 1TB. Option B is wrong because synchronous replication across regions is not supported in Cloud SQL for PostgreSQL. Option C is wrong because increasing backup frequency doesn't help with fast failover.

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 2

A company uses Cloud Firestore in Datastore mode for a multi-tenant SaaS application. Each tenant has a separate namespace. The application has grown rapidly, and the database engineer notices that write throughput is degrading. Monitoring shows that the number of writes per second is high but within Firestore limits. However, the latency for writes is increasing linearly with the number of tenants. The engineer suspects that index management is causing the problem. The current schema uses automatic indexes for all properties. What is the best corrective action?

Accepted Answer

Disable automatic indexing and create custom composite indexes only for queries that require them.. The correct answer is A because automatic indexing in Firestore in Datastore mode creates an index for every property, which causes write amplification as each write must update all relevant indexes. With many tenants in separate namespaces, the number of indexes grows linearly with tenants, increasing write latency. Disabling automatic indexing and creating custom composite indexes only for needed queries reduces the index write overhead, restoring write throughput.

Answer

Implement a data retention policy to delete old tenant data.

Answer

Request a throughput increase from Google Cloud support.

Answer

Shard tenants into separate Firestore databases to distribute the write load.

Question 3

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 4

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 5

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 6

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 7

A company has a Cloud SQL instance with a 500 GB database. They need to perform a major version upgrade from MySQL 5.7 to 8.0 with minimal downtime. Which strategy should they use?

Accepted Answer

Create a read replica with new version, then promote. Creating a read replica with the new MySQL version and then promoting it minimizes downtime because replication keeps the replica synchronized with the primary until promotion. This approach avoids the lengthy export/import process and reduces the risk of data loss or extended unavailability.

Answer

Export and import the database

Answer

Use in-place upgrade via gcloud command

Answer

Upgrade during maintenance window

Question 8

A developer needs a local development database that mirrors a Cloud SQL instance. What is the best practice?

Accepted Answer

Export data and import to local MySQL. Option C is correct because exporting the Cloud SQL instance data (e.g., using `gcloud sql export sql` or `mysqldump`) and importing it into a local MySQL database creates an exact, offline replica of the production schema and data. This allows the developer to work with a full, consistent dataset without network latency, connection overhead, or dependency on Cloud SQL availability, which is the standard best practice for local development mirrors.

Answer

Use Cloud SQL Proxy to connect locally

Answer

Use Cloud Functions

Answer

Use Cloud SQL's public IP

Question 9

Which TWO database services are fully managed and support global distribution of data for low-latency reads and writes?

Accepted Answer

Cloud Spanner. Cloud Spanner is a fully managed, globally distributed relational database service that provides strong consistency and horizontal scaling across regions. It supports global distribution of data for low-latency reads and writes by using synchronous replication and atomic clocks for TrueTime, enabling ACID transactions at global scale.

Answer

Memorystore

Answer

Bigtable

Answer

Cloud SQL

Question 10

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) in Cloud Spanner allows you to recover data to any point within the retention period (default 7 days), which is essential for granular disaster recovery against logical errors or accidental data changes. This complements backup strategies by providing fine-grained restore capabilities beyond full backups.

Answer

Schedule regular exports to Cloud Storage

Answer

Use read replicas in another region

Question 11

Which TWO actions can a team take immediately to resolve a Cloud SQL instance running out of storage?

Accepted Answer

Enable automatic storage increase. Increasing storage capacity and enabling automatic storage increase directly address the storage shortage. Deleting binary logs can free space but is not recommended as it may affect point-in-time recovery. Migrating to Cloud Spanner is a long-term change. Adding read replicas does not increase storage.

Answer

Delete binary log files

Answer

Migrate to Cloud Spanner

Answer

Add read replicas

Question 12

A global gaming company uses Cloud Spanner for user profiles and game state. They have a single-region instance in us-central1. Recently, they launched in Europe and notice high latency for European users. They also need to ensure data locality compliance (GDPR). The database is heavily written with throughput spikes. They want to minimize latency without sacrificing write throughput. They consider adding a secondary index on region column. What is the best course of action?

Accepted Answer

Create a multi-region configuration spanning US and Europe, and modify application to read from closest region.. Option D is correct because a multi-region Spanner configuration with regional endpoints allows the application to read from the closest region, reducing latency for European users while maintaining strong consistency and write throughput. Spanner's multi-region configurations use synchronous replication across regions, ensuring GDPR compliance by keeping European user data within Europe for reads, and the write throughput is not sacrificed because all writes are committed to the primary region and replicated asynchronously to other regions.

Answer

Shard the database by region into separate Spanner instances.

Answer

Use Cloud CDN to cache read data.

Answer

Create a read-only replica in Europe using Cloud Spanner's read replica feature.

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.. Option C is correct because 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 small business runs a Cloud SQL instance with 10 GB data. They want to automate daily backups with 7-day retention. They also need to restore quickly if needed. What is the simplest solution?

Accepted Answer

Enable automatic backups in Cloud SQL settings.. Option C is correct because Cloud SQL provides a built-in automatic backup feature that can be configured with a 7-day retention period directly in the instance settings. This eliminates the need for custom scripts or external schedulers, and enables point-in-time recovery for fast restoration without manual export/import overhead.

Answer

Use Cloud Scheduler to trigger exports.

Answer

Export to Cloud Storage daily using a cron job.

Answer

Use gcloud to create a backup schedule with retention.

Question 15

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.. Option C is correct because 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.