PCDE Practice Test 5 — 25 Questions

Question 1

Match each BigQuery feature to its purpose.

Accepted Answer

Partitioning: Divides table into segments based on a column (e.g., date) to reduce query cost and improve performance.. These features optimize cost, performance, and management in BigQuery. Partitioning reduces scan costs, Clustering improves query performance, Materialized Views accelerate complex queries, and Slots provide resource control.

Answer

Partitioning: Sorts data based on one or more columns to improve query performance.

Answer

Clustering: Divides table into segments based on a column to reduce cost.

Question 2

A company is designing a database schema for a global e-commerce platform. Orders are created with high frequency, and order status updates occur frequently. The team needs to choose a primary key strategy for the orders table in Spanner. Which approach minimizes hot-spotting?

Accepted Answer

Use a universally unique identifier (UUID) as the primary key. In Spanner, monotonically increasing or time-ordered primary keys cause hot-spotting because all new writes are directed to the same tablet server, creating a single point of contention. UUIDs are randomly distributed, ensuring writes are spread evenly across the entire key space, which minimizes hot-spotting and maximizes write throughput.

Answer

Use a monotonically increasing integer (e.g., auto-increment)

Answer

Use a timestamp as the primary key

Answer

Use a composite key with user_id and order_date

Question 3

Your Cloud SQL for PostgreSQL instance is experiencing intermittent slowdowns during peak hours. You notice that the CPU utilization spikes to 80% and the number of connections increases. The application team confirms they are not running any new queries. What should you do first to diagnose the issue?

Accepted Answer

Use Cloud SQL Insights to analyze query performance and wait statistics.. Cloud SQL Insights provides built-in query performance monitoring and wait statistics that can pinpoint the root cause of intermittent slowdowns without making changes. Since CPU spikes and increased connections are symptoms, not the cause, analyzing wait events (e.g., CPU, IO, lock contention) directly reveals which queries or resources are bottlenecked. This is the first diagnostic step before any scaling or configuration changes.

Answer

Increase the machine type of the Cloud SQL instance to add more CPU.

Answer

Enable connection pooling to reduce the number of connections.

Answer

Set a maximum connection limit and reduce the connection lifetime.

Question 4

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. Option C is correct because 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 5

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 colocates related rows, reducing cross-node communication. Option B is correct because denormalizing frequently joined lookup tables into the parent table as repeated fields avoids joins entirely, further reducing latency. Option C is correct because parent-child interleaved tables physically store child rows adjacent to their parent row, minimizing splits and cross-node communication for join queries. Options D and E are incorrect: secondary indexes do not provide the same physical colocation benefits, and storing foreign keys as JSON arrays in the parent table would complicate queries and fail to leverage Spanner's distributed architecture.

Answer

Create secondary indexes on foreign key columns

Answer

Store foreign key relationships as JSON arrays in the parent table

Question 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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

Question 14

You are managing a Cloud SQL for PostgreSQL instance that is experiencing high CPU usage and slow query performance. You notice that the database has a high number of idle-in-transaction connections. Which immediate action should you take to reduce CPU load without disrupting active transactions?

Accepted Answer

Set the cloudsql.enable_idle_in_transaction_session_timeout flag to true and configure idle_in_transaction_session_timeout.. Option C is correct because setting the `cloudsql.enable_idle_in_transaction_session_timeout` flag to true and configuring `idle_in_transaction_session_timeout` allows Cloud SQL to automatically terminate idle-in-transaction connections after a specified timeout, reducing CPU load without manually killing connections or disrupting active transactions. This is a built-in, non-disruptive mechanism that targets only connections that are holding resources while idle, freeing up CPU and memory for active queries.

Answer

Use VPC firewall rules to block new connections until the issue resolves.

Answer

Kill all idle-in-transaction connections using pg_terminate_backend.

Answer

Set a statement_timeout at the session level for new connections.

Question 15

You are running a production workload on Cloud Bigtable and notice that read latency has increased. Upon reviewing the monitoring dashboard, you see that CPU utilization is below 50% but the number of active tablets is high. What is the most likely cause of the increased read latency?

Accepted Answer

