PCDE Practice Test 2 — 25 Questions

Question 1

A company wants to implement a disaster recovery plan for their AlloyDB database. They need automatic failover with minimal data loss and RTO under 30 seconds. Which configuration should they use?

Accepted Answer

Enable high availability (HA) on the AlloyDB cluster, which provisions a standby in a different zone.. AlloyDB high availability (HA) provisions a standby instance in a different zone within the same region, with automatic failover that typically completes in under 30 seconds and uses synchronous replication to the standby, ensuring minimal data loss. This is the native, supported configuration for automatic failover with sub-30-second RTO and near-zero RPO.

Answer

Configure a cross-region read replica and promote it manually during a disaster.

Answer

Deploy AlloyDB in a single zone without HA.

Answer

Use AlloyDB with multiple read pools and a custom failover script.

Question 2

A company wants to store and analyze time-series metrics from thousands of servers. The data is write-heavy with occasional reads of recent data. They need low-latency writes and the ability to scan large ranges later. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Bigtable is designed for time-series data, high write throughput, and efficient range scans. It can handle large volumes of time-series data with low latency.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud SQL

Question 3

A Cloud Bigtable instance stores time-series data with a row key format: [metric_id]#[timestamp]. The team notices read throughput is low when scanning a metric over a time range. What is the likely cause?

Accepted Answer

All rows for a given metric are stored in a single tablet causing a hotspot.. The row key format [metric_id]#[timestamp] causes all rows for the same metric_id to share the same lexicographic prefix. Cloud Bigtable stores rows in sorted order by row key, so all rows for a given metric are co-located in a single tablet. When scanning a time range for that metric, all read requests hit the same tablet, creating a hotspot that limits throughput to the capacity of a single node.

Answer

Too many column families in the schema.

Answer

The number of nodes is insufficient.

Answer

Replication factor is set too low.

Question 4

A Cloud Spanner query is performing a join between two tables that are frequently accessed together. Which schema design can optimize this query?

Accepted Answer

Use interleaved tables. Interleaved tables store child rows physically with parent rows, improving locality for parent-child joins.

Answer

Use a foreign key constraint

Answer

Create a secondary index

Answer

Denormalize the data into a single table

Question 5

You want to ensure that a critical deployment on GKE has minimal downtime during rolling updates. You also want to ensure that at least 2 pods are always available. Which Kubernetes resource should you configure?

Accepted Answer

PodDisruptionBudget with minAvailable: 2. PodDisruptionBudget (PDB) specifies the minimum number or percentage of pods that must be available during voluntary disruptions like rolling updates. Setting minAvailable: 2 ensures at least 2 pods are running during updates. HPA, VPA, and cluster autoscaler do not control pod availability during updates.

Answer

Cluster autoscaler with minNodes: 2

Answer

HorizontalPodAutoscaler with minReplicas: 2

Answer

VerticalPodAutoscaler with updateMode: Auto

Question 6

A company runs a Cloud SQL PostgreSQL instance for a SaaS application. They notice that the database CPU is consistently above 90% during peak hours, and queries slow down. The application is read-heavy and can tolerate some replication lag. Which action would MOST effectively reduce CPU load on the primary?

Accepted Answer

Create read replicas and route read-only queries to them. Creating one or more read replicas and offloading SELECT queries to them reduces CPU load on the primary instance. Adding more vCPU to the primary (scaling up) increases capacity but is more expensive and may not be as cost-effective as read replicas. PgBouncer helps with connection overhead but not CPU load from queries. Query optimization helps but may not reduce load enough if the volume is high.

Answer

Use EXPLAIN ANALYZE to optimize the slowest queries

Answer

Increase the number of vCPUs on the primary instance

Answer

Enable connection pooling with PgBouncer via Cloud SQL Auth Proxy

Question 7

You need to set up disaster recovery for a Cloud SQL for PostgreSQL instance. The primary instance is in us-central1, and you want a standby in us-west1 that can be promoted to a standalone instance during a regional outage. The solution must minimize data loss and recovery time. Which approach should you take?

Accepted Answer

Create a cross-region read replica in us-west1. During a disaster, promote the replica to a standalone instance.. Creating a cross-region read replica in us-west1 and promoting it during a disaster is the correct approach for Cloud SQL for PostgreSQL. This provides a live standby with asynchronous replication, minimizing data loss (low RPO) and recovery time (low RTO) because promotion is quick and the replica is continuously updated.

Answer

Configure cross-region automated backups with a retention of 7 days. In the event of a disaster, restore the latest backup to a new instance in us-west1.

Answer

Set up an on-premise PostgreSQL instance and configure streaming replication from Cloud SQL to the on-premise instance.

Answer

Use gcloud sql instances create with a backup configuration pointing to us-west1, but this does not create a live standby.

