PCD Migrate Data Solutions Practice Test 2 — 15 Questions

Question 1

An organization is migrating a 2 TB Oracle database to Cloud SQL for PostgreSQL. They need to minimize downtime and have a limited maintenance window of 30 minutes. Which migration strategy should they use?

Accepted Answer

Use DMS with continuous migration (CDC) and promote the replica for cutover.. Database Migration Service (DMS) with continuous migration provides an online migration approach with minimal downtime. The full dump and continuous CDC replication allow the source to stay operational until cutover, which can be performed in a short window.

Answer

Export the database as a SQL dump and import into Cloud SQL.

Answer

Use BigQuery Data Transfer Service to migrate the data.

Answer

Use a one-time migration job with DMS.

Question 2

A company is migrating from MySQL 5.7 to Cloud SQL for MySQL 8.0. After migration, a legacy application that uses the 'GROUP BY' clause with implicit sorting stops working correctly. What is the most likely cause?

Accepted Answer

MySQL 8.0 removed the implicit sorting of GROUP BY results.. MySQL 8.0 removed the implicit sorting of GROUP BY that was present in MySQL 5.7. Applications relying on this behavior must explicitly use ORDER BY to guarantee sort order.

Answer

MySQL 8.0 changed the default collation, affecting string comparison.

Answer

MySQL 8.0 no longer supports the 'GROUP BY' clause.

Answer

The 'ONLY_FULL_GROUP_BY' SQL mode is enabled by default.

Question 3

A data engineer is migrating a Teradata data warehouse to BigQuery. They need to transfer historical data and set up continuous replication. Which service should they use?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service supports scheduled and continuous transfers from Teradata to BigQuery, including incremental data loads.

Answer

Cloud Storage Transfer Service

Answer

Cloud Data Fusion

Answer

Database Migration Service

Question 4

An organization is migrating a large Oracle database to Cloud Spanner using Database Migration Service. They need to convert stored procedures and functions from PL/SQL to Spanner's SQL dialect. What is the best approach?

Accepted Answer

Refactor the application to handle business logic outside the database using client libraries.. Spanner does not support stored procedures; the recommended approach is to migrate business logic to the application layer using client libraries, which provides better scalability and maintainability.

Answer

Use Ora2Pg to convert PL/SQL to PL/pgSQL and deploy to Cloud Spanner.

Answer

Migrate the PL/SQL code as-is; Cloud Spanner supports Oracle PL/SQL.

Answer

Manually rewrite PL/SQL as Spanner stored procedures.

Question 5

A company is migrating a MySQL database with MyISAM tables to Cloud SQL for MySQL. They want to ensure the target database uses InnoDB for better transactional support and crash recovery. What should they do?

Accepted Answer

Run ALTER TABLE table_name ENGINE=InnoDB on each MyISAM table before migration.. MyISAM tables must be converted to InnoDB before or during migration. The ALTER TABLE statement is the standard way to change the storage engine in MySQL.

Answer

Set the default_storage_engine flag to InnoDB on the source MySQL instance.

Answer

Enable the skip_myisam flag in Cloud SQL to automatically convert MyISAM to InnoDB.

Answer

Use mysqldump with the --compatible=innodb option.

Question 6

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 7

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 8

During an Oracle to PostgreSQL migration, a team uses the Ora2Pg tool. They notice that some PL/SQL code containing the 'DBMS_OUTPUT.PUT_LINE' procedure is not converted correctly. How should they handle this?

Accepted Answer

Replace DBMS_OUTPUT.PUT_LINE with RAISE NOTICE in PostgreSQL.. PostgreSQL does not have a direct equivalent; the typical approach is to use RAISE NOTICE for debugging output. Alternatively, the code can be replaced with logging to a table.

Answer

Replace with PostgreSQL's PRINT statement.

Answer

Use PostgreSQL's DBMS_OUTPUT extension from pgOracle.

Answer

Keep DBMS_OUTPUT.PUT_LINE as-is; PostgreSQL supports it.

Question 9

A company is migrating a Redshift data warehouse to BigQuery. They need to stage the data in an intermediate storage before loading into BigQuery. What should they use?

Accepted Answer

