Courseiva

CCNA Pcd Migrate Data Questions

25 of 100 questions · Page 2/2 · Pcd Migrate Data topic · Answers revealed

Practice these questionsExam hubAll questions
76
Multi-Selecthard

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?

Select 2 answers
A.Volume of BTEQ scripts and stored procedures that must be rewritten.
B.Use of Teradata-specific features like QUALIFY, MACROS, and SET tables.
C.Number of users and concurrent queries.
D.Number of tables and rows in the database.
E.Availability of network bandwidth between on-premises and GCP.
AnswersA, B

BTEQ scripts and stored procedures require manual conversion, which drives effort.

Why this answer

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.

Practice this question →
77
Multi-Selecteasy

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.)

Select 2 answers
A.Cloud Build
B.Liquibase
C.Dataform
D.Flyway
E.Terraform
AnswersB, D

Liquibase supports version-controlled schema migrations with rollback and CI/CD integration.

Why this answer

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.

Practice this question →
78
MCQmedium

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?

A.Pass all state as parameters to functions.
B.Use PostgreSQL's global variables in the public schema.
C.Create a table in the schema to hold the state variables.
D.Use custom session-level GUC variables set via SET.
AnswerC

A table can simulate package-level variables; each session can use its own row.

Why this answer

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.

Practice this question →
79
MCQeasy

A database administrator is using Liquibase for schema versioning during a migration to Cloud SQL. What is the primary benefit of using Liquibase?

A.Real-time replication of data
B.Version control and rollback of schema changes
C.Automated performance tuning
D.Automatic data type conversion
AnswerB

Liquibase is designed for schema versioning and rollback.

Why this answer

Liquibase provides version control of database schema changes, allowing rollbacks and tracking in a declarative manner (changelogs).

Practice this question →
80
MCQhard

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?

A.SYSDATE
B.GETDATE()
C.CURRENT_TIMESTAMP
D.CURRENT_DATE
AnswerC

CURRENT_TIMESTAMP returns the current date and time (including time zone), equivalent to SYSDATE in Oracle.

Why this answer

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.

Practice this question →
81
MCQhard

A team is migrating an Oracle database to Cloud SQL for PostgreSQL. They are using Ora2Pg for schema conversion. Which of the following data type mappings is correct?

A.RAW → TEXT
B.CLOB → BYTEA
C.NUMBER → INTEGER
D.VARCHAR2 → VARCHAR
AnswerD

Oracle VARCHAR2 maps to PostgreSQL VARCHAR.

Why this answer

Oracle's VARCHAR2 maps to PostgreSQL's VARCHAR, CLOB maps to TEXT, RAW maps to BYTEA, NUMBER without precision maps to NUMERIC, and DATE maps to TIMESTAMP (or timestamp with time zone).

Practice this question →
82
MCQmedium

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.)

A.Cloud SQL Proxy
B.Cloud Deployment Manager
C.Liquibase or Flyway
D.Database Migration Service
AnswerC

Both are designed for version-controlled schema migrations and integrate with CI/CD.

Why this answer

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

Practice this question →
83
MCQmedium

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?

A.Replace with PostgreSQL's PRINT statement.
B.Use PostgreSQL's DBMS_OUTPUT extension from pgOracle.
C.Replace DBMS_OUTPUT.PUT_LINE with RAISE NOTICE in PostgreSQL.
D.Keep DBMS_OUTPUT.PUT_LINE as-is; PostgreSQL supports it.
AnswerC

RAISE NOTICE is the closest PostgreSQL equivalent for printing debug messages.

Why this answer

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.

Practice this question →
84
MCQmedium

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?

A.Export the database as a SQL dump and import into Cloud SQL.
B.Use BigQuery Data Transfer Service to migrate the data.
C.Use a one-time migration job with DMS.
D.Use DMS with continuous migration (CDC) and promote the replica for cutover.
AnswerD

Continuous migration keeps the source online, replicates changes via CDC, and allows a zero-downtime cutover by promoting the replica within the 30-minute window.

Why this answer

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.

Practice this question →
85
MCQmedium

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?

A.Cloud Deployment Manager
B.Cloud Build
C.Flyway
D.Database Migration Service
AnswerC

Flyway is a schema migration tool that supports version-controlled migrations.

Why this answer

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.

Practice this question →
86
Multi-Selectmedium

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL using Database Migration Service. They need to convert stored procedures and ensure data type compatibility. Which TWO tools or steps are essential?

Select 2 answers
A.Use Cloud SQL Auth Proxy for source connectivity.
B.Use Database Migration Service for data migration.
C.Use Ora2Pg to convert PL/SQL to PL/pgSQL.
D.Use pg_dump to export the Oracle database.
E.Manually rewrite all triggers using MySQL syntax.
AnswersB, C