There are too many tablets, causing increased metadata operations and slower reads.. In Cloud Bigtable, each tablet is a contiguous range of rows managed by a tablet server. When the number of active tablets is high, the tablet server must perform more metadata operations (e.g., splitting, merging, and serving multiple tablets) which increases per-request overhead and can degrade read latency. This is true even when CPU utilization is below 50%, because the overhead is not purely CPU-bound but involves increased I/O and coordination.

Answer

Read requests are being throttled due to exceeding IOPS limits.

Answer

A hot node is throttling read requests.

Answer

The cluster is underprovisioned, causing resource contention.

Question 16

You are monitoring a Cloud Spanner instance and see that the average commit latency is high. The application performs many single-row inserts. Which metric would you check first to understand the root cause?

Accepted Answer

Lock conflicts (e.g., Spanner/API/Lock_wait).. High commit latency for single-row inserts in Cloud Spanner is often caused by lock conflicts, as concurrent transactions may contend for the same row or index. The metric Spanner/API/Lock_wait directly measures time spent waiting for locks, making it the first metric to check. High commit latency without high lock wait suggests other issues, but lock conflicts are the most common root cause for write-heavy single-row workloads.

Answer

Read latency.

Answer

Storage utilization.

Answer

CPU utilization per node.

Question 17

A company runs a BigQuery data warehouse. They notice that query performance has degraded over time. The data is loaded daily from Cloud Storage using batch loads. Which action is most likely to improve query performance?

Accepted Answer

Partition and cluster tables based on common query filters.. Partitioning and clustering tables based on common query filters directly reduces the amount of data scanned per query by organizing data into physical segments. In BigQuery, this allows the query engine to prune entire partitions and clusters, significantly lowering I/O and improving performance without additional cost or complexity.

Answer

Increase the number of slots in the reservation.

Answer

Create materialized views for all frequent queries.

Answer

Migrate the data to Cloud SQL for better performance.

Question 18

You are troubleshooting a slow-performing query on Cloud Spanner. The query scans a large table with a secondary index. Which TWO metrics from the Query Insights dashboard would most directly indicate the source of the performance issue?

Accepted Answer

CPU time. CPU time (A) is correct because high CPU usage indicates that the query is performing expensive operations like sorting, joining, or complex filtering, which can slow performance even if the index is used. Rows scanned (B) is correct because scanning a large number of rows, even with a secondary index, suggests the index is not selective enough or the query is retrieving many rows, leading to excessive I/O and latency. Both metrics directly point to query execution inefficiency.

Answer

Storage utilization

Answer

Commit latency

Answer

Lock wait time

Question 19

Your Cloud SQL for PostgreSQL instance is experiencing high CPU utilization during peak hours. You notice that the query `SELECT * FROM orders WHERE order_date >= '2024-01-01'` is frequently run against a table with 10 million rows. The table has a B-tree index on `order_date`. What is the most likely cause of the high CPU usage, and how should you address it?

Accepted Answer

The query retrieves all columns, causing significant heap lookup overhead; rewrite the query to select only required columns.. Option C is correct because the query uses `SELECT *`, which forces PostgreSQL to fetch all columns from the heap (the main table storage) even though the index on `order_date` can efficiently locate the matching rows. This results in significant heap lookup (also known as bitmap heap scan or index scan with tuple retrieval) overhead, consuming CPU cycles for each row fetched. Reducing the selected columns to only those needed minimizes I/O and CPU usage by avoiding unnecessary data retrieval from the heap.

Answer

The index is fragmented; rebuild the index to improve performance.

Answer

Increase the instance machine type to provide more CPU capacity.

Answer

The index on `order_date` is not being used; add a hint to force index usage.

Question 20

You are managing a Cloud SQL for MySQL instance that is experiencing high replication lag. The instance uses semi-synchronous replication. Which THREE actions could reduce the replication lag?

Accepted Answer

Change replication mode from semi-synchronous to asynchronous.. Option A is correct because switching from semi-synchronous to asynchronous replication removes the requirement for the primary to wait for at least one replica to acknowledge receipt of each transaction. In semi-synchronous mode, the primary waits for acknowledgment, which can introduce latency and contribute to replication lag under high write loads. Asynchronous replication allows the primary to commit transactions immediately without waiting, reducing the time the primary spends waiting for replica acknowledgment and thus lowering replication lag.

Answer

Reduce the binlog retention period on the primary.

Answer

Enable binary log compression on the primary.