PCDE Migrate data solutions Practice Test 10 — 15 Questions

Question 1

An organization is migrating a 2 TB MongoDB Atlas cluster to Firestore in Native mode. They require a one-time snapshot migration with minimal operational overhead and do not need continuous replication. The source cluster uses MongoDB 4.4 and has a single replica set. Which migration approach should they use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use the Google-provided mongodb-to-firestore migration tool or a custom Dataflow pipeline to transform and load documents.. Migrating MongoDB to Firestore in Native mode requires a tool that understands BSON and Firestore document structures. Google's migration tool or a custom Dataflow pipeline handles the transformation and can process large datasets efficiently. DMS does not target Firestore, and BigQuery Data Transfer Service lacks MongoDB support. The managed import/export feature is only for Firestore-native formats, making the dedicated tool or Dataflow the correct choice.

Answer

Use BigQuery Data Transfer Service to load MongoDB data into BigQuery, then export to Firestore.

Answer

Use Database Migration Service (DMS) with a MongoDB source and Firestore destination, configured for continuous replication.

Answer

Use the Firestore managed import/export feature with a JSON export from MongoDB.

Question 2

A startup is migrating a small 50 GB MySQL 8.0 database from a virtual machine in Google Compute Engine to Cloud SQL for MySQL. The database can tolerate a brief downtime of up to 30 minutes. They want a simple, cost-effective migration with minimal setup. Which method should they use?

Accepted Answer

Perform a mysqldump export, transfer the dump file to Cloud Storage, and import it into Cloud SQL.. For a small database with acceptable downtime, mysqldump combined with Cloud Storage import is the simplest and most cost-effective approach. It requires no continuous replication setup and can be completed within the allowed 30-minute window. DMS and replication methods add unnecessary complexity for this scenario.

Answer

Use Database Migration Service (DMS) for MySQL with continuous replication.

Answer

Use the Cloud SQL import feature to directly import from the source database over a private IP.

Answer

Set up a replica from the Compute Engine VM to Cloud SQL and promote it during cutover.

Question 3

A company is migrating a 5 TB on-premises SQL Server database to Cloud SQL for SQL Server. They want to minimize downtime and ensure data consistency. They plan to use Database Migration Service (DMS) for the migration. The database has a high volume of transactions, and the migration must be completed within a tight maintenance window. Which DMS migration type should they use to achieve minimal downtime?

Accepted Answer

Continuous migration. Continuous migration is designed for minimal downtime by performing an initial full dump followed by continuous change data capture. This keeps the destination synchronized with the source until cutover, allowing the source to remain active. For a high-transaction 5 TB database, continuous migration ensures that only a short final cutover is needed, fitting within a tight maintenance window.

Answer

One-time migration

Answer

Homogeneous migration

Answer

Heterogeneous migration

Question 4

A retail company is migrating a 3 TB on-premises MongoDB 4.4 replica set to Firestore in Native mode. They need to preserve document structure and minimize transformation effort. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Map MongoDB collections to Firestore collections and convert ObjectId values to a supported Firestore type during the transfer.. The supported route is to use the Dataflow MongoDB to Firestore template, which streams documents from MongoDB and writes them into Firestore while preserving nested structures. Because Firestore lacks an ObjectId type, identifiers and other BSON-specific values must be mapped to supported types, typically strings. Together these actions deliver the migration with minimal custom transformation code.

Answer

Export the MongoDB data with mongoexport in JSON format and import the files into Firestore using the managed import feature.

Answer

Enable the MongoDB wire protocol compatibility mode on Firestore so existing drivers can write directly without changes.

Answer

Shard the destination Firestore database into multiple named databases to parallelize the 3 TB write load.

Question 5

A financial services company is migrating a 4 TB PostgreSQL 13 database from an on-premises data center to Cloud SQL for PostgreSQL. The database has a write-heavy workload with continuous transactions. The migration must minimize downtime and ensure data consistency. They have a 1 Gbps dedicated network link. Which migration approach should they use?

Accepted Answer

Use Database Migration Service (DMS) with continuous replication and promote the destination after verifying replication lag.. Database Migration Service is designed for minimal-downtime migrations to Cloud SQL, supporting continuous replication from PostgreSQL sources. It performs an initial snapshot and then applies ongoing changes, allowing cutover with minimal disruption. The other options either cause significant downtime, are unsupported by Cloud SQL, or require unsupported extensions. Thus, DMS with continuous replication is the correct approach.

Answer

Perform a pg_dump and pg_restore over the network during a maintenance window, then switch the application connection string.

Answer

Set up a PostgreSQL streaming replication from on-premises to Cloud SQL, then promote the Cloud SQL replica.

Answer

Use a third-party logical replication tool like pglogical to replicate changes to Cloud SQL, then perform a cutover.

Question 6

You are migrating an on-premises PostgreSQL 14 database to Cloud SQL for PostgreSQL using Database Migration Service. The database is 800 GB and the business requires under five minutes of write downtime during cutover. You have already completed the initial full dump and the destination is replicating continuously from the source. Which action should you take to complete the migration with the least downtime?