DMS performs the actual data migration with options for continuous replication.

Why this answer

Ora2Pg is the standard tool for Oracle to PostgreSQL schema conversion, including stored procedures. DMS handles the data migration with CDC for minimal downtime.

Practice this question →
87
MCQmedium

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?

A.Manually rewrite all procedures from PL/SQL to PL/pgSQL.
B.Use Database Migration Service continuous migration to replicate the procedures.
C.Convert the Oracle database to PostgreSQL using pgloader instead.
D.Re-run Ora2Pg with the --debug flag to get detailed error output.
AnswerD

The debug flag provides detailed logs to pinpoint conversion errors.

Why this answer

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.

Practice this question →
88
MCQmedium

A company is migrating from MySQL 5.7 to Cloud SQL for MySQL 8.0. They have stored procedures that use GROUP BY with non-aggregated columns. In MySQL 5.7, this is allowed. What change might they need to make?

A.Switch to Cloud Spanner
B.Downgrade to MySQL 5.7 on Cloud SQL
C.Use ANY_VALUE() for non-aggregated columns in SELECT
D.No changes needed; MySQL 8.0 is backward compatible
AnswerC

ANY_VALUE() is a MySQL 8.0 function that suppresses the ONLY_FULL_GROUP_BY error.

Why this answer

MySQL 8.0 enables strict SQL mode by default and the ONLY_FULL_GROUP_BY mode, which disallows SELECTing non-aggregated columns not in GROUP BY. The procedures must be updated to comply or SQL mode changed.

Practice this question →
89
MCQhard

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?

A.The source Oracle database does not have archivelog enabled.
B.The Cloud SQL instance does not have enough storage to accommodate CDC logs.
C.The DMS connection profile for the source is misconfigured.
D.Some tables in the source database lack primary keys or a valid replica identity.
AnswerD

CDC requires a primary key or replica identity to track changes; without it, DMS cannot replicate changes.

Why this answer

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.

Practice this question →
90
MCQeasy

A company is migrating from Snowflake to BigQuery. They have a large amount of historical data. What is the recommended service to automate the data transfer?

A.Database Migration Service
B.Cloud Storage Transfer Service
C.Cloud Data Fusion
D.BigQuery Data Transfer Service
AnswerD

DTS for Snowflake is the native service for this migration.

Why this answer

BigQuery Data Transfer Service supports Snowflake as a source, allowing scheduled and automated transfers of data to BigQuery.

Practice this question →
91
Multi-Selectmedium

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.)

Select 2 answers
A.Set up a Compute Engine VM as a proxy and whitelist its static IP in the source firewall.
B.Create a continuous migration job in Database Migration Service.
C.Use Cloud SQL Auth Proxy to connect DMS to the source database.
D.Expose the source database on a public IP with SSL.
E.Configure Database Migration Service to use a one-time migration job.
AnswersA, B

This allows DMS to connect via the proxy with a known IP.

Why this answer

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.

Practice this question →
92
MCQmedium

A company plans to migrate a 200 GB Oracle database to Cloud SQL for PostgreSQL with minimal downtime. The source database has complex stored procedures and triggers. Which approach should the company use?

A.Use DMS with a one-time migration job and schedule downtime.
B.Export the Oracle database to a dump file and import into Cloud SQL using pg_restore.
C.Use DMS with continuous migration but skip Ora2Pg; DMS automatically converts PL/SQL to PL/pgSQL.
D.Use DMS with a continuous migration job, use Ora2Pg for schema conversion, and connect via Cloud SQL Auth Proxy.
AnswerD

Continuous migration with CDC achieves minimal downtime; Ora2Pg converts schema; Cloud SQL Auth Proxy connects securely.

Why this answer

Database Migration Service supports continuous migration with CDC for minimal downtime. Ora2Pg is used for schema conversion including stored procedures. Cloud SQL Auth Proxy provides secure connectivity without VPC peering.

Practice this question →
93
MCQmedium

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?

A.Ora2Pg
B.Database Migration Service
C.pgloader
D.Liquibase
AnswerA

Ora2Pg converts Oracle schemas to PostgreSQL, including PL/SQL to PL/pgSQL.

Why this answer

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.

Exam trap

The trap here is that candidates may confuse pgloader's data loading capabilities with schema conversion, or assume Database Migration Service is open-source, when in fact the question specifically asks for an open-source tool designed for schema conversion from Oracle to PostgreSQL.

How to eliminate wrong answers

