PCD Migrate Data Solutions Practice Test 6 — 15 Questions

Question 1

A company is planning to migrate an on-premises Oracle database to Cloud SQL for PostgreSQL. They want to use Google's Database Migration Service (DMS) for continuous migration with minimal downtime. Which source connectivity method does DMS support for connecting to the Oracle database over the internet?

Accepted Answer

Cloud SQL Auth Proxy. Google's Database Migration Service (DMS) supports connecting to an on-premises Oracle database over the internet using Cloud SQL Auth Proxy. This method establishes an encrypted tunnel using the Cloud SQL Auth Proxy client, which authenticates via IAM and provides secure connectivity without requiring a public IP on the source database. It is the only supported connectivity method for internet-based migrations in DMS, enabling continuous migration with minimal downtime.

Answer

Cloud Interconnect

Answer

VPC Network Peering to on-premises network via Cloud VPN

Answer

Direct connection using Oracle Net Services over public IP with SSL

Question 2

An organization is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0. They have many tables using MyISAM engine and want to ensure compatibility. What is the correct approach for handling MyISAM tables?

Accepted Answer

Convert MyISAM tables to InnoDB using ALTER TABLE ENGINE=InnoDB before migration.. Cloud SQL for MySQL 8.0 supports InnoDB as the default engine. MyISAM tables are not recommended because they lack transaction support and row-level locking. The best practice is to convert MyISAM tables to InnoDB using ALTER TABLE ... ENGINE=InnoDB before or after migration.

Answer

Migrate as-is and change engine after migration using mysqldump with --skip-create-options.

Answer

Use Database Migration Service which automatically converts MyISAM to InnoDB during migration.

Answer

Keep MyISAM as is; Cloud SQL fully supports MyISAM in MySQL 8.0.

Question 3

A team is migrating a Teradata data warehouse to BigQuery using the BigQuery Data Transfer Service. They need to schedule regular incremental transfers. What is the correct configuration for the transfer?

Accepted Answer

Create a transfer config with source type 'Teradata', target dataset, and schedule frequency.. BigQuery Data Transfer Service for Teradata supports scheduled transfers. The correct setup is to configure a transfer config with a schedule (e.g., daily) and specify the dataset and table mappings. The service handles incremental loads based on a watermark column.

Answer

Export Teradata data to Cloud Storage and use BigQuery load jobs scheduled via Cloud Scheduler.

Answer

Use Cloud Data Fusion to create a batch pipeline from Teradata to BigQuery.

Answer

Use a BigQuery scheduled query to SELECT * FROM Teradata via federated query.

Question 4

During an Oracle to PostgreSQL migration using Ora2Pg, a developer notices that PL/SQL packages are not being converted correctly. What is the recommended strategy to handle Oracle packages in PostgreSQL?

Accepted Answer

Recreate each procedure/function as a separate function in a dedicated schema, and use temporary tables for package variables.. PostgreSQL does not have a direct equivalent of Oracle packages. The recommended approach is to convert package procedures and functions to individual functions in a schema, and package variables to session-level variables or temporary tables. Ora2Pg can attempt conversion but often requires manual adjustments.

Answer

Use pglogical to replicate Oracle data to PostgreSQL and rewrite application code to call PostgreSQL functions directly.

Answer

Keep the Oracle database as a read replica and use Oracle Foreign Data Wrapper for PostgreSQL.

Answer

Use PostgreSQL's CREATE PACKAGE extension (available in some forks) to maintain package structure.

Question 5

A company is migrating a MySQL database to Cloud SQL. They plan to use Database Migration Service (DMS) with a continuous migration job to minimize downtime. How do they perform the cutover with zero downtime?

Accepted Answer

Use the DMS console to promote the replica; the promotion applies pending changes and makes the Cloud SQL instance writable.. DMS continuous migration uses a promotion step. When the source and target are in sync, the engineer promotes the Cloud SQL replica to a standalone instance. The application then switches to the new Cloud SQL instance. This is called 'promoting the replica' and can achieve near-zero downtime if coordinated correctly.

Answer

Take a snapshot of the Cloud SQL instance and restore it as a new instance.

