PCDE Practice Test 6 — 10 Questions

Question 1

An organization is migrating an Oracle database to PostgreSQL on Compute Engine. They used Ora2Pg to migrate schema and data. After migration, they want to validate that stored procedures produce correct results. Which tool should they use for unit testing the migrated PL/pgSQL code?

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

Accepted Answer

pgTAP. pgTAP is a unit testing framework for PostgreSQL that allows developers to write tests for stored procedures, functions, and other database objects in PL/pgSQL. It is specifically designed for testing PostgreSQL code, making it the correct tool for validating migrated PL/pgSQL code.

Answer

gcloud sql export

Answer

pg_dump

Answer

pglogical

Question 2

An engineer is migrating a MySQL database to Cloud SQL using DMS. The source database uses MyISAM tables. During the migration, the full dump phase fails with a timeout. What should the engineer do to increase the likelihood of success?

Accepted Answer

Convert the MyISAM tables to InnoDB before migration.. MyISAM tables require table-level locking during dump. Using --single-transaction is only for InnoDB. To reduce lock contention, the engineer should schedule the migration during low-traffic periods or convert MyISAM to InnoDB before migration.

Answer

Add the --single-transaction flag to the mysqldump command.

Answer

Increase the DMS migration job timeout.

Answer

Use --skip-lock-tables to avoid locking.

Question 3

A company uses AlloyDB for PostgreSQL for their operational database. They want to ensure automatic failover with the shortest possible RTO. Which configuration should they use?

Accepted Answer

Deploy a multi-zone AlloyDB cluster with automatic failover enabled. AlloyDB provides automatic failover with RTO under 60 seconds. Cross-zone failover in a multi-zone cluster provides the best availability within a region. Cross-region replication is not automatic and requires manual promotion. Basic instances offer no HA. Read pool failover is not automatic.

Answer

Deploy a single-zone AlloyDB cluster with a read pool

Answer

Configure a cross-region read replica and set up automated promotion scripts

Answer

Use an AlloyDB basic (single-node) instance

Question 4

You administer a Cloud SQL for PostgreSQL instance running with 4 vCPUs and 15 GB of memory. The application frequently runs complex read-only reporting queries during business hours. Recently, the database started throwing 'out of memory' errors, and the instance's memory usage is consistently above 90%. You have enabled the pgtune recommendations and applied them. The workload is read-heavy and does not require a high write throughput. Which change would most effectively reduce memory pressure while maintaining query performance?

Accepted Answer

Scale up the instance to a higher memory tier, such as 8 vCPUs and 30 GB of memory.. Scaling up to a higher memory tier directly addresses the root cause: the instance has insufficient RAM for the read-heavy reporting workload, as evidenced by memory usage consistently above 90% and OOM errors even after pgtune tuning. With 30 GB, PostgreSQL can allocate larger shared_buffers and work_mem, allowing complex queries to complete without spilling to disk or exhausting memory. This preserves query performance because the additional memory is used for caching and sort/hash operations, which are critical for read-only reporting queries.

Answer

Enable the pgzstd extension to compress data in memory.

Answer

Set up connection pooling using PgBouncer to limit the number of concurrent connections.

Answer

Decrease the shared_buffers configuration parameter to free memory for other uses.

Question 5

A company needs to control cost by setting a budget alert on their billing account. They want to be notified when spending exceeds 80% of the budget. What should they configure?

Accepted Answer

Create a budget in the GCP Billing console with alert threshold at 80%.. Google Cloud's native budget alerts in the Billing console allow you to set a threshold (e.g., 80%) and automatically send email notifications when actual spending exceeds that percentage of the budget. This is the simplest, most direct, and recommended approach for cost control without needing additional services or custom code.

Answer

Export billing data to BigQuery and set up a scheduled query with a Cloud Function to send alerts.

Answer

Create a budget alert rule in Cloud Monitoring with a metric threshold.

Answer

Use Cloud Scheduler to run a script that checks billing API and sends email.

Question 6

A team uses Terraform with remote state stored in a GCS bucket. They are implementing policy as code using Conftest to validate Terraform plans before apply. The Conftest checks run in a CI/CD pipeline. Which approach ensures that Conftest policies are enforced consistently across all Terraform workspaces?

Accepted Answer

