PCDE Practice Test 47 — 15 Questions

Question 1

A Dataflow streaming pipeline that writes to a BigQuery table fails with the error above. Which change should be made to the table schema to prevent this error?

Accepted Answer

Partition the table by ingestion time. Partitioning the table by ingestion time (e.g., _PARTITIONTIME) distributes the streaming buffer across multiple partitions, avoiding the per-partition buffer limit. Increasing the buffer size is a workaround but not a schema change. Clustering does not affect the streaming buffer. Using a wildcard table is unrelated.

Answer

Add a clustering column

Answer

Increase the streaming buffer size in the table definition

Answer

Change the table to use a wildcard table pattern

Question 2

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 3

Your organization uses Cloud SQL for PostgreSQL for a reporting application with read-heavy workloads. Queries are slow and you need to reduce load on the primary instance. You also need to ensure that all read queries from the reporting tool are isolated from the primary. What should you do?

Accepted Answer

Create a Cloud SQL read replica, and configure the reporting tool to connect to the replica's IP address.. Cloud SQL read replicas serve read traffic and offload the primary. For isolation, the reporting tool must connect to the replica's IP. Cloud SQL Auth Proxy is a secure tunnel, but does not provide connection pooling. PgBouncer can be used with proxies for connection pooling, but the question asks for isolation, which is achieved by pointing the reporting tool to the replica. The best answer is to create a read replica and configure the reporting tool to connect to it.

Answer

Create a Cloud SQL clone and point the reporting tool to the clone.

Answer

Enable connection pooling using Cloud SQL Auth Proxy and PgBouncer on the primary instance.

Answer

Configure the reporting tool to use the primary instance with a lower priority.

Question 4

In BigQuery, a BI analyst wants to store financial data with high precision and avoid rounding errors. Which data type should be used for currency columns?

Accepted Answer

NUMERIC. NUMERIC (also known as DECIMAL) is the correct choice because it stores exact numeric values with up to 38 digits of precision and a user-defined scale, making it ideal for financial data where rounding errors from binary floating-point representation are unacceptable. In BigQuery, NUMERIC uses fixed-point arithmetic, ensuring that calculations like tax or interest accruals remain exact to the specified decimal places.

Answer

FLOAT64

Answer

INT64

Answer

STRING

Question 5

A company is migrating a stateful web application from on-premises to Google Kubernetes Engine (GKE). The application has variable traffic patterns, with occasional spikes. The team wants to optimize performance and availability while minimizing cost during spikes. The application is not fault-tolerant to instance restarts. Which TWO strategies should the team implement? (Choose TWO)

Accepted Answer

Configure pod disruption budgets to ensure a minimum number of pods remain available during node scaling. Cluster autoscaler adds nodes during spikes, but to avoid disruption from node scaling activities, pod disruption budgets should be configured to protect critical pods. Preemptible VMs are not suitable because the application is not fault-tolerant to restarts. VPA with Auto mode can adjust resource requests without restarting pods, but may take time to react to spikes; HPA is better for handling spikes quickly.

Answer

Use preemptible VMs to reduce cost during spikes

Answer

Use Vertical Pod Autoscaler in Auto mode to automatically adjust pod resources without restarts

Answer

Set cluster autoscaler scale-down delay to 0 for immediate cost savings

Question 6

An organization wants to enforce that no Compute Engine instances have public IP addresses. Which TWO methods can achieve this? (Choose TWO.)

Accepted Answer

Set the organization policy constraint constraints/compute.vmExternalIpAccess at the desired folder or project level.. Organization policies can restrict external IPs at the project level (constraints/compute.vmExternalIpAccess). Also, using a Shared VPC and only creating VMs in subnets without external IP access (by not having a default route to the internet) can prevent public IPs, although a more direct method is the org policy.

Answer

Create a custom IAM role that denies the compute.instances.create permission with external IP.

Answer

Use VPC Service Controls to restrict access to Compute Engine API.

Answer

Set the organization policy constraint constraints/compute.vmCanIpForward to deny.

Question 7

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.. GitOps requires a Git repository as the source of truth, a CI/CD pipeline that triggers on changes to main, and a service account with appropriate permissions to apply changes. Terraform Cloud is not required; the pipeline can run Terraform directly.

Answer

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

Answer

A Terraform Cloud workspace configured with the same GCS backend.

Question 8

A company is migrating their on-premises data warehouse to BigQuery for BI. They have a fact table with billions of rows and many dimension tables. The current queries perform well in the on-prem system but are slow in BigQuery. The queries contain multiple JOINs and subqueries. Which optimization should they implement first?

Accepted Answer

