PCDE Practice Exam 56 — 20 Questions

Question 1

A company uses BigQuery for BI reporting. They have a table 'orders' with columns: order_id, customer_id, order_date, amount, status. The BI team frequently runs queries that filter on order_date and group by customer_id to compute total sales per customer. Which partitioning and clustering strategy optimizes query performance and cost?

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

Accepted Answer

Partition by order_date, cluster by customer_id. Partitioning by order_date allows BigQuery to prune partitions for queries filtering on order_date, reducing the amount of data scanned. Clustering by customer_id organizes data within each partition so that GROUP BY customer_id queries can efficiently read only relevant blocks, minimizing shuffle and cost. This combination directly aligns with the BI team's query pattern of filtering by date and aggregating by customer.

Answer

Partition by order_date, cluster by status

Answer

Do not partition, cluster by customer_id

Answer

Partition by customer_id, cluster by order_date

Question 2

A company has multiple teams in a GCP organization. They want to isolate environments (prod, staging, dev) and give each team a separate project for development. Which folder structure is recommended?

Accepted Answer

Create folders for each environment (prod, staging, dev), and within each, folders for teams/products containing projects. It aligns with Google Cloud's recommended resource hierarchy for multi-team, multi-environment isolation. By creating folders for each environment (prod, staging, dev) and then sub-folders for teams/products, you can apply consistent IAM policies and organization policies at the environment level (e.g., restrict prod access) while delegating project-level control to teams. This structure also supports the principle of least privilege and simplifies auditing.

Answer

Create a flat list of projects with naming conventions like `team-project-env`

Answer

Create a single folder per team, with projects for each environment inside

Answer

Create folders per product, and within each, environment folders

Question 3

A company uses Cloud Build to build and deploy microservices to GKE. Each microservice has environment-specific configurations (dev, staging, prod). They want to manage these configurations using Kustomize. How should they structure the pipeline?

Accepted Answer

Use a single cloudbuild.yaml with kustomize build command and pass the environment as a substitution. Kustomize allows overlays for different environments. The cloudbuild.yaml can use the kustomize builder to apply the appropriate overlay based on a substitution variable like $_ENV.

Answer

Store all configurations in separate branches

Answer

Use Helm charts with different values files

Answer

Create separate cloudbuild.yaml files for each environment

Question 4

A team is conducting a blameless postmortem after a production incident. Which three actions are part of an effective blameless postmortem process? (Choose 3)

Accepted Answer

Define action items with specific owners and due dates. Option A is correct because an effective blameless postmortem must produce concrete, trackable remediation: action items with a named owner and a due date ensure the identified improvements are actually implemented rather than left as vague intentions. Option B is correct because the 5 Whys technique is a standard root-cause analysis method that iteratively asks "why" to move past the immediate trigger and uncover the chain of contributing factors (e.g., configuration drift, missing guardrails, inadequate testing) behind the incident. Option E is correct because the defining principle of a blameless postmortem is focusing on system and process failures rather than individuals, which encourages honest disclosure of what happened and how, yielding more accurate causal data and better preventive fixes. Options C and D are incorrect because assigning blame to the person who made the error and punishing the on-call engineer for a missed alert are punitive, person-focused responses that violate the blameless premise, suppress candor, and reduce the quality of the incident analysis.

Answer

Assign blame to the individual who made the error

Answer

Punish the on-call engineer for missing the alert

Question 5

A healthcare company needs to run BI queries on patient data. The table is in BigQuery and contains 5 billion rows. Queries often filter on patient_id and date. But the table is not partitioned or clustered. Analysts run queries that scan the entire table. The data is updated daily. What is the most cost-effective way to improve performance?

Accepted Answer

Partition by date and cluster by patient_id.. Partitioning by date (e.g., ingestion or event date) allows BigQuery to prune entire partitions when queries filter on date, drastically reducing the data scanned. Clustering by patient_id within each partition further organizes the data so that queries filtering on patient_id can skip irrelevant blocks via block-level metadata. Together, this minimizes bytes billed and improves query performance without requiring table redesign or additional storage costs.

Answer

Partition the table by patient_id.

Answer

Use a view that only selects recent data.

Answer

