PCDE Practice Test 19 — 15 Questions

Question 1

You have a Cloud Spanner instance and need to add a new column and a secondary index to an existing table. The table is heavily used by production traffic. Which approach minimizes downtime and performance impact?

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

Accepted Answer

Use 'gcloud spanner databases ddl update' to add the column and create the index concurrently. Cloud Spanner supports online schema changes via DDL statements executed with 'gcloud spanner databases ddl update', which apply the schema modification without taking the table offline. Adding a column is a metadata-only operation, and creating a secondary index can be done with the index backfill running in the background while production traffic continues. This approach avoids data migration, dual-writes, or table recreation, minimizing both downtime and performance impact.

Answer

Export the table to Avro, modify the schema, import back into a new table, then rename

Answer

Create a new table with the new schema, use a temporary application to dual-write and backfill, then switch

Answer

Drop the table and recreate it with the new schema, then restore from backup

Question 2

A team is designing a schema for a time-series database in Bigtable to store IoT sensor readings. Each sensor sends a reading every minute. The team needs to create a row key that supports efficient queries for a specific sensor's readings over a time range. Which row key design is most appropriate?

Accepted Answer

sensor_id#reverse_timestamp. Bigtable stores rows sorted lexicographically by row key. By placing the sensor_id first, all readings for a given sensor are co-located in contiguous rows. Using reverse_timestamp (e.g., 9999-12-31 minus actual timestamp) ensures that the most recent readings appear first within that sensor's row range, which optimizes scans for the latest data and allows efficient range queries over a time window.

Answer

timestamp#sensor_id

Answer

hash(sensor_id)#timestamp

Answer

random_UUID

Question 3

An analyst writes a SQL query that joins a fact table with multiple dimension tables. The query runs slowly due to shuffling. Which optimization technique should be applied?

Accepted Answer

Cluster the fact table on the dimension join keys.. Shuffling occurs when data must be redistributed across nodes during joins, often because the join keys are not co-located. Clustering the fact table on the dimension join keys physically co-locates rows with the same join key values, minimizing data movement during the join. This is a direct optimization for shuffle-heavy workloads in distributed SQL engines like Spark SQL or Hive.

Answer

Use a subquery in the FROM clause to pre-aggregate.

Answer

Use a LIMIT clause to restrict rows.

Answer

Use a window function to precompute values.

Question 4

An engineer wants to deploy a set of GCP resources (e.g., Cloud SQL, Pub/Sub topics) alongside their Kubernetes workloads using a GitOps approach with Config Connector. What is the primary benefit of using Config Connector over deploying these resources manually?

Accepted Answer

It allows managing GCP resources using Kubernetes-style YAML, enabling version control and CI/CD for infrastructure.. Config Connector allows you to manage GCP resources (e.g., Cloud SQL, Pub/Sub topics) using Kubernetes-style YAML manifests. This enables GitOps workflows where infrastructure definitions are stored in a Git repository, version-controlled, and automatically applied via CI/CD pipelines, ensuring consistency and auditability.

Answer

It automatically scales GCP resources based on load.

Answer

It provides a graphical UI for managing GCP resources.

Answer

It reduces the cost of GCP resources by using committed use discounts.

Question 5

A startup needs a fully managed relational database for their e-commerce platform with high availability, automatic failover, and read replicas. They expect moderate traffic and want to minimize operational overhead. Which Google Cloud service should they use?

Accepted Answer

Cloud SQL. Cloud SQL offers fully managed MySQL, PostgreSQL, and SQL Server with high availability and read replicas. It is the best fit for moderate-traffic OLTP workloads.

Answer

Cloud Spanner

Answer

Firestore

Answer

Bigtable

Question 6

A company is performing a heterogeneous migration from Oracle to Cloud SQL for PostgreSQL using Ora2Pg. During testing, they find that a stored procedure using Oracle's PL/SQL `DBMS_OUTPUT.PUT_LINE` does not execute after conversion. What is the most likely issue, and how should it be resolved?

