PCDE Hard Questions — 25 Difficult Questions

Question 1

An engineer is designing a Cloud Spanner table for a global user activity tracking system with high write throughput. Which primary key design is BEST to avoid hotspots?

Accepted Answer

UUID string (generated by application). Using a UUID or hash-prefixed key distributes writes evenly across nodes, preventing hotspots that occur with monotonically increasing keys.

Answer

Monotonically increasing integer (INT64) with auto-increment

Answer

Composite key with user_id as first part

Answer

Timestamp as primary key

Question 2

An organization uses Cloud Spanner and needs to add a new column to an existing table without downtime. The table has billions of rows and is heavily used. What is the recommended approach to add the column?

Accepted Answer

Use ALTER TABLE ADD COLUMN statement. Cloud Spanner schema changes are online and non-blocking, so you can simply use ALTER TABLE to add the column; it will not cause downtime.

Answer

Create a new table with the column, then copy data and rename

Answer

Add the column using gcloud command and set a downtime window

Answer

Backup the database, add column, then restore

Question 3

An e-commerce platform uses Cloud Spanner as its database. The orders table has a monotonically increasing order_id as the primary key, and the team observes high write latency during peak hours. Which design change would BEST distribute write load across nodes?

Accepted Answer

Change the primary key to a UUID string. Using a UUID (or a hash prefix) as the primary key or as a leading part of a composite key prevents hotspotting on a single tablet server by distributing writes across the cluster.

Answer

Add a secondary index on order_id

Answer

Increase the number of nodes

Answer

Use interleaving with a parent table

Question 4

Refer to the exhibit. This DDL is used to create a table in Cloud Spanner. The table will be used for storing user data with high write throughput. What is one performance issue with this table design?

Accepted Answer

The primary key is a monotonically increasing integer. Using a monotonically increasing integer as the primary key in Cloud Spanner causes all writes to be directed to a single split (tablet), creating a hotspot that severely limits write throughput. Cloud Spanner distributes splits across nodes based on key ranges, and sequential keys prevent this distribution, leading to performance degradation under high write load.

Answer

The Name column is of type STRING(MAX)

Answer

There are no secondary indexes

Answer

The table is not interleaved with another table

Question 5

Your Cloud Bigtable instance uses HDD storage. You need to change to SSD to improve read performance. What is the correct procedure?

Accepted Answer

Create a new cluster with SSD storage in the same instance, replicate data, then delete the old cluster.. Cloud Bigtable does not support changing the storage type (HDD to SSD) on an existing cluster. Option A is incorrect because there is no online migration feature. Option B is incorrect because the 'gcloud bigtable instances update' command does not allow changing the storage type. Option D is possible but involves more downtime and complexity. Option C is correct: create a new cluster with SSD in the same instance, replicate data using replication, then delete the old HDD cluster. This minimizes downtime and is the recommended approach.

Answer

Use Cloud Bigtable's online storage migration feature to switch to SSD without downtime.

Answer

Update the instance's storage type using 'gcloud bigtable instances update' with the --storage-type flag.

Answer

Export the HDD cluster's data to Cloud Storage, then import into a new SSD cluster.

Question 6

An engineer is migrating a 20 TB PostgreSQL database to AlloyDB using DMS continuous migration. The full dump phase is taking longer than expected. Which THREE factors could affect the duration of the full dump phase? (Choose THREE.)

Accepted Answer

The number of tables and indexes on the source.. Full dump time depends on data volume, network bandwidth, CPU/memory of the source, and configuration (e.g., parallel workers).

Answer

The version of the source PostgreSQL database.

Answer

The choice of SSL or non-SSL connection.

Question 7

A financial services company is designing a Cloud Spanner schema for a trading system. They have two main entities: 'accounts' and 'transactions'. Each account has many transactions, and queries almost always retrieve transactions for a specific account. Which TWO schema design strategies should they employ?

Accepted Answer

Ensure the primary key of transactions includes the account_id as the first part.. Cloud Spanner distributes rows across splits based on the primary key prefix. By making `account_id` the first part of the transactions table primary key, all transactions for a given account are co-located, enabling efficient range scans and point lookups without cross-node shuffling.

Answer

Use a secondary index on transactions.account_id.

Answer

Define a foreign key constraint from transactions to accounts.

Answer

Store transactions as a JSON array of repeating fields within the account record.

Question 8

A Cloud Spanner database is experiencing high CPU utilization on one node. Users report slow queries. The table uses a UUID primary key. What is the most effective action?

Accepted Answer

Use a hash key prefix to distribute writes.. A hash key prefix can distribute writes more evenly across nodes, preventing hot spots that cause high CPU on a single node. Adding nodes may not resolve a hot spot.

Answer

Add a database index on frequently queried columns.

Answer

Convert the UUID to a monotonically increasing integer.

Answer

Increase the number of nodes in the instance.

Question 9

A company uses Cloud Monitoring to create an SLO for a service. They want to define a request-based SLO with a ratio of good requests to valid requests. Which of the following is a valid way to define the SLI in Cloud Monitoring SLOs?

Accepted Answer

