PCDE Plan and manage database infrastructure — 40 Questions

Question 1

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

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

Accepted Answer

Enable point-in-time recovery (PITR) with a 7-day retention.. 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. Option C is correct because a multi-region Spanner instance uses synchronous replication across regions, ensuring durability and automatic failover within seconds, which satisfies the RTO of 2 hours. Option B is incorrect because Cloud Spanner does not support incremental backups; only full backups are available. Option D is incorrect because while backups can be stored in multi-regional Cloud Storage, this alone does not meet the RPO/RTO as it requires separate backup scheduling and restoration. Option E is incorrect because Cloud Spanner does not use read replicas for failover; it uses multi-region synchronous replication.

Answer

Schedule incremental backups every hour using Cloud Scheduler.

Answer

Store backups in a multi-regional Cloud Storage bucket.

Answer

Set up read replicas in a different region for failover.

Question 2

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 logical storage billing and partition the table by date; older data automatically moves to long-term storage after 90 days.. Using logical storage billing and partitioning by date allows BigQuery to automatically apply long-term storage discounts to partitions that have not been modified for 90 days. This reduces storage cost for older data without any manual action or data movement. Partitioning by date also enables efficient queries that scan only relevant partitions, maintaining fast performance on recent data. Option D is incorrect because partition expiration deletes the data, not moves it to long-term storage; long-term storage is applied automatically after 90 days of no modifications, not via partition expiration.

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.

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.

Question 3

A company is planning to migrate a large on-premises MySQL database (5 TB) to Cloud SQL. They need to choose the appropriate Cloud SQL edition. Which two factors should they consider? (Choose two.)

Accepted Answer

Maximum storage capacity supported by each edition.. Cloud SQL editions have different maximum storage capacities: Enterprise Plus supports up to 30 TB, while Enterprise supports up to 16 TB. For a 5 TB database, the edition must support at least that capacity, making this a key factor. Option D is correct because performance characteristics like maximum IOPS and throughput vary by edition and directly impact database performance, especially for a large 5 TB workload.

Answer

Availability of regional high availability.

Answer

Automated backup capabilities.

Answer

Integration with VPC networks.

Question 4

Which TWO considerations are critical when migrating from Cassandra to Cloud Bigtable?

Accepted Answer

Denormalizing data to avoid joins. Cloud Bigtable is a NoSQL wide-column database that does not support joins. Denormalization ensures efficient single-row lookups and avoids performance penalties. Option D is correct because row key design is critical for even distribution across nodes, preventing hotspots. Option C is wrong because Cloud Bigtable provides strong consistency, not eventual consistency, so maintaining eventual consistency is unnecessary when migrating. Options A and E are incorrect: CQL is Cassandra-specific and not supported by Bigtable, and secondary indexes in Bigtable are limited and not recommended for efficient filtering.

Answer

Using CQL for queries

Answer

Maintaining eventual consistency model

Answer

Using secondary indexes for efficient filtering

Question 5

A company is migrating their on-premises Oracle database to Cloud SQL for PostgreSQL. They want to minimize downtime during the cutover. Which two strategies should the database engineer recommend? (Choose 2.)

Accepted Answer

Use Database Migration Service with continuous replication.. Database Migration Service (DMS) with continuous replication is the only strategy that enables minimal downtime migration from Oracle to Cloud SQL for PostgreSQL. DMS handles full data load and ongoing change replication until cutover. Option C (pglogical) is invalid because pglogical only supports replication between PostgreSQL databases, not from Oracle. Option D (read replicas and promote) is not a migration strategy from an external Oracle database; it is for scaling within PostgreSQL. Options A and E involve significant downtime.

Answer

Use a Cloud VPN tunnel for data transfer.

Answer

Use a third-party tool like pglogical for replication.

Answer

Use Cloud SQL for PostgreSQL with read replicas and promote.

Answer

Perform an export using pg_dump and import using psql.

Question 6

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 7

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

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

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 10

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 11

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.. 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. Option D is also correct because read replicas in each continent can serve stale reads (read-only queries that tolerate slightly outdated data) with low latency, which is acceptable for many use cases like dashboards or reporting. Options B and C are incorrect: a single-region instance would cause high latency for users far from that region, and application caching does not guarantee consistency. Option E is incorrect: interleaved tables optimize query performance within a single table hierarchy but do not address global latency.

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 12

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

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

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 15

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 16

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 17

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

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 19

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

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