PCDE Manage a solution that can span multiple database technologies — Timed 50 Questions

Question 1

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. The Key Visualiser is a tool in the Google Cloud Console for Cloud Bigtable that provides a visual heatmap of row key access patterns, helping you identify hotspots and design better row keys. It shows read/write activity per row key range.

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 2

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 3

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 is the native GCP observability service, and Cloud SQL exposes the 'cloudsql.googleapis.com/database/disk/utilization' metric. Creating an alerting policy on that metric with an 80% threshold and a notification channel (email, Pub/Sub, PagerDuty) is the standard, supported way to be proactively alerted. This works alongside automatic storage increase, which only reacts after usage grows.

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 4

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 5

You have a Memorystore for Redis instance used as a cache. You need to scale it to handle increased load. The current instance is Basic Tier with 2 GB memory. Which TWO actions can you take to scale the instance? (Choose two.)

Accepted Answer

Vertically scale the instance by increasing its memory size or changing to a higher-tier (e.g., Standard Tier).. Option A is correct because Memorystore for Redis supports vertical scaling, allowing you to increase the memory capacity of an existing instance or upgrade from Basic Tier to Standard Tier, which provides replication and higher availability to handle increased load. Option E is correct because enabling Redis Cluster mode on a Memorystore instance allows horizontal scaling by distributing data across multiple shards, increasing total capacity and throughput beyond a single instance's limits. Option B is not a native Memorystore scaling action; while client-side sharding is a general Redis pattern, Memorystore does not provide built-in support for managing multiple instances as a single sharded cluster this way. Option C is incorrect because changing the eviction policy to noeviction does not add memory or capacity; it only changes how Redis behaves when memory is full, and can cause write errors instead of scaling. Option D is incorrect because Memorystore for Redis read replicas are only available in Standard Tier and are used for read scaling and high availability, not for increasing write capacity or memory size, and the current instance is Basic Tier.

Answer

Create additional instances and implement client-side sharding.

Answer

Change the eviction policy to noeviction to allow more data.

Answer

Add read replicas to offload read traffic.

Question 6

A company uses Cloud SQL for MySQL to run an e-commerce application. They need to ensure that they can recover the database to any point within the last 4 days. They also want to minimize storage costs. Which configuration should they use?

Accepted Answer

Enable binary logging with the 'log_bin' flag and set the transaction log retention to 4 days in the backup configuration.. Point-in-time recovery (PITR) in Cloud SQL for MySQL requires binary logging, which can be enabled with the 'log_bin' flag. The transaction log retention period can be set from 1 to 7 days. For 4-day recovery, set the retention to 4 days. Option B is correct: enable binary logging and set the transaction log retention to 4 days. Option A is wrong because automated backups alone only restore to backup time, not any point. Option C is wrong because disabling automated backups is not necessary—automated backups are still needed for initial restore points, but PITR uses transaction logs. Option D is wrong because point-in-time recovery is enabled by setting binary logging and retention; there is no separate 'point_in_time_recovery' flag.

Answer

Enable automated backups with a 4-day retention schedule.

Answer

Enable binary logging and set the transaction log retention to 4 days. Disable automated backups.

Answer

Enable point-in-time recovery by setting the 'point_in_time_recovery' flag to true with a 4-day window.

Question 7

A Cloud SQL for MySQL instance is running out of storage. The team wants to increase storage automatically without manual intervention. However, they also want to control costs and avoid a sudden increase in storage beyond 1 TB. What should they do?

Accepted Answer

Enable auto-storage increase and set the maximum storage size to 1 TB.. Cloud SQL for MySQL provides an 'auto-storage increase' feature that automatically adds storage when usage is high. To control costs and prevent storage from exceeding 1 TB, you can enable this feature and set the maximum storage size to 1 TB. Option B is correct because it combines automation with a cost cap. Option A (using a Cloud Function) is more complex and not necessary since Cloud SQL has a built-in feature; it also doesn't guarantee a cap. Option C (manual resize) is not automated. Option D (enable auto-storage increase without setting a maximum) would allow storage to grow beyond 1 TB, failing the cost control requirement.

Answer

Set up a Cloud Function to monitor disk usage and resize the disk when needed.

Answer

Manually increase disk size by 10% each time usage reaches 80%.

Answer

Enable auto-storage increase; it automatically stops at 1 TB.

Question 8

A financial application uses Cloud Spanner and requires daily full backups stored in another region for compliance. The backups must be restorable without exporting/importing. Which backup method should they use?

Accepted Answer

Use Spanner's scheduled backups and configure a cross-region backup policy.. Cloud Spanner's scheduled backups support a cross-region backup policy, allowing backups to be stored in a different region for compliance and restorable directly within Spanner without export/import. This satisfies the daily full backup, cross-region, and no-export/import requirements.

Answer

Export the database to Avro files in Cloud Storage in another region, then import when needed.

