PCDE Practice Test 35 — 15 Questions

Question 1

A company uses BigQuery for BI analytics. They want to improve query performance for a table with 10 TB of data. Which two actions should they take? (Choose two.)

Accepted Answer

Limit the number of columns queried using SELECT * with EXCEPT.. Using SELECT * with EXCEPT limits the number of columns scanned, reducing I/O and improving query performance in BigQuery. BigQuery charges by the amount of data processed, so reading fewer columns directly lowers both cost and query execution time.

Answer

Use a wildcard table to combine multiple tables.

Answer

Partition by a column with a high granularity.

Answer

Use a clustered column as the partition key.

Question 2

A data analyst runs a query joining several large tables and gets 'Resources exceeded' error. They need to reduce memory usage without changing the query logic. What should they do?

Accepted Answer

Use a subquery to pre-aggregate the largest table before joining. Pre-aggregating the largest table in a subquery reduces the amount of data that needs to be shuffled and joined in memory. In BigQuery, this minimizes the bytes processed and the memory footprint of the join operation, directly addressing the 'Resources exceeded' error without altering the overall query logic.

Answer

Use APPROX_COUNT_DISTINCT for counting distinct values

Answer

Increase the slot reservation

Answer

Use SELECT * in the subquery to ensure all columns are available

Question 3

A DevOps engineer wants to automatically trigger a Cloud Build pipeline when a pull request is opened against the main branch. The pipeline should run unit tests and provide results as a status check on the PR. Which type of trigger should they configure?

Accepted Answer

Pull request trigger. A pull request trigger is the correct choice because it is specifically designed to initiate a Cloud Build pipeline in response to PR events, such as when a PR is opened or updated. This allows the pipeline to run unit tests and report results as a status check on the PR, providing immediate feedback to developers. The trigger can be configured to fire only for PRs targeting the main branch, ensuring the pipeline runs exactly when needed.

Answer

Scheduled trigger

Answer

Manual trigger

Answer

Push to branch trigger

Question 4

An organization needs to continuously replicate data from an on-premises PostgreSQL database to AlloyDB with minimal downtime during cutover. They have set up DMS continuous migration. During the cutover phase, what is the correct sequence of steps?

Accepted Answer

Quiesce writes to source, confirm DMS lag is 0, promote destination, update connection strings, verify application, keep source running read-only.. The standard cutover: quiesce writes, verify DMS lag is 0, promote (stop replication), update connection strings, verify app, and keep source for rollback.

Answer

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

Answer

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

Answer

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

Question 5

A company is migrating an on-premises PostgreSQL 13 database to AlloyDB for PostgreSQL using DMS continuous migration. The source is configured with logical replication using the pglogical extension. During the initial sync, the migration job fails with the error 'could not open relation with OID xxxx'. What is the most likely cause?

Accepted Answer

A DDL operation on the source dropped or altered a table after the replication slot was created.. DMS uses logical replication slots when possible. If the source has DDL changes (e.g., table dropped) after the slot is created, the replication slot may become invalid.

Answer

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

Answer

The source database does not have the pglogical extension installed.

Answer

The target AlloyDB cluster does not have the pglogical extension.

Question 6

A company is migrating an Oracle database to Cloud SQL for PostgreSQL. They have a table with a column defined as NUMBER(10,2). To maintain data integrity, what should be the corresponding PostgreSQL data type?

Accepted Answer

NUMERIC(10,2). NUMBER(10,2) represents a decimal number with 10 digits total and 2 after decimal; NUMERIC(10,2) is the equivalent in PostgreSQL.

Answer

FLOAT

Answer

INTEGER

Answer

DECIMAL(10,0)

Question 7

A company is migrating an Oracle database to Cloud SQL for PostgreSQL. They need to convert data types. Which TWO data type mappings are correct?

Accepted Answer

NUMBER(10,2) → NUMERIC(10,2). Oracle NUMBER(10,2) maps to NUMERIC(10,2) in Cloud SQL for PostgreSQL because both store exact numeric values with precision and scale. Oracle NUMBER(10) maps to INTEGER because it stores whole numbers without scale. The other options are incorrect: CLOB maps to TEXT, BLOB maps to BYTEA, and Oracle DATE maps to TIMESTAMP (since Oracle DATE includes time).

Answer

CLOB → VARCHAR

Answer

DATE → DATE

Question 8

During cutover of a Database Migration Service continuous migration job, the engineer observes that the source database still receives writes after promoting the destination Cloud SQL instance. What should the engineer do to complete the migration?

Accepted Answer

Stop the application that writes to the source, then confirm DMS lag is zero before promoting.. Cutover requires quiescing writes to the source to ensure no new changes are generated. The application should be stopped or switched to read-only first.

Answer

Adjust the DMS job to ignore further changes.

Answer

