PCDE Practice Test 2 — 30 Questions

Question 1

A Cloud SQL for PostgreSQL instance needs to achieve a recovery point objective (RPO) of less than 1 minute and a recovery time objective (RTO) of less than 2 minutes in the event of a zone failure. Which disaster recovery strategy should be used?

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

Accepted Answer

Configure Cloud SQL HA (high availability) with a standby in a different zone.. Cloud SQL HA configuration provides automatic failover to a standby in a different zone within the same region, with RPO near zero and RTO typically under 60 seconds. Cross-region read replicas have RPO equal to replication lag (could be minutes) and RTO of minutes due to manual promotion. Cross-region backup restore has RPO equal to backup age (hours) and RTO of hours. Point-in-time recovery (PITR) is for within-region data recovery, not zone failure failover.

Answer

Perform regular backups to Cloud Storage and restore in a different zone.

Answer

Enable cross-region replication using a read replica.

Answer

Enable point-in-time recovery (PITR) with transaction log backups.

Question 2

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 3

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 4

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 5

The exhibit shows query metadata for a query that scans 10 GB. Given the table is 100 GB and partitioned by hire_date, why did the query scan 10 GB and not less?

Accepted Answer

The filter on hire_date is not selective enough to prune most partitions. Partition pruning in Google BigQuery depends on the selectivity of the filter predicate. If the filter on `hire_date` matches a large number of partitions (e.g., filtering on a range that covers 10 GB out of 100 GB), the query scans exactly those partitions. The table is 100 GB and partitioned by `hire_date`, so a 10 GB scan implies the filter pruned 90 GB of partitions but was not selective enough to reduce the scan further—e.g., the predicate may be a broad range or lack a precise equality condition.

Answer

Clustering on department is not being used because the query has ORDER BY

Answer

The query uses GROUP BY, which forces a full table scan

Answer

The table is not clustered properly

Question 6

A company runs Cloud SQL for PostgreSQL and wants to automatically increase storage when usage reaches a threshold. What should they enable?

Accepted Answer

Enable storage auto-increase in instance settings. Cloud SQL has a built-in 'auto-storage increase' feature that automatically adds storage when necessary, avoiding manual intervention.

Answer

Create a cron job to resize via gcloud

Answer

Configure automated backups

Answer

Use Active Assist recommendations to resize

Question 7

A team uses Kustomize to manage environment-specific configurations for their GKE deployments. They have a base overlay and overlays for dev, staging, and prod. Which command should they use to generate the final Kubernetes manifests for the staging environment?

Accepted Answer

kustomize build staging/. The `kustomize build staging/` command processes the Kustomize overlay located in the `staging/` directory, which includes a `kustomization.yaml` file that references the base overlay and applies environment-specific patches, configMapGenerator entries, and namespace settings. This generates the final Kubernetes manifests for the staging environment without applying them to a cluster. The `kustomize` CLI tool is the standard way to render Kustomize overlays, and it is the correct command for generating manifests as part of a CI/CD pipeline.

Answer

kubectl kustomize staging/

Answer

skaffold build --kustomize staging/

Answer

kubectl apply -k staging/

Question 8

A startup is using Firestore in Native mode for a real-time chat application. They want to design the schema for chat rooms and messages. Which TWO design patterns are recommended? (Choose two.)

Accepted Answer

Use a composite index on chat room ID and timestamp.. A composite index on chat room ID and timestamp is essential for efficiently querying messages in order within a specific chat room. Firestore requires composite indexes for queries that combine equality filters on one field (chat room ID) with an order on another (timestamp). Option E is correct because storing messages as documents in a subcollection under each chat room document is the recommended pattern for Firestore, as it allows scalable, independent message collections per room without hitting the 1 MiB document size limit.

Answer

Use arrays in the chat room document to store message IDs.

Answer

Store all messages in a single top-level collection with a field for chat room ID.

Answer

Use a separate top-level collection for each chat room.

Question 9

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 10

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 11

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 12

Refer to the exhibit. You receive an alert from this policy for a Cloud Spanner instance. Which action should you take first?

Accepted Answer

