PCDE Plan and manage database infrastructure — 30 Questions

Question 1

A company runs a production Cloud SQL for PostgreSQL instance used by a web application. The instance experiences intermittent latency spikes during peak hours. You need to diagnose the cause without downtime. Which tool should you use first?

Accepted Answer

Use Cloud SQL Insights to analyze query performance and identify slow queries.. Cloud SQL Insights provides built-in query performance monitoring and diagnostics without requiring any downtime. It surfaces slow queries, lock contention, and resource bottlenecks directly from the PostgreSQL engine, making it the ideal first step to identify the root cause of intermittent latency spikes during peak hours.

Answer

Use Database Migration Service to failover to a read replica.

Answer

Use gcloud sql instances describe to check instance configuration.

Answer

Use VPC Flow Logs to analyze network traffic.

Question 2

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. The database is 2 TB in size and the network bandwidth to Google Cloud is limited to 500 Mbps. The migration window is 48 hours. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Export the database to flat files, compress, upload to Cloud Storage, then import to Cloud SQL.. Option C is correct because the 2 TB database size and 500 Mbps bandwidth yield a theoretical transfer time of approximately 9.5 hours (2 TB * 1024 GB/TB * 8 bits/byte / 500 Mbps / 3600 seconds/hour), which fits within the 48-hour window. However, pg_dump/pg_restore over a VPN (Option A) would be slower due to TCP overhead and latency, and Database Migration Service with continuous replication (Option B) requires ongoing connectivity and may not complete the initial load within the window. Exporting to flat files, compressing them (e.g., with gzip), uploading to Cloud Storage, and then importing to Cloud SQL leverages high-throughput parallel uploads and avoids network latency issues, making it the most reliable strategy for a one-time migration within the given constraints.

Answer

Create a VPN tunnel and use pg_dump/pg_restore over the network.

Answer

Use Database Migration Service with continuous replication.

Answer

Request a dedicated interconnect and then migrate.

Question 3

A financial services company uses Cloud Spanner for transaction processing. They need to ensure zero downtime during a schema change that adds a new column with a default value to a large table. Which approach should the Database Engineer take?

Accepted Answer

Use ALTER TABLE to add the column; Spanner handles schema changes online.. Option D is correct because Cloud Spanner supports online schema changes, including adding columns with default values, without requiring a maintenance window or causing downtime. Spanner applies schema updates asynchronously across all nodes while the database remains fully available for reads and writes, making it the ideal approach for zero-downtime requirements.

Answer

Create a new table with the new column, then use a fan-out pattern to write to both tables until the old table is deprecated.

Answer

Use an ALTER TABLE statement during a maintenance window.

Answer

Drop the table and recreate it with the new schema.

Question 4

A company runs a BigQuery data warehouse. They notice that query performance has degraded over time. The data is loaded daily from Cloud Storage using batch loads. Which action is most likely to improve query performance?

Accepted Answer

Partition and cluster tables based on common query filters.. Partitioning and clustering tables based on common query filters directly reduces the amount of data scanned per query by organizing data into physical segments. In BigQuery, this allows the query engine to prune entire partitions and clusters, significantly lowering I/O and improving performance without additional cost or complexity.

Answer

Increase the number of slots in the reservation.

Answer

Create materialized views for all frequent queries.

Answer

Migrate the data to Cloud SQL for better performance.

Question 5

A gaming company uses Memorystore for Redis to cache player session data. They need to ensure high availability with automatic failover in case of a zone failure. Which configuration should the Database Engineer choose?

Accepted Answer

Deploy a Standard tier Redis instance with replication across two zones.. Memorystore for Redis Standard tier provides cross-zone replication with automatic failover, ensuring high availability during a zone failure. The Standard tier uses a primary and replica instance in different zones, and if the primary fails, the replica is automatically promoted. This meets the requirement for automatic failover without manual intervention.

Answer

Deploy a Basic tier Redis instance with multiple read replicas.

Answer

Deploy a Memcached cluster with multiple nodes.

Answer

Deploy a Basic tier Redis instance in a single zone.

Question 6

