PCDE Mock Exam 75 — 25 Questions

Question 1

You are building a globally distributed leaderboard application that requires strongly consistent reads with latency under 10 ms and high write throughput. Which Google Cloud database service is most suitable?

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

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides strongly consistent reads across globally distributed regions with latency under 10 ms, while also supporting high write throughput. It uses TrueTime and synchronous replication to ensure ACID transactions and global consistency, meeting the exact requirements of a globally distributed leaderboard application.

Answer

Cloud Bigtable

Answer

Memorystore

Answer

Firestore

Question 2

A database administrator is planning to migrate an on-premises MySQL database to Cloud SQL. Which two steps are required to ensure a secure migration?

Accepted Answer

Configure Cloud SQL to use a private IP address. Using a private IP address for Cloud SQL ensures that the database instance is not exposed to the public internet, reducing the attack surface. This is a fundamental security best practice for database migrations, as it restricts network access to within a Virtual Private Cloud (VPC) and requires traffic to traverse Google's internal network, which is more secure than public IP routing.

Answer

Add authorized networks for all client IPs

Answer

Ensure the database is encrypted at rest using CMEK

Answer

Enable SSL/TLS for all connections

Question 3

A developer wants to deploy a containerized application to Cloud Run using the command line. They need to set the maximum number of concurrent requests per container instance to 80. Which flag should they use with 'gcloud run deploy'?

Accepted Answer

--concurrency. The `--concurrency` flag in `gcloud run deploy` directly sets the maximum number of simultaneous requests that a single container instance can handle. By specifying `--concurrency=80`, the developer limits each instance to processing up to 80 concurrent requests, which helps control resource usage and scaling behavior in Cloud Run.

Answer

--platform

Answer

--cpu-throttling

Answer

--max-instances

Question 4

A database engineer is configuring a Memorystore for Redis instance for a session store application. The application requires persistent storage to survive node failures. Which tier and configuration should be used?

Accepted Answer

Standard tier with AOF persistence enabled. The Standard tier in Memorystore for Redis provides a replicated architecture with a primary and read replica, ensuring high availability and automatic failover. Enabling AOF (Append-Only File) persistence writes every write operation to an AOF file, which is stored on persistent disk and can be replayed to restore data after a node failure. This combination meets the session store requirement for data durability across failures, as the Basic tier lacks replication and cannot guarantee data survival.

Answer

Standard tier with no persistence

Answer

Basic tier with RDB persistence enabled

Answer

Basic tier with AOF persistence enabled

Question 5

An e-commerce application uses Cloud Spanner for its global inventory database. The application experiences high write latency during peak hours. After reviewing the schema, the database engineer notices that the primary key is an auto-incrementing integer. What is the most likely cause of the high write latency?

Accepted Answer

The monotonically increasing primary key creates a hotspot.. Cloud Spanner distributes data across splits based on the primary key range. A monotonically increasing integer primary key, such as an auto-incrementing ID, causes all new writes to target the same split (the highest key range), creating a hotspot. This single split becomes a bottleneck, leading to high write latency during peak hours, as Spanner cannot parallelize the writes across multiple nodes.

Answer

The application is using read-only transactions instead of read-write transactions.

Answer

The database is configured with a backup retention period of 2 seconds.

Answer

The application is using read replicas that are out of date.

Question 6

A developer reports that their application cannot connect to a Cloud SQL instance using private IP, but public IP works. The Cloud SQL instance is in VPC peering with the application's VPC. The application is in the same region. What is the most likely cause?

Accepted Answer

The private IP range of the Cloud SQL instance conflicts with the application's VPC.. When a Cloud SQL instance is configured with a private IP address that overlaps with the application's VPC CIDR range, the VPC peering connection cannot route traffic correctly. This is due to the fact that VPC peering requires non-overlapping IP ranges to establish proper routing tables; overlapping ranges cause route conflicts and connectivity failures. Since public IP works, the issue is isolated to private IP routing, making IP range conflict the most likely cause.

Answer

The VPC peering connection is not established.

Answer

The Cloud SQL proxy is not running.

Answer

The Cloud SQL instance has 'require SSL' enabled.

Question 7

Which TWO metrics should you monitor in Cloud Monitoring to evaluate the performance of a Cloud Spanner instance? (Choose two.)

Accepted Answer

Commit latency. Commit latency is a critical metric for Cloud Spanner because it directly measures the time taken to commit a transaction, which reflects the database's ability to handle write operations efficiently. High commit latency can indicate contention, node overload, or suboptimal schema design, making it essential for performance evaluation.

Answer

Row reads per second

Answer

Connection count

Answer

Disk IOPS

Question 8

Which tool is best for identifying hot spots in a Cloud Spanner database?

Accepted Answer

Key Visualizer. Key Visualizer is the correct tool because it is specifically designed to visualize access patterns in Cloud Spanner and identify hot spots—keys or ranges that receive a disproportionate share of reads or writes. Unlike generic monitoring tools, Key Visualizer provides a heatmap of key-space activity, enabling you to pinpoint and mitigate performance bottlenecks caused by uneven distribution of workload across splits.