Add more nodes to the instance. The alert from the policy indicates that the Cloud Spanner instance is experiencing high CPU utilization, which is a sign of resource saturation. Adding more nodes increases the total compute and I/O capacity of the instance, directly alleviating the CPU bottleneck and improving throughput. This is the first and most immediate corrective action because Spanner's performance scales linearly with nodes, and other optimizations (like index or query tuning) are secondary to ensuring sufficient capacity.

Answer

Identify and remove unused indexes

Answer

Review the top queries by CPU usage in the Spanner console

Answer

Split large tables into smaller ones

Question 13

A data warehouse in BigQuery stores event logs with nested and repeated fields (e.g., page views within a session). Which schema type is optimal for storing this data?

Accepted Answer

Use ARRAY> columns for nested repeated data. Using ARRAY> columns allows storing nested repeated data natively in BigQuery, enabling efficient querying without joins. Option A (RECORD type columns) is a legacy approach not optimized for repeated data; arrays of structs are preferred. Option B (normalizing into separate tables) would require costly joins. Option D (storing as JSON strings) loses schema enforcement and query performance.

Answer

Use RECORD type columns for each nested level

Answer

Normalize into separate tables and join

Answer

Store as JSON strings and parse at query time

Question 14

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 15

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 16

A global e-commerce company is designing a Cloud Spanner schema for order processing. They need strong consistency across regions and high write throughput. Orders are identified by a globally unique order ID (UUID). Currently, they use the UUID as the primary key, but they observe write hotspots during peak hours. What primary key design change should they make to distribute writes more evenly?

Accepted Answer

Use a composite primary key starting with a hash of the order ID, followed by the order ID.. Using a composite primary key starting with a hash of the order ID distributes writes evenly across all Cloud Spanner nodes, preventing hotspots. Cloud Spanner uses the first column of the primary key to determine row distribution; a monotonically increasing UUID as the first column causes all new writes to land on the same tablet, creating a hotspot. By hashing the UUID first, writes are spread uniformly across the key space, while the order ID ensures uniqueness.

Answer

Use the timestamp of order creation as the primary key.

Answer

Use a sequential integer primary key with auto-increment.

Answer

Keep UUID as primary key but add a secondary index on a hash of the UUID.

Question 17

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 18

A data analyst reports that a BI dashboard query on BigQuery is taking over 30 seconds to execute. The table is partitioned by date and clustered by customer_id. The query filters on a specific date range and aggregates sales by customer. What is the most likely cause of the slow performance?

Accepted Answer

The query filters on a date column that is not the partition column, causing a full table scan.. The query is slow due to filtering on a date column that is not the partition column. Even though the table is partitioned by date, if the WHERE clause uses a different date column, BigQuery cannot perform partition pruning and must scan all partitions, leading to high latency. Option A is incorrect because the main issue is partition pruning, not clustering benefits. Option B is incorrect as no join is mentioned in the question. Option D is incorrect because BigQuery does not use primary keys or indexes.

Answer

The query does not include a filter on the clustering column, so clustering provides no benefit.

Answer

The query uses a LEFT JOIN that requires a broadcast join, increasing network overhead.

Answer

The table does not have a primary key, so BigQuery cannot use index scans.

Question 19

A company uses Cloud Build to build Docker images. They want to cache intermediate layers to speed up subsequent builds. The build runs on a private pool with access to Artifact Registry. Which caching approach should they use?

Accepted Answer

Use Kaniko with the --cache-repo flag pointing to an Artifact Registry repository.. Kaniko is the recommended tool for building Docker images in Cloud Build when using private pools, as it does not require a Docker daemon and supports layer caching via the `--cache-repo` flag. By pointing this flag to an Artifact Registry repository, Kaniko can push and pull cached intermediate layers, significantly speeding up subsequent builds. This approach works seamlessly with private pools and Artifact Registry, unlike the other options which are either invalid or not supported.

Answer

Use Google Cloud Build's built-in image optimization feature.

Answer

Enable Cloud Build's automatic caching by setting cache: true in cloudbuild.yaml.

Answer

Configure Docker cache import/export steps in cloudbuild.yaml.

Question 20

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.