PCD Migrate Data Solutions Practice Test 4 — 15 Questions

Question 1

An organization is migrating an on-premises PostgreSQL database to Cloud SQL. They want to minimize downtime during the cutover. Which Google Cloud service should they use to perform an online migration with near-zero downtime?

Accepted Answer

Database Migration Service. Database Migration Service (DMS) supports continuous migration jobs that perform a full dump, then CDC (change data capture), allowing for near-zero-downtime cutover by promoting the Cloud SQL replica.

Answer

BigQuery Data Transfer Service

Answer

Cloud Dataflow

Answer

Transfer Appliance

Question 2

A team plans to migrate an Oracle database to Cloud SQL for PostgreSQL. They have identified complex PL/SQL packages that need conversion. Which open-source tool is specifically designed to assist with schema conversion from Oracle to PostgreSQL?

Accepted Answer

Ora2Pg. Ora2Pg is an open-source tool specifically designed to automate the migration of Oracle databases to PostgreSQL. It converts Oracle PL/SQL code, including complex packages, procedures, and functions, into PostgreSQL-compatible PL/pgSQL, and also handles schema objects like tables, views, sequences, and indexes. This makes it the correct choice for the team's need to convert complex PL/SQL packages.

Answer

Database Migration Service

Answer

pgloader

Answer

Liquibase

Question 3

A company is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0. Their application uses utf8 charset and GROUP BY with nonstandard behavior (e.g., missing expressions in SELECT list). After migration, they notice queries that previously ran now fail. What is the most likely cause?

Accepted Answer

ONLY_FULL_GROUP_BY SQL mode is enabled by default in MySQL 8.0.. MySQL 5.7 allowed GROUP BY with columns not in the SELECT list by default (ONLY_FULL_GROUP_BY was off), but MySQL 8.0 enables it by default. This change can cause queries to fail if they rely on the old behavior.

Answer

The caching_sha2_password plugin changed authentication.

Answer

JSON data type behavior changed in MySQL 8.0.

Answer

The utf8 charset is not supported in MySQL 8.0.

Question 4

An engineer is migrating a large on-premises SQL Server database to Cloud SQL for PostgreSQL. They need to continuously replicate changes during the migration to minimize downtime. Which Database Migration Service job type should they use?

Accepted Answer

Continuous migration. DMS offers continuous migration jobs that perform a full dump followed by CDC, enabling near-zero-downtime cutover.

Answer

Bulk migration

Answer

Scheduled migration

Answer

One-time migration

Question 5

A company wants to migrate their data warehouse from Teradata to BigQuery. They need to automate the loading of data on a regular schedule. Which Google Cloud service should they use?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service supports scheduled transfers from Teradata (and other sources) to BigQuery.

Answer

Dataproc

Answer

Cloud Data Fusion

Answer

Cloud Composer

Question 6

During a migration from MySQL to Cloud SQL, an engineer notices that the source database uses MyISAM tables with full-text indexes. What must be done to successfully migrate these tables to Cloud SQL (which uses InnoDB by default)?

Accepted Answer

Convert the tables to InnoDB and recreate full-text indexes.. Cloud SQL for MySQL uses InnoDB as its default storage engine and does not support MyISAM. Therefore, MyISAM tables with full-text indexes must be converted to InnoDB, and the full-text indexes must be recreated because InnoDB supports full-text indexes natively. Option D correctly identifies this required conversion and index recreation process.

Answer

Migrate the tables to Cloud Storage and use external tables.

Answer

Use the MEMORY storage engine instead.

Answer

Create the tables as MyISAM in Cloud SQL.

Question 7

A company is migrating their MySQL database to Cloud SQL. They have a requirement to automate schema changes as part of their CI/CD pipeline. Which tools should they consider for version-controlled schema migrations? (Select the best option.)

Accepted Answer

Liquibase or Flyway. Liquibase and Flyway are both popular tools for versioned database schema migrations, commonly used in CI/CD pipelines.

Answer

Cloud SQL Proxy

Answer

Cloud Deployment Manager

Answer

Database Migration Service

Question 8

An organization is migrating from Oracle to Cloud SQL for PostgreSQL using Database Migration Service. They need to convert Oracle NUMBER(10,2) and VARCHAR2(100) columns to appropriate PostgreSQL types. What are the correct mappings?

Accepted Answer

NUMBER(10,2) → NUMERIC(10,2); VARCHAR2(100) → VARCHAR(100). Oracle NUMBER maps to PostgreSQL NUMERIC (or DECIMAL), and VARCHAR2 maps to VARCHAR (or TEXT). The specified lengths are preserved.

Answer

NUMBER(10,2) → DECIMAL(10,2); VARCHAR2(100) → TEXT

Answer

NUMBER(10,2) → INTEGER; VARCHAR2(100) → TEXT