Cluster the table by date.

Question 6

A company has a Google Cloud organization with separate folders for development, staging, and production. They want to deploy Terraform using a CI/CD pipeline that runs in a shared tools project. Where should the Terraform state files be stored and how should the pipeline authenticate?

Accepted Answer

Store state in a central Cloud Storage bucket in the tools project; use a service account in the tools project with Workload Identity Federation to access the bucket and assume roles in environment projects.. Terraform state must be stored in a shared, remote backend accessible by the CI/CD pipeline, and a central Cloud Storage bucket in the tools project is the standard pattern for multi-environment deployments. Workload Identity Federation lets the pipeline authenticate without long-lived service account keys, and the tools-project service account can be granted impersonation or IAM roles in the environment projects to apply changes there. This combination provides centralized state management with keyless, least-privilege authentication.

Answer

Store state in Cloud Firestore; use a service account key stored in Secret Manager.

Answer

Store state in a Cloud Storage bucket in each environment project; use user credentials passed as secrets.

Answer

Store state locally in the CI/CD runner; use Application Default Credentials from the runner's environment.

Question 7

Refer to the exhibit. A company creates these Cloud Spanner tables. What happens when a customer record is deleted?

Accepted Answer

All orders for that customer are automatically deleted.. Cloud Spanner enforces referential integrity through interleaved tables. When a parent row in the Customers table is deleted, all child rows in the Orders table that are interleaved under that customer are automatically deleted via a cascading delete. This behavior is inherent to the interleaved table structure, not an explicit ON DELETE CASCADE clause.

Answer

The deletion fails if there are orders.

Answer

The orders are deleted only if the order date is older than 30 days.

Answer

The orders remain orphaned.

Question 8

A Cloud Spanner instance is backing up a 2 TB database daily to Cloud Storage using the built-in backup feature. The compliance team requires the backup to be stored in a specific regional bucket with a retention policy of 14 days. How should the database administrator configure this?

Accepted Answer

Use the gcloud spanner databases export command to export the database to a Cloud Storage bucket in the desired region, then set a retention policy on the bucket. Cloud Spanner's built-in backup feature stores backups in a Google-managed location and does not allow you to specify a custom Cloud Storage bucket or region. To meet the compliance requirement of a specific regional bucket with a 14-day retention policy, the administrator must use the export feature (gcloud spanner databases export) to write the database to a Cloud Storage bucket in the desired region, then apply a bucket retention policy. This is the only option that satisfies both the regional bucket and retention policy requirements.

Answer

Schedule a cron job to copy the backup from Spanner's default location to the regional bucket

Answer

Use the CREATE BACKUP statement and specify a Cloud Storage bucket in the desired region

Answer

Use the backup retention period in Spanner's backup settings to keep backups for 14 days

Question 9

During a one-time (non-continuous) migration from MySQL to Cloud SQL using mysqldump, the engineer wants to ensure the dump does not lock InnoDB tables. Which flag should be included in the mysqldump command?

Accepted Answer

--single-transaction. --single-transaction starts a transaction at the time of dump, ensuring a consistent snapshot without locking tables (for InnoDB).

Answer

--skip-lock-tables

Answer

--flush-logs

Answer

--lock-all-tables

Question 10

A database administrator needs to restore a Cloud SQL for PostgreSQL instance to a specific point in time within the last 3 hours. Which configurations must be enabled to perform a point-in-time recovery (PITR)?

Accepted Answer

Automated backups and WAL archiving with a retention period. Cloud SQL for PostgreSQL performs point-in-time recovery (PITR) by combining automated backups with continuous Write-Ahead Log (WAL) archiving. The WAL segments are streamed to Cloud Storage and retained for the configured retention period (up to 7 days), allowing the instance to be restored to any second within that window. Therefore, both automated backups and WAL archiving with a valid retention period must be enabled.

Answer

Automated backups and binary logging

Answer

On-demand backups and binary logging

Answer

Automated backups and cross-region replicas

Question 11

