PCDE Manage a solution that can span multiple database technologies Practice Test 2 — 15 Questions

Question 1

A company uses Cloud Spanner for a global inventory system. They need to add a new index on an existing table to support a new query pattern without downtime. What is the correct approach to create the index?

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

Accepted Answer

Use the gcloud CLI to create the index; Spanner handles it as an online, non-blocking operation. Cloud Spanner supports online schema changes, including index creation, without requiring downtime. When you use the gcloud CLI or the Cloud Console to create an index, Spanner performs the operation as a non-blocking, asynchronous background process that allows continued reads and writes on the table.

Answer

Create a new table with the index and migrate data manually

Answer

Use the Cloud Console to create the index; it requires a maintenance window

Answer

Take the table offline, create the index, then bring it back online

Question 2

A company is using Memorystore for Redis and needs to persist data across restarts. Which option should they use?

Accepted Answer

Take periodic snapshots and store them in Cloud Storage. Memorystore for Redis does not natively support AOF persistence; it relies on periodic RDB snapshots to persist data. By taking these snapshots and storing them in Cloud Storage, you can restore the Redis instance after a restart or failure, ensuring data durability across restarts.

Answer

Enable AOF persistence in Memorystore settings

Answer

Use a standard tier instance with persistence enabled

Answer

Enable auto-backup in Memorystore

Question 3

A company is using Memorystore for Redis as a session store. They need to scale horizontally to handle increasing traffic and ensure high availability with automatic failover. Which TWO actions should they take?

Accepted Answer

Enable Redis Cluster mode (sharding). Memorystore for Redis supports sharding for horizontal scaling, and standard tier provides replication with automatic failover.

Answer

Create multiple standalone instances and route traffic manually

Answer

Use Persistent Disk for data persistence

Answer

Use the Basic tier

Question 4

An engineer needs to restore a Cloud Spanner database from a backup that was taken 2 hours ago. The backup is stored in Cloud Storage. What steps are required?

Accepted Answer

Use gcloud spanner databases restore --backup= --destination-database=. Cloud Spanner backups are not stored directly in Cloud Storage; they are stored within the instance's internal backup storage. The question's mention of 'stored in Cloud Storage' is a common misstatement. The correct restoration method uses the `gcloud spanner databases restore` command with a backup ID, as shown in option A. Import/export uses Cloud Storage, but that is a different process.

Answer

Create a new instance and use gcloud spanner instances restore.

Answer

Use gcloud spanner databases import from the Cloud Storage backup file.

Answer

Restore is not possible; you must use point-in-time recovery.

Question 5

A Bigtable instance is experiencing high latency and uneven load distribution. The operations team suspects a hot spot. Which tool should they use to identify the hot spot, and what action can they take to mitigate it?

Accepted Answer

Use Key Visualiser to identify the hot row/key; then redesign the row key to distribute load.. Key Visualizer is the correct tool for identifying hot spots in Cloud Bigtable because it provides a heatmap of row key access patterns, allowing you to pinpoint specific rows or key ranges causing uneven load. Once identified, redesigning the row key (e.g., by salting or reversing keys) distributes writes and reads across nodes, mitigating the hot spot without requiring manual table splits or storage changes.

Answer

Use 'gcloud bigtable hot-spots list' command; then split the hot table into multiple tables.

Answer

Use Cloud Monitoring to check CPU utilisation; then add more nodes to the cluster.

Answer

Use the Bigtable admin console to view table statistics; then change the storage type from HDD to SSD.

Question 6

A Cloud SQL for PostgreSQL read replica is experiencing replication lag of several minutes. You need to reduce the lag. What is the most effective solution?

Accepted Answer

Increase the CPU and memory of the read replica to speed up applying changes.. Replication lag in Cloud SQL for PostgreSQL read replicas is often caused by the replica being unable to keep up with the rate of write-ahead log (WAL) apply from the primary. Increasing the CPU and memory of the read replica directly improves its ability to replay WAL records faster, reducing the lag. This is the most effective solution as it addresses the bottleneck at the replica side without impacting the primary instance.