Question 8

A company is designing a data model for a BI dashboard that requires real-time updates and historical analysis. Which THREE practices should be followed?

Accepted Answer

Use clustering on frequently filtered columns.. Clustering on frequently filtered columns in BigQuery organizes data into blocks based on the values of those columns, allowing queries with filters on those columns to skip irrelevant blocks entirely. This reduces the amount of data scanned, improving query performance and lowering costs, which is critical for a BI dashboard that needs real-time updates and fast historical analysis.

Answer

Create a separate table for each day's data.

Answer

Use the default BigQuery table expiration setting.

Question 9

A data engineer is designing a BI solution in BigQuery for a retail chain. They need to support queries that aggregate sales by store, product, and date across millions of transactions. The data is loaded in near real-time from Cloud Pub/Sub. Which table design provides the best balance of query performance and cost?

Accepted Answer

Partition by date, cluster by store_id and product_id. Partitioning by date enables BigQuery to prune entire partitions when querying by date range, which is the most common filter in sales aggregation queries. Clustering on store_id and product_id further reduces the data scanned within each partition by colocating rows with similar store and product values. This design minimizes both query cost (bytes billed) and latency, while supporting near-real-time ingestion from Pub/Sub without requiring table rewrites.

Answer

Partition by store_id, cluster by product_id

Answer

Unpartitioned table with clustering on store_id and product_id

Answer

Use materialized views with aggregation on store_id, product_id, and date

Question 10

An engineer needs to monitor the replication lag of a Cloud SQL read replica. Which metric should they use in Cloud Monitoring?

Accepted Answer

replication_lag. The `replication_lag` metric in Cloud Monitoring directly measures the time delay between a primary Cloud SQL instance and its read replica, reported in seconds. This is the standard metric for monitoring how far behind the replica is in applying changes from the primary, which is critical for ensuring read-after-write consistency and data freshness.

Answer

sent_bytes_count

Answer

disk_bytes_used

Answer

cpu_utilization

Question 11

A startup needs a database for its new web application that will serve a global user base. The application requires low-latency reads and writes (single-digit milliseconds), strong consistency, and the ability to handle high concurrency (thousands of transactions per second). Which Google Cloud database service should the startup choose?

Accepted Answer

Cloud Spanner. Cloud Spanner offers global distribution, strong consistency, and low-latency transactions at high concurrency. It is designed for globally scaled OLTP workloads.

Answer

Cloud SQL for MySQL

Answer

Bigtable

Answer

Firestore

Question 12

Match each Cloud SQL high-availability feature to its description.

Accepted Answer

Automatic failover: Automatically switches to a standby instance in a different zone if the primary instance fails, ensuring minimal downtime.. Automatic failover switches to a standby zone on failure, read replicas provide read-only copies for scaling, and point-in-time recovery allows restoring to any time within retention. Common confusions mix automatic failover with read replicas.

Answer

Automatic failover: Provides read-only copies of the primary instance to distribute read load.

Answer

Read replicas: Automatically switches to a standby instance in a different zone if the primary fails.

Question 13

A team has an SLO of 99.9% availability over a 30-day period. How many minutes of downtime does the error budget allow per month?

Accepted Answer

43 minutes. An SLO of 99.9% availability allows 0.1% unavailability. Over a 30-day month (30 × 24 × 60 = 43,200 minutes), 0.1% equals 43,200 × 0.001 = 43.2 minutes, commonly rounded to 43 minutes. This is the error budget — the maximum downtime permitted while still meeting the SLO.

Answer

4.32 minutes

Answer

432 minutes

Answer

4,320 minutes

Question 14

Refer to the exhibit. The query joins two large tables and aggregates results. Which optimization would most likely reduce the high shuffle bytes in Stage 3?

Accepted Answer

Add a WHERE clause to filter rows before the join.. Adding a WHERE clause before the join reduces the amount of data that needs to be shuffled across the network in Stage 3. In Spark SQL (the engine behind Databricks and many PCDE scenarios), predicate pushdown filters rows early, minimizing the input to the join and subsequent aggregation, which directly reduces shuffle bytes.

Answer

Ensure both tables are clustered on the join key.

Answer

Use a broadcast join hint to force one table to be broadcast.

Answer

Add an ORDER BY clause to sort the data before aggregation.

Question 15

Order the steps to perform a disaster recovery drill for a Cloud Spanner database using backups.

Accepted Answer

Create backup -> Restore to another region -> Verify data integrity -> Update application configuration -> Test application failover. The correct sequence for a Cloud Spanner disaster recovery drill using backups is: first create a backup, then restore it to another region. After restoration, verify data integrity, then update application configurations to point to the restored database, and finally test the application to ensure failover works. This order ensures data consistency and minimizes risk.

