PCDE Practice Test 49 — 15 Questions

Question 1

You are managing a Cloud SQL for MySQL instance that is experiencing high latency and connection timeouts during peak hours. The current configuration uses 4 vCPUs, 15 GB memory, and 100 GB SSD storage. The database workload is a mix of transactional queries and batch inserts. Which TWO actions would most effectively reduce latency and improve performance?

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

Accepted Answer

Increase the instance to 8 vCPUs and 30 GB memory.. Increasing vCPUs and memory directly addresses the resource bottleneck causing high latency and connection timeouts during peak hours. Cloud SQL for MySQL performance is heavily dependent on CPU for query processing and memory for buffer pool caching; doubling these resources reduces query execution time and improves concurrency handling. Option E is correct because using Cloud SQL proxy with connection pooling reduces the overhead of establishing new connections, which is a common cause of latency and timeouts under high concurrency. Connection pooling reuses database connections, minimizing connection setup time and improving throughput. The other options are incorrect: A (disabling binary logging is not recommended for production as it compromises point-in-time recovery), B (increasing storage does not linearly improve IOPS beyond a baseline and does not address compute/memory bottleneck), and D (decreasing max_connections may cause more timeouts under peak load rather than reduce them).

Answer

Disable binary logging to reduce write I/O.

Answer

Increase the storage size to 200 GB to improve IOPS.

Answer

Decrease the max_connections parameter to reduce overhead.

Question 2

A company runs a critical application on Cloud SQL for PostgreSQL with a primary instance in us-central1 and a cross-region read replica in us-west1 for disaster recovery. The database engineer is responsible for ensuring that in the event of a regional outage in us-central1, the application can continue with minimal data loss and within 15 minutes of downtime. The application writes about 1000 transactions per second. The current setup has automated backups enabled with point-in-time recovery (7-day retention) and the cross-region replica is configured with asynchronous replication. Which action should the database engineer take to meet the recovery objectives?

Accepted Answer

Promote the cross-region read replica to a new primary and redirect application traffic.. Promoting the cross-region read replica (Option A) is the correct action because it allows the application to resume operations quickly (under 15 minutes) with minimal data loss. Asynchronous replication means some transactions may be lost, but this approach is faster than restoring from a backup, which would exceed the 15-minute RTO for a high-write application. Options B, C, and D are incorrect: synchronous replication across regions is not supported in Cloud SQL for PostgreSQL (B); restoring from backup takes longer than 15 minutes and results in more data loss (C); increasing backup frequency does not enable fast failover (D).

Answer

Change the cross-region replica to synchronous replication and enable automatic failover.

Answer

Create a new instance from the latest backup in us-west1 and redirect traffic.

Answer

Increase automated backup frequency to every hour and ensure binary logging is enabled.

Question 3

A company uses Cloud Spanner with a schema that has a table 'Orders' with primary key (CustomerId, OrderDate, OrderId). They notice hotspots on a specific customer. Which schema change would best distribute load?

Accepted Answer

Add a hash of CustomerId as a prefix to the primary key.. Hotspots occur due to concentrated traffic on a single key range, such as a specific customer's orders. Adding a hash of CustomerId as a prefix to the primary key (Option C) distributes writes across multiple splits, alleviating the hotspot. Option A (secondary index) improves read performance but does not affect write distribution. Option B (splitting by region) adds complexity without directly addressing the key-ordering issue. Option D (OrderId only) loses the natural ordering and may still cause hotspots on the most recent OrderId.

Answer

Use a secondary index on CustomerId.

Answer

Split the table into multiple tables per region.

Answer

Change primary key to OrderId only.

Question 4

Which two of the following are best practices when designing BigQuery schemas? (Choose two.)

Accepted Answer

Use denormalization to reduce the number of joins. Best practices in BigQuery schema design include denormalization (option B) to reduce joins and improve query performance, and using repeated fields (option D) to model parent-child relationships without expensive JOIN operations. Options A and C are incorrect: column-level security (option A) is a data governance feature, not a schema design best practice; using RECORD type (option C) is a way to model nested data but is not a standalone best practice—repeated fields are more appropriate for avoiding joins. Option E is incorrect because using a single table for all data leads to poor performance and maintenance issues; BigQuery supports multiple tables and logical data models.

Answer

Use column-level security to restrict access

Answer

Use the type RECORD for structured data

Answer

Use a single table for all data to simplify queries

Question 5

Which GCP database service automatically replicates data across multiple zones within a region and provides an SLA of 99.999% for multi-region configurations?

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, strongly consistent relational database that automatically replicates data across zones. It offers 99.999% SLA for multi-region instances. Cloud SQL HA replicates across zones but has a lower SLA. Bigtable replicates asynchronously across regions but does not guarantee strong consistency. Firestore offers multi-region replication but not 99.999% SLA.

Answer

Firestore

Answer

Cloud SQL

Answer

Cloud Bigtable

Question 6

A company wants to use the Vertical Pod Autoscaler (VPA) to automatically adjust resource requests for their pods. They want the VPA to update the resource requests of running pods. Which VPA updateMode should they use?

Accepted Answer

Auto. The VPA Auto mode automatically updates resource requests for running pods (by evicting and recreating them when necessary). Initial only assigns requests at pod creation and never updates running pods. Recreate mode evicts and recreates pods to apply recommendations, similar to Auto but more aggressive. Off only provides recommendations without applying changes. Since the requirement is to update running pods, Auto is the appropriate mode.

