PCDE Practice Test 24 — 15 Questions

Question 1

A Firestore database has a collection with a composite index on (status, timestamp desc). The query `where status == 'active' order by timestamp desc limit 50` is returning empty results even though there are active documents. What could be the issue?

Accepted Answer

The composite index exists but the order of timestamp is asc instead of desc.. If the composite index has timestamp in ascending order, the order by desc cannot use the index efficiently, leading to empty results due to scanning in wrong order.

Answer

The status field is not indexed.

Answer

The documents have 'active' status but timestamp field is missing.

Answer

The query requires an index on timestamp only.

Question 2

Based on the exhibit from Cloud Spanner Query Insights, what is the most likely performance issue?

Accepted Answer

No index on customer_id. Option D is correct because the exhibit shows a query with a filter on `customer_id` that is not indexed, forcing Cloud Spanner to perform a full table scan to find matching rows. This is the most likely performance issue, as indicated by high latency and high row scan counts in Query Insights, which directly points to a missing index on the filtered column.

Answer

High network latency

Answer

Full table scan

Answer

Inefficient join

Question 3

A data warehouse in BigQuery is running slower due to large full-table scans. Which feature can reduce the amount of data processed for common queries?

Accepted Answer

All of the above. Option A is correct because partitioning, clustering, and materialized views all reduce the amount of data scanned in BigQuery. Partitioning limits scans to specific date ranges, clustering sorts data within partitions to skip irrelevant blocks, and materialized views precompute and store query results so subsequent queries read only the pre-aggregated output instead of scanning the base table. Together, these features minimize full-table scans and improve query performance.

Answer

Clustering

Answer

Materialized Views

Answer

Partitioning

Question 4

Your Bigtable cluster is experiencing high CPU utilization and write latency during a daily batch job that writes 500 GB of data. The job uses bulk mutations with 1000 rows per request. What is the most effective way to reduce CPU usage?

Accepted Answer

Increase the number of rows per mutation to 10,000. Option B is correct because larger mutations reduce overhead per row. Option A is wrong because increasing nodes would help but is not as efficient as optimizing the write pattern. Option C is wrong because increasing cluster size doesn't reduce CPU per request. Option D is wrong because write-ahead logs are internal; tuning them doesn't reduce CPU for writes.

Answer

Disable write-ahead logs

Answer

Increase the number of nodes temporarily

Answer

Create a new cluster in a different zone

Question 5

A data analytics team runs ad-hoc queries on BigQuery that often exceed their slot capacity, causing queuing. They want to ensure predictable performance for their critical dashboard while still allowing ad-hoc queries. What is the most cost-effective solution?

Accepted Answer

Use a single reservation with a baseline of slots for the dashboard (top priority), and allow ad-hoc queries to use idle slots.. Setting a baseline number of slots for the dashboard guarantees resources, while allowing idle slots to be used by ad-hoc queries. Adding a reservation for only the dashboard with a separate project would waste slots; converting to on-demand is unpredictable; changing the BQ location does not affect slots.

Answer

Create a separate BigQuery reservation for the dashboard with a fixed number of slots, and let ad-hoc queries use on-demand pricing.

Answer

Switch all queries to on-demand pricing; the dashboard will automatically get priority.

Answer

Move the data to a different BigQuery region with more slot availability.

Question 6

Which THREE actions can help reduce read latency in Cloud Spanner?

Accepted Answer

Use read-only transactions with strong consistency. Read-only transactions with strong consistency in Cloud Spanner use lock-free reads that return the most recent data without blocking writes. This reduces read latency because the system can serve the data directly from the current timestamp without waiting for write locks or replication delays, making it ideal for low-latency, strongly consistent reads.

Answer

Increase the staleness allowed for read queries

Answer

Batch multiple write operations into a single mutation

Question 7

You are monitoring a Cloud Spanner instance that is experiencing high CPU utilization (consistently above 70%). You want to identify the root cause. Which TWO metrics should you examine first? (Choose two.)

Accepted Answer

Average commit latency. Examining read and write throughput helps identify if the workload is pushing the instance. Analyzing commit latency and lock wait time reveals contention. Stale reads show replica lag but are not primary indicators of high CPU. Node count is configuration, not utilization.

Answer

Lock wait time

Answer

Stale read rate

Answer

Number of nodes

Question 8

You are monitoring a Memorystore for Redis instance serving as a cache for an e-commerce application. The cache hit ratio has dropped from 95% to 70%. Which action is most likely to restore the hit ratio?

Accepted Answer

Increase the maxmemory setting to accommodate more cache entries.. A drop in cache hit ratio from 95% to 70% indicates that the cache is evicting frequently accessed keys to make room for new ones. Increasing the maxmemory setting allows Redis to store more entries, reducing evictions and restoring the hit ratio. This is the most direct way to address the capacity issue without changing application behavior.

Answer

Reduce the network latency between the application and Redis by moving them to the same zone.

Answer

Change the eviction policy to 'noeviction' to prevent key removal.

Answer

Enable AOF persistence to ensure data survives restarts.

Question 9

