PCD Migrate Data Solutions — Timed 25 Questions

Question 1

A company is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). They need to minimize downtime during the cutover. What should they do to achieve a zero-downtime cutover?

Accepted Answer

Create a continuous migration job, let it replicate changes, then promote the Cloud SQL replica to primary.. Option A is correct because Database Migration Service (DMS) supports continuous migration jobs that use PostgreSQL logical replication to keep the Cloud SQL replica synchronized with the on-premises source. When you are ready to cut over, you promote the Cloud SQL replica to a standalone primary, which applies any remaining changes and makes the database available with minimal downtime — typically only seconds for the final replication lag to clear.

Answer

Set up VPC peering between on-premises and Cloud SQL, then use pg_dump and pg_restore.

Answer

Use Cloud SQL Auth Proxy to connect and manually copy data using a script.

Answer

Create a one-time migration job and stop the source database during the migration.

Question 2

An organization is migrating a large Oracle database to Cloud SQL for PostgreSQL using Ora2Pg. They have complex PL/SQL packages with overloaded procedures. What is the correct approach to handle these packages in PostgreSQL?

Accepted Answer

Convert each Oracle package to a PostgreSQL schema containing the procedures and functions as individual objects.. PostgreSQL does not have packages. The recommended approach is to map each Oracle package to a separate schema containing the package's procedures and functions as top-level objects. Overloaded procedures can be handled by creating separate functions with different names or using default parameters. Ora2Pg can automate much of the conversion but manual review is needed.

Answer

Convert each package to a single PostgreSQL function containing all logic, using conditional logic to mimic overloaded procedures.

Answer

Keep the packages as is; PostgreSQL supports packages natively.

Answer

Use Oracle compatibility mode in PostgreSQL to enable package support.

Question 3

A company is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0. They use MyISAM tables and utf8 charset. What changes must they make during migration?

Accepted Answer

Convert MyISAM tables to InnoDB and change charset from utf8 to utf8mb4.. MySQL 8.0 defaults to InnoDB, and utf8mb4 is recommended. MyISAM tables should be converted to InnoDB. utf8 in MySQL is an alias for utf8mb3 (3-byte), which cannot store emoji; utf8mb4 (4-byte) is recommended. MySQL 5.7 to 8.0 introduces changes like caching_sha2_password authentication and stricter GROUP BY. They must convert MyISAM to InnoDB and change charset to utf8mb4.

Answer

Convert MyISAM to InnoDB only; utf8 is fine in MySQL 8.0.

Answer

No changes needed; Cloud SQL automatically converts MyISAM to InnoDB and utf8 to utf8mb4.

Answer

Change charset to utf8mb4 only; MyISAM is supported in Cloud SQL.

Question 4

A team is migrating a Teradata data warehouse to BigQuery. They use BTEQ scripts for ETL. What is the best approach to migrate the BTEQ scripts?

Accepted Answer

Rewrite BTEQ scripts as BigQuery SQL using GoogleSQL syntax, manually handling differences.. Teradata BTEQ scripts use Teradata SQL dialect. BigQuery uses GoogleSQL (or legacy SQL). Direct translation is needed. The BigQuery Data Transfer Service can automate loading from Teradata but does not convert BTEQ scripts. Manual rewriting or using a SQL translation tool is required. Some BTEQ commands have no BigQuery equivalent (e.g., .EXPORT).

Answer

Use a third-party tool like Ora2Pg to convert BTEQ scripts.

Answer

Use BigQuery Data Transfer Service to automatically convert BTEQ scripts to BigQuery SQL.

Answer

Run BTEQ scripts directly in BigQuery using the BigQuery Connector for Teradata.

Question 5

An organization wants to migrate a Redshift data warehouse to BigQuery. They have a large dataset stored in Redshift. What is the most efficient migration approach for the initial data load?

Accepted Answer