Promote the destination again; this will force a stop of writes.

Answer

Delete the source database to force the cutover.

Question 9

A migration from Oracle to PostgreSQL using Ora2Pg is complete. The team wants to run unit tests on the converted stored procedures to ensure they produce correct results. Which tool is most suitable for this purpose?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows writing tests for functions, procedures, and other database objects. It is commonly used to validate converted PL/pgSQL code.

Answer

pgAdmin

Answer

pg_dump

Answer

pg_stat_statements

Question 10

A company is planning a migration from an on-premises SQL Server database to Cloud SQL for PostgreSQL. They need to convert both schema and data. Which two Google Cloud services should they consider? (Choose 2)

Accepted Answer

Schema Conversion Tool (SCT). Database Migration Service (DMS) handles data migration and can assist with schema conversion for homogeneous migrations. However, for heterogeneous migrations like SQL Server to PostgreSQL, Schema Conversion Tool (SCT) is needed for schema conversion. Cloud SQL is the target. BigQuery and Cloud Spanner are not applicable here.

Answer

Cloud SQL

Answer

BigQuery

Answer

Cloud Spanner

Question 11

An engineer is migrating an Oracle database to Cloud SQL for PostgreSQL. They use Ora2Pg to convert the schema. One source table uses a column with data type NUMBER(10,2). What is the appropriate PostgreSQL data type after conversion, and which tool should the engineer use to verify the correctness of converted stored procedures?

Accepted Answer

Use NUMERIC(10,2) and pgTAP to write unit tests for stored procedures.. NUMBER(10,2) maps to NUMERIC(10,2) as it is a fixed-point number. Stored procedures converted from PL/SQL to PL/pgSQL should be tested with unit tests, and pgTAP is a popular PostgreSQL testing framework for this purpose. PL/pgSQL is the procedural language, not a testing tool. Ora2Pg handles conversion but does not provide unit tests.

Answer

Use VARCHAR2 and test with pgTAP.

Answer

Use FLOAT and rely on Ora2Pg's built-in validation.

Answer

Use INTEGER for the data type and PL/pgSQL for testing stored procedures.

Question 12

Your Bigtable cluster is showing high read latency for row key lookups. The application accesses rows with keys in the format 'user_id#timestamp'. You notice that most reads are for recent timestamps. Which optimization should you implement?

Accepted Answer

Reverse the row key order to start with the timestamp. High read latency for recent timestamps occurs because Bigtable stores rows in lexicographic order by row key. With the format 'user_id#timestamp', older timestamps appear first, causing recent data to be scattered across tablets and requiring more seeks. Reversing the row key to start with the timestamp (e.g., 'timestamp#user_id') groups recent data together in contiguous tablets, enabling faster point lookups and reducing latency.

Answer

Increase the number of nodes in the cluster

Answer

Configure single cluster routing to reduce cross-cluster latency

Answer

Use a scan with a prefix filter instead of point reads

Question 13

A company uses Cloud Firestore in Datastore mode for a multi-tenant SaaS application. Each tenant has a separate namespace. The application has grown rapidly, and the database engineer notices that write throughput is degrading. Monitoring shows that the number of writes per second is high but within Firestore limits. However, the latency for writes is increasing linearly with the number of tenants. The engineer suspects that index management is causing the problem. The current schema uses automatic indexes for all properties. What is the best corrective action?

Accepted Answer

Disable automatic indexing and create custom composite indexes only for queries that require them.. Automatic indexing in Firestore in Datastore mode creates an index for every property, which causes write amplification as each write must update all relevant indexes. With many tenants in separate namespaces, the number of indexes grows linearly with tenants, increasing write latency. Disabling automatic indexing and creating custom composite indexes only for needed queries reduces the index write overhead, restoring write throughput.

Answer

Implement a data retention policy to delete old tenant data.

Answer

Request a throughput increase from Google Cloud support.

Answer

Shard tenants into separate Firestore databases to distribute the write load.

Question 14

A company wants to enforce that no Compute Engine instances are created with external IP addresses unless explicitly allowed. Which organization policy constraint should be used?

Accepted Answer

constraints/compute.vmExternalIpAccess. The 'constraints/compute.vmExternalIpAccess' policy restricts external IP usage on VMs. It can be set at the organization, folder, or project level.

Answer

constraints/compute.disableSerialPortAccess

Answer

constraints/compute.setCommonInstanceMetadata

Answer

constraints/compute.requireOsLogin

Question 15

A company is migrating an Oracle database to Cloud SQL for PostgreSQL using Ora2Pg for schema conversion. After conversion, they want to test the correctness of converted stored procedures. Which tool should they use?

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows writing tests for functions, procedures, and other database objects.

Answer

Database Migration Service validation

Answer

Ora2Pg --test flag

Answer

Cloud SQL Insights