PCDE Practice Test 33 — 15 Questions

Question 1

A team is migrating a 5 TB MySQL database to Cloud SQL using DMS. The full dump phase is taking longer than expected. They suspect network bandwidth is the bottleneck. Which action can they take to improve the dump speed within DMS?

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

Accepted Answer

Enable parallel dump in the DMS migration job settings.. DMS uses a single-threaded dump by default. Enabling parallel dump can improve speed for large databases.

Answer

Use a larger machine type for the source connection profile.

Answer

Increase the Cloud SQL instance storage size.

Answer

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

Question 2

A company uses Cloud Spanner and needs to back up a large database (several TB) for compliance reasons. They want to retain the backup for 400 days. What is the optimal approach to meet this requirement?

Accepted Answer

Export the database to Cloud Storage using Dataflow or an export job, which can be retained indefinitely. Cloud Spanner allows creating backups that can be retained for up to 365 days. For a retention of 400 days, you cannot use a single backup. Instead, you can create a backup and then take another backup before the first expires, or export data to Cloud Storage (e.g., using Dataflow) which has no expiration limit. Using the console, you are limited to 365 days; to exceed that, you must use long-term retention outside of Spanner backups.

Answer

Create a backup with 365-day retention, and before it expires, create another backup to extend coverage

Answer

Create a backup and set the expiration time to 400 days using the gcloud command

Answer

Use continuous PITR to retain transaction logs for 400 days

Question 3

A global e-commerce platform uses Cloud Spanner in multi-region configuration. The application writes a significant portion of traffic from Europe and requires the lowest read latency in that region. Which configuration step should be taken to minimise read latency in Europe while maintaining write availability?

Accepted Answer

Set the leader region to Europe (e.g., eur3).. In Spanner multi-region, the leader region determines where writes are committed. Setting the leader region to Europe ensures that writes are committed there, which reduces write latency for European traffic. Read-only replicas in other regions can serve reads with low latency without affecting write availability. Adding more read replicas in Europe would not reduce latency if the leader region is elsewhere. The number of read-write replicas does not directly reduce read latency. Splitting the database would not help for a single database.

Answer

Configure a separate Spanner instance for European traffic and split the database.

Answer

Add more read-write replicas in the European region.

Answer

Use a regional Spanner instance in Europe coupled with a copy of the data in Bigtable.

Question 4

A financial services company uses Cloud Bigtable for real-time fraud detection. They have a cluster with 10 nodes using HDD storage and are experiencing high latency due to disk throughput bottlenecks. They need to improve performance with minimal downtime. What should they do?

Accepted Answer

Create a new cluster with SSD storage, replicate data, then update the application to point to the new cluster. Cloud Bigtable does not support in-place conversion of storage from HDD to SSD. The only way to migrate to SSD storage is to create a new cluster with SSD, replicate data using Bigtable replication, and then update the application connection string to point to the new cluster. This approach minimizes downtime by allowing the old cluster to serve reads during replication.

Answer

Modify the existing cluster's storage type to SSD via the Cloud Console

Answer

Use Cloud Bigtable's hot tablet detection to rebalance the data

Answer

Increase the number of nodes in the existing cluster

Question 5

A company is using Cloud Spanner and wants to perform disaster recovery testing. They need to validate that their backup and restore process works without impacting production. Which TWO actions should they take? (Choose 2 correct answers.)

Accepted Answer

Restore a backup to a new database in the same instance using a different database ID. Restoring a backup to a new instance in a separate project is non-destructive and tests the restore process. Using a different database ID in the same instance also tests restore without overriding production. Option C is destructive (deletes production). Option D is not about testing. Option E is not a built-in feature for Spanner.

Answer

Export the database to Cloud Storage and import it into a new instance

Answer

Perform a failover to a read replica and then fail back

Answer

Directly restore a backup to the production database to test recovery time

Question 6

A team is migrating a MySQL database to Cloud SQL using mysqldump for the initial snapshot. The source database uses InnoDB tables. Which mysqldump flags should be used to ensure a consistent snapshot without locking tables?

Accepted Answer

--single-transaction --skip-lock-tables. --single-transaction uses a read transaction to get a consistent snapshot without table locks for InnoDB.

Answer

--all-databases --routines

Answer

--lock-tables --single-transaction

Answer

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

Question 7

A data engineer is migrating a large Teradata data warehouse to BigQuery using the Schema Conversion Tool (SCTS). They need to convert BTEQ scripts and Teradata DDL to BigQuery-compatible SQL. After conversion, several date functions are not working correctly. What is the most likely reason?

Accepted Answer

The SCTS tool did not fully convert the Teradata-specific date functions to BigQuery equivalents.. SCTS converts syntax but may not perfectly translate all functions; manual review is often needed for specific date functions.

Answer

The BigQuery Data Transfer Service is required for date conversion.

Answer

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

Answer

BigQuery does not support date arithmetic.

Question 8