A company is designing a global application using Cloud Spanner. They need to ensure low latency reads and writes across three continents. Which TWO configurations should they consider?

Accepted Answer

Use a multi-region configuration with leader regions in each continent.. Option A is correct because Cloud Spanner multi-region configurations allow you to place leader regions in multiple continents, which enables low-latency strongly consistent reads and writes by directing traffic to the nearest leader. This is achieved through Spanner's TrueTime and Paxos-based replication, ensuring global consistency without sacrificing performance.

Answer

Use a single-region instance and rely on application caching.

Answer

Use strongly consistent reads from a single region.

Answer

Use interleaved tables to optimize query performance.

Question 7

A company is migrating their on-premises MySQL database to Cloud SQL. The database is 500 GB and they have a 1 Gbps network connection. They want to minimize downtime. Which THREE steps should they take?

Accepted Answer

Perform a test migration to validate the process.. Option A is correct because performing a test migration validates the entire process, including schema compatibility, data integrity, and application connectivity, before the actual cutover. This reduces the risk of unexpected failures during the production migration, which is critical for minimizing downtime. A test migration also helps estimate the time required for the final cutover and allows for tuning of the Database Migration Service settings.

Answer

Export the database to a SQL file and import after the cutover.

Answer

Schedule a maintenance window for the cutover.

Question 8

Your company runs a critical PostgreSQL database on Cloud SQL. You need to minimize downtime during a schema migration that could take up to 30 minutes. What should you do?

Accepted Answer

Create a clone of the instance, perform the migration on the clone, then promote the clone.. Option B is correct because creating a clone of the Cloud SQL instance allows you to perform the schema migration on an isolated copy without affecting the production database. Once the migration is complete and verified, you can promote the clone to take over as the primary instance, minimizing downtime to just the brief promotion switchover (typically seconds). This approach avoids the long 30-minute migration window on the live database.

Answer

Use Database Migration Service to migrate to a new instance during the migration window.

Answer

Configure a high-availability instance and perform the migration during a planned failover.

Answer

Add a read replica, perform the migration on the replica, then promote it.

Question 9

Your team is migrating a 5 TB MySQL database from on-premises to Cloud SQL. The database receives 2,000 writes per second and the network link has 500 Mbps bandwidth. You need to minimize migration time with zero data loss. What should you do?

Accepted Answer

Configure Database Migration Service with a connectivity test and start continuous replication.. Database Migration Service (DMS) with continuous replication is the correct choice because it supports live migration with minimal downtime and zero data loss for large databases like 5 TB. DMS uses MySQL binlog-based replication to synchronize changes from the on-premises source to Cloud SQL while the source remains operational, then performs a cutover with only seconds of downtime. This approach handles the 2,000 writes/second load and 500 Mbps bandwidth efficiently by streaming incremental changes rather than transferring the entire 5 TB in one shot.

Answer

Export the database using mysqldump and import into Cloud SQL using the console.

Answer

Use gcloud sql import with a compressed CSV export from on-premises.

Answer

Set up a VPN, then use mysqldump with --master-data and pipe to mysql on Cloud SQL.

Question 10

Your organization requires that all database backups be stored in a different region for disaster recovery. You are using Cloud SQL for MySQL. What backup configuration should you use?

Accepted Answer

Enable automated backups and select a different region for the backup location.. Cloud SQL for MySQL allows you to specify a different region for automated backup storage, which satisfies the disaster recovery requirement of storing backups in a separate region. By selecting a different region for the backup location, you ensure that if the primary region fails, the backups remain accessible for recovery. This is the only built-in option that directly meets the cross-region backup requirement without additional manual steps.

Answer

Enable automated backups and select the same region as the instance.

Answer

Use on-demand exports to Cloud Storage in the same region.

Answer

Configure a multi-region Cloud Storage bucket and point automated backups there.

Question 11

You are designing a database architecture for a global e-commerce application. The application requires low-latency reads in multiple regions and must handle up to 100,000 writes per second globally. Which Google Cloud database solution should you use?

Accepted Answer

