PCDE Mock Exam 82 — 25 Questions

Question 1

A Cloud Spanner instance is experiencing increased latency during peak hours. Monitoring shows CPU utilization nearing 70%. How should they scale?

Accepted Answer

Add more nodes.. Adding more nodes is the correct scaling approach for a Cloud Spanner instance experiencing high CPU utilization and latency. Cloud Spanner distributes data and query processing across nodes; each node provides a fixed amount of compute and storage capacity. Increasing the number of nodes directly increases the available CPU resources, reducing per-node utilization and improving query throughput and latency.

Answer

Change to a higher-tier machine type.

Answer

Increase the number of splits.

Answer

Add more processing units.

Question 2

A company wants to deploy a containerized application to Cloud Run and gradually shift traffic from the current revision to a new revision. They want to send 10% of traffic to the new revision initially. Which command should they use?

Accepted Answer

gcloud run deploy --image ... --no-traffic; then gcloud run services update-traffic ... --to-revisions=NEW=10,OLD=90. It first deploys the new revision without any traffic using `--no-traffic`, then uses `gcloud run services update-traffic` to explicitly set 10% traffic to the new revision and 90% to the old revision. This two-step approach is required when you want to gradually shift traffic to a new revision without immediately serving any traffic to it, as Cloud Run does not support setting a specific traffic percentage directly in the `gcloud run deploy` command.

Answer

gcloud run revisions update-traffic ... --to-revisions=NEW=10

Answer

gcloud run deploy --image ... --to-revisions=NEW=10

Answer

gcloud run deploy --image ... --traffic=10

Question 3

You are designing a Cloud SQL for PostgreSQL database. The application has a table with 1 million rows that is frequently queried using equality on the 'email' column and range queries on the 'created_at' column. Which index strategy minimizes query latency?

Accepted Answer

Create a composite B-tree index on (email, created_at).. A composite B-tree index on (email, created_at) allows the database to satisfy both the equality condition on 'email' and the range condition on 'created_at' in a single index scan. PostgreSQL can use the leftmost column for equality filtering and then traverse the index tree to retrieve the range portion efficiently, minimizing random I/O and query latency.

Answer

Create a full-text index on email.

Answer

Create a B-tree index on email only.

Answer

Create separate B-tree indexes on email and created_at.

Question 4

Your company runs a large e-commerce application on Google Cloud using Cloud SQL for MySQL (version 8.0) with 2 TB of data. The database experiences intermittent performance degradation during peak hours (10am-2pm). Cloud Monitoring shows a spike in CPU utilization to 90% and increased query latency. The database has been running for 6 months with default settings. You notice many slow queries like "SELECT * FROM orders WHERE customer_id=12345 ORDER BY order_date DESC LIMIT 10" that take 5-10 seconds. The orders table has 50 million rows, customer_id has a B-tree index, and order_date is not indexed. The query execution plan indicates a full table scan and a filesort. What is the most effective course of action to resolve the performance issue?

Accepted Answer

Add a composite index on (customer_id, order_date). The slow query uses a WHERE clause on customer_id (which is indexed) and an ORDER BY on order_date (not indexed). The index on customer_id alone is insufficient because the query still requires sorting, leading to a filesort. Adding a composite index on (customer_id, order_date) allows the database to retrieve rows for a specific customer in sorted order without a full scan or filesort. Option B (increasing memory) may help but does not address the root cause. Option C (read replicas) offloads read traffic but does not fix the query plan. Option D (partitioning) might help with data management but is not as direct or efficient as adding the appropriate index.

Answer

Create multiple read replicas to offload read traffic

Answer

Partition the orders table by month using range partitioning

Answer

Increase the memory size of the Cloud SQL instance to 30 GB

Question 5

A team is migrating a 2 TB MySQL database from on-premises to Cloud SQL. They want to minimize downtime. The source is MySQL 8.0 with InnoDB tables, and the application can be read-only during cutover. Which approach provides the lowest downtime while ensuring data consistency?