Unload data from Redshift to Amazon S3, then load from S3 into BigQuery using BigQuery Data Transfer Service.. The recommended approach is to unload data from Redshift to Amazon S3 in Parquet or Avro format, then use BigQuery Data Transfer Service or load jobs to ingest from S3. Direct export from Redshift to BigQuery is not possible. Using a Cloud Storage intermediary would require copying from S3 to GCS, adding latency and cost.

Answer

Copy Redshift data to Cloud Storage using gsutil, then load into BigQuery.

Answer

Export data directly from Redshift to BigQuery using a JDBC connection.

Answer

Use the BigQuery Connector for Redshift to stream data directly.

Question 6

A company is migrating from Oracle to Cloud SQL for PostgreSQL. They have a stored procedure that uses Oracle's DBMS_OUTPUT.PUT_LINE for logging. How should they convert this to PostgreSQL?

Accepted Answer

Use RAISE NOTICE to output messages.. PostgreSQL does not have DBMS_OUTPUT. The equivalent is RAISE NOTICE, which sends messages to the client. Alternatively, they can log to a table using INSERT. RAISE is the most direct replacement. RAISE EXCEPTION is for errors, and dbms_output is not available.

Answer

Use RAISE EXCEPTION to output messages.

Answer

Use dbms_output package (installed via extension) to convert.

Answer

Use DBMS_OUTPUT.PUT_LINE as is; PostgreSQL supports it via the orafce extension.

Question 7

A team is planning to migrate a 500 GB MySQL database to Cloud SQL. The application can tolerate up to 2 hours of downtime. Which migration method should they choose?

Accepted Answer

Use a one-time migration job with Database Migration Service.. For a 500 GB database with a 2-hour downtime window, a one-time migration (offline) is sufficient because the dump and restore can complete within the window. Continuous migration is used for minimal downtime but adds complexity. Database Migration Service supports both one-time and continuous jobs.

Answer

Use mysqldump to export and import manually.

Answer

Use Cloud SQL replication to set up a read replica, then promote.

Answer

Use Database Migration Service with a continuous migration job.

Question 8

A company is migrating from Snowflake to BigQuery. They have a large dataset with semi-structured data stored as VARIANT columns in Snowflake. How should they represent this data in BigQuery?

Accepted Answer

Store as ARRAY> (RECORD type) with nested fields.. Snowflake's VARIANT type is used for semi-structured data like JSON. BigQuery has the RECORD (STRUCT) and REPEATED modes for nested and repeated data, and supports JSON data type (in preview) or storing as a STRING. The best practice is to use the native RECORD type to represent the structure for query performance and ease of use.

Answer

Store as STRING and use JSON_EXTRACT functions.

Answer

Store as JSON data type and use JSON functions for queries.

Answer

Store as BYTES and handle encoding in application.

Question 9

You need to migrate an on-premises PostgreSQL database to Cloud SQL using Database Migration Service. The source database is behind a firewall without a public IP. Which connectivity option should you configure?

Accepted Answer

Use VPC peering with a VPN tunnel between on-premises and Google Cloud.. For source databases without public IP and behind a firewall, DMS supports connectivity via VPC peering (if the source is in a VPC) or using Cloud SQL Auth Proxy. However, if the source is on-premises, you need to establish connectivity via VPN or Interconnect and then use VPC peering. Cloud SQL Auth Proxy is not for source connectivity in DMS. The correct option is to use VPC peering with a VPN.

Answer

Use a Cloud SQL proxy container on-premises to forward connections.

Answer

Expose the source database on a public IP with SSL.

Answer

Use Cloud SQL Auth Proxy to connect to the source database.

Question 10

A company is migrating from Oracle to Cloud SQL for PostgreSQL. They have a table with a column of type RAW(16). Which PostgreSQL data type should they use?

Accepted Answer

BYTEA. Oracle RAW is a variable-length binary data type. PostgreSQL equivalent is BYTEA. UUID is for universally unique identifiers, but RAW can store any binary data, not just UUIDs. TEXT is for strings, and BYTEA is the correct binary type.