Answer

Delete the migration job and manually update DNS to point to Cloud SQL.

Answer

Stop the source database, take a final dump, and load it into Cloud SQL.

Question 6

An organization is migrating from on-premises Microsoft SQL Server to Cloud SQL for SQL Server. They want to use a lift-and-shift approach to minimize application changes. Which migration method is most appropriate?

Accepted Answer

Export the database as a .bacpac file and import it into Cloud SQL.. Lift-and-shift means moving the database as-is to the cloud with minimal changes. Cloud SQL for SQL Server is compatible with on-premises SQL Server. The easiest method is to export a .bacpac file from the source and import it into Cloud SQL via the console or import/export feature.

Answer

Use Database Migration Service with continuous migration.

Answer

Use Cloud Dataflow to stream data from SQL Server to Cloud SQL.

Answer

Create a dump file using SQL Server Management Studio and restore it in Cloud SQL using the gcloud command.

Question 7

During a MySQL 5.7 to Cloud SQL for MySQL 8.0 migration, the team notices that some queries that worked before are now failing with 'ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause'. What is the cause and solution?

Accepted Answer

The error is due to the removal of the GROUP BY implicit grouping in MySQL 8.0. Upgrade the application to include all non-aggregated columns in GROUP BY.. MySQL 8.0 defaults to strict SQL mode, which changes GROUP BY behavior. MySQL 5.7 may have had sql_mode with ONLY_FULL_GROUP_BY disabled. The solution is to either enable ONLY_FULL_GROUP_BY in the source or adjust queries to comply, or set the sql_mode in Cloud SQL to include ONLY_FULL_GROUP_BY if not already set, and fix the queries.

Answer

The GROUP BY behavior change is due to the new caching SHA-2 password plugin. Disable it.

Answer

The queries are using JSON functions that are incompatible with MySQL 8.0. Rewrite them.

Answer

The default SQL mode in MySQL 8.0 includes ONLY_FULL_GROUP_BY, which was not enabled in 5.7. Set sql_mode to exclude ONLY_FULL_GROUP_BY in Cloud SQL.

Question 8

A company is planning to migrate from Snowflake to BigQuery. They have existing complex SQL transformations using Snowflake-specific functions like LISTAGG and PIVOT. What is the best approach to handle these differences?

Accepted Answer

Rewrite the SQL queries to use BigQuery equivalent functions and syntax.. Snowflake and BigQuery have different SQL dialects. The best approach is to use a migration assessment tool to identify incompatible syntax, rewrite queries to BigQuery-compatible syntax (e.g., use STRING_AGG instead of LISTAGG), and test thoroughly. BigQuery does not support PIVOT natively; use conditional aggregation.

Answer

Use BigQuery Data Transfer Service to automatically convert Snowflake functions.

Answer

Keep Snowflake for complex queries and use BigQuery only for simple analytics.

Answer

Use Cloud Spanner as an intermediary to translate queries.

Question 9

A team is migrating a legacy application database to Cloud SQL. They want to implement versioned schema changes as part of their CI/CD pipeline. Which tool should they use?

Accepted Answer

Liquibase. Both Liquibase and Flyway are popular database schema migration tools that integrate with CI/CD pipelines. They allow version-controlled, repeatable schema changes. The question asks for a tool; either is correct. But in GCP context, Liquibase and Flyway are both commonly used. The answer should be one of them.

Answer

gcloud sql import

Answer

Cloud Deployment Manager

Answer

Cloud Build

Question 10

During an Oracle to PostgreSQL migration using Database Migration Service (DMS), the continuous replication fails with an error about unsupported data types. The source table uses Oracle's RAW data type. How should this be handled?

Accepted Answer

Verify DMS supports RAW to BYTEA mapping; if not, use a manual export/import for that table.. Oracle RAW type maps to PostgreSQL BYTEA. DMS should handle this conversion automatically. If it fails, it may be due to version incompatibility or configuration. The correct action is to check DMS logs and ensure the mapping is correct; if DMS cannot handle it, a manual workaround is to convert RAW to BYTEA using a custom script.

Answer

