PCDE Mock Exam 76 — 25 Questions

Question 1

A Memorystore for Redis instance is running out of memory. The application can tolerate some data loss but not crashes. The team wants to ensure the instance remains available without manual intervention. Which eviction policy should they configure?

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

Accepted Answer

allkeys-lru. (allkeys-lru) is correct because it allows Redis to evict the least recently used keys from the entire keyspace when memory is full, which keeps the instance available without crashing. Since the application can tolerate some data loss but not crashes, this policy ensures memory pressure is relieved automatically, preventing out-of-memory errors that would cause the instance to become unavailable.

Answer

volatile-lru

Answer

volatile-ttl

Answer

noeviction

Question 2

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 3

A company uses BigQuery for BI reporting with a star schema. The fact table 'sales' is partitioned by date and clustered by 'product_id'. The dimensions 'product' and 'customer' are updated nightly via merge statements. Recently, a report that joins 'sales' with 'product' on 'product_id' and filters on sale_date for the last 7 days started timing out. The query plan shows a 'SCAN' of the entire 'product' table. Which optimization should be applied to improve performance?

Accepted Answer

Cluster the 'product' table on 'product_id'. Clustering the 'product' table on 'product_id' physically co-locates rows with the same product_id into the same blocks, drastically reducing the amount of data scanned when the report joins on that column. The query plan's full SCAN of the 'product' table indicates that BigQuery must read every row, even though only a subset of products are referenced by the last 7 days of sales. Clustering on product_id enables block-level pruning, so only the relevant blocks are read, eliminating the full table scan.

Answer

Partition the 'product' table by 'product_id'

Answer

Partition the 'sales' table by 'product_id' instead of date

Answer

Remove clustering from the 'sales' table

Question 4

A financial services company uses BigQuery for BI reporting. They need to design a data model that ensures data consistency and avoids duplicate records in the fact table. Which three practices should they follow? (Choose three.)

Accepted Answer

Use the OVERWRITE partition option for incremental loads.. Using the OVERWRITE partition option for incremental loads ensures that only the specific partition being loaded is replaced, preventing duplicate records within that partition while preserving data in other partitions. This approach maintains data consistency by avoiding full table overwrites and is efficient for incremental updates in BigQuery.

Answer

Apply a unique constraint on the fact table.

Answer

Use a daily load job that replaces the entire table with WRITE_TRUNCATE.

Question 5

You need to design a Bigtable row key for a time-series application that records temperature readings from thousands of sensors. The most common query is 'get all readings for a specific sensor in the last hour'. Which row key design is optimal?

Accepted Answer

sensorID#reverse_timestamp. Optimal because it groups all readings for a sensor together (via sensorID as the row key prefix) while using reverse timestamps to ensure the most recent data appears first within each row. This design allows Bigtable to efficiently scan a single row for the last hour's readings using a prefix scan on sensorID with a timestamp range filter, minimizing the number of rows accessed.

Answer

timestamp#sensorID

Answer

sensorID#timestamp

Answer

hash(sensorID)#timestamp

Question 6

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 7

A company runs an e-commerce application on Cloud SQL for MySQL. They need to recover the database to a specific point in time 15 minutes ago after an accidental data deletion. What must be configured beforehand?

Accepted Answer

Configure binary logging and set a PITR retention period. Point-in-time recovery (PITR) in Cloud SQL for MySQL relies on binary logging (binlog) to capture all write operations. To recover to a specific moment (e.g., 15 minutes ago), you must enable automated backups (which provide the base restore point) and configure binary logging with a retention period that covers the desired recovery window. Option C correctly specifies both requirements: binary logging must be enabled, and a PITR retention period (e.g., 1–7 days) must be set to retain the necessary binlog files for replay.

Answer

Create a cross-region replica for failover

Answer

Enable automated backups only

Answer

Set up an on-demand backup every hour

Question 8

You are optimizing a Cloud SQL for MySQL instance for an OLTP application. You observe frequent buffer pool contention and high disk reads per second. The instance has 16 vCPUs and 120 GB memory. What is the most effective initial tuning action?

Accepted Answer

Increase innodb_buffer_pool_size to 80% of available memory.. Buffer pool contention and high disk reads per second are classic symptoms of an undersized InnoDB buffer pool. For a dedicated Cloud SQL for MySQL OLTP instance with 120 GB memory, increasing innodb_buffer_pool_size to 80% (96 GB) maximizes cached data and index pages in memory, reducing physical disk I/O and contention. This directly addresses the root cause—insufficient memory for the working set—without introducing side effects like connection overhead or query cache invalidation.

Answer

Increase the max_connections parameter to handle more concurrent sessions.

