PCD Migrate Data Solutions Practice Test 5 — 15 Questions

Question 1

A company plans to migrate an on-premises Oracle database to Cloud SQL for PostgreSQL. They need to minimize downtime and ensure continuous sync during migration. Which migration approach should they use?

Accepted Answer

Use DMS with a continuous migration job, allowing CDC replication until cutover.. Database Migration Service (DMS) with a continuous migration job enables ongoing Change Data Capture (CDC) replication from the source Oracle database to Cloud SQL for PostgreSQL. This approach keeps the target database synchronized with minimal downtime, allowing a controlled cutover when the migration is complete. It is the only option that satisfies the requirements of continuous sync and minimal downtime.

Answer

Export the Oracle database as a dump file and import it into Cloud SQL manually.

Answer

Use DMS with a one-time migration job, then manually sync changes before cutover.

Answer

Use Cloud SQL Auth Proxy to connect Oracle to Cloud SQL and replicate data using a custom script.

Question 2

During an Oracle to Cloud SQL for PostgreSQL migration using DMS, the full dump phase completes successfully, but the CDC phase fails with a 'missing table' error. What is the most likely cause?

Accepted Answer

Some tables in the source database lack primary keys or a valid replica identity.. CDC typically requires a primary key or replica identity on each table to track changes. If tables lack these, CDC may fail. DMS expects tables to have primary keys or a valid replica identity for logging.

Answer

The source Oracle database does not have archivelog enabled.

Answer

The Cloud SQL instance does not have enough storage to accommodate CDC logs.

Answer

The DMS connection profile for the source is misconfigured.

Question 3

A company wants to migrate their MySQL 5.7 database to Cloud SQL for MySQL. They are concerned about performance after migration. Which step is essential for ensuring optimal performance on Cloud SQL?

Accepted Answer

Convert all MyISAM tables to InnoDB.. MyISAM tables are not supported on Cloud SQL for MySQL 8.0; they must be converted to InnoDB. InnoDB is the default engine and provides better performance, crash recovery, and concurrency.

Answer

Use Cloud SQL Auth Proxy to connect to the source.

Answer

Enable binary logging on the source database before migration.

Answer

Set the character set to utf8mb4 on the source.

Question 4

An organization is migrating a Teradata data warehouse to BigQuery. They want to minimize manual SQL rewriting. Which approach is most effective for converting Teradata SQL to BigQuery-compatible SQL?

Accepted Answer

Use BigQuery Data Transfer Service for Teradata to automate migration.. BigQuery Data Transfer Service can automate migration from Teradata, including schema and SQL conversion. It handles dialect differences and reduces manual effort.

Answer

Use gcloud command-line tool to export Teradata data and import into BigQuery.

Answer

Manually rewrite all Teradata SQL queries to BigQuery syntax.

Answer

Use Cloud SQL to create a linked server to Teradata.

Question 5

A company is migrating their on-premises SQL Server database to Cloud SQL for SQL Server. They need to ensure that schema changes are version-controlled and repeatable across environments. Which tool should they use?

Accepted Answer

Flyway. Flyway is a database migration tool that supports version-controlled schema migrations and integrates well with CI/CD pipelines. It is commonly used for SQL Server and Cloud SQL.

Answer

Cloud Deployment Manager

Answer

Cloud Build

Answer

Database Migration Service

Question 6

During a live migration from MySQL to Cloud SQL using DMS, the migration job enters a 'failed' state after the full dump. The error log indicates 'Binlog position out of bounds'. What is the most likely cause?

Accepted Answer

The source MySQL binary log retention period is too short.. DMS CDC requires that binary logs on the source are retained from the point of the full dump until cutover. If the binary logs are purged (e.g., by expiring logs or manual cleanup), the CDC phase cannot continue.

Answer

The Cloud SQL instance is not using InnoDB engine.

Answer

The DMS job is configured for one-time migration instead of continuous.

Answer

The source database has GTID mode disabled.

Question 7

A company wants to migrate an on-premises PostgreSQL 9.6 database to Cloud SQL for PostgreSQL with minimal downtime. Which service should they use?

Accepted Answer

Database Migration Service. Database Migration Service (DMS) supports PostgreSQL migrations with continuous CDC for minimal downtime. It manages the full dump and incremental sync.