A startup is building a real-time analytics dashboard that ingests 500,000 events per second and needs to query the last hour of data with sub-second latency. The data has a high write volume and the query pattern is time-range scans. Which Google Cloud database is most appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is the correct choice because it is a fully managed, scalable NoSQL database designed for high-throughput writes and low-latency time-series data access. It supports sub-second latency on time-range scans by storing data in sorted order by row key, making it ideal for ingesting 500,000 events per second and querying the last hour of data.

Answer

Cloud Spanner

Answer

Cloud SQL (PostgreSQL)

Answer

BigQuery

Question 9

Match each Cloud Spanner replication type to its purpose.

Accepted Answer

Read-write replica: Can serve reads and writes and participates in voting.. Cloud Spanner supports read-write, read-only, and witness replicas. Read-write replicas handle all operations and vote; read-only replicas scale reads without voting; witness replicas provide quorum without storing data. Common confusions include swapping the roles of read-only and witness replicas.

Answer

Read-write replica: Serves reads only and does not vote.

Answer

Read-only replica: Does not store data but participates in voting.

Question 10

An organization is migrating an Oracle database to Cloud SQL for PostgreSQL using Ora2Pg. During testing, a stored procedure that uses Oracle's SYSDATE produces incorrect results. What is the correct approach to handle this conversion?

Accepted Answer

Replace SYSDATE with CURRENT_TIMESTAMP.. Oracle's SYSDATE returns date and time; PostgreSQL equivalent is CURRENT_TIMESTAMP (or NOW()). Simply mapping to CURRENT_DATE would lose time component, breaking logic.

Answer

Replace SYSDATE with LOCALTIMESTAMP.

Answer

Use TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') to convert the value.

Answer

Replace SYSDATE with CURRENT_DATE in the converted procedure.

Question 11

A Cloud SQL for PostgreSQL instance has a read replica lagging behind the primary. The team needs to monitor the replica lag and set up an alert if it exceeds 60 seconds. Which metric should they use?

Accepted Answer

replication_lag. The correct metric is `replication_lag` because it directly measures the delay in seconds between the primary and read replica in Cloud SQL for PostgreSQL. This metric reflects how far behind the replica is in applying changes from the primary's write-ahead log (WAL), making it the precise indicator for alerting when lag exceeds 60 seconds.

Answer

bytes_received

Answer

disk_read_ops

Answer

cpu_utilization

Question 12

You are designing a Spanner schema for a financial application that stores transactions for user accounts. To avoid hotspots and optimize performance, which TWO practices should you follow?

Accepted Answer

Use the STORING clause in secondary indexes to include frequently accessed columns.. The STORING clause in a Spanner secondary index allows you to include non-key columns directly in the index, enabling index-only scans that avoid a back-join to the base table. This reduces read latency and resource consumption, which is critical for high-throughput financial transaction queries.

Answer

Store all data in a single table without interleaving.

Answer

Use a monotonically increasing integer as the primary key.

Answer

Create secondary indexes without the STORING clause.

Question 13

A company is setting up access control for a BigQuery dataset using the above IAM policy. An analyst who is a member of the group 'analysts@example.com' also has the user account 'analyst@example.com'. They need to create new tables in the dataset. What will be the outcome?

Accepted Answer

The analyst can create tables because they have dataOwner role on their user account.. IAM policies grant permissions based on the union of all roles assigned to the user, regardless of whether they come from group membership or direct user assignment. The analyst has the `dataOwner` role directly on their user account, which includes the `bigquery.tables.create` permission required to create new tables. Group membership with a lower-privilege role (e.g., `dataViewer`) does not override or conflict with the higher-privilege role on the user account.

Answer

The analyst will get an error because of conflicting roles.

Answer

The analyst cannot create tables because the group only has dataViewer.

Answer

The analyst can create tables if they also have jobUser role.

Question 14

A data engineer needs to grant a service account read-only access to a Cloud Storage bucket containing sensitive data. The service account is used by a Compute Engine instance. What is the most secure way to assign the permissions?

Accepted Answer

Grant the service account the Storage Object Viewer role on the specific bucket.. Granting the Storage Object Viewer role at the bucket level applies the principle of least privilege, restricting the service account's read-only access to only that specific bucket. This avoids granting broader permissions at the project level, which would inadvertently allow access to all buckets in the project. Using IAM roles is more secure and manageable than legacy bucket ACLs.

Answer

Set bucket ACLs to allow read access for the service account.

Answer

Make the bucket public and rely on network restrictions.

Answer

Grant the service account the Storage Object Viewer role at the project level.

Question 15

A company runs a Cloud Spanner database with a multi-region configuration. They notice that write latency is higher than expected for clients in a region far from the leader region. What action should be taken to reduce write latency?

Accepted Answer

Change the default leader option to 'NEAREST'. Changing the default leader option to 'NEAREST' allows Cloud Spanner to dynamically assign the leader replica to the region closest to the majority of write requests, reducing the network round-trip time for clients far from the original leader region. This directly addresses the high write latency caused by geographic distance, as writes must be confirmed by the leader before committing.

Answer

Reduce the number of replicas

Answer

Use directed reads

Answer

Enable follower reads for writes