PCDE Migrate data solutions Practice Test 2 — 15 Questions

Question 1

A data analyst needs to load data from an on-premises Teradata system into BigQuery on a recurring daily schedule. Which Google Cloud service should they use?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service is the correct service for scheduled, recurring data loads from on-premises Teradata into BigQuery. It supports Teradata via an agent-based transfer configuration. Cloud Composer is a workflow orchestration tool, not directly designed for this use case. Cloud Data Fusion provides ETL capabilities but is not the best fit for simple daily Teradata-to-BigQuery transfers. Database Migration Service is intended for migrating databases to Cloud SQL, not for loading data into BigQuery.

Answer

Cloud Composer

Answer

Cloud Data Fusion

Answer

Database Migration Service (DMS)

Question 2

A company is migrating a PostgreSQL database to Cloud SQL using DMS continuous migration. After the full dump, the CDC phase is replicating changes. To prepare for cutover, which TWO actions should the engineer take? (Choose 2)

Accepted Answer

Verify that the DMS migration job lag is 0 seconds.. Quiesce writes to ensure no new changes, and confirm DMS lag is 0 before promoting.

Answer

Enable binary logging on the source.

Answer

Take a full backup of the source.

Answer

Delete the DMS migration job to stop replication.

Question 3

A data team is migrating an Oracle database to Cloud SQL for PostgreSQL. They have used Ora2Pg to convert the schema. After conversion, they notice several issues with data type mappings. Which THREE Oracle-to-PostgreSQL mappings are correct? (Choose 3)

Accepted Answer

NUMBER(10,2) -> NUMERIC(10,2). NUMBER(10) -> INTEGER, NUMBER(10,2) -> NUMERIC(10,2), DATE -> TIMESTAMP are correct. CLOB -> TEXT is also correct but only three needed.

Answer

VARCHAR2(10) -> TEXT

Answer

DATE -> DATE

Question 4

An organization is migrating a large on-premises MySQL database to Cloud SQL using DMS. They want to minimize downtime. Which TWO configurations should they implement? (Choose 2)

Accepted Answer

Enable continuous migration with CDC.. Continuous migration enables CDC. Parallel dump speeds up the initial sync.

Answer

Disable binary logging on the source.

Answer

Use a one-time migration job.

Answer

Use mysqldump and import manually.

Question 5

A company is migrating a MySQL database to Cloud SQL using Database Migration Service (DMS). The source database is on-premises with a public IP address. Which networking configuration is required on the source to allow DMS to connect?

Accepted Answer

Allowlist the DMS public IP addresses in the source database firewall.. For DMS to connect to a source with a public IP, the source must allowlist the IP addresses of the DMS instance to bypass firewall restrictions.

Answer

Create a VPC peering connection between the source and DMS.

Answer

Install the Cloud SQL Auth Proxy on the source database.

Answer

Configure a Cloud VPN tunnel between the on-premises network and Google Cloud.

Question 6

An organization is migrating an Oracle database to Cloud SQL for PostgreSQL using Ora2Pg. During testing, a stored procedure that uses Oracle's SYSDATE produces incorrect results. What is the correct approach to handle this conversion?

Accepted Answer

Replace SYSDATE with CURRENT_TIMESTAMP.. Oracle's SYSDATE returns date and time; PostgreSQL equivalent is CURRENT_TIMESTAMP (or NOW()). Simply mapping to CURRENT_DATE would lose time component, breaking logic.

Answer

Replace SYSDATE with LOCALTIMESTAMP.

Answer

Use TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') to convert the value.

Answer

Replace SYSDATE with CURRENT_DATE in the converted procedure.

Question 7

A company is using DMS to migrate a MySQL database to Cloud SQL with continuous replication. During the CDC phase, the replication lag is increasing and not catching up. The source database is heavily used for OLTP workloads. Which action would most likely reduce the replication lag?

Accepted Answer

Reduce the number of concurrent write operations on the source database.. Increasing the source database's binary log retention period does not reduce lag; it retains more logs. Reducing concurrent writes on source directly slows the rate of change, allowing DMS to catch up.

Answer

Increase the size of the Cloud SQL destination instance.

Answer

Increase the binary log retention period on the source.

Answer

Enable parallel replication on DMS.

Question 8