Accepted Answer

Stop application writes to the source, wait for the destination replica lag to reach zero, verify row counts, then promote the Cloud SQL instance.. Continuous migration keeps the destination caught up, but cutover still requires a controlled write freeze so no transactions are lost. Waiting for replication lag to reach zero confirms full synchronization, and promotion then flips the Cloud SQL instance to a standalone primary in a short window.

Answer

Increase the source database max_wal_senders and max_replication_slots, then keep the source writable permanently as a dual-write primary.

Answer

Run a second full dump with pg_dump while the destination is still replicating, then import it to overwrite the destination tables.

Answer

Promote the Cloud SQL instance immediately, then use logical replication slots to replay any transactions that the source received after promotion.

Question 7

A logistics company is migrating an on-premises MySQL 5.7 database to Cloud SQL for MySQL 8.0. They want to use Database Migration Service and need the initial load to complete without errors. The source database uses the MyISAM storage engine for several tables. What must the engineer do before starting the DMS migration job?

Accepted Answer

Convert the MyISAM tables to InnoDB on the source before the migration begins.. Database Migration Service for MySQL depends on InnoDB for consistent snapshots and row-based change capture. MyISAM tables cannot provide the transactional guarantees the service needs, so the migration would fail during the initial load. Converting those tables to InnoDB on the source before starting the job is the required remediation.

Answer

Set the binlog_format to STATEMENT on the source so MyISAM changes are captured during CDC.

Answer

Enable the MyISAM storage engine on the destination Cloud SQL instance by setting a database flag.

Answer

Configure the DMS job to use the mysqldump utility with the --skip-lock-tables option for MyISAM tables.

Question 8

A media company is migrating a 10 TB on-premises MongoDB 4.4 replica set to MongoDB Atlas on Google Cloud. They need to minimize downtime and ensure that all writes during migration are captured. They have a dedicated 10 Gbps interconnect. Which migration approach should they use?

Accepted Answer

Use MongoDB Atlas Live Migration Service to perform a continuous migration.. MongoDB Atlas Live Migration Service is the managed tool for migrating on-premises MongoDB replica sets to Atlas with minimal downtime. It performs an initial sync and then continuously applies oplog changes until cutover, ensuring all writes are captured. With a high-speed interconnect, the migration is efficient. Other methods either lack continuous replication or are not designed for this purpose.

Answer

Use Google Cloud Dataflow to read from MongoDB and write to Atlas.

Answer

Use mongodump and mongorestore to copy the data, then manually sync changes.

Answer

Configure cross-region replica set between on-premises and Atlas, then promote Atlas nodes.

Question 9

A company is migrating a large on-premises Microsoft SQL Server database to Cloud SQL for SQL Server using Database Migration Service (DMS). The database uses SQL Server Agent jobs for nightly ETL processes. The migration must be continuous with minimal downtime. Which of the following should they do to ensure the SQL Server Agent jobs are migrated correctly?

Accepted Answer

Manually recreate the SQL Server Agent jobs on the Cloud SQL instance after the migration is complete.. DMS migrates user databases but not instance-level objects like SQL Server Agent jobs. These jobs are stored in msdb and must be manually recreated on the target Cloud SQL instance after cutover. This ensures that scheduled ETL processes continue to function. While DMS handles schema and data, it does not cover server-level automation, so manual intervention is required.

Answer

Configure DMS to include the msdb database in the migration scope so that jobs are transferred automatically.

Answer

Use the SQL Server Integration Services (SSIS) to export the jobs and import them into Cloud SQL after migration.

Answer

Enable the 'Migrate instance-level objects' option in the DMS migration job settings.

Question 10

A company is migrating a small on-premises MySQL 5.7 database to Cloud SQL for MySQL using a one-time dump and load. They want to use the simplest method that requires minimal configuration. Which tool should they use?

Accepted Answer

mysqldump with the --single-transaction option, then import using the Cloud SQL import feature.. For a small one-time MySQL migration to Cloud SQL, mysqldump with --single-transaction and the Cloud SQL import feature is the simplest and most straightforward method. It requires minimal configuration, avoids ongoing replication setup, and is well-suited for databases where downtime is acceptable. DMS and replication are more complex and intended for larger or minimal-downtime scenarios. The non-existent wizard is not a valid option.

Answer

Use the Google Cloud Console's 'Migrate MySQL' wizard that automatically handles the migration.

Answer

MySQL replication by configuring the source as a master and Cloud SQL as a replica.

Answer

Database Migration Service (DMS) with continuous replication.

Question 11

A healthcare company is migrating a 10 TB on-premises Apache Cassandra cluster to Cloud Bigtable. The data model uses wide rows with millions of columns per partition key, and the application performs point reads by row key. They need to minimize read latency after migration. Which schema and migration design should the engineer choose?

Accepted Answer

