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

Question 1

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 2

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 3

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 4

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 5

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.

Question 6

A Database Engineer is deploying a Cloud SQL for PostgreSQL instance for a financial services application that requires high availability and automatic failover. The engineer also needs to ensure that backups are taken daily and retained for 30 days. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Enable automated backups and set backup retention to 30 days.. Option B is correct because Cloud SQL automated backups can be configured with a retention period of up to 365 days, and setting it to 30 days meets the requirement for daily backups with 30-day retention. Option C is correct because enabling high availability (HA) on a Cloud SQL instance with a regional persistent disk provides automatic failover to a standby instance in a different zone within the same region, ensuring high availability for the financial services application.

Answer

Create a cross-region replica and configure automatic failover.

Answer

Schedule a Cloud Scheduler job to export the database to Cloud Storage daily.

Answer

Enable point-in-time recovery (PITR) with a 7-day retention.

Question 7

A company's Cloud Spanner database currently uses a regional configuration in us-central1. Due to growth, the database must support global reads with low latency and maintain strong consistency. The database engineer is evaluating options. Which THREE considerations should the engineer include in the design? (Choose three.)

Accepted Answer

Select a configuration that places the leader region close to the majority of write traffic.. Option B is correct because in a multi-region Cloud Spanner configuration, the leader region handles all writes and must be placed close to the majority of write traffic to minimize write latency. This ensures strong consistency, as all reads are served from the same leader region by default, and global reads with low latency require careful leader placement to avoid cross-region round trips.

Answer

Use interleaved tables to reduce the number of reads required for hierarchical data.

Answer

Add read replicas in remote regions to serve reads with eventual consistency.

Question 8

Your company is deploying a new application on Google Cloud and needs to choose a database solution. The application requires strong transactional consistency, complex SQL queries, and the ability to scale horizontally for read-heavy workloads. Which database service should you recommend?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides strong transactional consistency (ACID) across globally distributed nodes, supports complex SQL queries with standard SQL syntax, and offers horizontal scaling for read-heavy workloads through automatic sharding and read replicas. Unlike other options, Spanner uniquely combines these three requirements—strong consistency, SQL, and horizontal scaling—in a single managed service.

Answer

BigQuery

Answer

Cloud SQL

Answer

Cloud Firestore

Question 9

A developer reports that an application cannot connect to a Cloud SQL SQL Server instance. The error log shows the message in the exhibit. The instance exists and the user credentials are correct. What is the most likely cause?

Accepted Answer

The database name specified in the connection string is incorrect.. The error message in the exhibit indicates that the login failed for the user, which is a common symptom when the database name in the connection string does not match an existing database on the Cloud SQL SQL Server instance. Even though the user credentials are correct, SQL Server requires a valid database context to establish the connection; an incorrect database name causes the server to reject the login attempt. This is a configuration issue, not an authentication or network problem.

Answer

The Cloud SQL instance has reached its maximum number of connections.

Answer

The Cloud SQL proxy is not running.

Answer

The Cloud SQL instance is not in the same VPC network as the application.

Question 10

A startup is using Cloud SQL for PostgreSQL and wants to minimize downtime during maintenance. The application can tolerate a few minutes of read-only mode. Which configuration should they use?

Accepted Answer

Use a read replica and promote it during maintenance.. Option A is correct because using a read replica and promoting it during maintenance allows the application to switch to a read-write capable instance with minimal downtime. The application can tolerate a few minutes of read-only mode, so the brief period when the replica is promoted and the original primary is unavailable is acceptable. This approach avoids the longer downtime associated with other methods like HA failover or simply waiting for maintenance to complete.

Answer

Enable automatic storage increase.

Answer

Configure a high availability (HA) instance with regional failover.

Answer

Schedule maintenance during off-peak hours only.

Question 11

Your company runs a global application on Cloud Spanner. You notice that recent schema changes have caused a significant increase in latency for cross-node transactions. The previous schema used interleaved tables for parent-child relationships, but the new schema uses separate tables with foreign keys. What is the most likely cause of the increased latency?

Accepted Answer

The new schema uses foreign keys that require cross-node transactions.. The new schema uses separate tables with foreign keys instead of interleaved tables. In Cloud Spanner, interleaved tables guarantee that parent and child rows are co-located on the same split, allowing local joins without cross-node communication. Foreign keys between non-interleaved tables can reference rows stored on different splits, forcing distributed transactions that require two-phase commit across nodes, which significantly increases latency.

Answer

The new schema does not use commit timestamps for versioning.

Answer

The new schema lacks secondary indexes on foreign key columns.

Answer

The Spanner instance was not resized after the schema change.

Question 12

A company is migrating its on-premises PostgreSQL database to Cloud SQL for PostgreSQL. They want to minimize downtime during the migration. Which TWO actions should they take?

Accepted Answer

Set up a Cloud SQL read replica and promote it to the primary after migration.. Option C is correct because setting up a Cloud SQL read replica from the source PostgreSQL database and then promoting it to primary allows for a controlled cutover with minimal downtime. The replica stays in sync with the source using PostgreSQL's native streaming replication, and promotion is a fast metadata operation that typically takes seconds, not minutes or hours.

Answer

Increase the disk size of the Cloud SQL instance before migration to improve performance.

Answer

Use pg_dump to export the database and pg_restore to import into Cloud SQL.

Answer

Decrease max_connections to reduce load during migration.

Question 13

A company uses Cloud Spanner. The backup service account 'sa-backup' needs to create and manage backups of the 'orders' database. However, backup creation fails with a permission error. What is the most likely cause?

Accepted Answer

The backup role must be granted at the instance level, not on the database.. Option C is correct because Cloud Spanner backup permissions must be granted at the instance level, not on the database itself. The service account 'sa-backup' needs the `spanner.backups.create` permission on the instance resource to create backups, and assigning a role like `roles/spanner.databaseBackupAdmin` at the database level does not propagate the necessary instance-level permissions, causing the backup creation to fail with a permission error.

Answer

The service account lacks the spanner.databases.read permission.

Answer

The service account is assigned the role roles/spanner.databaseBackupAdmin, which is a custom role that does not include the spanner.backups.create permission.

Answer

The instance 'orders-db' is in a regional configuration, which does not support backups.

Question 14

Arrange the steps to create and connect to a Cloud SQL for PostgreSQL instance using the gcloud command-line tool.

Question 15

Arrange the steps to configure high availability for a Cloud SQL for MySQL instance.