A team needs to migrate a large Teradata data warehouse to BigQuery. They want to automatically convert Teradata DDL and BTEQ scripts to BigQuery-compatible SQL. Which Google Cloud service should they use?

Accepted Answer

Schema Conversion Tool (SCTS). Schema Conversion Tool (SCTS) is designed for heterogeneous migrations, converting DDL and scripts from sources like Teradata to BigQuery.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

Cloud Dataflow

Question 9

An engineer is manually migrating a MySQL database to Cloud SQL using mysqldump and mysql import. They need to ensure the dump captures a consistent snapshot without locking InnoDB tables. Which mysqldump flags should they use?

Accepted Answer

--single-transaction --skip-lock-tables. --single-transaction uses a transaction to get a consistent snapshot for InnoDB without locking. --skip-lock-tables prevents table locks. Together they achieve consistent backup without disrupting writes.

Answer

--single-transaction --lock-tables

Answer

--lock-tables --skip-lock-tables

Answer

--all-databases --single-transaction

Question 10

During a cutover from an on-premises Oracle database to Cloud SQL for PostgreSQL, the team needs to minimize downtime. They have set up continuous replication using a custom tool. Which sequence of steps should they follow to perform the cutover with minimal data loss?

Accepted Answer

Quiesce writes, verify lag is 0, promote destination, update connection strings, verify application.. The correct sequence: quiesce writes to source (stop application writes), verify replication lag is zero (all changes replicated), promote destination (make it writable), update connection strings, then verify the application. Rolling back involves keeping source read-only.

Answer

Quiesce writes, promote destination, verify lag, update connection strings.

Answer

Promote destination, quiesce writes, verify lag, update connection strings, verify application.

Answer

Update connection strings, quiesce writes, verify lag, promote destination.

Question 11

A company is migrating a PostgreSQL database to Cloud SQL using DMS. The source database has logical replication enabled for the migration. What is the purpose of logical replication in this context?

Accepted Answer

To capture changes for continuous replication after the initial dump.. DMS uses PostgreSQL logical replication to capture ongoing changes. The source database must have logical replication enabled (via WAL with logical decoding) to allow DMS to replicate changes continuously after the initial dump. Binary logging is a MySQL concept, not applicable to PostgreSQL.

Answer

To enable point-in-time recovery on the source.

Answer

To improve performance of the initial dump.

Answer

To convert data types between source and destination.

Question 12

An organization is migrating a MySQL database to Cloud SQL using DMS with continuous replication. After promoting the destination, they need a rollback plan. Which approach should they use to enable a quick rollback if issues arise?

Accepted Answer

Keep the source database running in read-only mode for a few days.. A rollback plan should keep the source database running but read-only for a validation window. This allows switching back if needed without data loss.

Answer

Delete the source database immediately after promotion.

Answer

Take a snapshot of the source database before promotion.

Answer

Enable binary logging on the Cloud SQL instance after promotion.

Question 13

A company is migrating a PostgreSQL database to AlloyDB using DMS. They need to test the converted stored procedures. Which tool should they use to write and run unit tests for PL/pgSQL functions?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows writing tests in SQL/PL/pgSQL. It is suitable for testing stored procedures.

Answer

Cloud SQL Insights

Answer

pgAdmin

Answer

pg_stat_statements

Question 14

A team is migrating an on-premises PostgreSQL database to Cloud SQL using DMS. They want the migration job to continuously replicate changes after the initial dump. Which type of migration job should they create?

Accepted Answer

Continuous migration job. DMS offers 'one-time' for single dump and 'continuous' for ongoing CDC. Continuous jobs replicate changes after dump.

Answer

One-time migration job

Answer

Scheduled export job

Answer

Bulk load job

Question 15

A company is migrating an Oracle database to Cloud SQL for PostgreSQL. They need to convert data types. Which TWO data type mappings are correct?

Accepted Answer

NUMBER(10,2) → NUMERIC(10,2). Oracle NUMBER(10,2) maps to NUMERIC(10,2) in Cloud SQL for PostgreSQL because both store exact numeric values with precision and scale. Oracle NUMBER(10) maps to INTEGER because it stores whole numbers without scale. The other options are incorrect: CLOB maps to TEXT, BLOB maps to BYTEA, and Oracle DATE maps to TIMESTAMP (since Oracle DATE includes time).

Answer

CLOB → VARCHAR

Answer

DATE → DATE