PCDE Practice Exam 54 — 20 Questions

Question 1

Your company runs a business intelligence (BI) dashboard on BigQuery that refreshes every hour. The dashboard queries are complex with multiple JOINs and aggregations. Recently, the queries started taking longer than 30 minutes, causing timeouts. You check the BigQuery monitoring and see that the slot utilization consistently reaches 100% during the dashboard refresh. The project uses a flat-rate pricing model with 1000 slots. Other team members run ad-hoc queries during the same period. What is the most effective action to improve the dashboard performance?

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

Accepted Answer

Create a separate reservation for the dashboard queries with a baseline of 500 slots and use a low priority job queue for ad-hoc queries.. Creating a separate reservation for the dashboard queries with a baseline of 500 slots ensures that the critical BI dashboard always has guaranteed compute capacity, preventing starvation by ad-hoc queries. Using a low-priority job queue for ad-hoc queries allows them to use any remaining idle slots without interfering with the dashboard's reserved slots. This directly addresses the 100% slot utilization and timeout issue without requiring query rewrites or schedule changes.

Answer

Rewrite the dashboard queries to use fewer joins and aggregations.

Answer

Increase the total number of slots to 2000 to provide more capacity for all queries.

Answer

Schedule the dashboard refresh to run at a different time when ad-hoc usage is low.

Question 2

A Cloud Bigtable instance is experiencing high latency on reads. The operations team suspects a hot spot. Which tool should they use to identify the hot spot?

Accepted Answer

Key Visualiser. Key Visualizer is the correct tool because it is specifically designed to visualize access patterns in Cloud Bigtable and identify hot spots—regions of a table that receive disproportionate read/write traffic. It generates heatmaps of row key access, allowing operators to pinpoint uneven load distribution that causes high latency.

Answer

Stackdriver Debugger

Answer

Bigtable cbt tool

Answer

Cloud Monitoring dashboard

Question 3

A team is migrating a large on-premise Oracle database to Cloud SQL for PostgreSQL. They need to minimize downtime and ensure data consistency. Which migration approach is recommended?

Accepted Answer

Use Database Migration Service with continuous replication. Using Database Migration Service (DMS) with continuous replication provides near-zero downtime and maintains consistency.

Answer

Use pg_dump and pg_restore

Answer

Export data as CSV, import to Cloud SQL

Answer

Create a Cloud SQL read replica from on-premise

Question 4

A company uses Memorystore for Redis with Standard Tier (replication) and needs to ensure data durability in case of a zone failure. They also need to scale read throughput beyond a single instance. What should they do?

Accepted Answer

Migrate to Redis Cluster with 3+ shards.. Memorystore for Redis Standard Tier provides cross-zone replication for high availability. To scale read throughput, they can use Redis Cluster (which shards data across multiple shards) or create read replicas. However, Memorystore does not support read replicas for Redis; instead, Redis Cluster provides horizontal scalability and high availability. Upgrading to a larger instance scales vertically but not read throughput horizontally. Persistence is not natively supported in Memorystore.

Answer

Create a cross-region replica and use it for read traffic.

Answer

Upgrade to a larger machine type with more memory.

Answer

Enable persistence using AOF and configure a backup schedule.

Question 5

A financial institution uses Cloud SQL for MySQL to handle transaction processing. They need to generate daily BI reports that aggregate millions of transactions per account. The BI queries are CPU-intensive and degrade OLTP performance. What is the most effective solution?

Accepted Answer

Create a Cloud SQL read replica and run reports against it. Creating a Cloud SQL read replica allows you to offload BI reporting queries to a separate instance that replicates data from the primary using MySQL's asynchronous replication. This isolates the CPU-intensive aggregation queries from the OLTP workload, preventing performance degradation on the primary instance while still providing near-real-time data for reports.

Answer

Schedule reports during off-peak hours only

Answer

Use Cloud SQL's high availability configuration

Answer

Upgrade the primary instance to a higher machine type

Question 6

Arrange the steps to perform a point-in-time recovery (PITR) for a Cloud SQL instance.

Accepted Answer

