Migrate data solutions practice questions

The source database does not have the pglogical extension installed.

Why wrong: pglogical is not required; DMS uses built-in logical replication via publication/slot.

The destination AlloyDB cluster has insufficient storage capacity.

Why wrong: Storage capacity affects writes but does not cause a slot error; the error is source-side.

The DMS connection profile uses the wrong user credentials.

Why wrong: Incorrect credentials would cause initial connection failure, not a replication slot error after dump.

Use a REFCURSOR variable and OPEN it with a query using the parameter.

Why wrong: This approach uses a REFCURSOR and opens it with a query using the parameter. While functional, it does not match the parameterized cursor declaration syntax. In PL/pgSQL, the direct equivalent is to declare the cursor with parameters as in option D.

Replace the cursor with a FOR loop that returns a set of rows.

Why wrong: A FOR loop that returns a set of rows is a different pattern and not a direct conversion of a parameterized cursor.

Use a temporary table to store the query results.

Why wrong: Using a temporary table is unnecessary and not the correct conversion for a parameterized cursor.

Error log

Why wrong: The error log records errors, not data changes.

Slow query log

Why wrong: Slow query log is for performance analysis, not replication.

General query log

Why wrong: The general query log is not used for replication.

Use pg_dump with --no-owner flag; Oracle PL/SQL is compatible with PostgreSQL.

Why wrong: pg_dump is for PostgreSQL, not Oracle. PL/SQL is not compatible with PostgreSQL.

Use mysqldump to export Oracle schema; PL/SQL can be reused as-is.

Why wrong: mysqldump is for MySQL, not Oracle. PL/SQL is not directly compatible with PostgreSQL.

Use Database Migration Service (DMS) with built-in schema conversion; PL/SQL is automatically converted.

Why wrong: DMS does not have built-in schema conversion for Oracle to PostgreSQL; it requires external tools like Ora2Pg.

Cloud SQL Auth Proxy is not configured on the source.

Why wrong: Cloud SQL Auth Proxy is for connecting to Cloud SQL, not for source MySQL.

The Cloud SQL instance does not have binary logging enabled.

Why wrong: Cloud SQL is the target; binary logging is not required on the target for DMS.

The DMS migration job uses the wrong source connection profile.

Why wrong: A wrong connection profile would cause failure earlier, not specifically a missing binlog error.

pglogical

Why wrong: pglogical is a logical replication tool, not a schema conversion tool.

pg_dump

Why wrong: pg_dump is for PostgreSQL backups, not for converting Oracle schemas.

Database Migration Service (DMS)

Why wrong: DMS handles continuous migration and data replication but does not perform schema conversion for heterogeneous databases.

Use pg_dump to export the database and then import into Cloud SQL.

Why wrong: pg_dump is for PostgreSQL, not SQL Server.

Create a one-time migration job to copy the database during a maintenance window.

Why wrong: One-time migration requires a long downtime equal to the dump and import time.

Use mysqldump to export and import the database.

Why wrong: mysqldump is for MySQL, not SQL Server, and would require downtime.

The source database has a firewall blocking the DMS IP address.

Why wrong: Firewall issues typically cause connection timeouts, not relation OID errors.

The source database does not have the pglogical extension installed.

Why wrong: Missing extension would cause a different error about the extension itself.

The target AlloyDB cluster does not have the pglogical extension.

Why wrong: AlloyDB uses built-in logical replication; pglogical is not required on target.

The BigQuery Data Transfer Service is required for date conversion.

Why wrong: Data Transfer Service is for data loading, not schema conversion.

The Teradata source has a different date format that is incompatible with BigQuery.

Why wrong: Format differences can be handled in conversion, but the issue is function conversion.

BigQuery does not support date arithmetic.

Why wrong: BigQuery supports date arithmetic via DATE_ADD, DATE_DIFF, etc.

--all-databases --routines

Why wrong: These capture all DBs and routines but don't guarantee consistency without --single-transaction.

--lock-tables --single-transaction

Why wrong: --lock-tables and --single-transaction are incompatible; --lock-tables is not needed.

--flush-logs --master-data=2