Use Amazon S3 as intermediate staging, then copy to Cloud Storage.. The common pattern is to unload Redshift data to Amazon S3, then use Google Cloud Storage Transfer Service to move the files to Cloud Storage, and finally load into BigQuery.

Answer

Directly connect Redshift to BigQuery using VPN.

Answer

Use BigQuery Data Transfer Service for Redshift.

Answer

Use Google Cloud Interconnect for direct transfer.

Question 10

An organization is migrating a MySQL database to Cloud SQL for MySQL. The source database uses the 'utf8' character set. To support emoji characters, what should they do before migration?

Accepted Answer

Change the character set to 'utf8mb4' on the source database before migration.. MySQL's 'utf8' character set does not support 4-byte Unicode characters like emojis. Cloud SQL for MySQL supports 'utf8mb4', which is the correct charset for full Unicode support.

Answer

No change needed; 'utf8' supports emoji in Cloud SQL.

Answer

Use 'latin1' character set instead for better performance.

Answer

Enable the 'character_set_emoji' flag in Cloud SQL.

Question 11

A team is migrating a 500 GB Snowflake data warehouse to BigQuery. They have a tight deadline and need to minimize manual SQL rewriting. Which approach is most efficient?

Accepted Answer

Export Snowflake data to Cloud Storage as Parquet, create external tables, and use BigQuery's SQL translation for queries.. BigQuery SQL dialect is similar to standard SQL, but Snowflake-specific functions need to be converted. Using a converter tool or manual rewrite with testing is typical. There is no automated full conversion tool that guarantees correctness without review.

Answer

Use Cloud Data Fusion to transform Snowflake data into BigQuery-compatible format.

Answer

Manually rewrite all SQL queries to BigQuery syntax using a text editor.

Answer

Use the BigQuery Data Transfer Service with Snowflake connector.

Question 12

A company is using Database Migration Service to migrate an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The source database is behind a firewall that does not allow direct connections from Google Cloud. What connectivity option should they use?

Accepted Answer

Use Cloud SQL Auth Proxy on a machine in the source network.. Cloud SQL Auth Proxy provides a secure tunnel and can be run on the source network to connect to Cloud SQL. VPC peering requires direct network connectivity, which may not be possible through a firewall.

Answer

Use a Cloud VPN tunnel to connect the on-premises network to Google Cloud.

Answer

Set up VPC peering between the on-premises network and Google Cloud.

Answer

Configure a public IP for Cloud SQL and whitelist the source IP.

Question 13

A team is migrating a MySQL database to Cloud SQL for MySQL. They want to test the migration with a dual-write strategy before cutting over to the new database. Which TWO steps should they take?

Accepted Answer

Configure the application to write to both the source and target databases simultaneously.. Shadow writes (dual-write) involve writing to both databases and comparing results. Comparison queries and load testing are validation techniques.

Answer

Delete the source database after the first successful write.

Answer

Direct all reads to Cloud SQL to validate performance.

Answer

Use Database Migration Service to set up continuous replication.

Question 14

An organization is migrating a large Oracle database to Cloud SQL for PostgreSQL. They need to handle Oracle's SEQUENCE objects, which are used for primary key generation. Which THREE approaches are valid for this migration?

Accepted Answer

Replace Oracle sequences with PostgreSQL SERIAL columns.. PostgreSQL has SERIAL, sequences, and IDENTITY columns. Oracle sequences can be converted to PostgreSQL sequences or IDENTITY columns. SERIAL is a shorthand for creating a sequence on a column.

Answer

Remove all sequences and use UUIDs generated by the application.

Answer

Use Oracle sequences directly in PostgreSQL via a compatibility layer.

Question 15

A company is planning to migrate from MySQL to Cloud SQL for MySQL. They want to ensure the migrated database meets performance and security requirements. Which TWO configurations should they apply to Cloud SQL?

Accepted Answer

Configure the Cloud SQL instance to require SSL/TLS for all connections.. Enabling point-in-time recovery is important for data protection. Enforcing TLS ensures encrypted connections. These are common security and operational requirements.

Answer

Migrate all tables to MyISAM storage engine for better performance.

Answer

Set the database flag 'skip_grant_tables' for faster migration.

Answer

Disable the query cache to improve concurrency.