A social media application uses Memorystore for Redis to cache user profiles and session data. Recently, the application experienced intermittent errors and high latency. You observe that the Redis CPU utilization is consistently above 90% and the cache hit ratio is 85%. The instance type is a Standard tier M2 (30 GB) with a maxmemory setting of 25 GB. The eviction policy is allkeys-lru. The number of keys is 10 million with an average value size of 2 KB. You suspect memory pressure is causing CPU spikes. What should you do to reduce CPU utilization and improve performance?

Accepted Answer

Upgrade the Memorystore instance to a larger size (e.g., M5 with 60 GB) to provide more CPU and memory resources.. Upgrading to a larger instance type provides more memory and CPU capacity. Reducing the eviction policy to volatile-lru would only evict keys with TTL, potentially increasing misses. Monitoring and tuning will not reduce current load. Reducing value size is application-level and cannot be applied quickly.

Answer

Reduce the maxmemory setting to force more aggressive eviction and free up memory.

Answer

Change the eviction policy to volatile-lru to prefer evicting keys with TTL.

Answer

Implement Redis monitoring with Cloud Monitoring and set alerts to notify when CPU exceeds 80%.

Question 10

You are using Cloud Monitoring to track performance of a Cloud Spanner instance. Which metric indicates that a database is approaching its throughput limits and may need a split or additional nodes?

Accepted Answer

High CPU utilization (percentage). High CPU utilization (percentage) is the correct metric because Cloud Spanner's CPU utilization directly reflects the processing load on the instance's nodes. When CPU utilization consistently exceeds 65-70%, it indicates the database is approaching its throughput limits, often requiring a split (to distribute load across more tablets) or additional nodes to maintain performance and avoid throttling.

Answer

Storage utilization (bytes used)

Answer

Query error rate (errors per second)

Answer

Average commit latency

Question 11

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. Option C is correct because high CPU utilization indicates the instance is overloaded; the immediate fix is to add nodes. Option A is wrong because reviewing queries is important but the alert signals capacity issue. Option B is wrong because indexing might not reduce overall CPU if workload is balanced. Option D is wrong because splitting tables is a schema change and doesn't address node 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 12

You are a Cloud Database Engineer managing a Cloud Spanner instance. You notice that some queries are taking longer than expected. You suspect that the queries are not using secondary indexes efficiently. Which TWO metrics should you monitor in Cloud Monitoring to validate your suspicion? (Choose two.)

Accepted Answer

Statement scan rows returned. Option B: 'Statement scan rows returned' shows the number of rows scanned per query; a high value compared to rows returned indicates inefficient index usage. Option D: 'Row count returned by index' shows how many rows are returned from index scans; if it's high but the final result is small, the index may be too broad. Option A is about overall latency, not specific to index usage. Option C is about CPU, not directly about index usage. Option E is about lock conflicts, unrelated.

Answer

CPU utilization

Answer

Lock conflicts

Answer

Query scan latency (mean)

Question 13

You manage a Cloud SQL for PostgreSQL instance that is experiencing high read latency. You have already tuned the buffer cache and queries. Which THREE actions can further reduce read latency? (Choose three.)

Accepted Answer

Increase the number of vCPUs on the primary instance.. Creating read replicas offloads reads; adding an in-memory cache (e.g., Redis) reduces database load; and query optimization (indexing, rewriting) directly reduces read time. Vertical scaling adds hardware but may not be cost-effective; enabling query logging adds overhead, not help.

Answer

Enable the PostgreSQL slow query log and analyze it.

Answer

Use connection pooling to reduce the number of open connections.

Question 14

A data engineering team wants to monitor BigQuery query performance and slot utilization. Which TWO tools or features should they use? (Choose two.)

Accepted Answer

INFORMATION_SCHEMA tables (e.g., JOBS_BY_PROJECT). BigQuery's INFORMATION_SCHEMA provides query statistics and slot usage. Cloud Monitoring can alert on slot utilization. Cloud Logging stores audit logs but not detailed performance; the BigQuery CLI is for management; Cloud Profiler is for code profiling, not BigQuery slots.

Answer

Cloud Logging (audit logs)

Answer

bq command-line tool with the '--format=prettyjson' flag

Answer

Cloud Profiler

Question 15

You are managing a Cloud SQL for MySQL instance that supports a web application. Recently, users have reported that the application is responding slowly during peak hours. You examine the Query Insights dashboard and see that a specific query is running frequently and has a high execution time. The query involves JOINs on three tables, each with tens of thousands of rows. The query plan shows a full table scan on two tables. What should you do first to improve performance?

Accepted Answer

Add indexes on the columns used in JOIN conditions.. Option C is correct: Adding appropriate indexes on the join columns will reduce full table scans, which is the most effective immediate action. Option A (increasing instance size) may help but does not address the root cause. Option B (enabling database flags like query cache) is less effective and query cache is deprecated in newer versions. Option D (rewriting the query) could help but is more complex and time-consuming; indexing is usually the first step.

Answer

Enable the query cache flag in Cloud SQL database flags.

Answer

Increase the instance size to provide more memory and CPU.

Answer

Rewrite the query to use subqueries instead of JOINs.