Option B is wrong because Database Migration Service (DMS) is a fully managed service for migrating databases to Google Cloud, but it is not an open-source tool; it is a proprietary Google Cloud service. Option C is wrong because pgloader is an open-source tool for loading data into PostgreSQL, but it focuses on data migration from sources like MySQL, SQLite, and MS SQL Server, not on converting Oracle PL/SQL packages or schema objects. Option D is wrong because Liquibase is an open-source database schema change management tool that uses changelogs to track and apply schema modifications, but it does not specialize in converting Oracle PL/SQL to PostgreSQL syntax.

Practice this question →
94
MCQeasy

A data engineering team needs to migrate a 10 TB Teradata data warehouse to BigQuery. They want to automate the migration of historical data and ongoing changes. Which Google Cloud service should they use?

A.Dataproc
B.Cloud Data Fusion
C.BigQuery Data Transfer Service
D.Cloud SQL for PostgreSQL
AnswerC

BigQuery Data Transfer Service supports scheduled transfers from Teradata, including incremental loads.

Why this answer

BigQuery Data Transfer Service can automate data transfers from Teradata (and other sources) into BigQuery. It handles both historical and incremental loads.

Practice this question →
95
MCQmedium

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?

A.Use BigQuery Data Transfer Service for Teradata to automate migration.
B.Use gcloud command-line tool to export Teradata data and import into BigQuery.
C.Manually rewrite all Teradata SQL queries to BigQuery syntax.
D.Use Cloud SQL to create a linked server to Teradata.
AnswerA

Data Transfer Service supports Teradata and automates schema and SQL conversion.

Why this answer

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

Practice this question →
96
MCQmedium

An engineer is using Database Migration Service for a continuous migration from on-premises Oracle to Cloud SQL for PostgreSQL. The migration job has completed the full dump and is now in the CDC phase. The engineer wants to perform a zero-downtime cutover. What action should they take?

A.Promote the Cloud SQL replica to make it a standalone instance
B.Recreate the Cloud SQL instance and restart the migration
C.Stop the source database and then promote the Cloud SQL replica
D.Delete the migration job and manually import data
AnswerA

Promoting the replica stops replication and makes it a writable primary, achieving zero-downtime cutover.

Why this answer

Promoting the replica stops replication and makes the Cloud SQL instance writeable. This is the standard cutover procedure in DMS for continuous migration jobs.

Practice this question →
97
Multi-Selecthard

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?

Select 2 answers
A.Rewrite PL/SQL code into PL/pgSQL syntax.
B.Use pgAgent to schedule package execution.
C.Use Oracle compatibility mode in PostgreSQL.
D.Convert each package into a PostgreSQL schema containing functions and procedures.
E.Install the Orafce extension for Oracle compatibility.
AnswersA, D

PL/SQL must be converted to PL/pgSQL for PostgreSQL.

Why this answer

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.

Practice this question →
98
MCQmedium

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)?

A.Migrate the tables to Cloud Storage and use external tables.
B.Use the MEMORY storage engine instead.
C.Create the tables as MyISAM in Cloud SQL.
D.Convert the tables to InnoDB and recreate full-text indexes.
AnswerD

InnoDB supports full-text indexes; conversion is required.

Why this answer

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.

Exam trap

Cisco often tests the misconception that Cloud SQL supports MyISAM because it is MySQL-compatible, but Cloud SQL only supports InnoDB, so candidates must recognize the need to convert storage engines and recreate full-text indexes.

How to eliminate wrong answers

Option A is wrong because Cloud Storage external tables are used for querying data in object storage, not for migrating MyISAM tables; they do not solve the storage engine incompatibility. Option B is wrong because the MEMORY storage engine is volatile and unsuitable for persistent data, and it does not support full-text indexes, making it an invalid replacement for MyISAM tables with full-text indexes. Option C is wrong because Cloud SQL for MySQL does not support the MyISAM storage engine; attempting to create tables as MyISAM will fail or be silently converted to InnoDB, losing the full-text indexes.

Practice this question →
99
MCQmedium

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?

A.Store as STRING and use JSON_EXTRACT functions.
B.Store as ARRAY<STRUCT<...>> (RECORD type) with nested fields.
C.Store as JSON data type and use JSON functions for queries.
D.Store as BYTES and handle encoding in application.
AnswerB

RECORD type is the native way to represent semi-structured data in BigQuery and offers best performance.

Why this answer

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.

Practice this question →
100
MCQeasy

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?

A.Cloud Storage Transfer Service
B.Cloud Data Fusion
C.BigQuery Data Transfer Service
D.Database Migration Service
AnswerC

BigQuery Data Transfer Service has a Teradata connector that supports both historical backfill and continuous replication.

Why this answer

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

Practice this question →
← PreviousPage 2 of 2 · 100 questions total

Ready to test yourself?

Try a timed practice session using only Pcd Migrate Data questions.

Start 20-question session