Cloud Spanner with multi-region configuration.. Cloud Spanner with a multi-region configuration is the correct choice because it provides globally distributed, strongly consistent relational database service with horizontal scalability, supporting over 100,000 writes per second across multiple regions while maintaining ACID transactions and low-latency reads via regional replicas. This meets the requirements of a global e-commerce application needing high write throughput and low read latency in multiple regions.

Answer

Firestore in multi-region mode.

Answer

Bigtable with multiple clusters.

Answer

Cloud SQL with cross-region replication.

Question 12

Your team uses Cloud SQL for PostgreSQL and needs to run a one-time data correction query that will update 10 million rows. The instance has 8 vCPUs and 30 GB memory. The query is currently running for hours and impacting production performance. What should you do?

Accepted Answer

Create a clone of the instance, run the update on the clone, then promote it.. Option B is correct because creating a clone of the Cloud SQL instance provides an isolated environment where the heavy UPDATE can run without impacting production performance. After the update completes on the clone, you can promote it to become the new primary instance, effectively applying the data correction with minimal downtime. This approach avoids the performance degradation caused by running the query on the production instance and leverages Cloud SQL's cloning feature for a one-time data correction.

Answer

Create a read replica and run the update on the replica.

Answer

Run the query during off-peak hours with reduced concurrency.

Answer

Increase the instance size to 16 vCPUs and 60 GB memory before running the query.

Question 13

Your company is migrating a 2 TB SQL Server database to Cloud SQL. You need to choose a migration approach that minimizes downtime and supports ongoing changes. Which TWO options meet these requirements?

Accepted Answer

Use Database Migration Service with a source of SQL Server.. Database Migration Service (DMS) supports continuous change data capture (CDC) for SQL Server, enabling near-zero-downtime migrations by replicating ongoing changes from the source to Cloud SQL until cutover. This approach minimizes downtime and keeps the database synchronized during the migration window, meeting both requirements.

Answer

Export the database as a .bak file and restore to Cloud SQL.

Answer

Use BCP to export data and import via gcloud sql import.

Answer

Migrate to Azure SQL Managed Instance first, then to Cloud SQL.

Question 14

Your organization uses Cloud Spanner for a global financial application. You are designing a backup and disaster recovery strategy. Which THREE considerations are important for meeting RPO of 1 hour and RTO of 2 hours?

Accepted Answer

Enable point-in-time recovery (PITR) with a 7-day retention.. Option A is correct because Cloud Spanner's point-in-time recovery (PITR) allows you to restore the database to any point within the retention window (up to 7 days). With a 1-hour RPO, PITR can recover data to within the last hour, meeting the requirement without needing separate backup jobs. PITR is built into Spanner and does not require external scheduling or storage.

Answer

Store backups in a multi-regional Cloud Storage bucket.

Answer

Set up read replicas in a different region for failover.

Question 15

You run the above command to create a Spanner instance. Later, you need to increase the instance's compute capacity to handle higher traffic. What is the correct approach?

Accepted Answer

Run gcloud spanner instances update test-instance --nodes=5. Option D is correct because Cloud Spanner allows you to increase the compute capacity of an existing instance by updating the node count using the `gcloud spanner instances update` command. This operation is performed online without downtime, as Spanner supports live resizing of nodes to handle increased traffic.

Answer

Use the console to change the instance configuration to a larger one.

Answer

Delete the instance and recreate with --nodes=5.

Answer

Create a new instance with a larger config and migrate data.

Question 16

A company is migrating an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and must have minimal downtime during migration. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Use Database Migration Service to set up continuous replication from the on-premises database to Cloud SQL, then perform a minimal-downtime cutover.. Option C is correct because the Database Migration Service (DMS) supports continuous replication from an on-premises PostgreSQL database to Cloud SQL using logical replication (pglogical or native PostgreSQL logical replication). This allows the source database to remain operational during the migration, and the cutover to Cloud SQL can be performed with minimal downtime, typically seconds to minutes, meeting the 2 TB size and low-downtime requirements.

Answer

Export the database using pg_dump and import into Cloud SQL using pg_restore during a maintenance window.

