PCDOE Manage Database Solutions Practice Test 7 — 15 Questions

Question 1

A company is running a Cloud SQL for PostgreSQL instance for an e-commerce application. They need to enable point-in-time recovery (PITR) with a 7-day retention period. What configuration steps must be taken?

Accepted Answer

Enable WAL archiving and configure backup retention to 7 days using gcloud sql instances patch --backup-retention-days 7.. Option B is correct because Cloud SQL for PostgreSQL uses Write-Ahead Log (WAL) archiving to enable point-in-time recovery (PITR). You must enable automated backups (which automatically enables WAL archiving) and then set the backup retention period to 7 days using the `gcloud sql instances patch --backup-retention-days 7` command. This ensures that WAL logs are retained for the specified duration, allowing PITR within that window.

Answer

Create a cross-region backup replica with a 7-day retention.

Answer

Enable binary logging and set backup retention to 7 days.

Answer

Enable automated backups with a 7-day retention; WAL archiving is automatic.

Question 2

A team wants to create a Cloud Spanner database backup and store it in a Cloud Storage bucket for long-term archival. Which method should they use?

Accepted Answer

Use the gcloud spanner databases backup command to create a backup that resides in Cloud Storage.. Cloud Spanner provides database-level backups directly to Cloud Storage in Avro + Protobuf format. Export/import is for migration, not archival backups.

Answer

Use gcloud spanner instances create-backup to create a backup of the instance.

Answer

Take a snapshot of the Spanner instance using Cloud Storage snapshots.

Answer

Use the gcloud spanner databases export command to export to Cloud Storage.

Question 3

A Cloud Bigtable instance is experiencing high read latency due to hot spots. The operations team uses the Key Visualizer tool and identifies that a small set of row keys are being read disproportionately. Which action should they take to mitigate the hot spotting?

Accepted Answer

Redesign the row key scheme to distribute the workload across more tablets.. Option C is correct because hot spotting in Cloud Bigtable is typically caused by a suboptimal row key design that concentrates read/write traffic on a small number of tablets. Redesigning the row key scheme to distribute the workload—for example, by adding a hash prefix or using a more granular key—spreads requests across multiple tablets, alleviating the hotspot. Key Visualizer explicitly identifies the skewed row keys, guiding the redesign effort.

Answer

Increase the number of nodes in the Bigtable cluster to handle the load.

Answer

Change the storage type from HDD to SSD to improve performance.

Answer

Enable replication across multiple zones.

Question 4

A Memorystore for Redis instance is running out of memory and evicting keys. The application requires critical keys to be retained. The team wants to implement an eviction policy that removes less recently used keys first. Which eviction policy should they configure?

Accepted Answer

volatile-lru. The volatile-lru eviction policy is correct because it removes the least recently used keys among those that have an expiration (TTL) set, which allows the application to retain critical keys that are not given a TTL. This policy ensures that only volatile keys are candidates for eviction, protecting non-expiring keys from being removed when memory is exhausted.

Answer

noeviction

Answer

allkeys-lru

Answer

volatile-ttl

Question 5

An application uses Firestore in Native mode. The query filters on two fields: 'status' (string) and 'created_date' (timestamp). The query returns results but the billing shows high document reads. What is the most likely cause?

Accepted Answer

The query is using an inequality filter on 'created_date' which requires a composite index.. In Firestore Native mode, queries that apply an inequality filter (e.g., >=, >, <, !=) on a field automatically require a composite index on both the equality filter field and the inequality filter field to avoid a full collection scan. Without that composite index, Firestore performs a back-end scan of all documents matching the equality filter, then applies the inequality filter in memory, resulting in high document reads. Option A correctly identifies that the inequality filter on 'created_date' is the most likely cause of the excessive reads because it forces Firestore to read and discard many documents that do not satisfy the timestamp condition.

Answer

The query is using 'array-contains' which always scans the entire collection.

Answer

The 'status' field is not indexed because single-field indexes are not automatic.

Answer

The query is missing an ORDER BY clause causing a full scan.

Question 6

A Cloud SQL for MySQL instance is running low on disk space. You need to increase the storage without downtime. What is the correct approach?

Accepted Answer

Use the gcloud command to resize the storage; the operation is performed online.. Option D is correct because Cloud SQL for MySQL supports online storage resizing using the `gcloud sql instances patch` command or the Google Cloud Console, which allows you to increase the storage capacity without any downtime. The operation is performed while the instance remains available, and the underlying persistent disk is resized live, leveraging Google Cloud's live resize capability for Cloud SQL instances.

Answer

Create a new instance with larger storage and migrate the data using mysqldump.

Answer

Enable automatic storage increase; resize manually is not possible.

Answer

Stop the instance, resize the attached persistent disk, then restart.

Question 7

You are running a Memorystore for Redis instance with persistence disabled. The application requires high availability and data durability after a zone failure. What is the most cost-effective approach?

Accepted Answer

Use a Standard tier instance with cross-zone replication.. Option C is correct because Standard tier Memorystore for Redis instances provide cross-zone replication by default, ensuring automatic failover and data durability in the event of a zone failure. This meets the high availability and data durability requirements without the need for manual exports or complex multi-region setups, making it the most cost-effective solution.

Answer

Use a Basic tier instance and schedule periodic exports to Cloud Storage.

Answer

Enable persistence using the AOF or RDB configuration in Memorystore.

Answer

