PCDE Practice Test 2 — 25 Questions

Question 1

An engineer needs to connect Database Migration Service to a source MySQL database that has only a private IP address. The Cloud SQL destination is also private. What is the recommended method for DMS to connect to the source?

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

Accepted Answer

Use VPC peering or Cloud SQL Auth Proxy. For private IP sources, VPC peering or Cloud SQL Auth Proxy can be used. Auth Proxy is recommended for secure connectivity without public IP exposure.

Answer

Use a Cloud VPN tunnel with static IP

Answer

Use Cloud NAT to provide outbound connectivity

Answer

Use the source database's public IP with IP allowlisting

Question 2

A team needs to migrate a large Teradata data warehouse to BigQuery. They want to automatically convert Teradata DDL and BTEQ scripts to BigQuery-compatible SQL. Which Google Cloud service should they use?

Accepted Answer

Schema Conversion Tool (SCTS). Schema Conversion Tool (SCTS) is designed for heterogeneous migrations, converting DDL and scripts from sources like Teradata to BigQuery.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

Cloud Dataflow

Question 3

An organization needs to design a disaster recovery plan for Cloud SQL for PostgreSQL with an RPO of 10 seconds and an RTO of 5 minutes. Which TWO solutions meet these requirements? (Choose 2)

Accepted Answer

Cloud SQL cross-region read replica with manual promotion.. Cloud SQL HA provides near-zero RPO and RTO under 60 seconds within the same region. Cross-region read replicas can achieve RPO of seconds if replication lag is low, and manual promotion takes minutes.

Answer

Export the database to Cloud Storage and import in another region.

Answer

Cloud SQL point-in-time recovery from backups.

Answer

Use Cloud SQL automated backups with cross-region copy.

Question 4

A company is migrating their on-premises MySQL database to Cloud SQL. The database is 500 GB and they have a 1 Gbps network connection. They want to minimize downtime. Which THREE steps should they take?

Accepted Answer

Perform a test migration to validate the process.. Performing a test migration validates the entire process, including schema compatibility, data integrity, and application connectivity, before the actual cutover. This reduces the risk of unexpected failures during the production migration, which is critical for minimizing downtime. A test migration also helps estimate the time required for the final cutover and allows for tuning of the Database Migration Service settings.

Answer

Export the database to a SQL file and import after the cutover.

Answer

Schedule a maintenance window for the cutover.

Question 5

What is the maximum backup retention period for Cloud Spanner backups?

Accepted Answer

365 days. Cloud Spanner allows backups to be retained for up to 365 days.

Answer

730 days

Answer

90 days

Answer

30 days

Question 6

A Cloud SQL for PostgreSQL instance needs to achieve a recovery point objective (RPO) of less than 1 minute and a recovery time objective (RTO) of less than 2 minutes in the event of a zone failure. Which disaster recovery strategy should be used?

Accepted Answer

Configure Cloud SQL HA (high availability) with a standby in a different zone.. Cloud SQL HA configuration provides automatic failover to a standby in a different zone within the same region, with RPO near zero and RTO typically under 60 seconds. Cross-region read replicas have RPO equal to replication lag (could be minutes) and RTO of minutes due to manual promotion. Cross-region backup restore has RPO equal to backup age (hours) and RTO of hours. Point-in-time recovery (PITR) is for within-region data recovery, not zone failure failover.

Answer

Perform regular backups to Cloud Storage and restore in a different zone.

Answer

Enable cross-region replication using a read replica.

Answer

Enable point-in-time recovery (PITR) with transaction log backups.

Question 7

A financial services company is designing a Cloud Spanner schema for a trading system. They have two main entities: 'accounts' and 'transactions'. Each account has many transactions, and queries almost always retrieve transactions for a specific account. Which TWO schema design strategies should they employ?

Accepted Answer

Ensure the primary key of transactions includes the account_id as the first part.. Cloud Spanner distributes rows across splits based on the primary key prefix. By making `account_id` the first part of the transactions table primary key, all transactions for a given account are co-located, enabling efficient range scans and point lookups without cross-node shuffling.

Answer

Use a secondary index on transactions.account_id.

Answer

Define a foreign key constraint from transactions to accounts.

Answer

Store transactions as a JSON array of repeating fields within the account record.

Question 8

A Cloud Spanner database is experiencing high CPU utilization on one node. Users report slow queries. The table uses a UUID primary key. What is the most effective action?

Accepted Answer

