PCDE Migrate data solutions — Timed 100 Questions

Question 1

A company is migrating an on-premises PostgreSQL 13 database to AlloyDB for PostgreSQL using DMS continuous migration. The source is configured with logical replication using the pglogical extension. During the initial sync, the migration job fails with the error 'could not open relation with OID xxxx'. What is the most likely cause?

100 minute time limit — choose an answer to begin.

Accepted Answer

A DDL operation on the source dropped or altered a table after the replication slot was created.. DMS uses logical replication slots when possible. If the source has DDL changes (e.g., table dropped) after the slot is created, the replication slot may become invalid.

Answer

The source database has a firewall blocking the DMS IP address.

Answer

The source database does not have the pglogical extension installed.

Answer

The target AlloyDB cluster does not have the pglogical extension.

Question 2

A team is migrating a MySQL database to Cloud SQL using mysqldump for the initial snapshot. The source database uses InnoDB tables. Which mysqldump flags should be used to ensure a consistent snapshot without locking tables?

Accepted Answer

--single-transaction --skip-lock-tables. The --single-transaction flag makes mysqldump issue a START TRANSACTION WITH CONSISTENT SNAPSHOT before dumping, which leverages InnoDB's MVCC to produce a point-in-time consistent snapshot without acquiring table-level locks. --skip-lock-tables explicitly disables the default --lock-tables behavior, which would otherwise issue LOCK TABLES ... READ on each dumped table and block writes. Together they allow a hot backup of InnoDB tables while the source remains fully writable.

Answer

--all-databases --routines

Answer

--lock-tables --single-transaction

Answer

--flush-logs --master-data=2

Question 3

A team is migrating a 5 TB MySQL database to Cloud SQL using DMS. The full dump phase is taking longer than expected. They suspect network bandwidth is the bottleneck. Which action can they take to improve the dump speed within DMS?

Accepted Answer

Enable parallel dump in the DMS migration job settings.. Google Cloud Database Migration Service (DMS) supports parallel dump for MySQL migrations, which uses multiple threads to read and dump tables concurrently, significantly improving the full dump phase speed when the bottleneck is the dump process rather than network bandwidth alone. Enabling parallel dump in the migration job settings allows DMS to utilize more resources and reduce the overall dump time for large databases like a 5 TB MySQL instance.

Answer

Use a larger machine type for the source connection profile.

Answer

Increase the Cloud SQL instance storage size.

Answer

Switch to a one-time migration job instead of continuous.

Question 4

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 (BQ DTS) is a fully managed service designed to automate data ingestion into BigQuery from various sources, including Teradata, on a scheduled basis. It supports recurring transfers with built-in scheduling, and for Teradata it uses the Teradata Connector for Hadoop (TDCH) to extract data. Since the requirement is a recurring daily load from an on-premises Teradata system, BQ DTS provides the simplest, most direct solution without needing to manage infrastructure.

Answer

Cloud Composer

Answer

Cloud Data Fusion

Answer

Database Migration Service (DMS)

Question 5

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). Option C is correct because Oracle NUMBER(10,2) maps directly to PostgreSQL NUMERIC(10,2), preserving both precision and scale for exact decimal values. Option D is correct because Oracle DATE stores both date and time (down to the second), so it maps to PostgreSQL TIMESTAMP rather than DATE, which holds only a calendar date. Option E is correct because Oracle NUMBER(10) with zero scale represents an integer up to 10 digits, which fits PostgreSQL INTEGER (32-bit, range roughly ±2.1 billion) for typical values. Option A is not correct because VARCHAR2(10) should map to VARCHAR(10) to preserve the length constraint, not TEXT, which is unbounded. Option B is not correct because mapping Oracle DATE to PostgreSQL DATE would discard the time component that Oracle DATE actually stores.

Answer

VARCHAR2(10) -> TEXT

Answer

DATE -> DATE

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

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 9

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 10

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

Question 11

During a cutover from MySQL to Cloud SQL, the team must minimize downtime. Which THREE steps are essential for a successful cutover with minimal data loss?

Accepted Answer