Convert RAW to VARCHAR2 before migration using a trigger.

Answer

Raw data type is not supported by DMS; you must drop the column and re-add as BYTEA after migration.

Answer

Use Cloud Dataflow to transform the data as it streams.

Question 11

A company is migrating a critical on-premises PostgreSQL database to Cloud SQL. They need to test the migration with production data shape without affecting the source. What is the recommended testing strategy?

Accepted Answer

Use Database Migration Service to set up a continuous migration job and perform a dry run cutover.. To test migration with production data shape, one approach is to take a snapshot of the source, restore it in a test environment, and then perform the migration to Cloud SQL. Alternatively, use shadow writes (dual-writes) to both old and new DBs in a test environment. Comparing query results and load testing are also part of validation.

Answer

Run shadow writes from the application to both source and Cloud SQL concurrently.

Answer

Export the production data using pg_dump and import into a test Cloud SQL instance.

Answer

Use Cloud SQL's clone feature to create a copy of the source after migration.

Question 12

An organization is migrating from Amazon Redshift to BigQuery. They have large datasets stored in Amazon S3. What is the most efficient method to migrate the data?

Accepted Answer

Export Redshift data to S3, copy to Cloud Storage using Storage Transfer Service, then load into BigQuery.. The recommended approach is to export Redshift data to S3, then use BigQuery Data Transfer Service for Redshift to load data directly into BigQuery, or use Cloud Storage as intermediate. The most efficient is to use the BigQuery Data Transfer Service which can read from S3 directly (via a transfer config) or use Cloud Storage as a staging area.

Answer

Create a VPN between AWS and GCP and use federated query in BigQuery to read from Redshift directly.

Answer

Use Cloud Data Fusion to read from Redshift directly and write to BigQuery.

Answer

Use AWS Database Migration Service (DMS) to replicate data to Cloud SQL and then use BigQuery federated query.

Question 13

A company is migrating from Oracle to Cloud SQL for PostgreSQL using Database Migration Service. They need to ensure that the migration is validated before cutover. Which TWO actions should they take?

Accepted Answer

Run comparison queries (e.g., row counts, checksums) between source and target.. Option A is correct because running comparison queries (e.g., row counts, checksums) between the source Oracle database and the target Cloud SQL for PostgreSQL instance is a standard validation technique to ensure data consistency and completeness before cutover. Database Migration Service (DMS) handles continuous replication, but manual validation using queries like SELECT COUNT(*) or checksum functions (e.g., MD5 on concatenated columns) catches any discrepancies that might have been introduced during migration or replication lag.

Answer

Take a snapshot of the source database before migration.

Answer

Disable foreign key constraints on the target to speed up migration.

Answer

Implement shadow writes from the application to both databases simultaneously.

Question 14

An organization is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0. They must ensure compatibility of character sets and collations. Which THREE aspects should they review?

Accepted Answer

Review deprecated collations (e.g., utf8mb4_unicode_ci) that may be removed in future versions.. MySQL 8.0 has changed default character set to utf8mb4 and default collation to utf8mb4_0900_ai_ci. They should check: 1) Usage of utf8 vs utf8mb4 (utf8 is now an alias for utf8mb3). 2) The default collation change. 3) Any collation that is deprecated or removed in 8.0.

Answer

Ensure that all tables use the InnoDB storage engine.

Answer

Update the caching SHA-2 password plugin to ensure compatibility.

Question 15

A team is migrating a Teradata data warehouse to BigQuery. They need to assess the migration complexity. Which TWO factors are most critical in determining the level of effort for SQL translation?

Accepted Answer

Volume of BTEQ scripts and stored procedures that must be rewritten.. Teradata uses BTEQ scripts and has specific SQL syntax (e.g., QUALIFY, MACROS). The two critical factors are: 1) The volume of BTEQ scripts and stored procedures that need conversion. 2) The use of Teradata-specific functions and query constructs (like QUALIFY, MACROS, SET tables) that have no direct BigQuery equivalent.

Answer

Number of users and concurrent queries.

Answer

Number of tables and rows in the database.

Answer

Availability of network bandwidth between on-premises and GCP.