Select a metric for good requests and a separate metric for total valid requests, then define the SLI as good-request-count / valid-request-count. Cloud Monitoring SLOs require two metrics: one for good events and one for total valid events. The ratio is good/total. The correct configuration uses separate metrics for good and valid requests.

Answer

Use a distribution metric for latency and set a threshold for good latency

Answer

Create a custom metric for requests and use Cloud Monitoring's built-in availability SLI for HTTP services

Answer

Use a single metric that indicates success (1 for success, 0 for failure) and set a threshold filter

Question 10

Your Cloud SQL for MySQL instance is experiencing intermittent performance degradation. You suspect that the issue is due to a sudden spike in connections from a specific application. Which metric and monitoring approach would best help you correlate the connection spike with performance degradation?

Accepted Answer

Monitor 'cloudsql.googleapis.com/database/mysql/threads/threads_connected' and correlate with CPU utilization and query latency.. The 'threads_connected' metric directly measures the number of active connections to the MySQL instance. Correlating this with CPU utilization and query latency allows you to pinpoint whether a sudden spike in connections is causing resource contention and degraded query performance, which is the exact scenario described.

Answer

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Answer

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Answer

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Question 11

You are designing disaster recovery for a globally distributed application using Cloud Spanner with a multi-region configuration. The application requires an RPO of 0 and an RTO of less than 5 seconds in the event of a full region failure. Which Spanner configuration provides these guarantees?

Accepted Answer

A multi-region Spanner instance with two read-write regions and one witness region.. A multi-region configuration with a leader region and at least two voting regions provides synchronous replication and automatic failover. Regional failover occurs within seconds, preserving zero data loss.

Answer

A single-region Spanner instance with a read replica in another region.

Answer

A multi-region Spanner instance with one read-write region and two read-only regions.

Answer

A dual-region Spanner instance using default leader placement.

Question 12

A multinational corporation uses BigQuery to combine sales data from multiple regions. Each region stores data in separate tables with identical schemas. The BI team needs to create a unified view for a dashboard that queries data by region and product. Which TWO strategies should the data engineer implement to optimize query performance and reduce costs?

Accepted Answer

Partition the table by date and cluster by region and product. Partitioning the table by date and clustering by region and product enables partition pruning and clustering block elimination, reducing data scanned and costs. Option B is correct because wildcard tables with a filter on _TABLE_SUFFIX allow BigQuery to query only the required region tables, minimizing data read and cost. The other options are not optimal: C (UNION ALL view) does not reduce data scanned unless underlying tables are partitioned/clustered; D (materialized views for each region) adds complexity and cost; E (single table with region column) may still scan all data if not partitioned/clustered properly, and does not leverage region-specific tables.

Answer

Create a view with UNION ALL of all region tables

Answer

Create materialized views for each region

Answer

Store all data in a single table with region as a column

Question 13

A company is migrating a large Oracle database to Cloud Spanner. They need to define the schema for relational tables with foreign keys. Which THREE considerations are important when designing the Spanner schema? (Choose three.)

Accepted Answer

Use INTERLEAVE tables to model parent-child relationships.. Options B, D, and E are correct. Interleaved tables (B) model parent-child relationships and optimize joins and data locality. Secondary indexes (D) are essential for efficient queries on non-key columns. Cloud Spanner enforces foreign key constraints (E) to maintain referential integrity. Option A is incorrect because primary key columns cannot be NULL. Option C is incorrect because composite primary keys are commonly used in Spanner.

Answer

Use NULL values in primary key columns to allow optional fields.

Answer

Avoid using composite primary keys; use single-column keys instead.

Question 14

A team uses Terraform with a GCS backend for state. They want to use remote state from another project to read output values. What Terraform configuration element is used to retrieve outputs from a different state file?

Accepted Answer

`data.terraform_remote_state`. The `data.terraform_remote_state` data source is the correct Terraform configuration element used to retrieve output values from a different state file stored in a remote backend, such as GCS. It reads the state data from the specified backend configuration and exposes the outputs via the `outputs` attribute, allowing cross-project or cross-workspace state access without requiring direct module dependencies.

Answer

`module.terraform_remote_state`

Answer

`output.terraform_remote_state`

Answer

`resource.terraform_remote_state`

Question 15

A BI team uses BigQuery to report on customer orders. The 'customers' dimension table is updated nightly with Type 2 Slowly Changing Dimensions (SCD). However, some reports show incorrect historical aggregates because the fact table references only the current customer key. Which approach resolves this issue?

Accepted Answer

Store the surrogate customer key from the dimension table in the fact table at transaction time. With Type 2 SCD, each customer row has a unique surrogate key that represents a specific version of the customer's attributes over time. Storing that surrogate key in the fact table at transaction time ensures that historical facts are permanently linked to the correct customer attributes as they existed at the time of the order. This prevents incorrect aggregates when the dimension table is updated, as the fact table will always join to the precise version of the customer record that was active when the transaction occurred.

Answer

Update the fact table nightly to replace old customer keys with the current key

Answer

Denormalize customer attributes into the fact table

Answer

