PCDOE Manage Database Solutions Practice Test 2 — 15 Questions

Question 1

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 C is correct: enable binary logging and set retention to 4 days. Option A is wrong because automated backups alone only restore to backup time, not any point. Option B is wrong because disabling binary logs prevents PITR. Option D is wrong because the retention is set via the backup configuration, not a separate 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 2

A Cloud SQL for PostgreSQL instance is experiencing high read traffic. You need to offload read queries and ensure the solution can survive a regional outage. What should you do?

Accepted Answer

Create a cross-region read replica and direct read traffic to it.. Cross-region read replicas serve read traffic and provide disaster recovery if the primary fails. Creating a cross-region read replica reduces read load and can be promoted if the primary region fails. Option D is correct. Option A (same-region replica) does not survive a regional outage. Option B (increasing tier) helps performance but not regional failover. Option C (cache) reduces latency but not a full recovery solution.

Answer

Set up Cloud Memorystore as a cache in front of the database.

Answer

Increase the machine type of the primary instance to handle the load.

Answer

Create a read replica in the same region and use it for read queries.

Question 3

A development team uses Cloud Spanner and needs to add a new index to a table with 10 TB of data. They require that the operation does not block writes or cause downtime. Which approach should they take?

Accepted Answer

Execute 'CREATE INDEX' statement; Spanner will build the index in the background without blocking.. In Cloud Spanner, CREATE INDEX is a non-blocking operation. It can be executed online while the database remains available for reads and writes. Option A is correct. Option B (batch updates) is unnecessary. Option C (maintenance window) is incorrect because Spanner handles online schema changes. Option D (drop and recreate) causes downtime.

Answer

Create a new table with the index and batch copy data from the original table.

Answer

Schedule a maintenance window, then create the index during the window.

Answer

Drop the table and recreate it with the index, then restore from backup.

Question 4

A company is using Cloud Firestore to store user profiles. They need to query users by both their 'age' and 'city' fields. What must they configure to perform this query efficiently?

Accepted Answer

Create a composite index on age and city.. Firestore automatically creates single-field indexes for simple queries. However, for queries filtering on multiple fields like 'age' and 'city', a composite index must be created manually. Option B is correct. Option A (single-field index) is insufficient. Option C (index exemptions) is for arrays and maps, not for multi-field queries. Option D (Datastore mode) does not change this requirement.

Answer

Use Firestore in Datastore mode for automatic composite indexes.

Answer

Configure index exemptions for the fields.

Answer

No configuration needed; single-field indexes are sufficient.

Question 5

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 offers 'auto-storage increase' feature that automatically adds storage in increments when usage is high. To cap the growth, set the maximum storage size. Option C is correct. Option A (automatic increase without cap) could exceed 1 TB. Option B (manual resize) is not automatic. Option D (increase disk size) is manual and not automatic.

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 6

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.. Spanner database-level backups can be created and stored in a different region (cross-region backup). These backups can be restored directly to a new database without export/import. Option A is correct. Option B (export to GCS) requires import. Option C (on-demand backup) can be cross-region, but the question asks for daily; scheduled backups are better. Option D (snapshots using Cloud Storage) is not a built-in Spanner feature.

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 7

A Cloud Bigtable instance is experiencing high read latency. The team suspects hot spotting on a single node. Which tool should they use to identify the hotspot?

Accepted Answer

Key Visualizer. Key Visualizer is the correct tool because it is specifically designed to analyze access patterns in Cloud Bigtable and identify hot spotting—where a small range of row keys receives a disproportionate amount of traffic. It visualizes read and write heatmaps across row key space and time, allowing teams to pinpoint the exact keys causing the hotspot. Unlike general-purpose monitoring tools, Key Visualizer provides row-key-level granularity essential for diagnosing hot spotting.

Answer

Cloud Logging to review request logs

Answer

Bigtable Admin API to list tables

Answer

Cloud Monitoring (Stackdriver) to check CPU utilization

Question 8

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 9

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 10

A Cloud Memorystore for Redis instance is running out of memory. The team wants to automatically remove the least recently used keys when memory is full. Which eviction policy should they configure?

Accepted Answer

allkeys-lru. The `allkeys-lru` eviction policy is correct because it applies the LRU (Least Recently Used) algorithm to all keys in the Redis instance, not just those with an expiry set. This ensures that when memory is full, the least recently accessed keys are automatically removed, regardless of whether they have a TTL, which directly meets the requirement to free memory without manual intervention.

Answer

volatile-lru

Answer

volatile-ttl

Answer

noeviction

Question 11

A company has a Cloud Bigtable cluster using HDD storage. They are migrating to a workload that requires lower latency, so they need to switch to SSD storage. How should they accomplish this?

Accepted Answer

Create a new Bigtable cluster with SSD storage in the same instance, replicate data, then delete the old cluster.. Option D is correct because Cloud Bigtable does not support in-place conversion of storage type from HDD to SSD. The proper migration path is to create a new cluster with SSD storage within the same instance, enable replication to keep data synchronized, and then delete the old HDD cluster. This approach minimizes downtime and ensures data consistency.

Answer

Add more nodes to the existing cluster to improve latency.

Answer

Export data to Avro files, create a new SSD cluster, and import the data.

Answer

Modify the existing cluster's storage type to SSD using the Cloud Console.

Question 12

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 an index exemption for the array field.. Option C is correct because Cloud Firestore does not support single-field indexes on array fields; instead, it automatically creates array indexes for equality queries. However, to optimize queries involving array fields, you can create an index exemption to exclude the automatic array index and then manually define a composite index that includes the array field. This allows you to control indexing behavior and improve query performance for specific use cases.

Answer

Create a collection group index on the field.

Answer

Create a composite index on the array field and another field.

Answer

Rely on the automatically created single-field index.

Question 13

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. Spanner provides change streams to capture row-level changes. The changes can be read using the Spanner API and streamed to Pub/Sub for downstream processing. Option A (Change streams) is correct; Option C (Pub/Sub integration) is correct. Option B (Cloud SQL) is not Spanner. Option D (Datastream) is for database migrations, not CDC. Option E (Bigtable) is not Spanner.

Answer

Cloud SQL for PostgreSQL logical replication

Answer

Datastream

Answer

Bigtable replication

Question 14

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 in Cloud Bigtable, writes are acknowledged only after they are committed to the tablet server's log and replicated to the underlying Colossus file system. By waiting for this confirmation before performing a read, the application ensures that the read will see the latest write, providing read-after-write consistency within the region.

Answer

Disable replication and use only the primary cluster.

Answer

Use multi-cluster routing to distribute reads across regions.

Question 15

A Cloud SQL for MySQL instance is being used for a production application. The team wants to implement a disaster recovery plan that can recover from a regional outage with minimal data loss and automatic failover. Which three steps should they take? (Choose THREE.)

Accepted Answer

Enable automated backups with a suitable retention period.. Option A is correct because automated backups in Cloud SQL for MySQL provide a baseline for disaster recovery by creating daily backups that can be restored to a new instance. With a suitable retention period, you can recover from data corruption or accidental deletion, though backups alone do not provide automatic failover or minimal data loss during a regional outage.

Answer

Create a read replica in the same region.

Answer

Increase the storage size to accommodate future growth.