Use a hash key prefix to distribute writes.. A hash key prefix can distribute writes more evenly across nodes, preventing hot spots that cause high CPU on a single node. Adding nodes may not resolve a hot spot.

Answer

Add a database index on frequently queried columns.

Answer

Convert the UUID to a monotonically increasing integer.

Answer

Increase the number of nodes in the instance.

Question 9

A company uses Cloud Monitoring to create an SLO for a service. They want to define a request-based SLO with a ratio of good requests to valid requests. Which of the following is a valid way to define the SLI in Cloud Monitoring SLOs?

Accepted Answer

Select a metric for good requests and a separate metric for total valid requests, then define the SLI as good-request-count / valid-request-count. Cloud Monitoring SLOs require two metrics: one for good events and one for total valid events. The ratio is good/total. The correct configuration uses separate metrics for good and valid requests.

Answer

Use a distribution metric for latency and set a threshold for good latency

Answer

Create a custom metric for requests and use Cloud Monitoring's built-in availability SLI for HTTP services

Answer

Use a single metric that indicates success (1 for success, 0 for failure) and set a threshold filter

Question 10

The exhibit shows query metadata for a query that scans 10 GB. Given the table is 100 GB and partitioned by hire_date, why did the query scan 10 GB and not less?

Accepted Answer

The filter on hire_date is not selective enough to prune most partitions. Partition pruning in Google BigQuery depends on the selectivity of the filter predicate. If the filter on `hire_date` matches a large number of partitions (e.g., filtering on a range that covers 10 GB out of 100 GB), the query scans exactly those partitions. The table is 100 GB and partitioned by `hire_date`, so a 10 GB scan implies the filter pruned 90 GB of partitions but was not selective enough to reduce the scan further—e.g., the predicate may be a broad range or lack a precise equality condition.

Answer

Clustering on department is not being used because the query has ORDER BY

Answer

The query uses GROUP BY, which forces a full table scan

Answer

The table is not clustered properly

Question 11

A company runs Cloud SQL for PostgreSQL and wants to automatically increase storage when usage reaches a threshold. What should they enable?

Accepted Answer

Enable storage auto-increase in instance settings. Cloud SQL has a built-in 'auto-storage increase' feature that automatically adds storage when necessary, avoiding manual intervention.

Answer

Create a cron job to resize via gcloud

Answer

Configure automated backups

Answer

Use Active Assist recommendations to resize

Question 12

A team uses Kustomize to manage environment-specific configurations for their GKE deployments. They have a base overlay and overlays for dev, staging, and prod. Which command should they use to generate the final Kubernetes manifests for the staging environment?

Accepted Answer

kustomize build staging/. The `kustomize build staging/` command processes the Kustomize overlay located in the `staging/` directory, which includes a `kustomization.yaml` file that references the base overlay and applies environment-specific patches, configMapGenerator entries, and namespace settings. This generates the final Kubernetes manifests for the staging environment without applying them to a cluster. The `kustomize` CLI tool is the standard way to render Kustomize overlays, and it is the correct command for generating manifests as part of a CI/CD pipeline.

Answer

kubectl kustomize staging/

Answer

skaffold build --kustomize staging/

Answer

kubectl apply -k staging/

Question 13

A startup is using Firestore in Native mode for a real-time chat application. They want to design the schema for chat rooms and messages. Which TWO design patterns are recommended? (Choose two.)

Accepted Answer

Use a composite index on chat room ID and timestamp.. A composite index on chat room ID and timestamp is essential for efficiently querying messages in order within a specific chat room. Firestore requires composite indexes for queries that combine equality filters on one field (chat room ID) with an order on another (timestamp). Option E is correct because storing messages as documents in a subcollection under each chat room document is the recommended pattern for Firestore, as it allows scalable, independent message collections per room without hitting the 1 MiB document size limit.

Answer

Use arrays in the chat room document to store message IDs.

Answer

Store all messages in a single top-level collection with a field for chat room ID.

Answer

Use a separate top-level collection for each chat room.

Question 14

Your Cloud SQL for MySQL instance is experiencing intermittent performance degradation. You suspect that the issue is due to a sudden spike in connections from a specific application. Which metric and monitoring approach would best help you correlate the connection spike with performance degradation?

Accepted Answer

Monitor 'cloudsql.googleapis.com/database/mysql/threads/threads_connected' and correlate with CPU utilization and query latency.. The 'threads_connected' metric directly measures the number of active connections to the MySQL instance. Correlating this with CPU utilization and query latency allows you to pinpoint whether a sudden spike in connections is causing resource contention and degraded query performance, which is the exact scenario described.

