PCD Practice Test 30 — 15 Questions

Question 1

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?

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

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 2

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 3

A team is migrating a Redshift data warehouse to BigQuery using BigQuery Data Transfer Service. They need to ensure the transfer is secure and cost-effective. Which THREE considerations are important?

Accepted Answer

Use Amazon S3 as intermediate staging for unloaded data.. Intermediate staging in S3, encrypting data in transit, and enabling automatic refresh are typical considerations for Redshift to BigQuery transfers using Data Transfer Service.

Answer

Configure private VPC peering between Redshift and BigQuery.

Answer

Enable Cloud NAT for the transfer service to access Redshift.

Question 4

A company wants to migrate their MySQL 5.7 database to Cloud SQL for MySQL. They are concerned about performance after migration. Which step is essential for ensuring optimal performance on Cloud SQL?

Accepted Answer

Convert all MyISAM tables to InnoDB.. MyISAM tables are not supported on Cloud SQL for MySQL 8.0; they must be converted to InnoDB. InnoDB is the default engine and provides better performance, crash recovery, and concurrency.

Answer

Use Cloud SQL Auth Proxy to connect to the source.

Answer

Enable binary logging on the source database before migration.

Answer

Set the character set to utf8mb4 on the source.

Question 5

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

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 8

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 9

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

Accepted Answer

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

Answer

Cloud SQL Proxy

Answer

Cloud Deployment Manager

Answer

Database Migration Service

Question 10

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?

Accepted Answer

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

Answer

Use Cloud SQL Auth Proxy for source connectivity.

Answer

Use pg_dump to export the Oracle database.

Answer

Manually rewrite all triggers using MySQL syntax.

Question 11

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?

Accepted Answer

Promote the Cloud SQL replica to make it a standalone instance. Promoting the replica stops replication and makes the Cloud SQL instance writeable. This is the standard cutover procedure in DMS for continuous migration jobs.

Answer

Recreate the Cloud SQL instance and restart the migration

Answer

Stop the source database and then promote the Cloud SQL replica

Answer

Delete the migration job and manually import data

Question 12

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?

Accepted Answer

Rewrite PL/SQL code into PL/pgSQL syntax.. 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.

Answer

Use pgAgent to schedule package execution.

Answer

Use Oracle compatibility mode in PostgreSQL.

Answer

Install the Orafce extension for Oracle compatibility.

Question 13

A company deploys a microservice on Google Kubernetes Engine (GKE) with a Cloud Deploy delivery pipeline. The application uses a custom container image stored in Artifact Registry. After a successful deployment to a staging cluster, the production deployment fails with 'ImagePullErr: image not found'. The staging and production clusters are in different projects. What is the most likely cause?

Accepted Answer

The production cluster's node pool has not been granted access to pull images from Artifact Registry in the staging project.. The production cluster's node pool, which runs in a different project, does not have the necessary permissions to pull the custom container image from Artifact Registry in the staging project. By default, GKE node pools use the Compute Engine default service account, which only has access to images in the same project. To pull images across projects, the node pool's service account must be granted the Artifact Registry Reader role (roles/artifactregistry.reader) on the repository in the staging project.

Answer

The Cloud Deploy service account lacks permission to create pods in the production cluster.

Answer

Cloud Deploy is not configured to use Artifact Registry and still references Container Registry.

Answer

The container image tag used in production is different from the staging tag.

Question 14

A company runs a critical application on Compute Engine with a stateful database. They need to achieve 99.99% availability for the database tier. Which architecture should they implement?

Accepted Answer

A Compute Engine instance group with managed instance groups and a regional persistent disk configured for synchronous replication.. A managed instance group with a regional persistent disk configured for synchronous replication provides the necessary 99.99% availability by ensuring the database runs across two zones with synchronous writes to both replicas. This architecture allows automatic failover within seconds if one zone fails, meeting the high-availability requirement without data loss.

Answer

Use Cloud SQL with automatic failover and read replicas.

Answer

Two Compute Engine instances in different zones with a shared Zonal persistent disk.

Answer

Single Compute Engine instance with a persistent disk snapshot scheduled every hour.

Question 15

You are migrating an on-premises PostgreSQL database (OLTP, 200 GB) to Google Cloud. The database requires high availability with automatic failover and zero RPO. Which configuration should you use?

Accepted Answer

Cloud SQL HA instance. Cloud SQL HA instance provides synchronous replication to a standby in a different zone within the same region, ensuring automatic failover with zero RPO because no committed transaction is lost. This meets the requirement for high availability and zero data loss for an OLTP PostgreSQL database of this size.

Answer

AlloyDB with HA configuration

Answer

Cloud SQL single zone instance with an external replica

Answer

Cloud SQL instance with cross-region read replica