PCDE Practice Test 21 — 15 Questions

Question 1

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 2

A financial company runs BI queries on a BigQuery table that is partitioned by ingestion time. The table is 1 TB and receives streaming inserts every minute. Analysts query the last 24 hours of data. The queries are slow. The table is clustered by transaction_id. What is the likely cause?

Accepted Answer

The cluster column is not used in queries.. Option A is correct because queries likely filter on the time range (last 24 hours) but not on transaction_id, so the clustering key is not used; clustering only helps when the cluster column is in the filter. Option B (streaming buffer) is not a cause because data is committed quickly. Option C (partition expiration) is not mentioned. Option D (SELECT *) may affect but not primary cause.

Answer

Streaming buffer causes delays.

Answer

Queries use SELECT *.

Answer

Partition expiration is set too short.

Question 3

A company has a parent-child relationship in Cloud Spanner. They want to minimize cross-table join latency. What should they use?

Accepted Answer

Interleaved tables

Answer

Cloud Functions

Answer

Stored procedures

Answer

Secondary indexes

Question 4

Which THREE considerations are critical when migrating from Cassandra to Cloud Bigtable?

Accepted Answer

Denormalizing data to avoid joins. Option B is correct because Cloud Bigtable is a NoSQL wide-column database that does not support joins. Denormalization is a standard practice in Bigtable to model relational data into a single table, ensuring efficient single-row lookups and avoiding the performance penalty of multi-table queries that would require application-level joins.

Answer

Using CQL for queries

Answer

Using secondary indexes for efficient filtering

Question 5

A company has a Spanner instance with 5 nodes serving a global application. They receive alerts that write latency has increased significantly during business hours in the Asia-Pacific region. The team confirms that no application changes have been made. What is the most likely cause and recommended action?

Accepted Answer

Writes are hitting a hot spot due to monotonically increasing keys; consider using a hash prefix or bit-reversed key. Monotonically increasing keys (e.g., timestamps or auto-increment IDs) cause all new writes to target the same tablet leader in Spanner, creating a hot spot. This increases write latency because the single node becomes a bottleneck, especially during peak business hours in the Asia-Pacific region. Using a hash prefix or bit-reversed key distributes writes evenly across nodes, resolving the contention.

Answer

CPU utilization is above 70%; enable Spanner fine-grained access control

Answer

Set up interleaved indexes to speed up writes

Answer

The instance is under-provisioned; increase the number of nodes

Question 6

A database engineer is designing a schema for a Cloud Spanner database. Which three practices should they follow to ensure good performance? (Choose three.)

Accepted Answer

Use split points to distribute data across nodes.. Option A is correct because explicitly defining split points in Cloud Spanner allows you to control how data is distributed across nodes, which can prevent hot spots and improve read/write throughput. By specifying split boundaries, you ensure that frequently accessed data is spread evenly, avoiding performance bottlenecks.

Answer

Use locking read (SELECT ... FOR UPDATE) for all transactional reads.

Answer

Create secondary indexes on every column to speed up queries.

Question 7

A company is migrating their on-premises PostgreSQL database to Cloud SQL. They want to minimize downtime during the migration. Which approach should they use?

Accepted Answer

Use Database Migration Service with continuous replication. Database Migration Service (DMS) with continuous replication is the correct approach because it uses change data capture (CDC) to replicate ongoing transactions from the source PostgreSQL database to Cloud SQL, enabling a near-zero downtime migration. DMS handles schema conversion, data validation, and automated failover, which minimizes the cutover window to seconds or minutes.

Answer

Use pg_dump and pg_restore

Answer

Export data to CSV and import into Cloud SQL

Answer

Use a third-party ETL tool

Question 8

A company is designing a Cloud Spanner database for a global financial application. They need to minimize latency for customer queries while handling write-heavy workloads. The current design uses a single-region instance in us-central1. Which approach should they take to reduce latency for users in Europe?

Accepted Answer

Reconfigure the instance to a multi-region configuration with default leader in europe-west1.. Option D is correct because using a multi-region instance with leader options can place a read-write leader in a European region, reducing write latency for European users. Option A is wrong because read replicas in Spanner only serve stale reads (not strong reads). Option B is wrong because multi-region instance with default leader in US would not help. Option C is wrong because adding more nodes does not reduce cross-continental latency.

Answer

Reconfigure the instance to a multi-region configuration with default leader in us-central1.

Answer

Add a read-only regional replica in europe-west1.

Answer

Increase the number of nodes to improve throughput and automatically reduce latency.

Question 9