Answer

BYTES

Answer

UUID

Answer

TEXT

Question 11

A team is migrating a set of stored procedures from Oracle to Cloud SQL for PostgreSQL. The procedures use Oracle's package-level global variables. How should they handle this in PostgreSQL?

Accepted Answer

Create a table in the schema to hold the state variables.. PostgreSQL does not have package-level global variables. The recommended approach is to use a temporary table within a schema (the equivalent of a package) or use session-level variables with custom GUCs. The simplest is to create a table in the corresponding schema to hold the state.

Answer

Pass all state as parameters to functions.

Answer

Use PostgreSQL's global variables in the public schema.

Answer

Use custom session-level GUC variables set via SET.

Question 12

An organization is planning a re-architect migration of a monolithic Oracle database to a microservices architecture using Cloud SQL for PostgreSQL and Cloud Spanner. They need to split the schema into multiple databases while maintaining data consistency across services. Which approach should they use?

Accepted Answer

Use Cloud SQL for PostgreSQL for each service and implement sagas to handle distributed transactions.. For microservices, each service should own its database. To maintain consistency across services, they should implement compensating transactions (sagas) rather than distributed transactions. Cloud Spanner can be used if strong consistency is required across global scales, but for microservices, the pattern is to use eventual consistency with sagas.

Answer

Use a single Cloud SQL for PostgreSQL instance with different schemas per service.

Answer

Use BigQuery to store all data and query across services.

Answer

Use Cloud Spanner for all services to maintain ACID transactions across databases.

Question 13

Your company is migrating a 2 TB Oracle database to Cloud SQL for PostgreSQL using Database Migration Service (DMS). The source database is behind a firewall and only allows connections from specific IP ranges. The migration must have minimal downtime. Which TWO actions should you take? (Choose 2 options.)

Accepted Answer

Set up a Compute Engine VM as a proxy and whitelist its static IP in the source firewall.. For minimal downtime, continuous migration is needed. To connect to the source behind a firewall, you need either VPC peering with a VPN or a Compute Engine forwarding proxy with a whitelisted IP. Cloud SQL Auth Proxy is for connecting to Cloud SQL, not for source connectivity. A one-time migration would cause downtime.

Answer

Use Cloud SQL Auth Proxy to connect DMS to the source database.

Answer

Expose the source database on a public IP with SSL.

Answer

Configure Database Migration Service to use a one-time migration job.

Question 14

A company is migrating from MySQL 5.7 to Cloud SQL for MySQL 8.0. They have a mix of MyISAM and InnoDB tables, and their application uses utf8 charset. They want to minimize changes to the application code. Which THREE changes should they make during migration? (Choose 3 options.)

Accepted Answer

Convert all MyISAM tables to InnoDB.. MySQL 8.0 changes default authentication to caching_sha2_password, which may break application connections if the client driver does not support it. MyISAM tables should be converted to InnoDB for reliability and features like foreign keys. utf8 charset should be changed to utf8mb4 to avoid truncation of 4-byte characters. The caching_sha2_password plugin is default; if the app uses older connectors, they may need to set the default to mysql_native_password or update the connector.

Answer

Keep utf8 charset to avoid application code changes.

Answer

Keep the default authentication plugin as caching_sha2_password.

Question 15

A data engineering team wants to implement version-controlled schema migrations for their PostgreSQL database on Cloud SQL. They need to integrate with their CI/CD pipeline. Which TWO tools should they consider? (Choose 2 options.)

Accepted Answer

Liquibase. Liquibase and Flyway are the two most popular database schema migration tools that support versioning, rollback, and CI/CD integration. Dataform is for data transformation in BigQuery, not schema migration. Cloud Build is a CI/CD service but not a schema migration tool. Terraform is for infrastructure provisioning, not schema migration.

Answer

Cloud Build

Answer

Dataform

Answer

Terraform

Question 16

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 17

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 18

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 19

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 20

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.