Enable automatic backups and binary logging, then create a new Cloud SQL instance from the backup at the desired point in time.. PITR requires backups and binary logs enabled; then you create a new instance from backup at the specific time.

Answer

Create a new Cloud SQL instance from the latest backup, then enable binary logging, then perform point-in-time recovery.

Answer

Perform point-in-time recovery directly on the existing Cloud SQL instance by restoring the backup.

Answer

Enable binary logging only, then create a new instance from the backup, then enable automatic backups.

Question 7

Which THREE techniques can improve query performance in BigQuery for BI workloads? (Choose three.)

Accepted Answer

Use approximate aggregation functions when exact results are not required.. Approximate aggregation functions (Option A) improve performance by using algorithms like HyperLogLog++ to return near-exact results with lower resource consumption. Selecting only needed columns (Option B) reduces the amount of data scanned, lowering query costs and improving speed. Clustering tables on columns used in WHERE clauses (Option E) enables better data pruning and reduces the amount of data processed. These three techniques are commonly tested in the Google Cloud Professional Data Engineer exam as performance optimizations for BI workloads.

Answer

Use SELECT * with LIMIT to preview data.

Answer

Use ORDER BY on large result sets without LIMIT.

Question 8

A company uses Pub/Sub for event-driven processing and wants to ensure exactly-once delivery for critical messages. Which TWO configurations are required? (Choose two.)

Accepted Answer

Enable message ordering on the subscription.. For exactly-once delivery in Google Cloud Pub/Sub, the only required configuration is to enable the 'exactly-once delivery' flag on the subscription. This option is not listed. Message ordering and maximum retention duration are not prerequisites for exactly-once delivery. None of the provided options are necessary for exactly-once delivery.

Answer

Use multiple subscriber instances to increase throughput.

Answer

Set the acknowledgement deadline to 600 seconds.

Answer

Disable flow control to allow unlimited messages.

Question 9

A BigQuery table stores daily sales data. The team commonly queries data for a specific date range. Which schema optimization will reduce query cost and improve performance?

Accepted Answer

Partition the table by date column. Partitioning the table by the date column allows BigQuery to prune entire partitions when querying a specific date range, drastically reducing the amount of data scanned. Since BigQuery charges by the bytes processed, this directly lowers query cost and improves performance by reading only the relevant partitions.

Answer

Create a view over the table

Answer

Create a materialized view with a filter on date

Answer

Cluster the table by date column

Question 10

Refer to the exhibit. After creating this Bigtable instance, the administrator noticed high read latency during peak hours. Which configuration change would most likely help?

Accepted Answer

Increase cluster-num-nodes to at least 5. Increasing the number of nodes in the Bigtable cluster (option B) directly improves read throughput by distributing the read load across more tablet servers. High read latency during peak hours typically indicates that the existing nodes are saturated, and adding nodes reduces per-node load, lowering overall latency. This is the most direct and effective configuration change for addressing read latency in a single-cluster setup.

Answer

Change cluster-storage-type to HDD

Answer

Add more clusters in different zones

Answer

Increase cluster-autoscaling-max-nodes to 20

Question 11

A Cloud SQL for PostgreSQL instance is experiencing high replication lag between primary and read replica. Which TWO actions would reduce the lag?

Accepted Answer

Increase the replica's machine size.. Increasing the replica's machine size (Option B) reduces replication lag by providing more CPU and memory resources to apply WAL changes faster. Placing the replica in the same zone as the primary (Option C) minimizes network latency between the instances, which directly reduces the time for WAL data to travel from primary to replica, thus lowering lag.

Answer

Reduce the value of `max_wal_size`.

Answer

Use a higher number of read replicas.

Answer

Enable synchronous replication.

Question 12

You are troubleshooting a Cloud SQL read replica that is experiencing high replication lag. You check the 'replication_lag' metric and see it is consistently above 60 seconds. What is the most likely cause of this lag?

Accepted Answer