Accepted Answer

Use Database Migration Service with continuous CDC and promote when lag is zero.. DMS with continuous CDC provides near-zero downtime by replicating ongoing changes. After the initial dump, the source continues to replicate changes, allowing a quick cutover with minimal downtime.

Answer

Use mysqldump with --single-transaction to export, then import to Cloud SQL using mysql client.

Answer

Use Cloud Dataflow to stream data from MySQL to Cloud SQL.

Answer

Use gcloud sql import command with a compressed dump file from Cloud Storage.

Question 6

Refer to the exhibit. You are reviewing the following Cloud Spanner DDL statement for a table storing customer orders. What potential performance issue will arise with this schema?

Accepted Answer

The OrderId is likely to be sequentially generated, causing write hotspots. Cloud Spanner uses a distributed architecture that splits data across splits based on the primary key range. If OrderId is sequentially generated (e.g., auto-increment), all new inserts will target the same split, creating a write hotspot that degrades throughput and latency. This is a well-known anti-pattern in Spanner; the recommended approach is to use a UUID or a monotonically increasing key with a hash prefix to distribute writes evenly.

Answer

The primary key includes two columns which reduces insert performance

Answer

The TotalAmount column should be INTEGER for performance

Answer

The table lacks a foreign key constraint

Question 7

A retail company uses BigQuery to store sales transactions. The BI team needs to create a monthly customer lifetime value (CLV) report that aggregates purchase history across multiple tables. Which BigQuery feature should they use to define the data structure for this report?

Accepted Answer

Create a view that joins and aggregates the tables. A view in BigQuery allows the BI team to define a logical data structure that joins and aggregates multiple tables without storing the results. This ensures the monthly CLV report always reflects the latest data, as views are re-evaluated at query time, which is ideal for recurring reports that need up-to-date aggregations.

Answer

Create a materialized view with the aggregation query

Answer

Create an external table pointing to the raw data files

Answer

Create a new table to store the aggregated data using INSERT SELECT

Question 8

A retail company is designing a new inventory management system on Cloud Spanner. They need to ensure high write throughput for order processing. Which two schema design practices help avoid write hotspots? (Choose TWO.)

Accepted Answer

Avoid using a monotonically increasing primary key.. Monotonically increasing primary keys (e.g., auto-increment integers or timestamps) cause all new writes to be directed to the same tablet server, creating a hot spot. Cloud Spanner splits data by key range, so sequential keys concentrate load on a single split. Option E is correct because adding a hash prefix to the primary key distributes writes uniformly across splits, preventing any single node from becoming a bottleneck.

Answer

Create secondary indexes on frequently queried columns.

Answer

Store all data in a single table with no interleaving.

Answer

Use foreign keys to enforce referential integrity.

Question 9

Your Cloud Spanner database is experiencing a high volume of read-write conflicts, causing many aborts and high latency. You have already increased the compute capacity. Upon analyzing the schema, you find that most conflicts occur on a single table with frequent updates to the same row. What index or schema change would most effectively reduce contention?

Accepted Answer

Change the primary key to include a hash prefix of the original key.. Adding a hash prefix to the primary key distributes writes across multiple splits, reducing hot-spotting on a single row. Cloud Spanner uses range-based splits; without a hash prefix, monotonically increasing keys cause all writes to target the same split, leading to read-write conflicts and aborts. A hash prefix randomizes the key distribution, spreading load across nodes and minimizing contention.

Answer

Enable batch writes in the client library to combine updates.

Answer

Use a monotonically increasing timestamp as a prefix to the primary key.

Answer

Add a secondary index on the most frequently updated column.

Question 10

A company wants to implement a CI/CD pipeline for a multi-service application where each service is built from a separate repository. They need to run unit tests, build container images, and deploy to both Cloud Run and GKE. Which two Google Cloud services should be combined to achieve this?

Accepted Answer

