PCDE Practice Exam 61 — 20 Questions

Question 1

A financial services company runs a MySQL database on Compute Engine. They want to migrate to Cloud SQL for MySQL to reduce operational overhead. The current schema includes a table 'transactions' with a composite primary key on (transaction_id, account_id) and a secondary index on account_id for account lookups. The database also uses foreign key constraints to ensure referential integrity between 'transactions' and 'accounts'. During migration testing, they observe that INSERT operations on 'transactions' are slower than expected. What schema change should they implement to improve INSERT performance in Cloud SQL?

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

Accepted Answer

Remove the foreign key constraints and enforce referential integrity in the application logic instead.. Foreign key constraints in MySQL (including Cloud SQL) require an internal check on every INSERT to verify that the referenced parent key exists. This adds a latency penalty proportional to the size of the parent table. Removing the constraint and moving referential integrity to the application eliminates this per-row check, directly improving INSERT throughput.

Answer

Remove the secondary index on account_id because it adds write overhead.

Answer

Change the primary key to (account_id, transaction_id) to avoid secondary index overhead.

Answer

Convert the table to a temporal table with system-versioning to avoid constraint checking.

Question 2

A DevOps engineer wants to automatically trigger a Cloud Build pipeline whenever a pull request is created against the main branch of a repository. Which type of build trigger should they use?

Accepted Answer

Pull request trigger. A pull request trigger in Cloud Build is specifically designed to initiate a build when a pull request is created or updated against a target branch, such as main. This trigger type evaluates the PR event and automatically runs the pipeline, enabling validation of proposed changes before merging.

Answer

Push to branch trigger

Answer

Scheduled trigger

Answer

Manual trigger

Question 3

A Cloud SQL for SQL Server instance has been running for months. Recently, the database size grew significantly and now query performance has degraded. The DBA checks the query execution plan and sees index scans. The current storage is 500GB SSD. What is the most likely cause and solution?

Accepted Answer

Index fragmentation; rebuild or reorganize indexes. Index fragmentation occurs over time as data is inserted, updated, or deleted, causing indexes to become inefficient. The query execution plan showing index scans (instead of seeks) is a classic symptom of fragmented indexes. Rebuilding or reorganizing the indexes will defragment them, restoring query performance without requiring additional storage or infrastructure changes.

Answer

Increase storage capacity to 1TB SSD

Answer

Enable query insights to analyze performance

Answer

Enable read replicas to offload queries

Question 4

You are designing a Cloud Bigtable schema for a time-series application that stores temperature readings from sensors. Each reading has a sensor ID (string), a timestamp (microseconds), and a temperature value. Queries always filter by sensor ID and a time range. Which row key design is optimal?

Accepted Answer

[salted_hash]#[sensor_id]#[reversed_timestamp]. Optimal because it uses a salted hash to distribute writes across Bigtable tablets, avoiding hot-spotting on a single node for high-write sensors. The sensor_id ensures all data for a sensor is co-located for efficient range scans, and the reversed timestamp allows queries for the most recent data to be served from the start of the row range, leveraging Bigtable's lexicographic ordering.

Answer

[sensor_id]#[timestamp]

Answer

[timestamp]#[sensor_id]

Answer

[sensor_id]#[reversed_timestamp]

Question 5

A company needs to perform disaster recovery testing for their Cloud SQL for PostgreSQL instance. They want to validate that a cross-region failover works without affecting production. What is the BEST approach?

Accepted Answer

Promote a cross-region read replica to a standalone instance in the same project and test connectivity.. A read replica can be promoted to a standalone instance, which does not affect the primary instance. This allows testing read/write operations on the promoted instance. After testing, the promoted instance can be deleted or kept. Rolling back a promoted replica is not possible. Performing a failover on the primary would cause downtime. Creating a clone does not test the replication path. Testing in a separate project is not necessary.

Answer

Create a clone of the primary instance in another region and test failover to the clone.

Answer

Use the gcloud sql instances failover command on the primary instance.

Answer

Stop the primary instance and promote the read replica, then later re-promote the original primary.