The primary instance has a heavy write workload.. High replication lag in Cloud SQL is most commonly caused by a heavy write workload on the primary instance. When the primary processes a large volume of write operations (e.g., INSERT, UPDATE, DELETE), the replica must replay those changes from the binary log, and if the rate of writes exceeds the replica's ability to apply them, lag accumulates. This is a fundamental behavior of MySQL asynchronous replication, where the replica is always slightly behind the primary under load.

Answer

The replica's automatic storage increase is disabled.

Answer

The replica has point-in-time recovery enabled.

Answer

The replica is using a different storage type than the primary.

Question 13

A financial services company needs to design a BigQuery data model for real-time fraud detection. Data arrives from multiple streaming sources and must be joined with historical customer profiles (10 TB) and transaction lookup tables (500 GB). Which TWO design considerations are most important to minimize query latency and cost?

Accepted Answer

Use time-based partitioning on the historical customer table and cluster on customer_id.. Time-based partitioning on the historical customer table (10 TB) allows BigQuery to prune irrelevant partitions during queries, reducing the amount of data scanned and thus lowering cost and latency. Clustering on customer_id further optimizes joins with streaming data by colocating related rows, minimizing shuffle overhead.

Answer

Schedule a nightly script to recluster tables based on query patterns.

Answer

Use a single table for all streaming data without partitioning to avoid partition management overhead.

Answer

Denormalize all historical and lookup data into a single wide table.

Question 14

A company uses Memorystore for Redis and wants to ensure data is preserved in case of a node failure. They cannot afford any data loss. What should they do?

Accepted Answer

Enable AOF persistence on the Redis instance. To guarantee no data loss during a node failure, Memorystore for Redis should have persistence enabled. Standard Tier instances support both RDB and AOF persistence. AOF persistence, especially with an fsync policy of 'always' or 'everysec', ensures that writes are durably stored, minimizing data loss to at most one second. Periodic exports to Cloud Storage are backups that occur at scheduled intervals, so any data written between the last export and the failure would be lost, violating the zero-data-loss requirement. Increasing maxmemory only controls eviction, not durability. Cross-region replication provides high availability but does not persist data to disk on its own and can lose recently written data due to replication lag.

Answer

Increase the maxmemory setting to avoid eviction

Answer

Use active cross-region replication to a replica instance

Answer

Schedule periodic backups to Cloud Storage using export

Question 15

A company uses Cloud SQL for PostgreSQL and needs to enable point-in-time recovery (PITR) with a retention period of 14 days. Currently, automated backups are configured with a 7-day retention. What should the engineer do to meet this requirement?

Accepted Answer

Note that Cloud SQL for PostgreSQL only supports up to 7 days of PITR retention; consider exporting logs to Cloud Storage for longer recovery.. Cloud SQL PITR is based on write-ahead log (WAL) archiving. The transaction log retention is set via the 'transaction log retention' setting, not the backup retention. PITR for PostgreSQL in Cloud SQL supports up to 7 days of log retention; 14 days is not supported. The engineer must accept the limitation or use a different approach.

Answer

Increase the backup retention period to 14 days; PITR is automatically extended.

Answer

Set the 'transaction log retention' to 14 days in the Cloud SQL instance settings.

Answer

Enable binary logging and set the binary log retention to 14 days.

Question 16

A company runs a Cloud Bigtable instance with two clusters in separate regions for disaster recovery. They want to ensure that read requests automatically use the secondary cluster if the primary cluster becomes unhealthy. Which two steps should they take? (Choose two.)

Accepted Answer

Change the routing policy to 'cluster-group routing' (any-replica). To enable automatic failover for reads in Bigtable, you need to use a routing policy that distributes reads across clusters. The 'cluster-group routing' (also called 'any-replica' routing) automatically routes requests to the nearest healthy cluster. Additionally, you must configure the application to use this routing policy. Alternatively, you can use a load balancer with health checks, but that is not native to Bigtable. The correct native approach is to use cluster-group routing and ensure the application uses it.

Answer

Set up a load balancer in front of Bigtable instances

Answer

Create a third cluster in a third region for quorum

Answer

Configure Cloud DNS with a health check that points to the secondary cluster

Question 17