Answer

Reduce max_heap_table_size to avoid in-memory temp tables spilling to disk.

Answer

Enable query cache to cache SELECT results.

Question 9

A large e-commerce platform uses BigQuery for business intelligence. They have a fact table `orders` (10 TB, partitioned by order_date, clustered by customer_id) and a dimension table `customers` (2 TB, not partitioned, not clustered). The BI team runs a daily dashboard query that joins these tables on customer_id and filters on order_date = CURRENT_DATE() and customer_country = 'US'. The query currently scans the full `customers` table and 2 GB of the `orders` table, taking 30 seconds. The business wants to reduce cost and latency. The `customers` table has 500 million rows and is updated incrementally every hour. Which action will most effectively reduce the amount of data scanned and query time?

Accepted Answer

Cluster the `customers` table on customer_id.. Clustering the `customers` table on `customer_id` will physically co-locate rows with the same `customer_id`, allowing the query to use block-level pruning when joining with the filtered `orders` table. Since the query filters `orders` by `order_date = CURRENT_DATE()` (2 GB scanned) and then joins on `customer_id`, BigQuery can skip reading most of the `customers` table if it is clustered on the join key, drastically reducing the 2 TB full scan and lowering both cost and latency.

Answer

Denormalize customer country and other attributes into the `orders` table.

Answer

Create a materialized view that joins `orders` and `customers` on customer_id.

Answer

Partition the `customers` table by customer_id.

Question 10

A global gaming company uses Spanner to store player profiles and scores. The most common query is 'Get the top 10 players by score' across all regions. The 'Players' table has millions of rows. Which schema design and query approach provides the best performance?

Accepted Answer

Add a secondary index on score column and query 'SELECT * FROM Players ORDER BY score DESC LIMIT 10'.. A secondary index on the `score` column allows Spanner to perform an index scan in descending order, retrieving only the top 10 rows without scanning the entire `Players` table. The `ORDER BY score DESC LIMIT 10` query leverages the index's sorted structure, making it the most efficient approach for this common query pattern in a globally distributed database.

Answer

Add a generated column storing the score as a string and index it.

Answer

Use a STORING clause to store additional columns in the index.

Answer

Use a parent-child interleaving between a 'Leaderboard' parent table and 'Players' child table.

Question 11

Refer to the exhibit. Which BigQuery SQL query correctly flattens the items into rows?

Accepted Answer

SELECT * FROM orders CROSS JOIN UNNEST(items) AS items. `CROSS JOIN UNNEST(items)` is the standard BigQuery syntax to flatten a repeated (array) column into individual rows. The `UNNEST` operator expands each array element into a separate row, and `CROSS JOIN` ensures that all non-array columns from the `orders` table are preserved alongside each element. This is the only option that correctly transforms the nested `items` array into a normalized row-per-item structure.

Answer

SELECT * FROM orders WHERE items IS NOT NULL

Answer

SELECT * FROM orders, UNNEST(items) AS items

Answer

SELECT * FROM orders INNER JOIN items ON true

Question 12

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is designed for petabyte-scale, low-latency (single-digit ms), high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is optimised for analytics (seconds-to-minutes latency), Cloud SQL is for OLTP (limited to tens of thousands of QPS), and Firestore is for document data with hierarchical structure.

Answer

Cloud Spanner

Answer

BigQuery

Answer

Firestore

Question 13

Refer to the exhibit.

Which of the following statements is true regarding this schema design?

Accepted Answer

Deleting a Singer row will automatically delete all associated Album rows.. The `ON DELETE CASCADE` clause on the foreign key from `Albums` to `Singer` ensures that when a row in the `Singer` table is deleted, all rows in the `Albums` table that reference that singer are automatically deleted. This is a standard referential integrity behavior in relational databases, and in Cloud Spanner (the technology context for PCDE), it is enforced at the database level to maintain consistency.

Answer

The Albums table cannot have any secondary indexes because of the INTERLEAVE clause.

Answer

The Albums table's rows are physically stored independent of the Singer table.

Answer

The Albums table's primary key must include the SingerId column only.

Answer

The ON DELETE CASCADE clause ensures that deleting an Album row will delete the corresponding Singer row.

Question 14

You are planning capacity for a Cloud Spanner instance. Which TWO factors directly affect the number of nodes required?

Accepted Answer

Read throughput in queries per second (QPS). Cloud Spanner node capacity is primarily determined by compute and I/O requirements, which are directly driven by read and write throughput (QPS). Each node provides a fixed amount of processing power and throughput; therefore, to handle a given QPS, you must provision enough nodes to meet the peak read and write demand. Storage size (Option E) is not a direct factor because Spanner automatically uses available node resources for storage, and you can add nodes for throughput without exceeding storage limits.

