PCD Practice Test 20 — 15 Questions

Question 1

A company has an Oracle database and wants to migrate to Cloud SQL for PostgreSQL using Database Migration Service. They set up a connection profile and a continuous migration job. After the initial load, the CDC phase begins. Suddenly, the source database has a schema change (adding a column). What happens?

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

Accepted Answer

The CDC phase continues to replicate DML but may fail if the column is required. DMS does not automatically replicate DDL changes. The column would be added to the source but not to the destination, potentially causing the CDC to fail due to data type mismatches.

Answer

The migration job automatically skips the new column

Answer

The migration job pauses and asks for manual intervention

Answer

The schema change is automatically replicated to Cloud SQL

Question 2

A team is using Cloud SQL for MySQL and wants to recover the database to a specific point in time within the last 7 days. They have automated backups enabled. Which feature must be configured to enable point-in-time recovery (PITR)?

Accepted Answer

Enable binary logging and set a suitable binlog retention period. PITR for Cloud SQL MySQL requires binary log (binlog) retention to be set appropriately.

Answer

Use Database Migration Service for continuous replication

Answer

Configure cross-region replication

Answer

Enable automated backups only

Question 3

A team is performing a shadow writes testing strategy as part of database migration validation. What does this involve?

Accepted Answer

Duplicating all writes to both source and target databases and comparing. Shadow writes involve writing the same data to both the old and new databases simultaneously during the testing phase, then comparing the results to ensure consistency.

Answer

Creating a snapshot of the source database

Answer

Running comparison queries after migration

Answer

Writing test data only to the new database

Question 4

A company is migrating a 500 GB MySQL database to Cloud SQL for MySQL 8.0. They want to test the migration with minimal risk before the final cutover. Which TWO testing approaches should they use?

Accepted Answer

Set up shadow writes to dual-write to both source and target databases.. Shadow writes allow testing by writing to both old and new DBs. Comparison queries validate data integrity. Load testing and rollback plan are also important but not testing approaches per se.

Answer

Perform load testing using synthetic data only.

Answer

Use Database Migration Service to migrate only the schema first.

Answer

Create a rollback plan before starting migration.

Question 5

A company needs to migrate an on-premises Cassandra database to Google Cloud. They want a managed NoSQL database with high availability and low latency. Which service should they choose?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is a fully managed NoSQL wide-column database that is ideal for migrating from Cassandra. It provides high availability, low latency, and horizontal scalability.

Answer

Cloud SQL

Answer

Cloud Spanner

Answer

Firestore

Question 6

A company wants to migrate an on-premises Oracle database to Cloud SQL for PostgreSQL with minimal downtime. They plan to use Database Migration Service (DMS) with continuous migration. What must be configured on the source Oracle database to enable change data capture (CDC)?

Accepted Answer

Enable supplemental logging. Database Migration Service for Oracle to PostgreSQL requires supplemental logging at the database level to capture all changes for CDC. Without supplemental logging, DMS cannot replicate ongoing changes.

Answer

Set the database to force logging

Answer

Enable archive log mode

Answer

Configure Oracle GoldenGate

Question 7

A team is migrating an Oracle database to Cloud SQL for PostgreSQL using Database Migration Service. The source has a stored procedure that uses Oracle's 'SYSDATE' function. What should they replace it with in PostgreSQL?

Accepted Answer

CURRENT_TIMESTAMP. In PostgreSQL, 'CURRENT_TIMESTAMP' or 'NOW()' returns the current date and time with time zone, which is the equivalent of Oracle's SYSDATE. PostgreSQL does not have a direct SYSDATE function.

Answer

SYSDATE

Answer

GETDATE()

Answer

CURRENT_DATE

Question 8

A team is migrating an Oracle database to Cloud SQL for PostgreSQL. They are using Ora2Pg for schema conversion. Which of the following data type mappings is correct?

Accepted Answer

VARCHAR2 → VARCHAR. Oracle's VARCHAR2 maps to PostgreSQL's VARCHAR, CLOB maps to TEXT, RAW maps to BYTEA, NUMBER without precision maps to NUMERIC, and DATE maps to TIMESTAMP (or timestamp with time zone).

Answer

RAW → TEXT

Answer

CLOB → BYTEA

Answer

NUMBER → INTEGER

Question 9

Match each Cloud Logging and Monitoring concept to its definition.

Accepted Answer