Your organisation uses Cloud Spanner for a globally distributed application. You need to set up a backup strategy that allows restoring the database to a specific point in time within the last 7 days, with minimal impact on performance. Which THREE actions should you take? (Choose THREE.)

Accepted Answer

Create full database backups using gcloud spanner backups create and set retention to 7 days.. For point-in-time recovery within 7 days, Cloud Spanner offers built-in PITR (Option D) which enables restoring to any point within the retention period with minimal performance impact. Additionally, creating full backups (Option C) with a 7-day retention ensures you have backup files for restore if needed. Automating these backups with Cloud Scheduler (Option E) ensures regular backups without manual intervention. Options A and B are incorrect: A uses Dataflow export which is intended for long-term retention and can impact performance, and B restores from backups rather than enabling PITR. Option B is not needed when PITR is enabled.

Answer

Export the database daily using Dataflow to Cloud Storage.

Answer

Use gcloud spanner databases import to restore from previous exports.

Question 18

A company uses Cloud SQL for MySQL to run an e-commerce application. They need to ensure that they can recover the database to any point within the last 4 days. They also want to minimize storage costs. Which configuration should they use?

Accepted Answer

Enable binary logging with the 'log_bin' flag and set the transaction log retention to 4 days in the backup configuration.. Point-in-time recovery (PITR) in Cloud SQL for MySQL requires binary logging, which can be enabled with the 'log_bin' flag. The transaction log retention period can be set from 1 to 7 days. For 4-day recovery, set the retention to 4 days. Option B is correct: enable binary logging and set the transaction log retention to 4 days. Option A is wrong because automated backups alone only restore to backup time, not any point. Option C is wrong because disabling automated backups is not necessary—automated backups are still needed for initial restore points, but PITR uses transaction logs. Option D is wrong because point-in-time recovery is enabled by setting binary logging and retention; there is no separate 'point_in_time_recovery' flag.

Answer

Enable automated backups with a 4-day retention schedule.

Answer

Enable binary logging and set the transaction log retention to 4 days. Disable automated backups.

Answer

Enable point-in-time recovery by setting the 'point_in_time_recovery' flag to true with a 4-day window.

Question 19

Your team manages a Cloud SQL for MySQL instance used by a critical application. You need to ensure the instance is recoverable to any point within the last 4 days, with a Recovery Point Objective (RPO) of under 5 minutes. What configuration steps are required?

Accepted Answer

Enable automated backups and binary logging. Set the transaction log retention period to 4 days.. Point-in-time recovery (PITR) in Cloud SQL for MySQL requires both automated backups and binary logging to be enabled. The transaction log retention period (set via transactionLogRetentionDays) determines how far back you can perform PITR, with a maximum of 7 days. Enabling automated backups alone (option A) does not provide PITR because binary logging is needed. Option B is incorrect because automated backups are required for PITR, and binary logging only without backups does not meet the recovery window. Option C with on-demand daily backups cannot provide an RPO under 5 minutes because binary logging captures ongoing changes. Therefore, option D is correct: enable automated backups, binary logging, and set the transaction log retention to 4 days to meet the 4-day recovery window and RPO under 5 minutes.

Answer

Enable automated backups and set the backup retention to 4 days. Binary logging is not required because automated backups already capture all changes.

Answer

Enable binary logging and set the binary log retention to 4 days. Automated backups are optional and not needed for PITR.

Answer

Create an on-demand backup daily and set binary log retention to 4 days. This provides the same RPO as automated backups with binary logging.

Question 20

A DBA notices high query cache lock contention on this Cloud SQL for MySQL instance. Which configuration change should be recommended?

Accepted Answer

Set query_cache_type=0. Query cache lock contention occurs when multiple threads try to access the query cache simultaneously, causing serialization. Setting `query_cache_type=0` disables the query cache entirely, eliminating the lock contention. This is the recommended fix because MySQL 8.0 deprecated the query cache due to scalability issues, and Cloud SQL for MySQL instances benefit from removing this bottleneck.

Answer

Decrease max_connections

Answer

Increase innodb_buffer_pool_size

Answer

Increase query_cache_size