Answer

Set up PostgreSQL on Compute Engine and replicate data, then switch over to Cloud SQL.

Answer

Use Cloud SQL's built-in replication feature to connect directly to the on-premises database.

Question 17

A retail company uses Cloud Spanner to handle global transaction processing. The database has a single regional instance in us-central1. The company expects a 10x increase in write traffic from a new mobile app. The database engineer needs to design for low latency writes globally and high availability. What should the Database Engineer do?

Accepted Answer

Change the instance configuration to a multi-region configuration like nam3 (us-central1, us-east1, us-west1) and configure a dedicated write region.. Option C is correct because a multi-region configuration like nam3 (us-central1, us-east1, us-west1) with a dedicated write region provides low-latency writes globally by using Google's managed replication and automatic failover. This design ensures high availability and meets the 10x write traffic increase without sacrificing write performance, as writes are processed in the designated write region and asynchronously replicated to other regions.

Answer

Shard the database across multiple regional instances based on user geography.

Answer

Create read replicas in other regions to offload read traffic and keep writes in the primary region.

Answer

Increase the number of nodes in the existing regional instance to handle the increased write capacity.

Question 18

A Database Engineer is responsible for managing a Cloud SQL for MySQL instance. The engineer needs to ensure that automated backups are retained for 14 days and that point-in-time recovery (PITR) is enabled. Which configuration should the engineer set?

Accepted Answer

Enable automated backups, set backup retention to 14 days, and enable binary logging.. Option B is correct because enabling automated backups with a 14-day retention ensures backup files are kept for the required duration, and enabling binary logging (which is required for PITR) allows point-in-time recovery by replaying transaction logs against a base backup. In Cloud SQL for MySQL, PITR is not automatically enabled with automated backups; binary logging must be explicitly enabled.

Answer

Create scheduled manual backups each day and retain them for 14 days; PITR is not needed because backups are daily.

Answer

Enable automated backups with a retention of 14 days; PITR is automatically enabled.

Answer

Enable automated backups with 14-day retention and set up a cross-region replica for disaster recovery.

Question 19

A company runs a critical application on Cloud SQL for PostgreSQL. The database engineer needs to ensure that if the primary instance fails, a standby instance in a different region can take over with minimal data loss. Which configuration should the Database Engineer implement?

Accepted Answer

Create a cross-region replica with asynchronous replication and manually promote it during a disaster.. Option C is correct because Cloud SQL for PostgreSQL supports cross-region replicas with asynchronous replication, which allows a standby instance in a different region to be promoted manually during a disaster. This minimizes data loss by replicating changes asynchronously, though some transactions may be lost if the primary fails before replication completes. Automatic failover is not supported for cross-region replicas in Cloud SQL, so manual promotion is required.

Answer

Set up a second Cloud SQL instance and configure application-level dual-writes to both instances.

Answer

Configure high availability (HA) within the same region using a regional persistent disk.

Answer

Create a cross-region replica with synchronous replication and enable automatic failover.

Question 20

A Database Engineer is designing a tiered storage strategy for a large BigQuery dataset. The dataset contains data that is accessed frequently for the first 30 days, moderately for the next 6 months, and rarely after that. The engineer wants to minimize overall storage cost while maintaining fast query performance on recent data. Which approach should the engineer take?

Accepted Answer

Use physical storage billing, partition the table by ingestion date, cluster on frequently used columns, and set an expiration on partitions older than 6 months to move them to long-term storage.. Option D is correct because physical storage billing in BigQuery charges separately for active and long-term storage, and setting a partition expiration on partitions older than 6 months automatically moves that data to long-term storage at a lower cost. Partitioning by ingestion date and clustering on frequently used columns ensures fast query performance on recent data while minimizing overall storage costs.

Answer

Use logical storage billing and partition the table by date; older data automatically moves to long-term storage after 90 days.

Answer

Use Bigtable for recent data and BigQuery for historical data, with a scheduled transfer between them.

Answer

Export data older than 30 days to Cloud Storage as Avro files and delete them from BigQuery; query them externally when needed.