Verify that DMS replication lag is zero.. Option A is correct because verifying that Database Migration Service (DMS) replication lag is zero confirms the destination Cloud SQL instance has fully caught up with the source, so no in-flight transactions remain unapplied at cutover. Option B is correct because quiescing (stopping) all writes to the source database freezes the data state, preventing new changes from being written that would not be replicated before the switch. Option C is correct because promoting the destination Cloud SQL instance ends the replication relationship and makes it the new primary, allowing the application to resume writes on the migrated database. Option D is not essential for the cutover itself; point-in-time recovery is a backup/recovery feature that does not drive minimal-downtime cutover. Option E is not essential because a post-quiesce full backup of the source does not advance the cutover and is unnecessary once replication lag is zero and the destination is promoted.

Answer

Enable point-in-time recovery on the destination.

Answer

Take a full backup of the source after quiescing.

Question 12

A team is migrating a MySQL database to Cloud SQL using DMS. They need to configure the source database for binary logging. Which TWO parameters must be set correctly?

Accepted Answer

binlog_format = ROW. For Cloud SQL Database Migration Service (DMS) to perform a MySQL migration, the source must have binary logging enabled and use row-based replication. Option E, log_bin = ON, is correct because DMS requires the binary log to be active on the source so it can read and replicate ongoing changes to Cloud SQL. Option D, binlog_format = ROW, is correct because DMS requires row-based binary logging to reliably capture the exact row-level changes needed for consistent replication. Option A (expire_logs_days = 0) is not required; it would disable automatic log purging and is unrelated to DMS's binary logging requirement. Option B (binlog_format = STATEMENT) is incorrect because DMS requires ROW format, not STATEMENT. Option C (server_id = 1) is not a required DMS parameter; while a unique server_id is needed for replication topologies, DMS does not mandate the specific value 1.

Answer

expire_logs_days = 0

Answer

binlog_format = STATEMENT

Answer

server_id = 1

Question 13

During a MySQL to Cloud SQL migration using Database Migration Service, the full dump phase is taking much longer than expected. The source MySQL database is 500 GB and the Cloud SQL instance is of sufficient size. What is the most likely cause of the slow dump?

Accepted Answer

The source database has many MyISAM tables.. Without --single-transaction, mysqldump locks tables, causing contention and slow performance. DMS uses mysqldump internally and requires InnoDB tables with --single-transaction for consistent non-blocking dumps.

Answer

The source database is using a public IP address.

Answer

Binary logging is not enabled on the source.

Answer

The Cloud SQL instance is using a shared-core machine type.

Question 14

An engineer needs to connect Database Migration Service to a source MySQL database that has only a private IP address. The Cloud SQL destination is also private. What is the recommended method for DMS to connect to the source?

Accepted Answer

Use VPC peering or Cloud SQL Auth Proxy. Database Migration Service supports private connectivity to source databases via VPC peering (for sources in peered VPCs or on-prem via VPN/Interconnect) or via the Cloud SQL Auth Proxy for Cloud SQL sources. For a private-IP MySQL source, the recommended approach is to establish private network reachability — VPC peering or the Auth Proxy — so DMS can reach the source without exposing it to the internet.

Answer

Use a Cloud VPN tunnel with static IP

Answer

Use Cloud NAT to provide outbound connectivity

Answer

Use the source database's public IP with IP allowlisting

Question 15

A company is migrating a 2 TB PostgreSQL database to AlloyDB using Database Migration Service. They need to minimize downtime. After the initial full dump, the CDC lag remains high for hours. What should the engineer check first?

Accepted Answer

Source database resource utilization (CPU, IO). The engineer should first check source database resource utilization (CPU, IO) because high CDC lag often indicates that the source database is unable to keep up with the change stream due to resource constraints. If the source is overloaded, it may not generate or transmit changes quickly enough, causing lag. Other factors like binary log retention are important but secondary to source performance.

Answer

Cloud SQL Auth Proxy configuration

Answer

Binary log retention period

Answer

AlloyDB cluster size

Question 16

A company needs to migrate a 5 TB Oracle database to Cloud SQL for PostgreSQL. They need to convert schemas and data, and also set up ongoing replication with minimal downtime. Which TWO services/tools should they use? (Choose TWO.)

