PCD Practice Test 29 — 15 Questions

Question 1

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?

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

Accepted Answer

Replace DBMS_OUTPUT.PUT_LINE with RAISE NOTICE in PostgreSQL.. 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.

Answer

Replace with PostgreSQL's PRINT statement.

Answer

Use PostgreSQL's DBMS_OUTPUT extension from pgOracle.

Answer

Keep DBMS_OUTPUT.PUT_LINE as-is; PostgreSQL supports it.

Question 2

A gaming company uses Cloud Spanner for its global leaderboard. The leaderboard is updated frequently by millions of users. They notice that write latency spikes during peak hours due to hotspotting on the leaderboard table. Which schema change would best mitigate this?

Accepted Answer

Prefix the primary key with a server ID or hash of the user ID. Using a composite primary key with a hash prefix (e.g., server_id) as the first part distributes writes across splits, reducing hotspots. A monotonically increasing key (like rank) causes hotspotting.

Answer

Use UUID as the primary key

Answer

Add a secondary index on the rank column

Answer

Enable leaderboard caching with Memorystore

Question 3

An organization is migrating a MySQL 5.7 database to Cloud SQL for MySQL 8.0. They have many tables using MyISAM engine and want to ensure compatibility. What is the correct approach for handling MyISAM tables?

Accepted Answer

Convert MyISAM tables to InnoDB using ALTER TABLE ENGINE=InnoDB before migration.. Cloud SQL for MySQL 8.0 supports InnoDB as the default engine. MyISAM tables are not recommended because they lack transaction support and row-level locking. The best practice is to convert MyISAM tables to InnoDB using ALTER TABLE ... ENGINE=InnoDB before or after migration.

Answer

Migrate as-is and change engine after migration using mysqldump with --skip-create-options.

Answer

Use Database Migration Service which automatically converts MyISAM to InnoDB during migration.

Answer

Keep MyISAM as is; Cloud SQL fully supports MyISAM in MySQL 8.0.

Question 4

A company is migrating a 1 TB MySQL database to Cloud SQL for MySQL 8.0. The source uses the utf8 charset and MyISAM tables. They need to minimize downtime. Which TWO actions should the team take?

Accepted Answer

Convert MyISAM tables to InnoDB.. MyISAM must be converted to InnoDB before migration. utf8 must be changed to utf8mb4 to support full Unicode. DMS continuous migration minimizes downtime.

Answer

Use Database Migration Service with a one-time migration job.

Answer

Migrate using mysqldump and mysql import.

Answer

Enable binary logging on the source MySQL database.

Question 5

A financial services company is migrating a Redshift data warehouse to BigQuery. They need to stage data in Amazon S3 before transferring. Which service should they use to automate the transfer?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service supports scheduled transfers from Amazon S3 to BigQuery, handling incremental loads. It is the recommended service for this scenario.

Answer

Cloud Data Fusion

Answer

Cloud Storage Transfer Service

Answer

Cloud Composer

Question 6

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 7

An organization is migrating from Amazon Redshift to BigQuery. They have large datasets stored in Amazon S3. What is the most efficient method to migrate the data?

Accepted Answer

Export Redshift data to S3, copy to Cloud Storage using Storage Transfer Service, then load into BigQuery.. The recommended approach is to export Redshift data to S3, then use BigQuery Data Transfer Service for Redshift to load data directly into BigQuery, or use Cloud Storage as intermediate. The most efficient is to use the BigQuery Data Transfer Service which can read from S3 directly (via a transfer config) or use Cloud Storage as a staging area.

Answer

Create a VPN between AWS and GCP and use federated query in BigQuery to read from Redshift directly.

Answer

Use Cloud Data Fusion to read from Redshift directly and write to BigQuery.

Answer

Use AWS Database Migration Service (DMS) to replicate data to Cloud SQL and then use BigQuery federated query.

Question 8

A company wants to migrate their data warehouse from Teradata to BigQuery. They need to automate the loading of data on a regular schedule. Which Google Cloud service should they use?

Accepted Answer

BigQuery Data Transfer Service. BigQuery Data Transfer Service supports scheduled transfers from Teradata (and other sources) to BigQuery.

Answer

Dataproc

Answer

Cloud Data Fusion

Answer

Cloud Composer

Question 9