A startup needs to run complex analytical queries on large datasets (10+ TB) with sub-second to a few seconds latency. The data is structured and updated daily in batch. Which Google Cloud service is best suited for this use case?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse designed for petabyte-scale analytics with fast SQL queries using columnar storage and a distributed query engine. It supports sub-second to few-second latency on structured data via features like clustering, partitioning, and BI Engine acceleration, and it handles daily batch updates efficiently through batch loading or scheduled queries.

Answer

Cloud Bigtable

Answer

AlloyDB

Answer

Cloud SQL

Question 12

An engineer is designing a Cloud Spanner schema for a social media application. The database will have a User table and a Post table. Users have many posts, and the application frequently queries all posts for a user, ordered by timestamp. Which two schema design choices will improve performance? (Choose two.)

Accepted Answer

Interleave the Post table within the User table.. Option A is correct because interleaving Post within User physically co-locates child rows with their parent User row, so fetching all posts for a given user reads contiguous storage on the same split rather than scattering lookups across the keyspace, which is exactly the parent-child access pattern this application uses. Option E is correct because making (UserId, PostTimestamp) the Post primary key prefixes rows by UserId and orders them by timestamp within each user, so the frequent query for a user's posts ordered by timestamp is served by a single ordered range scan with no extra sort or index. Option B is not needed because a secondary index on PostId does not support the query pattern of retrieving a user's posts by timestamp, and Spanner primary keys are already indexed. Option C is wrong because a monotonically increasing integer PostId creates a hotspot where all new writes land on the last split, hurting write scalability. Option D is wrong because a random UUID PostId scatters writes but does not by itself optimize the read pattern of retrieving a user's posts in timestamp order.

Answer

Create a secondary index on PostId.

Answer

Use a monotonically increasing integer for PostId.

Answer

Use a UUID for PostId.

Question 13

You are a database engineer for an e-commerce platform running on Cloud SQL for PostgreSQL. The application team reports that a critical report query taking 5 seconds last week now takes over 30 seconds. The database CPU usage has increased from 40% to 85%. The query plan shows a sequential scan on the orders table. Which action should you take first to diagnose the problem?

Accepted Answer

Enable pg_stat_statements and check the query's execution statistics.. Enabling pg_stat_statements provides detailed execution statistics (e.g., total time, calls, rows, I/O) for each query, allowing you to identify the root cause of the performance regression without making speculative changes. Since the query plan shows a sequential scan and CPU is high, pg_stat_statements can reveal if the query is now reading more rows due to data growth or plan changes, guiding the next action (e.g., adding an index or tuning parameters).

Answer

Migrate the database to Cloud Spanner for better scaling.

Answer

Add a composite index on the orders table based on the query's WHERE clause.

Answer

Increase the machine type to add more vCPUs.

Question 14

A team is planning the cutover for a DMS continuous migration. They want to minimize downtime. What is the correct cutover procedure?

Accepted Answer

Quiesce writes, confirm lag 0, promote destination, update connection strings, verify.. The correct cutover procedure for a DMS continuous migration involves quiescing writes to the source database, waiting until replication lag is zero to ensure all changes are replicated, then promoting the destination to be the new primary, updating connection strings in applications, and finally verifying that everything works. This minimizes downtime because the source is only stopped briefly during the final catch-up and promotion. The key is to ensure no data loss by confirming lag is zero before promotion.

Answer

Update connection strings to point to destination, then promote.

Answer

Promote destination immediately, then stop source.

Answer

Stop source, promote destination, update connection strings.

Question 15

A Memorystore for Redis instance is running out of memory. The application can tolerate some data loss but not crashes. The team wants to ensure the instance remains available without manual intervention. Which eviction policy should they configure?

Accepted Answer

allkeys-lru. (allkeys-lru) is correct because it allows Redis to evict the least recently used keys from the entire keyspace when memory is full, which keeps the instance available without crashing. Since the application can tolerate some data loss but not crashes, this policy ensures memory pressure is relieved automatically, preventing out-of-memory errors that would cause the instance to become unavailable.

Answer

volatile-lru

Answer

volatile-ttl

Answer

noeviction

Question 16