Cloud Logging: Collects and stores log data from applications and services.. Correct matches: Cloud Logging handles log data, Cloud Monitoring provides performance insights, Log-based Metrics turn logs into metrics, Error Reporting aggregates errors. Common confusions include swapping Cloud Logging and Cloud Monitoring definitions.

Answer

Cloud Monitoring: Collects and stores log data from applications and services.

Answer

Error Reporting: Provides visibility into the performance, uptime, and health of cloud applications.

Question 10

A company needs to monitor the disk usage of their Cloud SQL instance to proactively increase storage before it runs out. They want to set an alert when disk usage exceeds 80%. Which metric should they use in Cloud Monitoring?

Accepted Answer

database/disk/bytes_used. Cloud SQL provides the `database/disk/bytes_used` metric, which represents the actual storage used. By setting an alert on this metric (e.g., as a percentage of total disk size), they can be notified when usage is high.

Answer

database/cpu/utilisation

Answer

database/disk/quota

Answer

database/memory/utilisation

Question 11

You need to migrate an on-premises PostgreSQL database to Cloud SQL using Database Migration Service. The source database is behind a firewall without a public IP. Which connectivity option should you configure?

Accepted Answer

Use VPC peering with a VPN tunnel between on-premises and Google Cloud.. For source databases without public IP and behind a firewall, DMS supports connectivity via VPC peering (if the source is in a VPC) or using Cloud SQL Auth Proxy. However, if the source is on-premises, you need to establish connectivity via VPN or Interconnect and then use VPC peering. Cloud SQL Auth Proxy is not for source connectivity in DMS. The correct option is to use VPC peering with a VPN.

Answer

Use a Cloud SQL proxy container on-premises to forward connections.

Answer

Expose the source database on a public IP with SSL.

Answer

Use Cloud SQL Auth Proxy to connect to the source database.

Question 12

A company's Cloud Bigtable instance is experiencing high latency and increased error rates for write requests. The Key Visualiser shows that a single row key prefix receives the majority of writes, creating a hotspot. What is the BEST long-term solution to distribute writes more evenly?

Accepted Answer

Redesign the row key by salting with a hash or a more distributed prefix. To avoid hot spotting, row key design should incorporate a nonce or sharding prefix to distribute writes across tablets. Salting row keys with a calculated hash or a field with high cardinality spreads the load. The Key Visualiser confirms the hotspot; redesigning the key is the correct fix.

Answer

Add more nodes to the Bigtable cluster

Answer

Switch from SSD to HDD storage

Answer

Create a secondary Bigtable cluster in another region

Question 13

A development team wants to connect their Cloud SQL for PostgreSQL instance from a Compute Engine VM without exposing it to the public internet. They also want to avoid managing IP allowlists. Which method should they use?

Accepted Answer

Use Cloud SQL Auth Proxy. The Cloud SQL Auth Proxy provides secure, IAM-based access to Cloud SQL instances without requiring public IP or allowlists. It runs on the client and uses an encrypted tunnel. It works over Private IP if the VM is in the same VPC, but does not require it.

Answer

Use Private IP only

Answer

Configure a public IP with SSL enforcement

Answer

Assign a static IP to the VM and add it to authorized networks

Question 14

A data engineer wants to perform version-controlled schema migrations as part of a CI/CD pipeline for a Cloud SQL for PostgreSQL database. Which tools should they use?

Accepted Answer

Liquibase or Flyway. Liquibase and Flyway are industry-standard tools for version-controlled database schema migrations. They integrate with CI/CD pipelines and support PostgreSQL.

Answer

Database Migration Service

Answer

gcloud sql import

Answer

Cloud Build and Cloud Scheduler

Question 15

A company is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0. They use MyISAM tables and utf8 charset. What changes must they make during migration?

Accepted Answer

Convert MyISAM tables to InnoDB and change charset from utf8 to utf8mb4.. MySQL 8.0 defaults to InnoDB, and utf8mb4 is recommended. MyISAM tables should be converted to InnoDB. utf8 in MySQL is an alias for utf8mb3 (3-byte), which cannot store emoji; utf8mb4 (4-byte) is recommended. MySQL 5.7 to 8.0 introduces changes like caching_sha2_password authentication and stricter GROUP BY. They must convert MyISAM to InnoDB and change charset to utf8mb4.

Answer

Convert MyISAM to InnoDB only; utf8 is fine in MySQL 8.0.

Answer

No changes needed; Cloud SQL automatically converts MyISAM to InnoDB and utf8 to utf8mb4.

Answer

Change charset to utf8mb4 only; MyISAM is supported in Cloud SQL.