Question 6

A team runs a Cloud SQL MySQL instance serving an e-commerce application. Read traffic is growing and causing increased latency on the primary instance. They want to offload read queries with minimal application changes. Which solution should they implement?

Accepted Answer

Create Cloud SQL read replicas and direct read-only queries to them. Cloud SQL read replicas are designed to offload read traffic from the primary instance. The application can be configured to send read-only queries to the replica endpoint. Connection pooling with PgBouncer helps with connection management but does not offload reads. Query optimization reduces load but doesn't scale reads. Read replicas are the standard solution.

Answer

Upgrade the primary instance to a higher machine type

Answer

Optimize slow queries using EXPLAIN ANALYZE and add indexes

Answer

Enable connection pooling with Cloud SQL Auth Proxy and PgBouncer

Question 7

A team wants to use Cloud Build to build a Maven application and store the artifact in Artifact Registry. Which repository format should they use?

Accepted Answer

Maven repository. The team is building a Maven application, which produces a JAR or WAR artifact. Artifact Registry supports a Maven repository format specifically designed to store and serve Maven artifacts, including pom.xml and JAR files, using the Maven repository layout. This allows Cloud Build to push the artifact directly to a Maven repository in Artifact Registry for dependency management and deployment.

Answer

Python repository

Answer

npm repository

Answer

Docker repository

Question 8

A team has an SLO of 99.9% availability over a 30-day month. They burn through their entire error budget in the first 10 days. Which of the following is the MOST appropriate immediate action according to SRE principles?

Accepted Answer

Freeze all feature releases and focus on reliability improvements. When error budget is exhausted, the team should halt all feature releases and focus on reliability improvements to prevent further degradation.

Answer

Reduce the error budget by lowering the SLO to 99%

Answer

Deploy a new feature to increase user engagement

Answer

Increase the SLO to 99.99% to act as a buffer

Question 9

An organization uses Cloud SQL for PostgreSQL with read replicas to offload reporting queries. During peak, the primary instance's CPU spikes to 90%. The team suspects the read replica is falling behind. Which two settings should they check to diagnose replication lag? (Choose TWO.)

Accepted Answer

pg_stat_replication view on the primary.. The `pg_stat_replication` view on the primary instance shows the WAL sender process state, including the `write_lag`, `flush_lag`, and `replay_lag` columns that directly measure replication lag in PostgreSQL. This view provides real-time data on how far behind each standby (including read replicas) is in receiving, flushing, and applying WAL data, making it the primary diagnostic tool for replication lag.

Answer

pg_replication_slots view on the replica.

Answer

max_wal_size configuration parameter.

Answer

SHOW REPLICATION STATUS command.

Question 10

Which TWO are best practices for optimizing write performance in Cloud Bigtable?

Accepted Answer

Group multiple mutations into a single request. Bigtable batches mutations into a single RPC request, reducing network round trips and improving throughput. Sending individual mutations incurs per-request overhead, so grouping them into a single atomic or non-atomic batch (via `MutateRows` or client-side batching) significantly increases write throughput.

Answer

Use short row keys to reduce storage size

Answer

Use the Dataflow Bulk Import API for real-time writes

Answer

Increase replication lag to allow more time for writes

Question 11

A DevOps engineer is using Terraform and wants to reference outputs from another Terraform configuration that manages networking. Which approach should they use?

Accepted Answer

Use `terraform_remote_state` data source pointing to the networking project's GCS state. The `terraform_remote_state` data source is the correct approach because it allows one Terraform configuration to securely read the latest state outputs (e.g., VPC IDs, subnet CIDRs) from another configuration's state file stored in a remote backend like Google Cloud Storage (GCS). This avoids hardcoding or manual copying of values, ensures consistency, and supports dependency management across configurations. By pointing to the networking project's GCS state, the DevOps engineer can dynamically reference outputs like `networking.vpc_id` without exposing sensitive data in environment variables or variable files.

Answer

Use Cloud Shell environment variables

Answer

Use Terraform Cloud's variable sets

Answer