Deploy a Redis Cluster across multiple regions using Memorystore.

Question 8

A company uses Cloud Bigtable to store session data. They need to monitor replication lag between clusters in different zones. Which metric should they use?

Accepted Answer

Instance-level 'replication_lag' metric.. Option A is correct because Cloud Bigtable exposes an instance-level 'replication_lag' metric that measures the time delay (in seconds) between clusters in a replicated instance. This metric directly reflects how far behind a replica cluster is relative to the primary cluster, making it the appropriate choice for monitoring replication lag between zones.

Answer

Cluster-level 'cpu_load' metric.

Answer

Use Key Visualizer to identify replication delays.

Answer

Table-level 'rows_returned' metric.

Question 9

A Cloud Spanner database needs a new index added to support a new query pattern. The database is serving live traffic. Which approach should be taken?

Accepted Answer

Create the index using DDL statement 'CREATE INDEX' — it will be applied online without downtime.. Cloud Spanner supports online schema changes, including index creation, without downtime. The 'CREATE INDEX' DDL statement is applied asynchronously in the background, allowing the database to continue serving live traffic while the index is built. This is the correct approach because it avoids any interruption to the application.

Answer

Export the data, create a new database with the index, and import the data.

Answer

Stop the application, add the index, and restart.

Answer

Use gcloud spanner databases add-index command.

Question 10

A team needs to monitor CPU utilization on Cloud SQL databases and receive an alert when it exceeds 80% for more than 5 minutes. Which Google Cloud service should they use?

Accepted Answer

Cloud Monitoring. Cloud Monitoring (formerly Stackdriver Monitoring) is the correct service because it provides built-in metrics for Cloud SQL, including CPU utilization, and allows you to create alerting policies based on threshold conditions (e.g., CPU > 80% for 5 minutes). It integrates directly with Cloud SQL and can trigger notifications via email, Pub/Sub, or other channels when the condition is met.

Answer

Cloud Audit Logs

Answer

Active Assist

Answer

Cloud Logging

Question 11

A company has a Cloud SQL for PostgreSQL instance with a read replica in another region. They want to perform disaster recovery testing without affecting the primary. What should they do?

Accepted Answer

Promote the read replica to a standalone instance and use it for testing.. Promoting the read replica to a standalone instance detaches it from the primary, creating an independent writable Cloud SQL instance. This allows you to perform disaster recovery testing (e.g., failover validation, data integrity checks) without any impact on the primary instance, as the replica no longer replicates changes from the primary.

Answer

Use the replica as a failover target; testing is not allowed.

Answer

Enable point-in-time recovery on the replica and restore to a new instance.

Answer

Create a clone of the primary instance and test on that.

Question 12

A Firestore database in Native mode has a collection with a composite index on fields (city, age). A new query filters on 'city == 'London' AND 'age > 30' ORDER BY 'name'. The query is slow and reads many documents. What should the engineer do?

Accepted Answer

Create a composite index on (city, age, name).. The query filters on `city == 'London'` and `age > 30`, then orders by `name`. For Firestore to efficiently satisfy both the equality and inequality filters combined with an `ORDER BY`, the composite index must include the fields in the correct order: equality fields first, then the inequality field, then the sort field. Option C provides the index `(city, age, name)`, which matches this pattern exactly, allowing Firestore to perform a targeted index scan without reading extra documents.

Answer

Remove the ORDER BY clause to improve performance.

Answer

Create a composite index on (city, name).

Answer

Create a single-field index on 'name'.

Question 13

A company is migrating on-premises MySQL databases to Cloud SQL. They need to ensure high availability and disaster recovery with minimal data loss. Which TWO configurations should they implement?

Accepted Answer

Enable High Availability (HA) configuration on the primary instance.. Option B is correct because enabling High Availability (HA) configuration on a Cloud SQL primary instance creates a synchronous standby in a different zone within the same region, providing automatic failover with minimal data loss (typically under one second). This directly addresses the need for high availability with minimal data loss during a zonal outage.

Answer

Configure automated backups with 7-day retention.

Answer

Use Cloud SQL Proxy for secure connections.

Answer

Enable binary logging for point-in-time recovery.

Question 14

A gaming company uses Cloud Spanner for a global leaderboard. They need to add a column to an existing table and create a secondary index on that column. The database must remain fully available during these changes. Which THREE statements are true?

Accepted Answer

The new column must have a default value if it is defined as NOT NULL.. Option B is correct because in Cloud Spanner, when adding a NOT NULL column to an existing table, the column must have a DEFAULT value. This ensures that existing rows, which will be populated with the default value, satisfy the NOT NULL constraint without requiring a full table scan or blocking writes.

Answer

The new column cannot be added if the table has existing data.

Answer

CREATE INDEX will block writes on the table until the index is built.

Question 15

A company runs Memorystore for Redis with a Standard tier instance. They want to scale to higher throughput and memory capacity beyond the current tier limits. Which TWO actions should they take?

Accepted Answer

Enable Redis Cluster to shard data across multiple nodes.. Redis Cluster shards data across multiple nodes, allowing horizontal scaling beyond the limits of a single Standard tier instance. This enables higher throughput and memory capacity by distributing the dataset across multiple primary nodes, each handling a subset of the keyspace.

Answer

Switch from HDD to SSD storage.

Answer

Add a cross-region replica to increase read capacity.

Answer

Increase the maxmemory setting beyond the tier limit.