Answer

Number of users

Answer

Number of indexes

Answer

Storage size in GB

Question 15

A company uses BigQuery to run business intelligence reports. The data engineer needs to implement a star schema for a sales data warehouse. Which THREE are best practices when designing the tables?

Accepted Answer

Use a primary key on fact tables to enforce uniqueness. In BigQuery, fact tables should have a primary key to enforce uniqueness of each sales transaction, preventing duplicate rows that would skew aggregations like SUM or COUNT. BigQuery does not enforce primary keys natively, but defining them in the schema (e.g., using PRIMARY KEY constraint in DDL) allows the query engine to optimize joins and deduplication, especially when using MERGE statements. This ensures data integrity in the star schema.

Answer

Use natural keys in dimension tables for simplicity

Answer

Store pre-aggregated data in dimension tables

Question 16

Your Cloud Spanner instance has several tables with interleaved parent-child relationships. You notice that queries that join parent and child tables are slow. What is the best practice to optimize these joins?

Accepted Answer

Ensure the tables are defined as interleaved with the parent key as the first part of the child primary key. Cloud Spanner optimizes interleaved table joins by physically co-locating parent and child rows on the same split, based on the parent key as the prefix of the child's primary key. This eliminates the need for distributed cross-split joins, dramatically reducing latency. Queries that join on the interleaved key benefit from local data access, making them fast and efficient.

Answer

Create secondary indexes on the join columns

Answer

Use batch update operations to reduce round trips

Answer

Remove interleaving and use a separate JOIN statement

Question 17

A Database Engineer is deploying a Cloud SQL for PostgreSQL instance for a financial services application that requires high availability and automatic failover. The engineer also needs to ensure that backups are taken daily and retained for 30 days. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Enable automated backups and set backup retention to 30 days.. Cloud SQL automated backups can be configured with a retention period of up to 365 days, and setting it to 30 days meets the requirement for daily backups with 30-day retention. Option C is correct because enabling high availability (HA) on a Cloud SQL instance with a regional persistent disk provides automatic failover to a standby instance in a different zone within the same region, ensuring high availability for the financial services application.

Answer

Create a cross-region replica and configure automatic failover.

Answer

Schedule a Cloud Scheduler job to export the database to Cloud Storage daily.

Answer

Enable point-in-time recovery (PITR) with a 7-day retention.

Question 18

A team is migrating a 10 TB PostgreSQL database to AlloyDB using DMS with continuous CDC. The migration starts, but the CDC phase is falling behind, with lag increasing over time. The source is a busy production database with high write throughput. What is the most effective action to reduce lag?

Accepted Answer

Increase the source database's resources (CPU/memory) to handle logical replication load.. Increasing the source database's resources (CPU/memory) directly addresses the root cause of CDC lag in a high-write-throughput PostgreSQL environment. DMS logical replication relies on the source's ability to decode WAL (Write-Ahead Log) records quickly; if the source is CPU- or memory-bound, it cannot keep up with the rate of changes, causing lag to grow. Scaling up the source reduces the bottleneck in WAL generation and decoding, allowing DMS to consume changes faster.

Answer

Reduce the batch size for the CDC phase.

Answer

Increase the number of DMS worker nodes.

Answer

Add more indexes to the target AlloyDB tables.

Question 19

A Cloud SQL instance stores financial data. They need to meet a 1-hour RPO and 30-minute RTO. What backup configuration should they use?

Accepted Answer

Automatic backups plus binary logging to enable point-in-time recovery (PITR).. Automatic backups combined with binary logging enable point-in-time recovery (PITR), which allows restoring the database to any point within the backup retention period. This configuration meets the 1-hour RPO by recovering transactions committed within the last hour, and the 30-minute RTO by using the most recent full backup plus binary logs to restore quickly.

Answer

Export once a day to Cloud Storage.

Answer

Enable high availability.

Answer

Automatic backups with a schedule of 1 hour.

Question 20

A Memorystore for Redis instance is running out of memory. The application can tolerate some data loss but not crashes. The team wants to ensure the instance remains available without manual intervention. Which eviction policy should they configure?

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

Accepted Answer

volatile-ttl. The volatile-ttl policy evicts keys with a TTL set, prioritizing those with the shortest remaining TTL. Since session data typically has a TTL and cache data may or may not, this policy allows you to protect session data by assigning it a longer TTL, while cache data with shorter TTLs is evicted first, minimizing cache misses.

Answer

noeviction

Answer

allkeys-lru

Answer

allkeys-random