A startup needs a database for a global user base with low-latency reads and writes, strong consistency, and the ability to scale horizontally without downtime. They anticipate variable traffic. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that provides strong consistency (external consistency) at global scale with horizontal scaling and no downtime. It uses TrueTime and Paxos to synchronize across regions, offering 99.999% availability for multi-region configurations. Its ability to scale reads and writes linearly while maintaining ACID transactions makes it ideal for a global user base with variable traffic.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 17

A Cloud Spanner database is experiencing read performance issues. The team wants to optimize query performance. Which two approaches should they use? (Choose TWO).

Accepted Answer

Create secondary indexes on frequently filtered columns. Option A is correct because Cloud Spanner uses secondary indexes to accelerate queries that filter or sort on non-primary-key columns; creating an index on frequently filtered columns lets Spanner perform an index scan instead of a full table scan, directly improving read performance. Option E is correct because the query explain plan (obtained via the Cloud Console or the client library's analyzeQuery/EXPLAIN) reveals whether Spanner is doing a table scan, index scan, or distributed cross-apply join, allowing the team to identify and fix inefficient query patterns. Option B is not applicable because Cloud Spanner does not offer a 'read replica' feature to enable; it uses regional and multi-region configurations with replicas managed automatically, and reads are already served by replicas without a toggle. Option C is wrong because interleaving is a physical data-locality mechanism for parent-child relationships, not a general performance fix, and interleaving all tables would create incorrect schemas and hotspots. Option D is incorrect because adding nodes increases write throughput and overall capacity, but it does not address the root cause of slow reads caused by missing indexes or inefficient query plans.

Answer

Enable read replicas

Answer

Use interleaved tables for all tables

Answer

Increase the number of nodes

Question 18

A company uses Terraform to manage infrastructure. They have a monolithic Terraform configuration that manages all projects in a single state file. As the organization grows, the configuration becomes slow and error-prone. The team wants to adopt a modular approach with separate state files for each project while reusing common modules. Which strategy should they follow?

Accepted Answer

Split the monolithic configuration into separate root modules per project, store state in GCS buckets with prefix per project, and use terraform_remote_state data sources to share outputs between modules.. The correct strategy is to split the monolithic configuration into separate root modules per project, store state in GCS buckets with a prefix per project, and use terraform_remote_state data sources to share outputs between modules. This approach isolates state files for each project, reducing the blast radius of changes and improving performance, while still allowing reuse of common modules. It aligns with Terraform best practices for modularity and state management.

Answer

Create a single Terraform module for all resources and call it with different variables for each project, using the same state file.

Answer

Keep the monolithic configuration but use Terraform workspaces to create separate state files for each project.

Answer

Use Cloud Deployment Manager with separate YAML templates for each project and a central state stored in Cloud Storage.

Question 19

You need to send alert notifications to a Slack channel. Which TWO components are required?

Accepted Answer

A notification channel of type 'slack' in Cloud Monitoring. Option A is correct because Cloud Monitoring requires a notification channel resource configured with type 'slack' to define where alerts are delivered; this channel is what alerting policies reference when routing notifications. Option B is correct because the Slack notification channel must be created with a Slack webhook URL (an incoming webhook endpoint from Slack) that Cloud Monitoring uses to POST alert payloads into the target Slack channel. Option C is not required since Pub/Sub is only needed for pub/sub-based notification channels, not for Slack delivery. Option D is not required because an email notification channel is a separate delivery method and is not a prerequisite for Slack notifications. Option E is not required because Cloud Monitoring natively formats and sends Slack notifications without any Cloud Function transformation.

Answer

A Cloud Pub/Sub topic

Answer

An email notification channel

Answer

A Cloud Function to transform the alert

Question 20

A Cloud SQL instance is running low on disk space. You want to increase disk size with zero downtime. Which action should you take?

Accepted Answer

Use gcloud sql instances patch to increase the storage size. Cloud SQL supports online storage increases without downtime. The `gcloud sql instances patch` command (or the equivalent Console/API operation) dynamically adds more disk capacity to the running instance while it continues serving traffic. This is possible because Cloud SQL uses persistent disks that can be resized online, and the database engine (e.g., MySQL, PostgreSQL) does not need to restart to recognize the additional space.

Answer

Stop the instance, increase disk, and restart

Answer

Export data, create a new instance with larger disk, and import

Answer

Create a clone with larger disk and failover