Answer

Initial

Answer

Recreate

Answer

Off

Question 7

An organization needs to define RTO and RPO for their database disaster recovery plan. They have a Cloud SQL for SQL Server instance with HA enabled. What are the typical RTO and RPO for an automatic HA failover?

Accepted Answer

RPO: near zero, RTO: under 60 seconds. Cloud SQL HA failover is automatic within the same region. It provides RPO near zero (minimal data loss) and RTO typically under 60 seconds.

Answer

RPO: replication lag (seconds), RTO: minutes

Answer

RPO: zero, RTO: zero

Answer

RPO: minutes, RTO: hours

Question 8

A company runs a critical application on Cloud SQL for PostgreSQL with a 5-minute RPO and 30-minute RTO. They have a cross-region read replica for disaster recovery. During a planned failover test, what is the expected RPO and RTO when promoting the read replica?

Accepted Answer

RPO = replication lag, RTO = minutes. Cross-region read replica promotion has an RPO equal to the replication lag (data not yet replicated) and an RTO of minutes (time to promote and make writable).

Answer

RPO = 0, RTO = less than 1 minute

Answer

RPO = 0, RTO = minutes

Answer

RPO = backup age, RTO = hours

Question 9

A company uses Cloud Spanner and needs to retain backups for 365 days for compliance. What is the maximum backup expiration period that can be set for a Spanner backup?

Accepted Answer

365 days. Spanner backups can have an expiration time set to a maximum of 365 days from the creation time. After that, the backup is automatically deleted.

Answer

30 days

Answer

Unlimited

Answer

90 days

Question 10

A company runs a critical application on AlloyDB in a single zone. They want to improve resiliency with an RTO under 30 seconds and RPO near zero. Which THREE steps should they take? (Choose 3)

Accepted Answer

Configure application connection retry logic to handle failover interruptions.. To achieve RTO<30s and RPO near zero, you need to enable AlloyDB HA (primary + standby in different zone), ensure the application retries connections during failover, and regularly test failover to validate RTO.

Answer

Increase the number of CPU cores in the primary instance.

Answer

Configure a cross-region read replica in a different region.

Question 11

Which THREE components are required to compute a 7-day moving average of daily sales using a window function? (Choose three.)

Accepted Answer

AVG() function. The AVG() function is the aggregate function that computes the arithmetic mean of the sales values over the specified window frame. In a moving average calculation, AVG() is applied to the rows defined by the window frame to produce the average for each row.

Answer

PARTITION BY product

Answer

WINDOW clause

Question 12

Which TWO metrics are most important to monitor for a Cloud SQL for PostgreSQL instance to detect performance degradation?

Accepted Answer

CPU utilization. CPU utilization (B) is a primary indicator of performance degradation because sustained high CPU usage (e.g., >80%) can lead to query queuing, reduced throughput, and increased latency. Disk IOPS (D) is equally critical because Cloud SQL for PostgreSQL relies on disk I/O for WAL writes, checkpointing, and query execution; hitting the IOPS limit of the underlying persistent disk (e.g., 3,000 IOPS for a pd-standard disk) causes throttling and severe performance drops.

Answer

Memory usage

Answer

Query latency

Answer

Network throughput (bytes sent/received)

Question 13

A company uses BigQuery for BI reporting. They have a table 'orders' with columns: order_id, customer_id, order_date, amount, status. The BI team frequently runs queries that filter on order_date and group by customer_id to compute total sales per customer. Which partitioning and clustering strategy optimizes query performance and cost?

Accepted Answer

Partition by order_date, cluster by customer_id. Partitioning by order_date allows BigQuery to prune partitions for queries filtering on order_date, reducing the amount of data scanned. Clustering by customer_id organizes data within each partition so that GROUP BY customer_id queries can efficiently read only relevant blocks, minimizing shuffle and cost. This combination directly aligns with the BI team's query pattern of filtering by date and aggregating by customer.

Answer

Partition by order_date, cluster by status

Answer

Do not partition, cluster by customer_id

Answer

Partition by customer_id, cluster by order_date

Question 14

An organization wants to enforce that no Compute Engine VM has an external IP address. Which approach should be used?

Accepted Answer

Use an organization policy with constraint `compute.vmExternalIpAccess`. The organization policy constraint `compute.vmExternalIpAccess` is specifically designed to prevent Compute Engine VMs from being assigned external IP addresses. This policy can be applied at the organization, folder, or project level to enforce that no VM in the scope can have an external IP, regardless of how the VM is created. It directly addresses the requirement without affecting other resources or relying on indirect controls.

Answer

Set IAM roles to deny `compute.instances.create` on all projects

Answer

Configure a firewall rule to block all traffic to 0.0.0.0/0

Answer

Use Shared VPC and only allow internal IPs

Question 15

A company is using Cloud Deploy to manage releases to GKE. They want to implement a deployment strategy where the new version is rolled out to a small subset of pods and traffic is gradually shifted based on prometheus metrics. Which deployment strategy should they configure in the delivery pipeline?

Accepted Answer

Canary strategy. B is correct because a canary strategy in Cloud Deploy allows you to gradually shift traffic to the new version based on Prometheus metrics, enabling fine-grained control and automated rollback if the metrics indicate degradation. This aligns with the requirement to roll out to a small subset of pods and shift traffic based on metrics.

Answer

Blue/green strategy

Answer

Rolling update strategy

Answer

Standard strategy