Answer

Disable automated backups on the primary to free up resources.

Answer

Promote the replica to a standalone instance and recreate the replica.

Answer

Increase the CPU and memory of the primary instance.

Question 7

Your team manages a Cloud SQL for MySQL instance used by a critical application. You need to ensure the instance is recoverable to any point within the last 4 days, with a Recovery Point Objective (RPO) of under 5 minutes. What configuration steps are required?

Accepted Answer

Enable automated backups and binary logging. Set the transaction log retention period to 4 days.. Point-in-time recovery (PITR) in Cloud SQL for MySQL requires both automated backups and binary logging to be enabled. The transaction log retention period (set via transactionLogRetentionDays) determines how far back you can perform PITR, with a maximum of 7 days. Enabling automated backups alone (option A) does not provide PITR because binary logging is needed. Option B is incorrect because automated backups are required for PITR, and binary logging only without backups does not meet the recovery window. Option C with on-demand daily backups cannot provide an RPO under 5 minutes because binary logging captures ongoing changes. Therefore, option D is correct: enable automated backups, binary logging, and set the transaction log retention to 4 days to meet the 4-day recovery window and RPO under 5 minutes.

Answer

Enable automated backups and set the backup retention to 4 days. Binary logging is not required because automated backups already capture all changes.

Answer

Enable binary logging and set the binary log retention to 4 days. Automated backups are optional and not needed for PITR.

Answer

Create an on-demand backup daily and set binary log retention to 4 days. This provides the same RPO as automated backups with binary logging.

Question 8

You are responsible for a Cloud Spanner instance that serves a global user base. A new feature requires adding an index on a column of an existing table that contains millions of rows. The table is actively used by production traffic. What is the recommended approach to add the index with minimal impact?

Accepted Answer

Use the gcloud command 'gcloud spanner databases ddl update' with a CREATE INDEX statement. The operation will be non-blocking and will not impact production traffic.. Cloud Spanner supports online, non-blocking index creation. The CREATE INDEX statement processes the index in the background and does not block reads or writes on the table. This is the most efficient and least impactful method. Using a backup/restore or export/import is unnecessarily complex and disruptive.

Answer

Create a second table with the index already defined, then copy the data using batch writes and switch traffic. This avoids any impact to the original table.

Answer

Take a full database backup to Cloud Storage, restore it to a new instance, create the index on the restored instance, and then fail over to the new instance.

Answer

Use the gcloud command 'gcloud spanner databases ddl update' with an ALTER TABLE statement to add the index. This will lock the table briefly but is acceptable.

Question 9

Your Cloud Bigtable instance is experiencing high latency for certain row key ranges. You suspect a hotspot is developing. Which Google Cloud tool should you use to visualize and diagnose the hotspot?

Accepted Answer

Key Visualiser. Key Visualiser is a tool specifically designed for Bigtable to identify hotspots and access patterns by visualizing row key distribution and traffic. It helps pinpoint uneven load distribution.

Answer

Cloud Monitoring dashboard to check CPU utilization per node

Answer

Bigtable's built-in 'hotspot detection' alert in Cloud Monitoring

Answer

Cloud Logging to review request logs and identify slow queries

Question 10

You manage a Memorystore for Redis instance that is regularly hitting its maximum memory limit. The application can tolerate some data loss and prefers to keep the most recently used data. Which eviction policy should you configure?

Accepted Answer

volatile-lru. volatile-lru evicts keys with an expiry set (volatile keys) using an LRU (least recently used) algorithm. This fits the requirement: the application can tolerate loss of expired data, and it prefers to keep recently used data. allkeys-lru would evict any key, which might include important unexpired keys.

Answer

noeviction

Answer

volatile-ttl

Answer

allkeys-lru

Question 11

Your Firestore database in Native mode is used by a mobile app. You need to query a collection where documents are filtered by two fields: 'status' (string) and 'createdAt' (timestamp). The query is not performing as expected. What action is required?

Accepted Answer

