PCDE Practice Test 7 — 10 Questions

Question 1

A company is migrating a large Oracle database to Cloud Spanner. The schema includes several tables with foreign key relationships. The team wants to minimize query latency for join queries that always involve a parent table and its children. Which THREE schema design strategies should the team consider? (Choose THREE.)

Accepted Answer

Design child table primary keys to start with the parent key (e.g., CustomerId, OrderId). Option A is correct because in Cloud Spanner, designing child table primary keys to start with the parent key (e.g., CustomerId, OrderId) enables efficient key-range scans and reduces the number of splits needed for join queries. This pattern leverages Spanner's distributed architecture to colocate related rows, minimizing cross-node communication and query latency.

Answer

Create secondary indexes on foreign key columns

Answer

Store foreign key relationships as JSON arrays in the parent table

Question 2

A retail company is designing a Cloud Spanner schema for an order management system. Orders are identified by a UUID and contain multiple line items. Each line item references a product. Which schema design best supports high read throughput for queries that retrieve all line items for a given order?

Accepted Answer

Create an Orders table and a LineItems table interleaved in Orders with ORDER_ID as the parent key.. Option B is correct because Cloud Spanner interleaved tables store child rows (LineItems) physically adjacent to their parent row (Orders) on the same split, enabling a single key lookup to retrieve all line items for a given order without cross-table joins or distributed queries. This colocation maximizes read throughput by minimizing latency and avoiding scatter-gather operations across nodes.

Answer

Store orders and line items in a single table with repeated fields for line items.

Answer

Create separate Orders and LineItems tables with a foreign key relationship and index on ORDER_ID.

Answer

Denormalize product information into the LineItems table and store orders separately.

Question 3

You are managing a Cloud SQL for MySQL instance that is experiencing high latency and connection timeouts during peak hours. The current configuration uses 4 vCPUs, 15 GB memory, and 100 GB SSD storage. The database workload is a mix of transactional queries and batch inserts. Which TWO actions would most effectively reduce latency and improve performance?

Accepted Answer

Increase the instance to 8 vCPUs and 30 GB memory.. Option C is correct because increasing vCPUs and memory directly addresses the resource bottleneck causing high latency and connection timeouts during peak hours. Cloud SQL for MySQL performance is heavily dependent on CPU for query processing and memory for buffer pool caching; doubling these resources reduces query execution time and improves concurrency handling.

Answer

Disable binary logging to reduce write I/O.

Answer

Increase the storage size to 200 GB to improve IOPS.

Answer

Decrease the max_connections parameter to reduce overhead.

Question 4

Which THREE factors should you consider when configuring Cloud SQL for MySQL query caching to optimize performance?

Accepted Answer

High write workloads can lead to frequent cache invalidation, reducing its effectiveness.. Option A is correct because in Cloud SQL for MySQL, high write workloads cause frequent updates to tables, which invalidates the query cache entries for those tables. This means the cache must be repopulated often, reducing the hit rate and potentially adding overhead from cache maintenance, making it less effective for performance optimization.

Answer

Query cache uses disk storage for cached results.

Answer

Prepared statements always bypass the query cache.

Question 5

Which TWO actions can help reduce the number of read replicas needed for a Cloud SQL for PostgreSQL instance that serves a read-heavy workload?

Accepted Answer

Implement connection pooling to reuse database connections.. Option A is correct because connection pooling reduces the overhead of establishing new database connections, which can consume significant CPU and memory resources on the primary instance. By reusing existing connections, the primary instance can handle more read requests without needing additional read replicas to offload the connection management load. This directly reduces the number of replicas required for a read-heavy workload.

Answer

Enable synchronous replication on all read replicas.

Answer

Use smaller machine types for read replicas.

Answer

Increase the max_connections parameter on the primary instance.

Question 6

Your Spanner instance is running a workload with high read throughput. You notice that read latency has increased significantly. Upon investigating, you find that the instance is experiencing high CPU utilization on the Spanner nodes. The workload consists of many small point lookups (reads by primary key). Which action is most likely to reduce read latency?

Accepted Answer

Use stale reads with a timestamp bound to allow reads from replicas.. B is correct because stale reads allow Cloud Spanner to serve read requests from read-only replicas, which offloads CPU-intensive processing from the leading replica nodes. By using a timestamp bound (e.g., exact_staleness or max_staleness), the workload can tolerate slightly outdated data, reducing the load on the nodes and lowering read latency for point lookups.

Answer

Add secondary indexes on the primary key columns.

Answer

Redesign the schema to use interleaved tables.

Answer

Reduce the number of Spanner nodes to lower CPU overhead.

Question 7

Refer to the exhibit. You are analyzing a slow query in Cloud SQL for PostgreSQL. The execution plan shows a sequential scan. Which index should you create to most effectively improve query performance?

Accepted Answer

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);. Option D is correct because the query likely filters on `user_id` and then sorts or filters by `created_at`. A composite index on `(user_id, created_at)` allows PostgreSQL to first narrow down by `user_id` using index seek, then efficiently access rows in `created_at` order, avoiding a sequential scan. This matches the most common pattern for slow queries involving equality on `user_id` and range or ordering on `created_at`.

Answer

CREATE INDEX idx_orders_partial ON orders(created_at) WHERE user_id = 123;

Answer

CREATE INDEX idx_orders_created_at ON orders(created_at);

Answer

CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);

Question 8

A company uses Cloud Bigtable for time-series data from IoT devices. Each device sends a reading every second. The row key is device_id#timestamp (reverse timestamp). The team reports that queries for a specific device's data over the last hour are fast, but queries for all devices' data over the last minute are very slow. What is the most likely cause?

Accepted Answer

The row key design does not allow efficient scanning for all devices because device_id is the prefix.. Option C is correct because the row key design uses device_id as the prefix, which means all data for a given device is co-located in contiguous rows, making per-device scans efficient. However, a query for all devices over the last minute requires scanning every row in the table because the timestamp suffix is reversed and not a prefix; Bigtable cannot perform a range scan across all devices for a recent time window without a full table scan, which is extremely slow.

Answer

The Bigtable cluster does not have enough nodes to handle the scan.

Answer

The query is scanning multiple column families.

Answer

The table has too many tablets, causing high overhead.

Question 9

A bigquery job is running slower than expected. Checking the job information, you see that the slot usage is at 100% for the entire duration of the query. You are using on-demand pricing. What is the most effective way to improve query performance?

Accepted Answer

Purchase a slot reservation and assign the project to it.. With on-demand pricing, your query is limited to the default per-project slot capacity (typically 2,000 slots in BigQuery). If slot usage is at 100% for the entire duration, the query is resource-constrained and cannot be sped up without additional slots. Purchasing a slot reservation and assigning the project to it provides dedicated slots, eliminating the contention and allowing the query to run faster.

Answer

Create materialized views for common aggregations.

Answer

Cluster the tables on frequently filtered columns.

Answer

Partition the tables by date.

Question 10

Which TWO actions improve query performance and reduce cost in BigQuery for BI workloads?

Accepted Answer

Cluster tables on columns used in GROUP BY. Clustering tables on columns used in GROUP BY improves query performance by physically co-locating rows with similar values, reducing the amount of data scanned during aggregation. Partitioning on columns frequently used in WHERE clauses allows BigQuery to prune entire partitions from the scan, directly reducing both cost (bytes billed) and query execution time. These two optimizations are specifically recommended for BI workloads where repeated, selective queries are common.

Answer

Load data using batch loads instead of streaming

Answer

Store data in CSV format

Answer

Use SELECT * in all queries