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

Question 1

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 change streams capture row-level changes in a Cloud Spanner database. These changes can be consumed via Pub/Sub integration, allowing downstream analytics pipelines to process the data. Therefore, Option E (Spanner change streams) is correct for capturing the changes, and Option A (Pub/Sub integration) is correct for streaming them. Option B (Cloud SQL for PostgreSQL logical replication) is not applicable as it is for Cloud SQL, not Spanner. Option C (Datastream) is used for database migrations and replication, not for CDC from Spanner. Option D (Bigtable replication) is for Bigtable, not Spanner.

Answer

Cloud SQL for PostgreSQL logical replication

Answer

Datastream

Answer

Bigtable replication

Question 2

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.. 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 3

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.. 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.

Question 4

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 5

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. To guarantee no data loss during a node failure, Memorystore for Redis should have persistence enabled. Standard Tier instances support both RDB and AOF persistence. AOF persistence, especially with an fsync policy of 'always' or 'everysec', ensures that writes are durably stored, minimizing data loss to at most one second. Periodic exports to Cloud Storage are backups that occur at scheduled intervals, so any data written between the last export and the failure would be lost, violating the zero-data-loss requirement. Increasing maxmemory only controls eviction, not durability. Cross-region replication provides high availability but does not persist data to disk on its own and can lose recently written data due to replication lag.

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 6

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 7

An e-commerce application uses Firestore in Native mode. The team needs to run a query that filters on two fields and orders by a third field. What is the correct approach to ensure this query runs efficiently?

Accepted Answer

Create a composite index on the three fields. Firestore requires a composite index for queries that filter on multiple fields and order by a different field, because single-field indexes cannot satisfy the combined filtering and ordering constraints. Creating a composite index on the three fields (the two filter fields and the order field) allows Firestore to efficiently execute the query without scanning all documents.

Answer

Rely on automatic single-field indexes

Answer

Use an index exemption on the fields

Answer

Use Firestore in Datastore mode instead

Question 8

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 9

A company runs a Cloud Bigtable instance with SSD storage and wants to switch to HDD storage to reduce costs. The instance cannot tolerate downtime for data migration. What is the correct approach?

Accepted Answer

Create a new Cloud Bigtable cluster with HDD storage and migrate data. Cloud Bigtable does not support in-place conversion of storage type from SSD to HDD. The only way to switch storage without downtime is to create a new cluster with HDD storage, then use a replicated setup (e.g., via a replication cluster or application-level dual-write) to migrate data while the original cluster remains operational. This ensures zero downtime during the migration.

Answer

Increase the number of nodes to reduce per-node cost

Answer

Add a cross-region replica and then promote it with HDD storage

Answer

Use the gcloud bigtable clusters update command to change storage type

Question 10

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. High CPU utilisation is often due to insufficient compute capacity. Increasing the instance tier (vertical scaling) provides more CPU and memory, reducing load. Enabling auto-storage increase only adds disk space, not compute. Creating a read replica distributes read traffic but does not help with CPU load on the primary for write-heavy workloads. Adjusting max_connections may help marginally but is not as effective as increasing compute capacity.

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 11

A company uses Memorystore for Redis with the 'volatile-lru' eviction policy. They notice that recently added keys with TTL are being evicted even though there is still memory available. What is the most likely cause?

Accepted Answer

The volatile-lru policy evicts only keys with an expiry set, and there are many keys without expiry occupying memory. The 'volatile-lru' eviction policy in Memorystore for Redis evicts only keys that have a TTL (expiry) set, using an LRU (Least Recently Used) algorithm among those keys. If many keys without expiry are consuming memory, the eviction process will still target only the volatile keys, even if overall memory is not fully exhausted, because the policy is constrained to evict only from the subset of keys with TTLs. This explains why recently added keys with TTL are being evicted prematurely — the non-volatile keys are 'protected' from eviction, forcing the eviction of volatile keys to free memory.

Answer

The allkeys-lru policy would be more appropriate

Answer

The maxmemory setting is not configured

Answer

The maxmemory-policy is misconfigured

Question 12

A Cloud Spanner database has a table with a primary key and a secondary index. The application frequently queries using a filter on the secondary index column and orders by the primary key. The queries are slow. What should the database administrator do to improve query performance?

Accepted Answer

Create a covering index using CREATE INDEX with the STORING clause to include the required columns. Cloud Spanner can use a secondary index to filter on a column, but when ordering by the primary key, Spanner often must fetch rows from the base table and then sort them, which is slow. Creating a covering index with the STORING clause includes all columns needed by the query (the filter column and the primary key, as well as any other selected columns). This allows Spanner to satisfy the query entirely from the index, avoiding both the table lookup and the sort, because the index already stores the primary key in order. Option C is the correct syntax: `CREATE INDEX ... ON table (filter_column) STORING (other_columns)`.

Answer

Increase the number of nodes to improve query throughput

Answer

Create an interleaved table that mirrors the data

Answer

Use ALTER TABLE to add a new index on the filter column

Question 13

A company uses Firestore in Native mode and has a collection with many documents containing array fields. They want to index the array values to support queries like 'array-contains'. What is the correct approach?

Accepted Answer

No additional index configuration is required; arrays are automatically indexed. In Firestore Native mode, all fields are automatically indexed by default, including array fields. For array fields, the index includes each element, which allows queries using the `array-contains` operator to work without any additional configuration. Index exemptions are used to exclude specific fields from automatic indexing, not to enable indexing. Therefore, no action is required.

Answer

Create an index exemption for the array field to enable indexing

Answer

Switch to Datastore mode and configure indexes manually

Answer

Create a composite index that includes the array field

Question 14

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. Cloud SQL provides a metric called 'replication_lag' for read replicas, which measures the time (in seconds) the replica is behind the primary. CPU utilisation and disk usage are for performance, not replication. For PostgreSQL, the metric is valid.

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 15

A company wants to use Cloud SQL for MySQL to serve a read-heavy application. They need to ensure high availability and offload read traffic. Which TWO steps should they take? (Choose two.)

Accepted Answer

Create a same-region read replica. To ensure high availability, create a Cloud SQL HA configuration (regional instance with standby). To offload read traffic, create read replicas. Cross-region replicas are for disaster recovery but add latency. On-demand backups are for data protection, not availability. Auto-storage increase is for storage management.

Answer

Schedule on-demand backups daily

Answer

Create a cross-region read replica

Answer

Enable auto-storage increase