Accepted Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which should be replaced with `RAISE NOTICE` in PostgreSQL.. Ora2Pg converts PL/SQL to PL/pgSQL. `DBMS_OUTPUT.PUT_LINE` in Oracle is equivalent to `RAISE NOTICE` in PostgreSQL. Ora2Pg may not automatically convert all procedural constructs, requiring manual review and adjustment.

Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which should be replaced with `RAISE EXCEPTION`.

Answer

The procedure uses `DBMS_OUTPUT.PUT_LINE`, which is supported natively in PostgreSQL.

Answer

The procedure must be rewritten in Python using PL/Python.

Question 7

A team uses Helm charts to deploy applications to GKE. They want to use Kustomize to manage environment-specific overlays for different clusters. Which two tools or approaches can they combine to achieve this?

Accepted Answer

Use Skaffold to run Helm and Kustomize in sequence. Option A is correct because Skaffold natively supports both Helm and Kustomize as deploy/render configs, and its `manifests` pipeline can run Helm to render a chart and then Kustomize to apply environment-specific overlays in sequence before deploying to GKE. Option D is correct because Kustomize's built-in `helmCharts` field (enabled via `--enable-helm` or `helmCharts:` in `kustomization.yaml`) inflates a Helm chart into plain manifests, after which Kustomize overlays can patch those resources for each cluster. Option B is wrong because Config Sync is a GitOps reconciler that applies manifests from a source of truth; it does not itself render Kustomize overlays on top of Helm charts as a combined toolchain. Option C is wrong because `kubectl apply -k` only builds a Kustomize directory and cannot render Helm charts without overlays, so it does not meet the requirement. Option E is wrong because Cloud Build's Helm support alone does not provide Kustomize overlay management for environment-specific clusters.

Answer

Use Config Sync to apply Kustomize overlays directly

Answer

Use 'kubectl apply -k' to apply Helm charts without overlays

Answer

Use Cloud Build's built-in Helm support only

Question 8

A data engineer notices that a scheduled query exporting BigQuery data to Cloud Storage is failing with a timeout error. The dataset contains 500 million rows. What should they do?

Accepted Answer

Export each partition separately.. Exporting a 500-million-row table as a single operation can exceed BigQuery's 6-hour timeout limit. By exporting each partition separately, you reduce the data volume per export job, allowing each to complete within the timeout window. BigQuery's partitioned tables enable parallel exports, avoiding the fixed timeout limit which cannot be increased.

Answer

Use SELECT * without filters.

Answer

Change the export format from CSV to Avro.

Answer

Increase the query timeout setting.

Question 9

An organization needs a fully managed, globally distributed relational database with strong consistency and horizontal scaling for a multi-region application. Which service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is Google's fully managed, globally distributed relational database that provides strong consistency (external consistency) and horizontal scaling via TrueTime and Paxos-based replication. It is the only option that combines relational semantics, global distribution, strong consistency, and horizontal scale-out. Bigtable and Firestore are NoSQL, and Cloud SQL is regional and vertically scaled.

Answer

Bigtable

Answer

Firestore

Answer

Cloud SQL

Question 10

An organization wants to enforce that no Compute Engine instances have external IP addresses except for a specific project. Which TWO steps should they take? (Choose 2)

Accepted Answer

In the exception project, override the policy to allow.. Option D is correct because the organization policy constraint `compute.vmExternalIpAccess` is the specific GCP Organization Policy constraint designed to control whether Compute Engine instances can have external IP addresses; setting it to deny at the organization level enforces the restriction across all projects in the organization. Option A is correct because Organization Policies support hierarchical overrides, so the specific exception project can override the inherited deny policy to allow external IPs, achieving the desired exception. Option B is incorrect because firewall rules control network traffic to/from instances and cannot prevent an instance from being assigned an external IP address. Option C is incorrect because manually removing external IPs is not an enforcement mechanism and does not prevent future instances from being created with external IPs. Option E is incorrect because VPC Service Controls restrict access to Google APIs and services at the perimeter level, not the assignment of external IP addresses to Compute Engine instances.

Answer

Add a firewall rule to block traffic to external IPs.