Why wrong: These are for binary log position capture, not for consistent snapshot.

FLOAT

Why wrong: FLOAT is approximate, not exact numeric.

INTEGER

Why wrong: INTEGER cannot store decimal places.

DECIMAL(10,0)

Why wrong: Scale 0 discards decimal places.

Update connection strings first, then promote destination, then verify lag.

Why wrong: Connection strings should be updated after promotion.

Stop source database, promote destination, then update connection strings.

Why wrong: Stopping source before verifying lag may cause data loss.

Promote target immediately, then verify lag, then update connection strings.

Why wrong: Must verify lag is 0 before promoting.

Assign a public IP to the source database.

Why wrong: Not recommended for security; the source is behind a firewall.

Use DMS with a connection profile that specifies the source's private IP without any network configuration.

Why wrong: DMS cannot reach private IP without network connectivity like VPC peering.

Configure Cloud SQL Auth Proxy on the source.

Why wrong: Cloud SQL Auth Proxy authenticates and encrypts connections to Cloud SQL instances but does not resolve network-level access restrictions; it requires the source to already have outbound connectivity to the Cloud SQL public IP or private IP endpoint. In this scenario, the source database is behind a firewall with no public IP and the target uses a private IP, so the proxy cannot establish a tunnel because the firewall blocks the outbound connection to the Cloud SQL private IP. It is tempting because Auth Proxy is commonly used to securely connect local or on-premises clients to Cloud SQL without configuring SSL or firewall rules, and it would be correct if the source had outbound internet access or a public IP to reach the Cloud SQL public endpoint.

Use a larger machine type for the source connection profile.

Why wrong: The connection profile is just metadata; it doesn't affect dump parallelism.

Increase the Cloud SQL instance storage size.

Why wrong: Storage size does not affect dump speed.

Switch to a one-time migration job instead of continuous.

Why wrong: One-time migration uses the same dump mechanism; no speed improvement.

pgAdmin

Why wrong: pgAdmin is a management tool, not a testing framework.

SQL Developer

Why wrong: SQL Developer is an Oracle IDE, not for PostgreSQL testing.

Ora2Pg

Why wrong: Ora2Pg is for schema conversion, not testing.

Cloud Composer

Why wrong: Composer is a workflow orchestrator, not a direct data transfer service.

Cloud Data Fusion

Why wrong: Cloud Data Fusion is unsuitable for this scenario as it is primarily designed for complex ETL/ELT pipelines requiring extensive data transformation, cleansing, and orchestration across various sources, rather than a direct data load. While it can connect to Teradata and BigQuery, its strength lies in visual, code-free development of intricate data workflows. It is tempting as a general data integration platform and would be the correct choice if the requirement involved sophisticated data manipulation, aggregation, or joining multiple disparate datasets before loading into BigQuery.

Database Migration Service (DMS)

Why wrong: DMS is for live migrations, not scheduled batch transfers.

Enable binary logging on the source.

Why wrong: Binary logging should already be enabled for CDC.

Take a full backup of the source.

Why wrong: Not needed; DMS already replicated data.

Delete the DMS migration job to stop replication.

Why wrong: Do not delete; need to promote first.

VARCHAR2(10) -> TEXT

Why wrong: VARCHAR2 should map to VARCHAR(10) to preserve length constraint.

DATE -> DATE

Why wrong: Oracle DATE includes time; PostgreSQL DATE is date-only. Should map to TIMESTAMP.

Disable binary logging on the source.

Why wrong: Binary logging must be enabled for CDC.

Use a one-time migration job.

Why wrong: One-time migration requires longer downtime.

Use mysqldump and import manually.

Why wrong: More downtime and manual work.

Create a VPC peering connection between the source and DMS.

Why wrong: VPC peering is used for private IP connectivity, not public IP.

Install the Cloud SQL Auth Proxy on the source database.

Why wrong: Cloud SQL Auth Proxy is used for connecting to Cloud SQL, not for DMS connectivity from source.

Configure a Cloud VPN tunnel between the on-premises network and Google Cloud.

Why wrong: VPN is used for private connectivity but not required if the source has a public IP.