A company is migrating a MySQL database to Cloud SQL for MySQL. They want to use Flyway for versioned schema migrations in CI/CD. Which TWO commands are part of a typical Flyway migration workflow?

Accepted Answer

flyway migrate. Flyway uses 'migrate' to apply pending migrations and 'validate' to verify the schema state. These are core commands in CI/CD pipelines.

Answer

flyway undo

Answer

flyway clean

Answer

flyway repair

Question 10

An engineer is planning a database migration and needs to assess the source database's size, schema complexity, stored procedures, and dependencies. Which activity is this part of?

Accepted Answer

Database assessment. Migration planning includes assessing the source database to understand its characteristics and plan the migration strategy.

Answer

Cutover planning

Answer

Schema conversion

Answer

Performance tuning

Question 11

An organization is migrating from Oracle to Cloud SQL for PostgreSQL using Database Migration Service. They need to convert Oracle NUMBER(10,2) and VARCHAR2(100) columns to appropriate PostgreSQL types. What are the correct mappings?

Accepted Answer

NUMBER(10,2) → NUMERIC(10,2); VARCHAR2(100) → VARCHAR(100). Oracle NUMBER maps to PostgreSQL NUMERIC (or DECIMAL), and VARCHAR2 maps to VARCHAR (or TEXT). The specified lengths are preserved.

Answer

NUMBER(10,2) → DECIMAL(10,2); VARCHAR2(100) → TEXT

Answer

NUMBER(10,2) → INTEGER; VARCHAR2(100) → TEXT

Answer

NUMBER(10,2) → BIGINT; VARCHAR2(100) → CHAR(100)

Question 12

During a migration from Teradata to BigQuery, the team needs to convert Teradata BTEQ scripts to BigQuery SQL. Which of the following is a key difference between Teradata SQL and BigQuery SQL that the team must account for?

Accepted Answer

Teradata uses CURRENT_DATE; BigQuery uses CURRENT_DATE() with parentheses. Teradata uses a different syntax for temporal queries (e.g., CURRENT_DATE) and does not use backticks for identifiers. BigQuery SQL is based on GoogleSQL, which uses backticks for escaping reserved keywords and has different date functions.

Answer

Teradata uses FROM clause first; BigQuery uses SELECT first

Answer

Teradata uses backticks for identifiers; BigQuery uses double quotes

Answer

Teradata uses CAST for data type conversion; BigQuery uses CONVERT

Question 13

A company is migrating from Redshift to BigQuery. They have large datasets in Amazon S3. What is the recommended approach to transfer this data into BigQuery?

Accepted Answer

Use BigQuery Data Transfer Service for Redshift.. The typical approach is to use BigQuery Data Transfer Service for Redshift (which can unload to S3 and then load into BigQuery), or manually export from Redshift to S3, transfer to GCS, and load into BigQuery. But the question specifies S3 as intermediate; using Transfer Service for Redshift is the managed option.

Answer

Use Cloud Dataflow to read from S3 and write to BigQuery.

Answer

Copy data from S3 to GCS using Storage Transfer Service, then load into BigQuery.

Answer

Use Database Migration Service with Redshift endpoint.

Question 14

A company is performing a lift-and-shift migration of an on-premises Oracle database to Cloud SQL for PostgreSQL using DMS with continuous migration. Which THREE prerequisites must be met before starting the migration job?

Accepted Answer

Create a source connection profile in DMS.. DMS continuous migration requires archivelog mode for CDC, appropriate network connectivity (VPC peering or proxy), and a connection profile for the source.

Answer

Install Cloud SQL Auth Proxy on the source server.

Answer

Convert all NUMBER columns to NUMERIC manually.

Question 15

A Cloud Bigtable instance is experiencing high read latency on a table storing time-series data. The row key is a concatenation of metric_type, timestamp, and device_id. Most queries filter by metric_type and a time range. Which row key redesign would MOST improve read performance?

Accepted Answer

Promote metric_type to the first part of the row key. Field promotion moves the most frequently filtered column to the start of the row key. Since queries filter by metric_type and time range, placing metric_type first allows Bigtable to efficiently scan a contiguous range of rows.

Answer

Use a salted key by prepending a hash of the metric_type

Answer

Use a separate column family for the timestamp

Answer

Reverse the timestamp portion of the key to distribute writes