Answer

Remove the external IP from all instances manually.

Answer

Use VPC Service Controls to restrict external access.

Question 11

An engineer wants to run two Cloud Build steps in parallel to speed up the build. How should they configure the cloudbuild.yaml?

Accepted Answer

Set `waitFor: ['-']` on both steps and ensure they are defined sequentially in the YAML. In Cloud Build, steps defined sequentially in the YAML run in order by default. To run two steps in parallel, you set `waitFor: ['-']` on both steps, which tells Cloud Build not to wait for any previous step. This allows them to start simultaneously. Option D correctly describes this configuration.

Answer

Use two separate cloudbuild.yaml files and run them concurrently

Answer

Set `waitFor: ['previous']` on both steps

Answer

Define steps under a `parallel` key in cloudbuild.yaml

Question 12

You need to ensure that read operations on a Cloud Spanner database return the most recent committed data. Which read type should you use?

Accepted Answer

Strong read. Strong read is the correct choice because it guarantees that read operations return the most recent committed data from Cloud Spanner. Unlike other read types, strong reads access the current state of the database at the time of the read, ensuring external consistency and linearizability, which is critical for applications requiring up-to-date data.

Answer

Read-only transaction

Answer

Stale read

Answer

Partitioned read

Question 13

You are designing a Firestore database for a chat application. Documents will store messages with fields: senderId, messageText, timestamp, conversationId. To efficiently retrieve the most recent 50 messages in a conversation, which index should you create?

Accepted Answer

A composite index on (conversationId, timestamp, __name__) descending. To efficiently retrieve the most recent 50 messages for a specific conversation, you need a composite index on (conversationId, timestamp, __name__) with descending order. This allows Firestore to perform a range query on conversationId and then order by timestamp descending, using the index to skip scanning irrelevant documents. The __name__ field is included to ensure the index is covering and to handle document name ordering ties, which is required for consistent pagination with descending order.

Answer

A single-field index on timestamp

Answer

An index on conversationId only

Answer

A composite index on (senderId, timestamp)

Question 14

An e-commerce platform uses Cloud SQL for PostgreSQL to manage orders. The application team reports that the database experiences performance degradation during peak hours due to high connection churn. They want to maintain a pool of established connections. Which configuration change addresses this without application code changes?

Accepted Answer

Enable the pgBouncer flag to use transaction pooling.. Enabling the pgBouncer flag in Cloud SQL for PostgreSQL provides a built-in connection pooler that maintains persistent connections to the database, reducing the overhead of frequent connection establishment. pgBouncer operates in transaction pooling mode, which allows multiple client connections to share a smaller pool of backend connections, directly addressing high connection churn without requiring any application code changes.

Answer

Reduce the max_connections flag to force the application to reuse connections.

Answer

Increase the max_connections flag to a higher value.

Answer

Switch to Cloud SQL for MySQL, which has built-in connection pooling.

Question 15

A company runs a financial analytics platform on BigQuery. They need to reduce query costs for frequent, predictable queries. Which three strategies can help? (Choose THREE.)

Accepted Answer

Use BI Engine to cache results of frequent queries.. Option A is correct because BI Engine is an in-memory analysis service that accelerates and caches frequently accessed data, reducing bytes processed and thus query costs for predictable, repeated queries. Option B is correct because materialized views pre-compute and store common aggregations, so recurring queries read the smaller pre-aggregated result instead of scanning the full base tables, lowering cost. Option D is correct because clustering tables on frequently filtered columns co-locates related data in storage blocks, so queries scan fewer bytes when filtering on those columns, reducing on-demand costs. Option C is not among the marked answers: partitioning by ingestion time prunes scans only when queries filter on the ingestion-time column, which is not stated as the frequent filter pattern here. Option E is not marked correct because DML statements (INSERT/UPDATE/DELETE/MERGE) are for modifying data, not a cost-reduction mechanism, and manually pre-aggregating with DML adds processing cost rather than reducing it.

Answer

Partition tables by ingestion time.

Answer

Use DML statements to pre-aggregate data.