Create a separate Git repository for Conftest policies. In the CI/CD pipeline, clone this repo and run conftest test against the Terraform plan.. Centralizing Conftest policies in a dedicated Git repository and cloning them in the CI/CD pipeline ensures every workspace runs the same policy set at plan time. This decouples policy versioning from workspace code, so updates propagate uniformly and cannot be skipped by individual teams. It also gives a single audit point for policy changes.

Answer

Use Terraform Cloud's Sentinel policies instead of Conftest, as they are more integrated.

Answer

Embed Conftest policies directly into the Terraform modules using JSON files.

Answer

Store Conftest policies in each Terraform workspace's directory and reference them in the pipeline configuration.

Question 7

A team uses Cloud Deploy to manage deployments to GKE across dev, staging, and prod. They want to implement a canary deployment strategy that automatically progresses based on latency metrics and includes a manual approval step before the full rollout. Which three Cloud Deploy features should they use together?

Accepted Answer

Canary deployment strategy. Option B (Canary deployment strategy) is correct because Cloud Deploy's canary strategy lets you define a progressive rollout with percentage-based phases (e.g., 25%, 50%, 75%) so traffic to the new revision increases incrementally rather than all at once. Option C (SLO-based verification) is correct because Cloud Deploy supports deployment verification using SLOs defined in Cloud Monitoring, allowing the pipeline to automatically evaluate latency (and other) metrics after each canary phase and advance or halt based on those results. Option E (Manual approval gate) is correct because Cloud Deploy supports an approval step in the pipeline that pauses the rollout and requires a human to approve before the final full-rollout phase proceeds. Option A (Blue/green strategy) is not correct because blue/green switches all traffic at once between two environments and does not provide the incremental canary progression the team wants. Option D (Automated rollback) is not correct because, while Cloud Deploy can roll back a failed rollout, it is not the feature that drives metric-based progression or the pre-full-rollout approval; the question asks for the features used together to implement the canary-with-verification-and-approval workflow.

Answer

Blue/green strategy

Answer

Automated rollback

Question 8

A Cloud Bigtable instance has a hot spot causing performance degradation. Which tool helps identify the hot spot?

Accepted Answer

Key Visualizer. Key Visualizer is a Bigtable-specific tool that produces a heatmap of row key access patterns over time, making hot spots immediately visible as bright bands. It is designed precisely for diagnosing row key distribution and access skew. Cloud Monitoring, Cloud Logging, and the Admin API do not provide this row-key-level visualization.

Answer

Cloud Monitoring dashboard

Answer

Cloud Logging

Answer

Bigtable Admin API

Question 9

A marketing team needs to analyze customer behavior using BigQuery. They want to create a table that stores the first and last purchase date for each customer from the `orders` table. Which SQL approach should they use?

Accepted Answer

SELECT customer_id, MIN(order_date) AS first_purchase, MAX(order_date) AS last_purchase FROM orders GROUP BY customer_id. It uses aggregate functions MIN() and MAX() with GROUP BY customer_id to directly compute the first and last purchase dates from the orders table. This is the most efficient and idiomatic SQL approach in BigQuery, leveraging the database engine's built-in aggregation to avoid self-joins or subqueries.

Answer

SELECT customer_id, (SELECT order_date FROM orders ORDER BY order_date LIMIT 1) AS first_purchase, ...

Answer

SELECT o1.customer_id, o1.order_date AS first_purchase, o2.order_date AS last_purchase FROM orders o1 JOIN orders o2 ON o1.customer_id = o2.customer_id

Answer

SELECT customer_id, order_date AS first_purchase, ... FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn) WHERE rn = 1

Question 10

You are monitoring a Cloud Spanner instance and see that the average commit latency is high. The application performs many single-row inserts. Which metric would you check first to understand the root cause?

Accepted Answer

Lock conflicts (e.g., Spanner/API/Lock_wait).. High commit latency for single-row inserts in Cloud Spanner is often caused by lock conflicts, as concurrent transactions may contend for the same row or index. The metric Spanner/API/Lock_wait directly measures time spent waiting for locks, making it the first metric to check. High commit latency without high lock wait suggests other issues, but lock conflicts are the most common root cause for write-heavy single-row workloads.

Answer

Read latency.

Answer

Storage utilization.

Answer

CPU utilization per node.