Denormalize the dimension tables into the fact table.. Denormalizing dimension tables into the fact table is the most impactful first optimization because it eliminates the need for expensive JOIN operations across billions of rows. In BigQuery, JOINs on large fact tables with multiple dimension tables can cause significant data shuffling and increased slot consumption, whereas denormalization reduces query complexity and leverages BigQuery's columnar storage and compression more efficiently. This directly addresses the root cause of slow performance in a BI workload where subqueries and JOINs are prevalent.

Answer

Use clustering on all join keys.

Answer

Use BigQuery's automatic query rewriting.

Answer

Convert subqueries to CTEs.

Question 9

A company is designing a data warehouse for BI. They need to support both detailed transaction analysis and high-level aggregated reports. Which schema design best balances storage and query performance?

Accepted Answer

Star schema with fact and dimension tables. The star schema is the optimal design for balancing storage and query performance in a BI data warehouse because it separates transactional data into fact tables (for detailed analysis) and dimension tables (for context), enabling fast aggregations via star joins while avoiding the storage overhead of full denormalization. This structure directly supports both granular transaction queries and high-level rollups without the complexity or performance penalty of snowflake schemas or the redundancy of fully denormalized tables.

Answer

Fully denormalized single table

Answer

Wide column store with no schema

Answer

Snowflake schema with normalized dimensions

Question 10

Which TWO of the following are best practices when designing data structures for business intelligence in BigQuery?

Accepted Answer

Partition tables on a column that aligns with common filter criteria. Partitioning tables on a column that aligns with common filter criteria (e.g., a date or timestamp column) allows BigQuery to prune partitions during query execution, drastically reducing the amount of data scanned and improving query performance and cost efficiency. This is a core best practice for optimizing BI workloads in BigQuery.

Answer

Store raw logs directly in fact tables without any aggregation

Answer

Use NULLable columns extensively to save storage

Answer

Use a single wide table for all data to simplify schema

Question 11

A company wants to use Cloud Logging to monitor for a specific error pattern in their application logs. They want to create a metric that counts occurrences of the error and then set up an alert when the count exceeds 100 in 5 minutes. Which TWO components are required? (Choose 2)

Accepted Answer

Alerting policy with metric threshold condition. A log-based counter metric counts matching log entries. An alerting policy can then be configured to trigger when the metric exceeds a threshold. The metric itself does not need to be distribution-based (that's for histogram values). The alignment period is part of the alert condition, not a separate component.

Answer

Notification channel (e.g., email)

Answer

Log-based distribution metric

Answer

Cloud Monitoring dashboard

Question 12

Which TWO are effective strategies to control costs when running BI queries on BigQuery? (Choose two.)

Accepted Answer

Set a maximum bytes billed limit for user projects.. Setting a maximum bytes billed limit for user projects in BigQuery allows you to cap the amount of data processed per query, preventing runaway costs from accidental or inefficient queries. This is a direct cost control mechanism that enforces a hard stop on query bytes processed, ensuring that users cannot exceed a predefined budget.

Answer

Create materialized copies of tables for each dashboard.

Answer

Schedule queries to run every minute to keep the cache warm.

Answer

Enable BI Engine for all tables to speed up queries.

Question 13

A BI team is troubleshooting a slow BigQuery query. Which TWO actions can help identify the bottleneck?

Accepted Answer

Review the query execution plan in the BigQuery UI.. Reviewing the query execution plan in the BigQuery UI (Option A) is correct because it provides a visual breakdown of query stages, including shuffle operations, data distribution, and stage-level timing. This allows the BI team to pinpoint which stage is consuming the most time or resources, such as a skewed join or a slow aggregation, directly identifying the bottleneck.

Answer

Increase the number of slots to maximum.

Answer

Remove all WHERE clauses to simplify.

Answer

Rewrite the query to use only CTEs.

Question 14

A company is using Cloud Run for a service that performs background computation even when there are no incoming requests. They find that the service is being throttled and not completing the background work. What is the most likely cause and solution?

Accepted Answer

The service must have CPU always-on enabled.. Cloud Run instances have CPU throttled when not handling requests. Setting CPU always-on ensures the instance can use CPU continuously for background tasks.

Answer

The service needs more memory; increase memory limit.

Answer

The service needs to set min instances to 1.

Answer

The service should be migrated to GKE.

Question 15

An engineer wants to cache Docker layers in Cloud Build to speed up subsequent builds. The build uses Kaniko to build images. What should they include in the cloudbuild.yaml?

Accepted Answer

Set `--cache=true` and `--cache-repo` in the Kaniko builder arguments. Kaniko uses `--cache=true` and `--cache-repo` to enable layer caching in a registry.

Answer

Add a step that runs `docker save` and `docker load`

Answer

Use the `docker` builder instead of Kaniko and set `--cache-from`

Answer

Enable Cloud Build's built-in caching by setting `cache: true` in cloudbuild.yaml