Accepted Answer

Database Migration Service. Database Migration Service (A) is correct because it is Google Cloud's managed service for migrating Oracle databases to Cloud SQL for PostgreSQL, supporting both the initial full dump/load and continuous change data capture (CDC) replication for minimal-downtime cutover. Ora2Pg (E) is correct because it is the open-source schema and data conversion tool that Database Migration Service uses under the hood to translate Oracle PL/SQL objects, data types, and schemas into PostgreSQL-compatible DDL and data. Cloud SQL for MySQL (B) is wrong because the target is PostgreSQL, not MySQL, so it is the wrong database engine. BigQuery Data Transfer Service (C) is wrong because it is for loading data into BigQuery for analytics, not for migrating or replicating an OLTP database into Cloud SQL. gcloud bigquery load (D) is wrong because it is a BigQuery data-loading command and has nothing to do with Oracle-to-PostgreSQL schema conversion or ongoing replication.

Answer

Cloud SQL for MySQL

Answer

BigQuery Data Transfer Service

Answer

gcloud bigquery load

Question 17

A company is migrating a MySQL database to Cloud SQL using Database Migration Service (DMS). The source database is on-premises and uses InnoDB tables. The migration job is configured as continuous (CDC). After starting the job, the full dump phase completes successfully, but the CDC phase shows no replicated changes. What is the most likely cause?

Accepted Answer

The source database does not have binary logging enabled.. Continuous CDC replication in DMS requires binary logging to be enabled on the source database. If binary logging is disabled, DMS cannot capture change data. The full dump works because it uses mysqldump, which does not rely on binary logs.

Answer

The Cloud SQL destination does not have public IP connectivity.

Answer

The source database is using MyISAM tables instead of InnoDB.

Answer

The DMS migration job was configured as one-time instead of continuous.

Question 18

An engineer is planning to migrate a PostgreSQL database to AlloyDB using Database Migration Service (DMS). The source database is version 13 and the target is AlloyDB. The migration must replicate ongoing changes with minimal downtime. Which source configuration is required to enable continuous change data capture (CDC) with DMS for PostgreSQL?

Accepted Answer

Set wal_level=logical and install the pglogical extension.. For PostgreSQL sources, DMS requires logical replication to capture ongoing changes. This means setting 'wal_level=logical' in postgresql.conf and installing the pglogical extension (or using the native logical decoding plugin). The pglogical extension provides the necessary replication set and slot management for DMS to read the write-ahead log and replicate changes continuously. Without logical decoding, DMS cannot perform CDC.

Answer

Configure streaming replication with a replication slot.

Answer

Enable binary logging and set binlog_format=ROW.

Answer

Set max_replication_slots=0 to disable replication conflicts.

Question 19

An organization is migrating a Teradata data warehouse to BigQuery. They need to convert existing Teradata DDL and BTEQ scripts to BigQuery SQL. Which Google Cloud service should they use for schema conversion?

Accepted Answer

Schema Conversion Tool (SCT). The Schema Conversion Tool (SCT) is specifically designed to automate the conversion of legacy data warehouse schemas and SQL dialects (including Teradata) to BigQuery. It parses Teradata DDL, BTEQ scripts, and other SQL constructs, then generates equivalent BigQuery SQL, handling data type mappings, function translations, and syntax differences. This directly addresses the need to convert existing Teradata DDL and BTEQ scripts.

Answer

Cloud Data Fusion

Answer

Cloud Composer

Answer

BigQuery Data Transfer Service

Question 20

A company is migrating a PostgreSQL database to Cloud SQL using DMS with continuous CDC. During cutover, the engineer checks DMS metrics and sees the replication lag is consistently 0 seconds. What is the next step to complete the migration?

Accepted Answer

Promote the destination Cloud SQL instance.. When DMS continuous CDC shows replication lag of 0 seconds, the source and destination are fully synchronized. The next step is to promote the destination Cloud SQL instance to make it the primary database, completing the cutover. This stops replication and redirects application traffic to the new instance.

Answer

Restart the migration job.

Answer

Increase the number of DMS worker nodes.

Answer

Delete the source database.