Answer

Use Cloud Scheduler to trigger a script that takes a snapshot of the database in Cloud Storage.

Answer

Create on-demand backups and copy them to another region manually.

Question 9

A Cloud Memorystore for Redis instance needs to ensure data is not lost during a maintenance event. The team decides to enable persistence. However, Memorystore does not support Redis persistence. What is the recommended alternative?

Accepted Answer

Use the Standard tier with replication and take regular snapshots to Cloud Storage using a scheduled script.. Cloud Memorystore for Redis does not support native Redis persistence (RDB or AOF). To prevent data loss during maintenance events, the recommended alternative is to use the Standard tier (which provides replication and automatic failover) and supplement it with scheduled snapshots exported to Cloud Storage. This approach ensures a durable backup outside the Redis instance that can be restored if needed.

Answer

Migrate to Cloud SQL for MySQL and use its backup feature.

Answer

Enable AOF persistence in Memorystore.

Answer

Increase the maxmemory setting and rely on the allkeys-lru eviction policy.

Question 10

A Cloud SQL for PostgreSQL instance has a read replica lagging behind the primary. The team needs to monitor the replica lag and set up an alert if it exceeds 60 seconds. Which metric should they use?

Accepted Answer

replication_lag. The correct metric is `replication_lag` because it directly measures the delay in seconds between the primary and read replica in Cloud SQL for PostgreSQL. This metric reflects how far behind the replica is in applying changes from the primary's write-ahead log (WAL), making it the precise indicator for alerting when lag exceeds 60 seconds.

Answer

bytes_received

Answer

disk_read_ops

Answer

cpu_utilization

Question 11

An application uses Cloud Firestore. The team notices that queries on a collection with an array field are slow. They want to create an index on the array field. What should they do?

Accepted Answer

Create a composite index on the array field and another field.. Cloud Firestore does not support single-field indexes on array fields. To enable efficient queries involving array fields, you must create a composite index that includes the array field along with another field. This allows Firestore to use the composite index for array-contains queries and other array operations, improving query performance.

Answer

Create a collection group index on the field.

Answer

Create an index exemption for the array field.

Answer

Rely on the automatically created single-field index.

Question 12

A company uses Cloud Spanner and needs to implement change data capture (CDC) to stream changes to a downstream analytics pipeline. Which two features can they use? (Choose TWO.)

Accepted Answer

Pub/Sub integration to consume change stream data. Option E, Spanner change streams, is correct because Cloud Spanner natively provides change streams, a built-in CDC feature that tracks inserts, updates, and deletes on tables and emits them as a queryable stream of data change records with commit timestamps. Option A, Pub/Sub integration to consume change stream data, is correct because Spanner change streams can be read via the Data Change Records API and integrated with Pub/Sub (using the change stream connector/Dataflow templates) to publish those records to a Pub/Sub topic for downstream analytics pipelines. Option B, Cloud SQL for PostgreSQL logical replication, is incorrect because it applies to Cloud SQL PostgreSQL, not Cloud Spanner, and Spanner is not a PostgreSQL logical replication source. Option C, Datastream, is incorrect because Datastream supports CDC from sources such as Oracle, MySQL, PostgreSQL, and SQL Server, but not Cloud Spanner. Option D, Bigtable replication, is incorrect because Bigtable replication is a data-duplication mechanism for Bigtable clusters and has nothing to do with capturing Spanner changes.

Answer

Cloud SQL for PostgreSQL logical replication

Answer

Datastream

Answer

Bigtable replication

Question 13

A Cloud Bigtable instance is configured with asynchronous replication across two regions for disaster recovery. The team needs to ensure that read-after-write consistency is maintained within a region. Which three strategies should they implement? (Choose THREE.)

Accepted Answer

After writing, have the application wait for a confirmation that the write is durable before performing a read.. Option A is correct because Cloud Bigtable's replication is eventually consistent across clusters, so an application must confirm the write has been durably committed (e.g., via a successful MutateRow/CheckAndMutateRow response) before issuing a read to guarantee read-after-write semantics. Option B is correct because single-cluster routing directs all reads and writes to one cluster in the primary region, which provides strong read-after-write consistency within that region since there is no cross-cluster replication delay to observe. Option E is correct because monitoring replication lag metrics (such as the replication latency/lag metrics exposed in Cloud Monitoring) lets the team verify that replicas are catching up and detect when eventual consistency windows might affect reads. Option C is not appropriate because disabling replication removes the disaster-recovery capability the scenario requires. Option D is not appropriate because multi-cluster routing spreads reads across regions and can return stale data from a lagging replica, breaking read-after-write consistency within a region.

Answer

Disable replication and use only the primary cluster.

Answer

Use multi-cluster routing to distribute reads across regions.

Question 14

A company has a Cloud SQL for PostgreSQL instance and wants to enable point-in-time recovery (PITR) with a recovery window of 5 days. Which configuration step is required?