Cloud Build. Cloud Build is correct because it is a fully managed CI/CD platform that can pull source code from multiple repositories (including Cloud Source Repositories, GitHub, or Bitbucket), run unit tests, build container images, and push them to Artifact Registry. Cloud Deploy is correct because it provides managed continuous delivery to both Cloud Run and GKE, supporting progressive delivery strategies like canary and blue-green deployments, and integrates directly with Cloud Build as a delivery pipeline target.

Answer

Config Sync

Answer

Artifact Registry

Answer

Cloud Source Repositories

Question 11

Which Cloud Monitoring metric indicates the number of queries waiting for locks in Cloud SQL?

Accepted Answer

Lock waits. The 'Lock waits' metric in Cloud SQL (for MySQL, PostgreSQL, or SQL Server) directly tracks the number of queries that are blocked because they are waiting for a lock held by another transaction. This is the correct indicator of query contention, as it measures the count of statements currently in a lock-wait state, not the total queries or connections.

Answer

Active connections

Answer

CPU utilization

Answer

Queries

Question 12

A developer runs the command shown in the exhibit and wants to verify that replication is enabled on the Bigtable instance. Where should they look for this information in the output?

Accepted Answer

View the 'clusters' list within the instance description.. The `gcloud bigtable instances describe` command returns a JSON representation of the instance, which includes a `clusters` list. Each cluster object in that list contains a `replication` field (e.g., `defaultStorageType` and `nodes`), and the presence of multiple clusters in the list indicates that replication is configured. Replication in Cloud Bigtable is enabled by adding more than one cluster to the instance, so examining the `clusters` list directly shows whether replication is active.

Answer

Examine the 'instanceType' field for 'MULTI_CLUSTER'.

Answer

Look for a 'replication' field in the JSON.

Answer

Check the 'state' field for 'REPLICATED'.

Question 13

A DevOps engineer is designing a CI/CD pipeline for a Cloud Run service. They need to implement a canary deployment that sends 10% of traffic to a new revision initially, then gradually increases to 100% if metrics are healthy. They also need to roll back instantly if the canary fails. Which THREE configurations should they use? (Select THREE)

Accepted Answer

Configure Cloud Deploy with a canary strategy and metric thresholds. Cloud Deploy supports canary deployments with automated metric thresholds (e.g., latency, error rate) that control traffic progression. This allows the pipeline to gradually shift from 10% to 100% traffic based on real-time health checks, enabling automated rollback if thresholds are breached.

Answer

Use gcloud run deploy with --no-traffic to deploy the new revision without receiving traffic

Answer

Use a manual approval gate in Cloud Deploy

Question 14

A DevOps team is designing a landing zone on GCP. They want to centralize networking, logging, and security. Which TWO projects should they create? (Choose 2)

Accepted Answer

A central Logging project. In a GCP landing zone, centralizing logging into a dedicated project ensures that audit logs, VPC flow logs, and other operational logs are aggregated in a single, secure location. This project is used to configure log sinks, export logs to BigQuery or Cloud Storage, and enforce retention policies across the organization, which is a best practice for compliance and troubleshooting.

Answer

A project for IAM roles

Answer

A project for each environment

Answer

A separate project per application

Question 15

A company uses BigQuery for BI reporting. They have a large table 'events' with nested and repeated fields (ARRAY<STRUCT>). Analysts often query unnested data, which is slow. What is the best practice to improve query performance without changing the source schema?

Accepted Answer

Create a materialized view that flattens the nested data. A materialized view in BigQuery can precompute and store the results of an UNNEST operation on nested fields, significantly reducing query time for repeated flattening queries. Unlike a regular view, a materialized view persists the flattened data and is automatically refreshed, so analysts query pre-joined, pre-flattened results without altering the source schema. This directly addresses the performance issue while preserving the original nested structure for other use cases.

Answer

Create a view that unnests the data

Answer

Redesign the table to be flat

Answer

Use a subquery with UNNEST and cache the results

Question 16

