PCDE Practice Test 2 — 30 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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.

Question 16

A developer wants to deploy a containerized application to Cloud Run with a requirement that the service has at least 2 instances always running to handle low-latency requests. Which flag should they use with gcloud run deploy?

Accepted Answer

--min-instances=2. The `--min-instances` flag in `gcloud run deploy` specifies the minimum number of container instances that must remain warm and ready to serve requests at all times. Setting `--min-instances=2` ensures Cloud Run keeps at least two instances always running, which eliminates cold starts and guarantees low-latency responses for incoming traffic.

Answer

--concurrency=2

Answer

--cpu-throttling

Answer

--max-instances=2

Question 17

Match each database migration term to its description.

Accepted Answer

Homogeneous migration: Migrating a database from one source engine to the same target engine.. The correct matches are: Homogeneous migration = same engine, Heterogeneous migration = different engine, Database Migration Service = Google Cloud's database migration tool. Common confusions involve swapping the definitions of homogeneous and heterogeneous, or misattributing DMS to application migration.

Answer

Homogeneous migration: Migrating a database to a different database engine.

Answer

Heterogeneous migration: Migrating a database to the same database engine.

Answer

Database Migration Service (DMS): A tool for migrating applications to compute engines.

Question 18

A Cloud Spanner database needs a new index added to support a new query pattern. The database is serving live traffic. Which approach should be taken?

Accepted Answer

Create the index using DDL statement 'CREATE INDEX' — it will be applied online without downtime.. Cloud Spanner supports online schema changes, including index creation, without downtime. The 'CREATE INDEX' DDL statement is applied asynchronously in the background, allowing the database to continue serving live traffic while the index is built. This is the correct approach because it avoids any interruption to the application.

Answer

Export the data, create a new database with the index, and import the data.

Answer

Stop the application, add the index, and restart.

Answer

Use gcloud spanner databases add-index command.

Question 19

A data engineer is designing a BigQuery schema for a dataset that will be used for both ad-hoc analysis and scheduled dashboards. They want to optimize costs and performance. Which three strategies should they consider? (Choose three.)

Accepted Answer

Use partitioning on a date column for time-based queries.. Partitioning on a date column (Option C) is correct because BigQuery uses the date column to prune partitions during query execution, significantly reducing the amount of data scanned and thus lowering costs and improving performance for time-based queries. This is a core optimization for both ad-hoc analysis and scheduled dashboards that frequently filter by date ranges.

Answer

Use wildcard tables with a suffix filter.

Answer

Store data in multiple tables per day.

Question 20

Which TWO actions are required to set up point-in-time recovery (PITR) for Cloud SQL for MySQL? (Choose 2)

Accepted Answer

Enable binary logging.

Answer

Set the 'log_bin' flag to ON in the database flags.

Answer

Create a read replica in a different region.

Answer

Configure a cross-region backup replica.