Accepted Answer

Enable automated backups and set backup retention to 5 days. PITR in Cloud SQL for PostgreSQL uses Write-Ahead Logging (WAL) archives. You must enable automated backups and set the backup retention to the desired recovery window (1-7 days). Binary logging is for MySQL, not PostgreSQL. Cloud Storage archiving is separate. WAL archiving is automatically managed when automated backups are enabled with a retention period.

Answer

Enable binary logging on the instance

Answer

Configure a Cloud Storage bucket for WAL archiving

Answer

Create a cross-region backup replica for disaster recovery

Question 15

A company uses Memorystore for Redis and wants to ensure data is preserved in case of a node failure. They cannot afford any data loss. What should they do?

Accepted Answer

Enable AOF persistence on the Redis instance. AOF (Append Only File) persistence logs every write operation to disk, so Redis can replay the log and recover data after a node failure with minimal loss. For a Memorystore for Redis instance where zero data loss is required, enabling AOF is the correct native persistence mechanism. It complements RDB snapshots by providing finer-grained durability.

Answer

Increase the maxmemory setting to avoid eviction

Answer

Use active cross-region replication to a replica instance

Answer

Schedule periodic backups to Cloud Storage using export

Question 16

A Cloud Bigtable instance experiences a sudden increase in read latency and request errors. The operations team notices that one node is handling disproportionately more traffic. Which tool should they use to diagnose the issue?

Accepted Answer

Key Visualiser. Key Visualizer is the correct tool because it provides a heatmap of access patterns across row key ranges, allowing you to identify hot spots where a single node is overloaded due to uneven key distribution. This directly addresses the symptom of one node handling disproportionately more traffic, which is a common cause of increased latency and errors in Cloud Bigtable.

Answer

Stackdriver Monitoring dashboard

Answer

gcloud bigtable instances describe

Answer

Use the cbt command to scan the table

Question 17

A company has a Cloud SQL for MySQL instance with automated backups enabled. They need to restore the database to a specific timestamp from 2 days ago. The backup retention is set to 7 days. How should they perform this restore?

Accepted Answer

Use gcloud sql instances clone with the --point-in-time flag and the desired timestamp. Cloud SQL for MySQL supports point-in-time recovery (PITR), which allows you to restore a database to a specific timestamp within the backup retention period (here, 7 days). The `gcloud sql instances clone` command with the `--point-in-time` flag creates a new instance that reflects the database state at the exact requested timestamp, leveraging the transaction logs retained by automated backups.

Answer

Export the database and import it into a new instance

Answer

Create an on-demand backup and restore from that backup

Answer

Use gcloud sql instances restore-backup with the backup ID from 2 days ago

Question 18

A Cloud SQL for MySQL instance is experiencing high CPU utilisation (>80%) and slow query performance. The instance has auto-storage increase enabled. Which step should the engineer take to immediately reduce CPU load?

Accepted Answer

Increase the instance tier to a larger machine type. Increasing the instance tier to a larger machine type immediately provides more CPU and memory resources, directly reducing CPU utilization and improving query performance. This is the most direct and immediate step to address high CPU on a Cloud SQL instance.

Answer

Reduce the max_connections parameter

Answer

Create a cross-region read replica to offload reads

Answer

Enable auto-storage increase if not already enabled

Question 19

A team is using Cloud SQL for PostgreSQL and wants to monitor replication lag on their read replicas. Which metric should they use?

Accepted Answer

cloudsql.googleapis.com/database/replication/lag. The metric cloudsql.googleapis.com/database/replication/lag is specifically designed to measure replication lag in Cloud SQL for PostgreSQL read replicas. It reports the time delay between the primary and replica, which is exactly what the team needs to monitor. Other metrics like network bytes, disk usage, or CPU utilization do not directly indicate replication lag.

Answer

cloudsql.googleapis.com/database/network/received_bytes_count

Answer

cloudsql.googleapis.com/database/disk/bytes_used

Answer

cloudsql.googleapis.com/database/cpu/utilization

Question 20

An organisation wants to back up their Cloud Spanner database and store the backup in a different region for disaster recovery. They require the backup to be in a format that can be restored into Spanner with minimal effort. Which THREE actions should they take? (Choose three.)

Accepted Answer

Specify a different location for the backup at creation time for cross-region storage. Cloud Spanner allows you to specify a different location for the backup at creation time, enabling cross-region storage for disaster recovery without additional export or import steps. Option D is correct because restoring a backup to a new database using `gcloud spanner databases restore` directly utilizes the backup format, requiring minimal effort. Option E is correct because creating a database-level backup using `gcloud spanner databases backup` is the native method to back up Spanner databases, and such backups can be stored in different regions for geographic redundancy.

Answer

Export the database using gcloud spanner databases export to CSV format

Answer

Use Dataflow to import the backup into a new instance