Hardcode the networking values in variables

Question 12

A Cloud Spanner database needs to add a column 'discount' to the 'Products' table without any downtime. The table is actively used. What is the correct approach?

Accepted Answer

Execute ALTER TABLE Products ADD COLUMN discount FLOAT64. Cloud Spanner supports online schema changes via ALTER TABLE without downtime. The operation is performed asynchronously in the background, allowing the table to remain fully available for reads and writes. The new column 'discount' is automatically populated with NULL for existing rows. Option A is incorrect because creating a new table and copying data introduces downtime and complexity. Option C is incorrect because a secondary index cannot add a column; it only indexes existing columns. Option D is incorrect because a generated column is derived from other columns, not used to add a new independent column.

Answer

Create a new table with the column and copy data over

Answer

Create a secondary index that includes the new column

Answer

Define a generated column based on an existing column

Question 13

Your organization uses Cloud Spanner for a global application with strong consistency requirements. You need to design a table schema to avoid hot spots while supporting queries that join two related entities (e.g., Customers and Orders). Which THREE design choices should you implement? (Choose three)

Accepted Answer

Use hash prefixes or random UUIDs as the primary key to distribute writes.. To avoid hot spots and support efficient joins in Cloud Spanner, three design choices are recommended. First, use hash prefixes or random UUIDs as the primary key (B) to distribute writes evenly across nodes, preventing hot spots caused by monotonically increasing keys. Second, create secondary indexes with the INTERLEAVE IN PARENT option (D) to store index data alongside the parent table, reducing read latency for queries that filter by the indexed columns. Third, use interleaved tables (E) to store related rows (e.g., Customers and Orders) physically together, enabling efficient joins without cross-node lookups. These choices leverage Spanner's distributed architecture for scalability and strong consistency.

Answer

Use monotonically increasing keys (e.g., auto-increment) for easy ordering.

Answer

Disable strict consistency to allow faster writes.

Question 14

A company has a Cloud SQL for MySQL instance with automated backups enabled. They need to restore the database to a specific timestamp from 2 days ago. The backup retention is set to 7 days. How should they perform this restore?

Accepted Answer

Use gcloud sql instances clone with the --point-in-time flag and the desired timestamp. Cloud SQL for MySQL supports point-in-time recovery (PITR), which allows you to restore a database to a specific timestamp within the backup retention period (here, 7 days). The `gcloud sql instances clone` command with the `--point-in-time` flag creates a new instance that reflects the database state at the exact requested timestamp, leveraging the transaction logs retained by automated backups.

Answer

Export the database and import it into a new instance

Answer

Create an on-demand backup and restore from that backup

Answer

Use gcloud sql instances restore-backup with the backup ID from 2 days ago

Question 15

A global gaming company uses Cloud Spanner for user profiles and game state. They have a single-region instance in us-central1. Recently, they launched in Europe and notice high latency for European users. They also need to ensure data locality compliance (GDPR). The database is heavily written with throughput spikes. They want to minimize latency without sacrificing write throughput. They consider adding a secondary index on region column. What is the best course of action?

Accepted Answer

Create a multi-region configuration spanning US and Europe, and modify application to read from closest region.. A multi-region Cloud Spanner configuration with regional endpoints allows the application to read from the closest region, reducing latency for European users while maintaining strong consistency and write throughput. Spanner's multi-region configurations use synchronous replication across all regions, ensuring GDPR compliance by keeping European user data within Europe for reads. Write throughput is not sacrificed because Spanner's architecture is designed for high scalability and uses synchronous replication efficiently.

Answer

Shard the database by region into separate Spanner instances.

Answer

Use Cloud CDN to cache read data.

Answer

Create a read-only replica in Europe using Cloud Spanner's read replica feature.

Question 16

A company uses BigQuery with a table 'orders' that has a column 'items' of type ARRAY>. An analyst needs to find orders that contain a specific product, 'ABC'. Which query is most efficient?

Accepted Answer

SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM UNNEST(items) WHERE product_id = 'ABC'). It uses a correlated subquery with `UNNEST` and `EXISTS`, which stops scanning as soon as a matching product_id is found within each row's array. This is the most efficient pattern for checking array membership in BigQuery, as it avoids unnecessary row multiplication and leverages short-circuit evaluation.

Answer

SELECT * FROM orders WHERE ARRAY_LENGTH(items) > 0

Answer

SELECT * FROM orders WHERE 'ABC' IN UNNEST(items)

Answer

SELECT o.*, item FROM orders o, UNNEST(items) item WHERE item.product_id = 'ABC'

Question 17

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is designed for exactly this use case — petabyte-scale, low-latency (single-digit ms), high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is optimised for analytics (seconds-to-minutes latency), Cloud SQL is for OLTP (limited to tens of thousands of QPS), and Firestore is for document data with hierarchical structure.

Answer

Firestore

Answer

Cloud Spanner

Answer

BigQuery

Question 18

Your company runs an e-commerce platform on Google Cloud. The platform uses Cloud SQL for MySQL to store product inventory. The inventory table has the following schema: CREATE TABLE inventory (product_id INT PRIMARY KEY, quantity INT, last_updated TIMESTAMP) ENGINE=InnoDB. The application performs frequent updates on quantity for a subset of popular products. Recently, you have noticed increased deadlock errors during peak hours. The application uses REPEATABLE READ isolation level. You suspect that the schema design is contributing to locking contention. After analyzing the workload, you find that the updates often involve incrementing or decrementing quantity by small amounts and are mostly on the same set of popular products. What would be the best course of action to reduce deadlocks without compromising data integrity?

Accepted Answer

Rewrite the update query to use atomic operations (e.g., UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?) without pre-fetching the current value.. The application's pattern of reading the current quantity before updating (e.g., SELECT quantity FROM inventory WHERE product_id = ?, then UPDATE inventory SET quantity = ? WHERE product_id = ?) causes gap locks and deadlocks under REPEATABLE READ. By using an atomic UPDATE (UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?), the database performs the update without a prior read, reducing lock contention. This maintains data integrity because the subtraction is atomic and accurate. Option B is wrong because MyISAM does not support transactions or row-level locking, which compromises data integrity. Option C is wrong because partitioning does not reduce locking contention on the same rows; it only improves data management. Option D is wrong because reducing isolation to READ COMMITTED may reduce some locking but introduces non-repeatable reads and does not address the fundamental read-before-write pattern that causes deadlocks.

Answer

Change the engine to MyISAM to avoid row-level locking.

Answer

Partition the inventory table by product_id range to spread the load.

Answer

Reduce the isolation level to READ COMMITTED to reduce locking.

Question 19

Refer to the exhibit. The query used DATE_TRUNC(order_date, MONTH) as month. order_date is a TIMESTAMP column. What is the data type of the month column in the result?

Accepted Answer

TIMESTAMP. In BigQuery (the SQL engine for the PCDE exam), DATE_TRUNC with a TIMESTAMP input and MONTH granularity returns a TIMESTAMP value, not a DATE or DATETIME. The function truncates the timestamp to the first day of the month at 00:00:00 UTC, preserving the TIMESTAMP data type. Therefore, the month column in the result is of type TIMESTAMP.

Answer

STRING

Answer

DATE

Answer

DATETIME

Question 20

A team uses Cloud Deploy with a delivery pipeline that has dev, staging, and prod targets. They want to automatically deploy to staging after a successful deployment to dev, but require manual approval before promoting to prod. How should they configure this?

Accepted Answer

Set the prod target to require approval in Cloud Deploy. Cloud Deploy supports a 'require approval' setting on delivery pipeline targets. By enabling this on the prod target, the pipeline will automatically promote from dev to staging (since no approval is required there) but will pause before promoting to prod, waiting for a manual approval action in the Cloud Deploy console or via the API.

Answer

Create a Cloud Build trigger that runs after dev promotion

Answer

Configure Cloud Deploy with a canary strategy for prod

Answer

Use a scheduled Cloud Build to check dev status and then deploy to staging