A company stores log files in Cloud Storage buckets. The logs are accessed frequently for the first 30 days, then rarely for the next 6 months, after which they must be archived for 7 years. They want to minimize storage costs. Which two actions should they take? (Choose two.)

Accepted Answer

Set a lifecycle rule to change storage class to Nearline after 30 days.. Options B and C are correct: they should transition to Nearline after 30 days, and then to Archive after 6 months. Option A is wrong because autoclass is a single setting that automatically transitions, but it may not precisely match the 30-day/6-month schedule; custom lifecycle rules give more control. Option D is wrong because deleting after 30 days loses data needed for 6 months. Option E is wrong because Standard class is the most expensive; not cost-effective for rare access. Correct: B and C.

Answer

Keep objects in Standard storage class for the entire retention period.

Answer

Set a lifecycle rule to delete the objects after 30 days.

Answer

Enable Autoclass to automatically transition objects to colder storage classes.

Question 10

You need to transfer 10 TB of data from on-premises servers to Cloud Storage for loading into Bigtable. Which method is the most efficient and reliable for this volume?

Accepted Answer

Use Storage Transfer Service. The Storage Transfer Service is designed for large-scale, online data transfers from on-premises or other cloud providers to Google Cloud. It handles 10 TB efficiently by automatically managing retries, checksums, and network optimization without requiring manual scripting or persistent VPN connections, making it the most reliable and efficient choice for this volume.

Answer

Set up Cloud VPN and use rsync

Answer

Use gsutil cp in parallel

Answer

Use Dataflow to read and write data

Question 11

A company needs a cross-region disaster recovery solution for their Cloud SQL MySQL database. Which feature should they use?

Accepted Answer

Cross-region replication using external replicas.. Option C is correct because Cloud SQL for MySQL does not natively support cross-region replication, but you can achieve it by configuring an external replica (a MySQL instance running outside Cloud SQL, such as on Compute Engine) that uses MySQL's native binary log (binlog) replication from the primary Cloud SQL instance. This setup allows you to maintain a standby database in a different region for disaster recovery, with the external replica continuously applying changes from the primary.

Answer

Read replicas in the same region.

Answer

Cloud SQL for MySQL does not support cross-region replication.

Answer

Use Database Migration Service to continuously copy data.

Question 12

A developer runs the command shown in the exhibit and wants to verify that replication is enabled on the Bigtable instance. Where should they look for this information in the output?

Accepted Answer

View the 'clusters' list within the instance description.. Option C is correct because the `gcloud bigtable instances describe` command returns a JSON representation of the instance, which includes a `clusters` list. Each cluster object in that list contains a `replication` field (e.g., `defaultStorageType` and `nodes`), and the presence of multiple clusters in the list indicates that replication is configured. Replication in Cloud Bigtable is enabled by adding more than one cluster to the instance, so examining the `clusters` list directly shows whether replication is active.

Answer

Examine the 'instanceType' field for 'MULTI_CLUSTER'.

Answer

Look for a 'replication' field in the JSON.

Answer

Check the 'state' field for 'REPLICATED'.

Question 13

You need to export data from Cloud Spanner for archival purposes. Which method is most cost-effective?

Accepted Answer

Use gcloud spanner databases export. Option A is correct because the native gcloud spanner databases export command exports data directly to Cloud Storage with minimal cost and no additional compute resources. Options B and D (Dataflow, Cloud Function) incur additional compute charges. Option C (execute-sql) is not suitable for full database export.

Answer

Use a Cloud Function to copy data

Answer

Use Dataflow to read and write to Cloud Storage

Answer

Use gcloud spanner databases execute-sql

Question 14

Refer to the exhibit. A developer tries to connect to a Cloud SQL instance from a VM using the public IP. The connection fails with this error. What should the developer do to fix the connection?

Accepted Answer

Connect using the Cloud SQL Proxy.. The error indicates that the Cloud SQL instance does not have an authorized network allowing the VM's public IP. However, the correct fix is to use the Cloud SQL Proxy, which establishes a secure, authenticated tunnel to the instance without needing to authorize the VM's IP. The proxy handles IAM-based authentication and encryption, bypassing the public IP network authorization requirement entirely.

Answer

Enable SSL-only mode on the Cloud SQL instance.

Answer

Add the client IP to the authorized networks.

Answer

Change the instance to require SSL for private connections.

Question 15

A Cloud Spanner instance is experiencing increased latency during peak hours. Monitoring shows CPU utilization nearing 70%. How should they scale?

Accepted Answer

Add more nodes.

Answer

Change to a higher-tier machine type.

Answer

Increase the number of splits.

Answer

Add more processing units.