Create a composite index on the 'status' and 'createdAt' fields.. Firestore in Native mode requires a composite index to efficiently query documents filtered by multiple fields, such as 'status' and 'createdAt'. Without this index, the query may fail or perform poorly, as Firestore cannot combine separate single-field indexes for equality and range filters. Option C is correct because creating a composite index on both fields enables the query to run as expected.

Answer

Create an index exemption for the collection to allow multi-field queries.

Answer

Add a third field to the query to make it more specific.

Answer

Ensure that single-field indexes exist for both 'status' and 'createdAt'.

Question 12

You need to set up disaster recovery for a Cloud SQL for PostgreSQL instance. The primary instance is in us-central1, and you want a standby in us-west1 that can be promoted to a standalone instance during a regional outage. The solution must minimize data loss and recovery time. Which approach should you take?

Accepted Answer

Create a cross-region read replica in us-west1. During a disaster, promote the replica to a standalone instance.. Cross-region read replicas in Cloud SQL for PostgreSQL use synchronous replication (for regional replicas, it's asynchronous across regions, but still the best option for DR). Creating a cross-region read replica in us-west1 allows it to be promoted to a standalone instance in DR scenarios. Cross-region backups are point-in-time backups, not a live standby. External replication is not managed by Cloud SQL.

Answer

Configure cross-region automated backups with a retention of 7 days. In the event of a disaster, restore the latest backup to a new instance in us-west1.

Answer

Set up an on-premise PostgreSQL instance and configure streaming replication from Cloud SQL to the on-premise instance.

Answer

Use gcloud sql instances create with a backup configuration pointing to us-west1, but this does not create a live standby.

Question 13

A Cloud SQL for MySQL instance is running low on disk space. You have enabled automatic storage increase, but you also want to be proactively alerted when disk usage exceeds 80%. Which steps should you take?

Accepted Answer

Create a Cloud Monitoring alert on the metric 'disk/utilization' with a threshold of 80%.. Cloud Monitoring allows you to create alerting policies based on metrics. The disk usage metric for Cloud SQL is available, and you can set a threshold of 80%. Automatic storage increase is a separate setting that automatically increases storage when needed.

Answer

Set the storage size to a fixed large value to avoid alerts.

Answer

Create a Cloud Logging sink for disk usage logs and set up a Pub/Sub notification.

Answer

Use Cloud SQL's built-in email notifications for disk usage.

Question 14

Your Memorystore for Redis instance is used as a session store for a web application. You need to ensure that session data is not lost during a node failure. What should you do?

Accepted Answer

Enable AOF persistence in the Memorystore instance.. Memorystore for Redis supports persistence via RDB snapshotting or AOF (Append-Only File). For minimal data loss during a node failure, AOF persistence is recommended because it logs every write operation, allowing recovery with only a few seconds of data loss (depending on fsync settings). Standard Tier with replication provides high availability but does not persist data to disk, so a full zone failure can cause data loss. Periodic exports to Cloud Storage have gaps between exports, leading to data loss of up to the export interval. Therefore, enabling AOF persistence is the best approach to prevent data loss during a node failure.

Answer

Use a Basic Tier instance with a large maxmemory setting.

Answer

Configure the instance as Standard Tier (with replication) and schedule periodic exports to Cloud Storage.

Answer

Enable persistence by setting the 'persistence' parameter to 'rdb' in the instance configuration.

Question 15

You have a Cloud Spanner database that needs to be migrated from one region to another. You want to ensure no data loss and minimal downtime. Which approach should you use?

Accepted Answer

Create a backup of the database and restore it to a new instance in the target region.. Cloud Spanner does not support in-place region changes or read replicas in different regions. The only supported method to migrate a Cloud Spanner database between regions with no data loss and minimal downtime is to create a backup of the database and restore it to a new instance in the target region. This approach ensures a consistent snapshot of the data and allows you to plan the cutover window to minimize downtime.

Answer

Use gcloud spanner instances move to change the region of the existing instance.

Answer

Create a read replica in the target region and promote it after replication catches up.

Answer

Use gcloud command 'gcloud spanner databases export' to export the database to CSV files, then import into the new instance.