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

Question 1

A company is planning to migrate a self-managed MongoDB database to a fully managed Google Cloud service. They need to maintain high availability and support complex queries with aggregation pipelines. Which TWO services should they consider?

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

Accepted Answer

MongoDB Atlas on Google Cloud. MongoDB Atlas on Google Cloud is a fully managed MongoDB service that provides native support for MongoDB's aggregation pipeline and high availability through replica sets and automated failover. Cloud SQL for PostgreSQL with JSON data type is a relational database that offers advanced JSON functions (e.g., jsonb_path_query) and can emulate many aggregation pipeline operations, making it a viable option for complex queries while still being fully managed. The other options lack native aggregation pipeline support: Cloud Spanner is a relational database with strong consistency but limited JSON support; Cloud Firestore is a document database with simpler queries; Cloud Bigtable is a wide-column store without aggregation pipeline capabilities.

Answer

Cloud Spanner

Answer

Cloud Firestore

Answer

Cloud Bigtable

Question 2

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 (Option A) is the correct action because it allows the application to resume operations quickly (under 15 minutes) with minimal data loss. Asynchronous replication means some transactions may be lost, but this approach is faster than restoring from a backup, which would exceed the 15-minute RTO for a high-write application. Options B, C, and D are incorrect: synchronous replication across regions is not supported in Cloud SQL for PostgreSQL (B); restoring from backup takes longer than 15 minutes and results in more data loss (C); increasing backup frequency does not enable fast failover (D).

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 3

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.. 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 4

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 5

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 6

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 7

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 8

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 9

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. 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 10

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 11

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). 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 12

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

Accepted Answer

Enable automatic storage increase. Enabling automatic storage increase on a Cloud SQL instance allows the database to automatically add storage when it detects that the available space is running low, preventing out-of-storage errors without manual intervention. This is a quick, immediate action that requires no downtime and is configured via the Cloud Console or gcloud command.

Answer

Delete binary log files

Answer

Migrate to Cloud Spanner

Answer

Add read replicas

Question 13

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.. A multi-region Cloud 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 all regions, ensuring GDPR compliance by keeping European user data within Europe for reads. Write throughput is not sacrificed because Spanner's architecture is designed for high scalability and uses synchronous replication efficiently.

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 14

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 15

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.. 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.