Use the natural customer ID in the fact table and join with the dimension using a BETWEEN condition on effective dates

Question 16

A company uses Cloud Build with GitHub triggers. The build pipeline runs tests, builds a Docker image, and pushes it to Artifact Registry. Recently, builds started failing with '403 Forbidden' when pushing to Artifact Registry. The Cloud Build service account has the Artifact Registry Writer role. What else could be causing the failure?

Accepted Answer

The Artifact Registry API is not enabled in the project.. If the Artifact Registry API is not enabled for the project, any attempt to push a Docker image to Artifact Registry will fail with a 403 Forbidden error, regardless of the service account's IAM roles. The Cloud Build service account may have the Artifact Registry Writer role, but without the underlying API enabled, the service cannot interact with the Artifact Registry service at all. Enabling the `artifactregistry.googleapis.com` API is a prerequisite for any API calls to succeed.

Answer

The Docker credential helper is not configured in the build step.

Answer

The repository is in a different region than the Cloud Build worker.

Answer

The Cloud Build service account does not have the 'iam.serviceAccountUser' role on the Compute Engine default service account.

Question 17

You are a cloud database engineer for a financial services firm. The firm uses Cloud SQL for PostgreSQL to support a BI reporting tool. The main table 'transactions' has 500 million rows and is growing daily. Reports often run aggregations over date ranges and group by account_id. The 'transactions' table has indexes on date and account_id separately. Despite these indexes, the reporting queries are slow, often taking over 30 minutes. The database is deployed on a high-memory machine with 32 vCPUs and 256 GB RAM. You notice that the queries perform sequential scans instead of using indexes. What is the most likely reason, and what single change would you make to improve performance?

Accepted Answer

Create a composite index on (date, account_id). The reporting queries filter by date ranges and group by account_id, but the existing separate indexes on date and account_id cannot be combined efficiently for both conditions. PostgreSQL's query planner often chooses a sequential scan over using two separate indexes because it estimates that reading the entire table is cheaper than the bitmap scan overhead of combining them. A composite index on (date, account_id) allows the database to directly locate rows matching the date range and then access them in account_id order, eliminating the need for a separate sort or join step.

Answer

Partition the table by date using PostgreSQL declarative partitioning

Answer

Increase the shared_buffers setting to 128 GB

Answer

Disable sequential scans by setting enable_seqscan = off

Question 18

You administer a Cloud SQL for PostgreSQL instance running with 4 vCPUs and 15 GB of memory. The application frequently runs complex read-only reporting queries during business hours. Recently, the database started throwing 'out of memory' errors, and the instance's memory usage is consistently above 90%. You have enabled the pgtune recommendations and applied them. The workload is read-heavy and does not require a high write throughput. Which change would most effectively reduce memory pressure while maintaining query performance?

Accepted Answer

Scale up the instance to a higher memory tier, such as 8 vCPUs and 30 GB of memory.. Scaling up the instance to a higher memory tier (8 vCPUs, 30 GB) directly increases available memory, addressing the 'out of memory' errors and high memory usage while maintaining read performance. Option A (pgzstd compression) does not significantly reduce memory consumption; it helps with storage or network I/O. Option B (PgBouncer) reduces memory per connection but does not reduce overall memory needs when the database itself requires more memory for caching and operations. Option D (decreasing shared_buffers) sacrifices cache performance, likely slowing queries, and may not free enough memory to resolve the issue.

Answer

Enable the pgzstd extension to compress data in memory.

Answer

Set up connection pooling using PgBouncer to limit the number of concurrent connections.

Answer

Decrease the shared_buffers configuration parameter to free memory for other uses.

Question 19

A team uses Cloud Deploy with a delivery pipeline that deploys to GKE clusters across dev, staging, and prod targets. They want to automatically roll back a release if the canary deployment in staging fails to meet a defined service-level objective (SLO) for error rate. Which Cloud Deploy feature enables this?

Accepted Answer

Configure canary deployment with the '--canary-percentage' flag and enable SLO verification in the delivery pipeline.. Cloud Deploy's canary deployment strategy supports SLO verification via the `--canary-percentage` flag combined with a `canaryDeployment` configuration that includes a `verify` phase. This allows the pipeline to automatically roll back the release if the canary fails to meet the defined error rate SLO, without manual intervention or external services.

Answer

Use preDeploy and postDeploy hooks to run Cloud Run jobs that check error rate and roll back.

Answer

Use Binary Authorization with a custom attestor that checks error rate.

Answer

Set an approval gate on the staging target that requires manual review.

Question 20

A company wants to reduce BigQuery query costs for their BI workloads. Which THREE actions effectively lower the amount of data processed per query? (Choose THREE.)

Accepted Answer

Use partitioned tables on date column. Partitioned tables in BigQuery allow queries to use the WHERE clause to filter on the partition column (e.g., a date column), so BigQuery can prune entire partitions from the scan. This directly reduces the amount of data read and billed, lowering query costs. Option A is correct because it is a primary cost-control mechanism in BigQuery.

Answer

Use LIMIT in subqueries to reduce output

Answer

Use SELECT * to avoid missing columns