Answer

Restore to another region -> Create backup -> Verify data integrity -> Update application configuration -> Test application failover

Answer

Create backup -> Verify data integrity -> Restore to another region -> Update application configuration -> Test application failover

Answer

Create backup -> Restore to another region -> Update application configuration -> Verify data integrity -> Test application failover

Question 16

An organization uses Terraform with a GCS backend for state. They want to implement a GitOps workflow where changes merged to the main branch are automatically applied. The CI/CD pipeline uses a service account with Workload Identity Federation. Which THREE components are required? (Choose three.)

Accepted Answer

A service account with permissions to modify resources in the target projects.. Option C is correct because the pipeline's service account must hold IAM roles (e.g., roles/editor or specific resource-level roles) on the target projects so that Terraform apply can create, modify, and delete the managed resources. Option D is correct because a GitOps workflow requires a Git repository holding the Terraform configurations; merges to main are the trigger and the repo is the source of truth for desired state. Option E is correct because an automated CI/CD system such as Cloud Build is needed to run terraform init, plan, and apply on merge to main, using Workload Identity Federation to impersonate the service account without long-lived keys. Option A is not required: while GCS object versioning is a recommended safeguard for state recovery, it is not a mandatory component for the GitOps apply workflow itself. Option B is not required: Terraform Cloud is an alternative execution platform and is unnecessary when the pipeline runs Terraform directly against a GCS backend.

Answer

A Cloud Storage bucket with object versioning enabled for Terraform state.

Answer

A Terraform Cloud workspace configured with the same GCS backend.

Question 17

You are using Cloud Spanner and need to add a new column to an existing table. The table has millions of rows and must remain fully available for reads and writes during the schema change. What is the correct approach?

Accepted Answer

Use gcloud spanner databases ddl update with the ALTER TABLE statement; Spanner applies the change online.. Cloud Spanner supports online schema changes using DDL statements executed via `gcloud spanner databases ddl update`. The ALTER TABLE statement adds the column without locking the table or requiring downtime; Spanner applies the change in the background while the table remains fully available for reads and writes. This is the standard, supported method for schema modifications in Spanner.

Answer

Export the table using Dataflow, add the column locally, then import the data back.

Answer

Use gcloud spanner instances update to modify the table schema.

Answer

Take the instance offline, run the ALTER TABLE, then bring it back online.

Question 18

A company is designing a star schema for a BI dashboard that tracks sales performance. The dashboard needs to aggregate sales by product, store, and date. Which schema design is most appropriate?

Accepted Answer

Create a fact table with foreign keys to dimension tables for product, store, and date. A star schema is the standard dimensional model for BI dashboards: a central fact table (sales measures) joined via foreign keys to denormalized dimension tables (product, store, date). This keeps joins simple and shallow, which lets the query engine scan and aggregate quickly for slice-and-dice reporting. Option C matches that pattern exactly, with each dimension represented by its own table referenced from the fact table.

Answer

Store all data in a single table using nested JSON arrays for product and store details

Answer

Create a single wide table with all attributes (product, store, date, sales)

Answer

Use a fully normalized snowflake schema with separate tables for each level of hierarchy

Question 19

A data analyst runs a query joining several large tables and gets 'Resources exceeded' error. They need to reduce memory usage without changing the query logic. What should they do?

Accepted Answer

Use a subquery to pre-aggregate the largest table before joining. Pre-aggregating the largest table in a subquery reduces the amount of data that needs to be shuffled and joined in memory. In BigQuery, this minimizes the bytes processed and the memory footprint of the join operation, directly addressing the 'Resources exceeded' error without altering the overall query logic.

Answer

Use APPROX_COUNT_DISTINCT for counting distinct values

Answer

Increase the slot reservation

Answer

Use SELECT * in the subquery to ensure all columns are available

Question 20

A company uses Cloud Build to build a multi-module Maven project. They want to run unit tests for module A and integration tests for module B in parallel. In cloudbuild.yaml, how should they configure the steps to run in parallel?

Accepted Answer

steps:
- id: 'unit'
  waitFor: ['-']
  ...
- id: 'integration'
  waitFor: ['-']
  .... In Cloud Build, setting `waitFor: ['-']` on a step makes it start immediately without waiting for any other step, effectively allowing both the 'unit' and 'integration' steps to run in parallel. This configuration meets the requirement to run unit tests for module A and integration tests for module B concurrently.

Answer

steps:
- id: 'unit'
  ...
- id: 'integration'
  waitFor: ['-']
  ...

Answer

steps:
- id: 'unit'
  waitFor: ['previous']
  ...
- id: 'integration'
  waitFor: ['unit']
  ...

Answer

Steps run sequentially by default; parallelism is not supported.