Map each Cassandra row key to a Bigtable row key and each Cassandra column to a Bigtable column qualifier within a single column family, then load with the Dataflow Cassandra to Bigtable template.. Bigtable's wide-column model maps naturally onto Cassandra's partition-and-column layout, so using the Cassandra row key as the Bigtable row key and columns as qualifiers preserves the application's point-read pattern. Keeping all qualifiers in one column family avoids extra lookups, and the Dataflow template provides the parallel bulk load needed for 10 TB.

Answer

Store each Cassandra row as a serialized blob in a single Bigtable column, and decode it in the application after the read.

Answer

Use BigQuery as an intermediate store, export the Cassandra data to Avro, load into BigQuery, and then run scheduled queries to copy into Bigtable.

Answer

Map each Cassandra partition to a Bigtable column family and each Cassandra column to a separate Bigtable row, then query with a row-range scan.

Question 12

A financial services firm is migrating an on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL using Database Migration Service (DMS) with continuous replication. During the CDC phase, they observe that changes to tables with large object (LOB) columns are not being replicated, while changes to other tables replicate normally. The source uses the default logical decoding plugin. Which action should they take to resolve this?

Accepted Answer

Exclude the LOB tables from CDC and migrate them separately using pg_dump and pg_restore during the cutover window.. PostgreSQL logical decoding does not capture changes to large objects, so DMS continuous replication silently omits LOB changes. The supported workaround is to exclude those tables from the CDC stream and migrate them separately with pg_dump and pg_restore during cutover. Adjusting replication slots, changing output plugins, or installing pglogical does not overcome this fundamental decoding limitation.

Answer

Switch the source logical decoding plugin to wal2json, which supports large object changes, and restart the migration job.

Answer

Increase the max_replication_slots parameter on the source and restart the DMS migration job.

Answer

Enable the pglogical extension on the source and configure DMS to use it as the replication method for LOB tables.

Question 13

A startup is migrating a small 50 GB MySQL 8.0 database from a single on-premises server to Cloud SQL for MySQL. They want to use Database Migration Service (DMS) for a one-time migration with minimal configuration and no continuous replication. Which source connection profile configuration should they use?

Accepted Answer

Configure the source connection profile with the host IP, port 3306, a user with SELECT, RELOAD, and LOCK TABLES privileges, and no binary logging requirement.. For a one-time MySQL migration with DMS, the source user requires SELECT, RELOAD, and LOCK TABLES privileges to read data and obtain a consistent snapshot. Binary logging and replication privileges are only needed for continuous replication. Granting ALL PRIVILEGES or omitting RELOAD and LOCK TABLES would either violate least privilege or cause the migration to fail.

Answer

Configure the source connection profile with the host IP, port 3306, a user with ALL PRIVILEGES, and enable GTID mode on the source.

Answer

Configure the source connection profile with the host IP, port 3306, a user with SELECT only, and rely on DMS to handle table locking automatically without additional privileges.

Answer

Configure the source connection profile with the host IP, port 3306, a user with REPLICATION SLAVE and REPLICATION CLIENT privileges, and binary logging enabled.

Question 14

A financial services firm is migrating an on-premises Microsoft SQL Server 2019 database to Cloud SQL for SQL Server. The database is 1.5 TB, and the team wants a managed, low-downtime path with continuous replication and built-in validation. Which approach should they use?

Accepted Answer

Use Database Migration Service for SQL Server to perform a full backup plus transaction log shipping for continuous replication, then promote at cutover.. Database Migration Service for SQL Server is purpose-built for this migration, combining an initial full backup with continuous transaction log shipping so the destination stays current until a short promotion window. It also provides validation of the migrated data, matching the managed, low-downtime goal.

Answer

Configure transactional replication from the on-premises SQL Server to a Cloud SQL for SQL Server subscriber over a VPN.

Answer

Use SQL Server Always On availability groups with the Cloud SQL instance as an asynchronous secondary replica.

Answer

Back up the database to a BAK file, upload it to Cloud Storage, and restore it to Cloud SQL for SQL Server using the import feature.

Question 15

A media company is migrating a 10 TB on-premises PostgreSQL 14 database to Cloud SQL for PostgreSQL using Database Migration Service (DMS) with continuous replication. The migration must minimize downtime, and the source database has a high write throughput. Which TWO actions should they take to ensure the CDC phase can keep up and the cutover is as short as possible? (Choose two.)

Accepted Answer

Increase the Cloud SQL destination instance's vCPU and memory before starting the migration to handle the initial load and CDC apply rate.. To keep CDC up with high write throughput and minimize cutover time, the source must have adequate wal_sender and max_replication_slots so the replication slot is not starved, and the destination Cloud SQL instance must be sized to apply changes quickly. Making the source read-only, tweaking dump parallelism, or disabling autovacuum either increases downtime or degrades performance without addressing the core bottlenecks.

Answer

Configure the DMS migration job to use a larger dump parallelism setting to speed up the initial load and reduce CDC catch-up time.

Answer

Disable autovacuum on the destination Cloud SQL instance during the migration to reduce I/O overhead and improve apply throughput.

Answer

Set the source database to read-only mode during the initial dump to prevent writes from accumulating in the WAL.