Answer

Cloud Dataflow

Answer

BigQuery Data Transfer Service

Answer

Cloud SQL Auth Proxy

Question 8

An organization is migrating from Oracle to Cloud SQL for PostgreSQL using Ora2Pg. After conversion, several stored procedures fail to compile. Which step should they take to identify and fix the issues?

Accepted Answer

Re-run Ora2Pg with the --debug flag to get detailed error output.. Ora2Pg provides a log file with conversion details and error messages. Reviewing this log helps identify syntax differences and unsupported features that need manual adjustment.

Answer

Manually rewrite all procedures from PL/SQL to PL/pgSQL.

Answer

Use Database Migration Service continuous migration to replicate the procedures.

Answer

Convert the Oracle database to PostgreSQL using pgloader instead.

Question 9

A company is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0. They have many queries using GROUP BY that return different results in 8.0. What is the most likely cause?

Accepted Answer

The sql_mode is different between MySQL 5.7 and 8.0.. MySQL 8.0 defaults to strict SQL mode, which disables the MySQL 5.7 non-standard GROUP BY behavior (GROUP BY can select nonaggregated columns not in GROUP BY). In 8.0, this causes errors or different results unless the sql_mode includes ONLY_FULL_GROUP_BY.

Answer

The character set changed from utf8 to utf8mb4.

Answer

The authentication plugin changed from mysql_native_password to caching_sha2_password.

Answer

The storage engine changed from MyISAM to InnoDB.

Question 10

A financial services company is migrating a Redshift data warehouse to BigQuery. They need to stage data in Amazon S3 before transferring. Which service should they use to automate the transfer?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service supports scheduled transfers from Amazon S3 to BigQuery, handling incremental loads. It is the recommended service for this scenario.

Answer

Cloud Data Fusion

Answer

Cloud Storage Transfer Service

Answer

Cloud Composer

Question 11

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 12

During a MySQL to Cloud SQL migration using DMS, the migration job completes the full dump and CDC phases, but the cutover step fails. The error says 'Source database write operations still occurring'. What should the engineer do to complete cutover?

Accepted Answer

Stop all write operations to the source database, then promote the Cloud SQL replica.. For a zero-downtime cutover, you must first stop writes to the source database, then promote the Cloud SQL replica. The error indicates writes are still happening; stopping them allows the CDC to catch up and then cutover.

Answer

Restart the DMS job from the beginning.

Answer

Manually apply remaining changes to Cloud SQL using a SQL script.

Answer

Increase the source database's binary log retention period.

Question 13

A company is migrating a Snowflake data warehouse to BigQuery. They need to convert Snowflake SQL to BigQuery SQL. Which TWO tools or services can assist with this conversion?

Accepted Answer

BigQuery Data Transfer Service. The BigQuery Migration Service (option E) provides a dedicated SQL translation capability that automatically converts Snowflake SQL dialect to BigQuery SQL, including schema, DDL, and DML statements. The BigQuery Data Transfer Service (option B) can be used to schedule and automate the transfer of data from Snowflake to BigQuery, which is a key part of the migration workflow. Together, these two services address both the SQL conversion and the data movement aspects of the migration.

Answer

gcloud bigquery copy

Answer

Cloud SQL Auth Proxy

Answer

Cloud Dataflow

Question 14

A company is performing a lift-and-shift migration of an on-premises Oracle database to Cloud SQL for PostgreSQL using DMS with continuous migration. Which THREE prerequisites must be met before starting the migration job?

Accepted Answer

Create a source connection profile in DMS.. DMS continuous migration requires archivelog mode for CDC, appropriate network connectivity (VPC peering or proxy), and a connection profile for the source.

Answer

Install Cloud SQL Auth Proxy on the source server.

Answer

Convert all NUMBER columns to NUMERIC manually.

Question 15

A company is migrating a MySQL database to Cloud SQL for MySQL. They want to use Flyway for versioned schema migrations in CI/CD. Which TWO commands are part of a typical Flyway migration workflow?

Accepted Answer

flyway migrate. Flyway uses 'migrate' to apply pending migrations and 'validate' to verify the schema state. These are core commands in CI/CD pipelines.

Answer

flyway undo

Answer

flyway clean

Answer

flyway repair