Answer

Query Insights

Answer

Cloud Trace

Answer

Cloud Monitoring

Question 9

A team notices that queries on a Cloud Spanner database are slow. They want to identify which queries are consuming the most resources. What should they use?

Accepted Answer

Query Insights. Query Insights is the correct tool because it is specifically designed for Cloud Spanner to analyze query performance, providing detailed metrics such as execution latency, CPU usage, and rows scanned per query. It helps identify the most resource-intensive queries by breaking down performance by query fingerprint, allowing the team to pinpoint and optimize slow queries directly.

Answer

Cloud Logging

Answer

Performance Dashboard

Answer

Cloud Monitoring metrics

Question 10

You are designing a Cloud Spanner schema for a global social media application. The application reads the most recent 100 posts for a user's timeline. The Posts table has a primary key of (UserId, PostTimestamp DESC). You observe that queries for the timeline are hitting high read latency and the transaction abort rate is increasing. What is the most likely cause?

Accepted Answer

The descending timestamp in the primary key is causing hotspotting on the tablet leader for the most recent data.. The descending timestamp in the primary key causes all writes for the most recent posts to be concentrated on the same tablet leader, because Cloud Spanner uses the first primary key column for splitting and distributing data. This hotspotting leads to high read latency and increased transaction abort rates as the single leader becomes overloaded with both writes and reads for the latest data.

Answer

The instance has too few nodes to handle the write volume.

Answer

The lack of a secondary index on UserId is forcing full table scans.

Answer

The table should be interleaved under a Users table.

Question 11

A company uses Bigtable for real-time analytics. They notice that writes to a specific row range are significantly slower. The Key Visualizer shows a hotspot. What is the most likely cause and recommended action?

Accepted Answer

Redesign the row key to avoid sequential writes to the same region.. Hotspots occur when a single tablet server is overloaded due to high write traffic to a contiguous key range. The solution is to redesign the row key to distribute writes.

Answer

Create a new cluster and replicate data to that cluster.

Answer

Change the storage type from HDD to SSD.

Answer

Increase the number of nodes to handle the load.

Question 12

You are a database engineer at a retail company. The company uses BigQuery for BI, with a fact table 'sales_fact' partitioned by order_date and containing 100 million rows. There is a dimension table 'products' with 10,000 rows. The BI team reports that the following query takes over 5 minutes to run: SELECT p.category, SUM(s.amount) FROM sales_fact s JOIN products p ON s.product_id = p.product_id WHERE s.order_date >= '2024-01-01' AND s.order_date < '2024-04-01' GROUP BY p.category. The table 'products' is not partitioned or clustered. 'sales_fact' is partitioned by order_date but not clustered. The query only scans 3 months of data (about 25 million rows). However, the join seems slow. What is the most likely cause and what single action would you take to improve performance?

Accepted Answer

Cluster the 'sales_fact' table on product_id. The query is slow because the join on `product_id` requires shuffling 25 million rows from `sales_fact` across nodes to match with `products`. Clustering `sales_fact` on `product_id` co-locates rows with the same `product_id` within each partition, reducing shuffle overhead and enabling more efficient broadcast or hash joins in BigQuery. This is the most impactful single action because it directly addresses the join performance bottleneck without changing the query logic.

Answer

Use a cross-join to avoid the join

Answer

Add an index on 'products.product_id'

Answer

Partition the 'products' table

Question 13

You are responsible for a Cloud SQL for MySQL instance that supports a content management system (CMS). The application frequently performs SELECT queries with ORDER BY and LIMIT. Recently, the response time for these queries has increased. The database has 4 vCPUs and 15 GB memory. You check the slow query log and find many queries that are taking over 1 second. The 'rows_examined' is much higher than 'rows_sent'. The EXPLAIN plan shows 'Using filesort' and 'Using temporary'. There is currently an index on the column used in the WHERE clause but not on the ORDER BY columns. The table has 5 million rows. What should you do to improve query performance?

Accepted Answer

Add a composite index on the columns used in the WHERE clause and the ORDER BY clause.. Adding a composite index on the columns used in the WHERE clause and the ORDER BY clause allows MySQL to avoid the expensive 'Using filesort' and 'Using temporary' operations. With a covering index, the database can retrieve rows in the required order directly from the index, eliminating the need to sort the result set after filtering. This dramatically reduces 'rows_examined' and improves query response time for SELECT queries with ORDER BY and LIMIT.

Answer

Increase the buffer pool size to 80% of memory.

Answer

Disable the query cache to reduce overhead.

Answer

Remove the ORDER BY clause and sort the results in application code.

Question 14

A company has multiple teams in a GCP organization. They want to isolate environments (prod, staging, dev) and give each team a separate project for development. Which folder structure is recommended?

Accepted Answer

Create folders for each environment (prod, staging, dev), and within each, folders for teams/products containing projects. It aligns with Google Cloud's recommended resource hierarchy for multi-team, multi-environment isolation. By creating folders for each environment (prod, staging, dev) and then sub-folders for teams/products, you can apply consistent IAM policies and organization policies at the environment level (e.g., restrict prod access) while delegating project-level control to teams. This structure also supports the principle of least privilege and simplifies auditing.