A financial services company runs a Cloud SQL for PostgreSQL instance for transactional data. They need to conduct regular security audits and compliance checks. The database engineer must ensure that all connections to the database are encrypted and that access is restricted to authorized VMs only. The database is currently accessible from the internet via an authorized network with a public IP. What should the database engineer do to meet these requirements?

Accepted Answer

Enable Cloud SQL private IP and disable public IP. Use VPC Service Controls and Cloud Identity-Aware Proxy for access.. It addresses both requirements: encryption and access restriction. Enabling Cloud SQL private IP ensures that the database is only reachable from within the VPC, eliminating internet exposure. VPC Service Controls provide a security perimeter to prevent data exfiltration, and Cloud Identity-Aware Proxy (IAP) enables fine-grained, identity-based access to the database without requiring a public IP or VPN.

Answer

Create a Cloud SQL proxy instance in the same VPC and force all clients to connect through the proxy.

Answer

Configure SSL/TLS for all connections and use an authorized network with a specific CIDR range.

Answer

Enable Cloud SQL public IP with SSL/TLS and restrict access using Cloud Armor.

Question 17

A company is adopting GitOps for their GKE clusters using Config Sync. They need to meet the following requirements: (1) automatically sync the cluster state to a Git repository every 5 minutes, (2) ensure that any changes made directly to the cluster are reverted to the desired state defined in Git. Which Config Sync settings should they configure? (Choose TWO).

Accepted Answer

Set spec.sync to '5m' in the RootSync or RepoSync object.. `spec.sync` in a RootSync or RepoSync object defines the sync interval for Config Sync. Setting it to `'5m'` ensures the cluster state is automatically reconciled against the Git repository every 5 minutes. Option D is correct because enabling drift management (`spec.driftManagement: true`) ensures that any manual changes made directly to the cluster are detected and reverted to the desired state defined in Git, meeting the second requirement.

Answer

Set spec.driftManagement to false.

Answer

Set spec.override to true.

Answer

Set spec.syncInterval to '5m' in the RootSync or RepoSync object.

Question 18

A company has a Cloud SQL for PostgreSQL instance with cross-region read replicas for disaster recovery. They want to test the failover process without affecting the primary instance. What is the recommended approach?

Accepted Answer

Promote the cross-region read replica to a standalone instance in a separate project for testing.. Cloud SQL cross-region read replicas can be promoted to a standalone instance. This promotion is a manual operation that creates a new primary instance. To test failover non-destructively, you can promote a read replica to a standalone instance in a different project or region. You can then test connectivity and read/write operations. After testing, you can either keep the promoted instance or delete it. You cannot failover and then revert the replica; promotion is irreversible. The promotion does not affect the original primary instance.

Answer

Create a clone of the primary instance and promote the clone to simulate failover.

Answer

Trigger a failover using the gcloud sql instances failover command on the primary instance.

Answer

Stop the primary instance and observe how the application behaves.

Question 19

An organization is using Memorystore for Redis and needs to ensure that when memory usage reaches the maximum, the cache evicts keys based on the least recently used (LRU) algorithm among keys with an expiry set. They also want to require password authentication for client connections. Which two configurations should be applied? (Choose TWO.)

Accepted Answer

Configure the AUTH password in the Memorystore instance. The eviction policy 'volatile-lru' evicts keys with an expiry set using LRU. AUTH is configured by setting a password via the Redis AUTH command or in the Memorystore instance settings.

Answer

Set maxmemory-policy to 'allkeys-lru'

Answer

Enable TLS for encryption

Answer

Enable persistence with AOF

Question 20

An organization is migrating an Oracle database to Cloud SQL for PostgreSQL. They have numerous stored procedures written in PL/SQL. Which tool should they use to automatically convert these procedures to PL/pgSQL?

Accepted Answer

Ora2Pg. Ora2Pg is a schema conversion tool that translates Oracle PL/SQL code to PostgreSQL PL/pgSQL, handling data type mappings and object conversions.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

gcloud sql import