Answer

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Answer

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Answer

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Question 15

You are designing disaster recovery for a globally distributed application using Cloud Spanner with a multi-region configuration. The application requires an RPO of 0 and an RTO of less than 5 seconds in the event of a full region failure. Which Spanner configuration provides these guarantees?

Accepted Answer

A multi-region Spanner instance with two read-write regions and one witness region.. A multi-region configuration with a leader region and at least two voting regions provides synchronous replication and automatic failover. Regional failover occurs within seconds, preserving zero data loss.

Answer

A single-region Spanner instance with a read replica in another region.

Answer

A multi-region Spanner instance with one read-write region and two read-only regions.

Answer

A dual-region Spanner instance using default leader placement.

Question 16

A multinational corporation uses BigQuery to combine sales data from multiple regions. Each region stores data in separate tables with identical schemas. The BI team needs to create a unified view for a dashboard that queries data by region and product. Which TWO strategies should the data engineer implement to optimize query performance and reduce costs?

Accepted Answer

Partition the table by date and cluster by region and product. Partitioning the table by date and clustering by region and product enables partition pruning and clustering block elimination, reducing data scanned and costs. Option B is correct because wildcard tables with a filter on _TABLE_SUFFIX allow BigQuery to query only the required region tables, minimizing data read and cost. The other options are not optimal: C (UNION ALL view) does not reduce data scanned unless underlying tables are partitioned/clustered; D (materialized views for each region) adds complexity and cost; E (single table with region column) may still scan all data if not partitioned/clustered properly, and does not leverage region-specific tables.

Answer

Create a view with UNION ALL of all region tables

Answer

Create materialized views for each region

Answer

Store all data in a single table with region as a column

Question 17

Refer to the exhibit. You receive an alert from this policy for a Cloud Spanner instance. Which action should you take first?

Accepted Answer

Add more nodes to the instance. The alert from the policy indicates that the Cloud Spanner instance is experiencing high CPU utilization, which is a sign of resource saturation. Adding more nodes increases the total compute and I/O capacity of the instance, directly alleviating the CPU bottleneck and improving throughput. This is the first and most immediate corrective action because Spanner's performance scales linearly with nodes, and other optimizations (like index or query tuning) are secondary to ensuring sufficient capacity.

Answer

Identify and remove unused indexes

Answer

Review the top queries by CPU usage in the Spanner console

Answer

Split large tables into smaller ones

Question 18

A data warehouse in BigQuery stores event logs with nested and repeated fields (e.g., page views within a session). Which schema type is optimal for storing this data?

Accepted Answer

Use ARRAY> columns for nested repeated data. Using ARRAY> columns allows storing nested repeated data natively in BigQuery, enabling efficient querying without joins. Option A (RECORD type columns) is a legacy approach not optimized for repeated data; arrays of structs are preferred. Option B (normalizing into separate tables) would require costly joins. Option D (storing as JSON strings) loses schema enforcement and query performance.

Answer

Use RECORD type columns for each nested level

Answer

Normalize into separate tables and join

Answer

Store as JSON strings and parse at query time

Question 19

A company is migrating a large Oracle database to Cloud Spanner. They need to define the schema for relational tables with foreign keys. Which THREE considerations are important when designing the Spanner schema? (Choose three.)

Accepted Answer

Use INTERLEAVE tables to model parent-child relationships.. Options B, D, and E are correct. Interleaved tables (B) model parent-child relationships and optimize joins and data locality. Secondary indexes (D) are essential for efficient queries on non-key columns. Cloud Spanner enforces foreign key constraints (E) to maintain referential integrity. Option A is incorrect because primary key columns cannot be NULL. Option C is incorrect because composite primary keys are commonly used in Spanner.

Answer

Use NULL values in primary key columns to allow optional fields.

Answer

Avoid using composite primary keys; use single-column keys instead.

Question 20

A team uses Terraform with a GCS backend for state. They want to use remote state from another project to read output values. What Terraform configuration element is used to retrieve outputs from a different state file?

Accepted Answer

`data.terraform_remote_state`. The `data.terraform_remote_state` data source is the correct Terraform configuration element used to retrieve output values from a different state file stored in a remote backend, such as GCS. It reads the state data from the specified backend configuration and exposes the outputs via the `outputs` attribute, allowing cross-project or cross-workspace state access without requiring direct module dependencies.

Answer

`module.terraform_remote_state`

Answer

`output.terraform_remote_state`

Answer

`resource.terraform_remote_state`