Answer

NUMBER(10,2) → BIGINT; VARCHAR2(100) → CHAR(100)

Question 9

A team is migrating a Snowflake data warehouse to BigQuery. They need to move historical data and set up a one-time batch transfer. Which method is most appropriate?

Accepted Answer

Export data from Snowflake to GCS, then load into BigQuery.. Exporting data from Snowflake to GCS (Google Cloud Storage) as files (e.g., Parquet, Avro, or CSV) and then loading them into BigQuery is the most appropriate method for a one-time batch transfer of historical data. This approach leverages Snowflake's native UNLOAD command to export data efficiently and BigQuery's batch load jobs to ingest the files, providing full control over the migration without requiring ongoing connectivity or complex tooling.

Answer

Use BigQuery Data Transfer Service for Snowflake.

Answer

Use Cloud Data Fusion with Snowflake connector.

Answer

Use Database Migration Service with Snowflake endpoint.

Question 10

An engineer is planning a database migration and needs to assess the source database's size, schema complexity, stored procedures, and dependencies. Which activity is this part of?

Accepted Answer

Database assessment. Migration planning includes assessing the source database to understand its characteristics and plan the migration strategy.

Answer

Cutover planning

Answer

Schema conversion

Answer

Performance tuning

Question 11

A company is migrating from Redshift to BigQuery. They have large datasets in Amazon S3. What is the recommended approach to transfer this data into BigQuery?

Accepted Answer

Use BigQuery Data Transfer Service for Redshift.. The typical approach is to use BigQuery Data Transfer Service for Redshift (which can unload to S3 and then load into BigQuery), or manually export from Redshift to S3, transfer to GCS, and load into BigQuery. But the question specifies S3 as intermediate; using Transfer Service for Redshift is the managed option.

Answer

Use Cloud Dataflow to read from S3 and write to BigQuery.

Answer

Copy data from S3 to GCS using Storage Transfer Service, then load into BigQuery.

Answer

Use Database Migration Service with Redshift endpoint.

Question 12

An engineer is using Database Migration Service for a continuous migration from on-premises MySQL to Cloud SQL. The source database is not directly reachable from DMS due to network restrictions. Which connectivity option should the engineer configure?

Accepted Answer

Set up VPC peering between the on-premises network and the DMS VPC.. DMS requires network connectivity to the source. If direct VPC peering is not possible, the Cloud SQL Auth Proxy can be used (though typically for Cloud SQL to client, not for DMS source). The correct method is to use a public IP with authorized networks or a VPN. However, among the options, Cloud SQL Auth Proxy is not for source connectivity; VPC peering is correct if networks are peered. The best answer here is to use a VPN or VPC peering. Since DMS supports VPC peering and also reverse SSH tunnel (but not listed), the most plausible correct answer is VPC peering.

Answer

Deploy DMS on-premises as a virtual appliance.

Answer

Use a public IP for the source database with SSL.

Answer

Configure Cloud SQL Auth Proxy on the source server.

Question 13

A company is migrating a MySQL database to Cloud SQL. They want to test the migration by sending writes to both the old and new databases simultaneously and comparing results. Which TWO techniques should they use?

Accepted Answer

Shadow writes (dual-write to both databases). Shadow writes, also known as dual-writes, allow the application to send the same write operations to both the old MySQL database and the new Cloud SQL database simultaneously. This technique enables real-time comparison of the results from both databases to verify that the migration is handling writes correctly without disrupting the existing production workload.

Answer

Rollback plan documentation

Answer

Load testing with production data shape

Answer

Database Migration Service continuous migration

Question 14

An organization is migrating from Oracle to Cloud SQL for PostgreSQL. They need to convert Oracle packages (which use PL/SQL) into PostgreSQL equivalents. Which TWO approaches are correct for handling Oracle packages?

Accepted Answer

Rewrite PL/SQL code into PL/pgSQL syntax.. Oracle packages group procedures/functions. In PostgreSQL, the equivalent is to use schemas to namespace them. Additionally, converting PL/SQL to PL/pgSQL is required.

Answer

Use pgAgent to schedule package execution.

Answer

Use Oracle compatibility mode in PostgreSQL.

Answer

Install the Orafce extension for Oracle compatibility.

Question 15

A team is migrating a Redshift data warehouse to BigQuery using BigQuery Data Transfer Service. They need to ensure the transfer is secure and cost-effective. Which THREE considerations are important?

Accepted Answer

Use Amazon S3 as intermediate staging for unloaded data.. Intermediate staging in S3, encrypting data in transit, and enabling automatic refresh are typical considerations for Redshift to BigQuery transfers using Data Transfer Service.

Answer

Configure private VPC peering between Redshift and BigQuery.

Answer

Enable Cloud NAT for the transfer service to access Redshift.