PCDE Practice Test 4 — 10 Questions

Question 1

A company runs a Cloud SQL PostgreSQL instance for a SaaS application. They notice that the database CPU is consistently above 90% during peak hours, and queries slow down. The application is read-heavy and can tolerate some replication lag. Which action would MOST effectively reduce CPU load on the primary?

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

Accepted Answer

Create read replicas and route read-only queries to them. Creating one or more read replicas and offloading SELECT queries to them reduces CPU load on the primary instance. Adding more vCPU to the primary (scaling up) increases capacity but is more expensive and may not be as cost-effective as read replicas. PgBouncer helps with connection overhead but not CPU load from queries. Query optimization helps but may not reduce load enough if the volume is high.

Answer

Use EXPLAIN ANALYZE to optimize the slowest queries

Answer

Increase the number of vCPUs on the primary instance

Answer

Enable connection pooling with PgBouncer via Cloud SQL Auth Proxy

Question 2

You need to set up disaster recovery for a Cloud SQL for PostgreSQL instance. The primary instance is in us-central1, and you want a standby in us-west1 that can be promoted to a standalone instance during a regional outage. The solution must minimize data loss and recovery time. Which approach should you take?

Accepted Answer

Create a cross-region read replica in us-west1. During a disaster, promote the replica to a standalone instance.. Creating a cross-region read replica in us-west1 and promoting it during a disaster is the correct approach for Cloud SQL for PostgreSQL. This provides a live standby with asynchronous replication, minimizing data loss (low RPO) and recovery time (low RTO) because promotion is quick and the replica is continuously updated.

Answer

Configure cross-region automated backups with a retention of 7 days. In the event of a disaster, restore the latest backup to a new instance in us-west1.

Answer

Set up an on-premise PostgreSQL instance and configure streaming replication from Cloud SQL to the on-premise instance.

Answer

Use gcloud sql instances create with a backup configuration pointing to us-west1, but this does not create a live standby.

Question 3

A company is designing a data model for a BI dashboard that requires real-time updates and historical analysis. Which THREE practices should be followed?

Accepted Answer

Use clustering on frequently filtered columns.. Clustering on frequently filtered columns in BigQuery organizes data into blocks based on the values of those columns, allowing queries with filters on those columns to skip irrelevant blocks entirely. This reduces the amount of data scanned, improving query performance and lowering costs, which is critical for a BI dashboard that needs real-time updates and fast historical analysis.

Answer

Create a separate table for each day's data.

Answer

Use the default BigQuery table expiration setting.

Question 4

A data engineer is designing a BI solution in BigQuery for a retail chain. They need to support queries that aggregate sales by store, product, and date across millions of transactions. The data is loaded in near real-time from Cloud Pub/Sub. Which table design provides the best balance of query performance and cost?

Accepted Answer

Partition by date, cluster by store_id and product_id. Partitioning by date enables BigQuery to prune entire partitions when querying by date range, which is the most common filter in sales aggregation queries. Clustering on store_id and product_id further reduces the data scanned within each partition by colocating rows with similar store and product values. This design minimizes both query cost (bytes billed) and latency, while supporting near-real-time ingestion from Pub/Sub without requiring table rewrites.

Answer

Partition by store_id, cluster by product_id

Answer

Unpartitioned table with clustering on store_id and product_id

Answer

Use materialized views with aggregation on store_id, product_id, and date

Question 5

An engineer needs to monitor the replication lag of a Cloud SQL read replica. Which metric should they use in Cloud Monitoring?

Accepted Answer

replication_lag. The `replication_lag` metric in Cloud Monitoring directly measures the time delay between a primary Cloud SQL instance and its read replica, reported in seconds. This is the standard metric for monitoring how far behind the replica is in applying changes from the primary, which is critical for ensuring read-after-write consistency and data freshness.

Answer

sent_bytes_count

Answer

disk_bytes_used

Answer

cpu_utilization

Question 6

A startup needs a database for its new web application that will serve a global user base. The application requires low-latency reads and writes (single-digit milliseconds), strong consistency, and the ability to handle high concurrency (thousands of transactions per second). Which Google Cloud database service should the startup choose?

Accepted Answer

Cloud Spanner. Cloud Spanner offers global distribution, strong consistency, and low-latency transactions at high concurrency. It is designed for globally scaled OLTP workloads.

Answer

Cloud SQL for MySQL

Answer

Bigtable

Answer

Firestore

Question 7

Match each Cloud SQL high-availability feature to its description.

Accepted Answer

Automatic failover: Automatically switches to a standby instance in a different zone if the primary instance fails, ensuring minimal downtime.. Automatic failover switches to a standby zone on failure, read replicas provide read-only copies for scaling, and point-in-time recovery allows restoring to any time within retention. Common confusions mix automatic failover with read replicas.

Answer

Automatic failover: Provides read-only copies of the primary instance to distribute read load.

Answer

Read replicas: Automatically switches to a standby instance in a different zone if the primary fails.

Question 8

A team has an SLO of 99.9% availability over a 30-day period. How many minutes of downtime does the error budget allow per month?

Accepted Answer

43 minutes. An SLO of 99.9% availability allows 0.1% unavailability. Over a 30-day month (30 × 24 × 60 = 43,200 minutes), 0.1% equals 43,200 × 0.001 = 43.2 minutes, commonly rounded to 43 minutes. This is the error budget — the maximum downtime permitted while still meeting the SLO.

Answer

4.32 minutes

Answer

432 minutes

Answer

4,320 minutes

Question 9

Refer to the exhibit. The query joins two large tables and aggregates results. Which optimization would most likely reduce the high shuffle bytes in Stage 3?

Accepted Answer

Add a WHERE clause to filter rows before the join.. Adding a WHERE clause before the join reduces the amount of data that needs to be shuffled across the network in Stage 3. In Spark SQL (the engine behind Databricks and many PCDE scenarios), predicate pushdown filters rows early, minimizing the input to the join and subsequent aggregation, which directly reduces shuffle bytes.

Answer

Ensure both tables are clustered on the join key.

Answer

Use a broadcast join hint to force one table to be broadcast.

Answer

Add an ORDER BY clause to sort the data before aggregation.

Question 10

Order the steps to perform a disaster recovery drill for a Cloud Spanner database using backups.

Accepted Answer

Create backup -> Restore to another region -> Verify data integrity -> Update application configuration -> Test application failover. The correct sequence for a Cloud Spanner disaster recovery drill using backups is: first create a backup, then restore it to another region. After restoration, verify data integrity, then update application configurations to point to the restored database, and finally test the application to ensure failover works. This order ensures data consistency and minimizes risk.

Answer

Restore to another region -> Create backup -> Verify data integrity -> Update application configuration -> Test application failover

Answer

Create backup -> Verify data integrity -> Restore to another region -> Update application configuration -> Test application failover

Answer

Create backup -> Restore to another region -> Update application configuration -> Verify data integrity -> Test application failover