Answer

Create a flat list of projects with naming conventions like `team-project-env`

Answer

Create a single folder per team, with projects for each environment inside

Answer

Create folders per product, and within each, environment folders

Question 15

A company runs an e-commerce platform on Cloud SQL for PostgreSQL. They need to perform point-in-time recovery (PITR) to recover from a user error that occurred 30 minutes ago. Which configuration is required to enable PITR?

Accepted Answer

Enable automated backups and configure a retention of 1-7 days; PITR uses WAL archiving automatically.

Answer

Enable binary logging and set a backup retention of 1-7 days.

Answer

Set up cross-region backup replicas and configure a retention of 1-7 days.

Answer

Enable PITR by setting the 'pitr_enabled' flag to true in the database flags.

Question 16

Your Cloud SQL for MySQL instance is experiencing high CPU usage due to a burst of concurrent connections. You want to handle up to 500 concurrent connections without over-provisioning the instance. What should you do?

Accepted Answer

Right-size the instance to a tier that supports 500 concurrent connections.. Cloud SQL for MySQL has a maximum connections limit (based on tier). To handle 500 concurrent connections, you need to choose a tier with sufficient vCPU and memory. The max_connections is typically calculated as (available memory)/1257280 * 500, but the simplest approach is to use a tier that supports at least 500 connections (e.g., db-n1-standard-2 or higher). However, using Cloud SQL Auth Proxy with PgBouncer is for PostgreSQL, not MySQL. Connection pooling with ProxySQL or a similar tool could help, but the question says 'without over-provisioning', so right-sizing the tier is key.

Answer

Create read replicas to distribute read traffic.

Answer

Enable automatic storage increase to handle the load.

Answer

Use Cloud SQL Auth Proxy with a connection pooler like PgBouncer.

Question 17

Which THREE are valid considerations when designing BigQuery tables for BI reporting?

Accepted Answer

Use partitioning on date columns to reduce query cost. Partitioning BigQuery tables by date columns (e.g., using _PARTITIONTIME or a DATE/TIMESTAMP column) allows the query engine to prune entire partitions during query execution. This significantly reduces the amount of data scanned, directly lowering query costs (since BigQuery charges per byte processed) and improving performance for time-range filters.

Answer

Use nested and repeated fields to avoid JOINs

Answer

Create indexes on frequently queried columns

Question 18

A team uses Terraform to manage infrastructure. They want to ensure that all Terraform code passes policy checks before being applied. They use Terraform Cloud. Which built-in feature allows them to define policies that are checked during the plan phase?

Accepted Answer

Sentinel. Sentinel is Terraform Cloud's built-in policy-as-code framework that allows teams to define and enforce policies during the plan phase. It integrates directly with Terraform Cloud's run lifecycle, enabling policy checks to be evaluated against the planned infrastructure changes before they are applied. This ensures compliance and governance without requiring external tools.

Answer

`terraform validate`

Answer

Conftest

Answer

OPA (Open Policy Agent)

Question 19

A company is designing a disaster recovery strategy for a Cloud SQL for PostgreSQL instance. They require an RPO of less than 5 minutes and an RTO of less than 2 minutes in the event of a regional outage. Which three components should they include in their solution? (Choose three.)

Accepted Answer

Cross-region read replica. To achieve RPO <5 minutes and RTO <2 minutes across regions, you need a solution that can recover quickly with minimal data loss. Cloud SQL cross-region read replicas replicate asynchronously, so RPO is the replication lag (which can be <5 minutes if the network is fast). Promoting a read replica manually takes minutes, but you can automate the promotion using Cloud Functions or scripts to reduce RTO. Alternatively, you could use a standby instance in another region with synchronous replication (but Cloud SQL does not support that). The best approach is to use a cross-region read replica with automated promotion (e.g., via Cloud Functions triggered by a health check). Additionally, you need to update application connection strings to point to the new primary. Using a load balancer with health checks can also help route traffic. The three correct components are: cross-region read replica, automated promotion script, and a global load balancer to redirect traffic.

Answer

Point-in-time recovery enabled with 7-day retention

Answer

Automated backup with 5-minute frequency

Question 20

A financial services company runs a critical application on Cloud SQL for PostgreSQL. They require point-in-time recovery (PITR) with the ability to recover to any second within the past 7 days. However, their current backup configuration only allows recovery to the previous 7 days, but not within seconds. What should they do to enable PITR?

Accepted Answer

Enable point-in-time recovery and set the transaction log retention to 7 days.. Enabling point-in-time recovery (PITR) on Cloud SQL for PostgreSQL automatically uses write-ahead log (WAL) archiving to allow recovery to any second within a specified retention period. Setting the transaction log retention to 7 days ensures that the archived WAL segments are kept for exactly 7 days, enabling recovery to any point within that window. This directly satisfies the requirement for second-granularity recovery over the past 7 days.

Answer

Use the Cloud SQL query insight feature to replay queries.

Answer

Enable binary logging and set the binary log retention period to 7 days.

Answer

Increase the number of automated backups to 7 per day.