PCDOE Manage Database Solutions Practice Test 6 — 15 Questions

Question 1

A retail company uses Cloud SQL for MySQL with point-in-time recovery (PITR) enabled. They need to recover the database to a specific second from 2 days ago. The backup retention is set to 7 days. Which action should the engineer take to perform the recovery?

Accepted Answer

Use gcloud sql instances restore-backup with the --point-in-time flag and specify the timestamp, restoring to a new instance.. Point-in-time recovery (PITR) in Cloud SQL uses binary logs to recover to any time within the configured retention period. The engineer can restore to a new instance using gcloud sql instances restore-backup with the --point-in-time flag and specify the exact timestamp. Restoring to the same instance is not supported; only to a new instance. The --async flag is optional but not required. The recovery is not limited to full backups; binary logs allow precise time recovery.

Answer

Use mysqldump to export the binary logs and replay them from the backup.

Answer

Use gcloud sql instances restore-backup with the --async flag and specify the timestamp.

Answer

Create an on-demand backup from the source instance and restore that backup to a new instance.

Question 2

A game development company wants to use Memorystore for Redis as a session store. They need to ensure that when memory is full, the least recently used keys are evicted first. Which eviction policy should they configure?

Accepted Answer

allkeys-lru. Option B (allkeys-lru) is correct because the requirement is to evict the least recently used keys from the entire keyspace when memory is full. The 'allkeys-lru' policy applies the LRU algorithm to all keys, regardless of whether they have an expiry set, which matches the need to evict the least recently used keys first across the entire dataset.

Answer

volatile-lru

Answer

volatile-ttl

Answer

noeviction

Question 3

A financial services company uses Cloud Spanner for a globally distributed application. They need to add a new column to an existing table that contains billions of rows. The change must not cause any downtime or performance degradation. How should the engineer implement this schema change?

Accepted Answer

Use ALTER TABLE ADD COLUMN; Spanner applies the change online without downtime.. Cloud Spanner supports online schema changes via the `ALTER TABLE ADD COLUMN` statement, which applies the change asynchronously without locking the table or causing downtime. Spanner uses a multi-phase approach to add columns, allowing reads and writes to continue uninterrupted while the schema change propagates across all nodes and regions.

Answer

Export the table, modify the schema in a temporary table, and import it back.

Answer

Use CREATE INDEX to add the column as an index.

Answer

Create a new table with the additional column, backfill data from the old table, then rename the tables.

Question 4

A company uses Cloud SQL for PostgreSQL and wants to create a cross-region read replica for disaster recovery (DR) purposes. They also want the option to promote the replica to a standalone instance in the event of a regional outage. What should the engineer do?

Accepted Answer

Create a cross-region read replica and use gcloud sql instances promote-replica to promote it during DR.. Option B is correct because Cloud SQL for PostgreSQL supports cross-region read replicas, which can be promoted to standalone instances using the `gcloud sql instances promote-replica` command. This provides a managed DR solution with minimal data loss (asynchronous replication) and faster failover compared to export/import or backup restore methods.

Answer

Export the database as a SQL file and import it into a new instance in the target region during DR.

Answer

Create a same-region read replica and enable point-in-time recovery on it.

Answer

Configure cross-region backup replicas and restore from backup in the target region.

Question 5

A data analytics company uses Bigtable for high-throughput writes. They notice that some rows are receiving a disproportionate number of reads and writes, causing performance degradation. Which tool should they use to identify and resolve this hot spotting issue?

Accepted Answer

Key Visualiser. Key Visualizer is the correct tool because it is specifically designed to analyze Bigtable access patterns and visualize hot spotting—where a small number of rows or row ranges receive a disproportionate share of reads and writes. It provides heatmaps and time-series graphs that pinpoint the exact keys causing performance degradation, enabling you to redesign your row keys or implement salting to distribute load evenly.

Answer

cbt tool

Answer

Cloud Logging

Answer

Cloud Monitoring

Question 6

A startup is building a mobile application and needs a real-time database that automatically scales to handle sudden spikes in user traffic. They want to minimise operational overhead and only pay for the resources they use. Which Google Cloud database should they choose?

Accepted Answer

Firestore. Firestore is a fully managed, serverless NoSQL document database that automatically scales horizontally to handle sudden traffic spikes without manual intervention. It offers real-time data synchronization via listeners, and its pay-per-use billing model aligns with the startup's requirement to minimize operational overhead and only pay for consumed resources.

Answer

Cloud Spanner

Answer

Cloud SQL

Answer

Cloud Bigtable

Question 7

A company uses Memorystore for Redis with Standard Tier (replication) and needs to ensure data durability in case of a zone failure. They also need to scale read throughput beyond a single instance. What should they do?

Accepted Answer

Migrate to Redis Cluster with 3+ shards.. Memorystore for Redis Standard Tier provides cross-zone replication for high availability. To scale read throughput, they can use Redis Cluster (which shards data across multiple shards) or create read replicas. However, Memorystore does not support read replicas for Redis; instead, Redis Cluster provides horizontal scalability and high availability. Upgrading to a larger instance scales vertically but not read throughput horizontally. Persistence is not natively supported in Memorystore.

Answer

Create a cross-region replica and use it for read traffic.

Answer

Upgrade to a larger machine type with more memory.

Answer

Enable persistence using AOF and configure a backup schedule.

Question 8

A Cloud SQL for PostgreSQL instance is running out of storage. The engineer wants to configure automatic storage increase to avoid manual intervention. What should they do?

Accepted Answer

Use gcloud sql instances patch with the --storage-auto-increase flag.. Option B is correct because Cloud SQL for PostgreSQL supports automatic storage increase, which can be enabled via the `--storage-auto-increase` flag in the `gcloud sql instances patch` command. This feature automatically increases the instance's storage capacity when it approaches the configured limit, eliminating the need for manual intervention.

Answer

Use gcloud sql instances patch with the --disk-size flag to set a larger size.

Answer

Create a cron job to monitor disk usage and increase storage via API.

Answer

Use gcloud sql instances create to create a new instance with larger storage.

Question 9

A company uses Firestore in Native mode for their application. They need to query a collection where documents must match a specific field value and be sorted by a different field. The query filters on 'status' and orders by 'timestamp'. What should the engineer do to ensure the query performs optimally?

Accepted Answer

Create a composite index on the 'status' and 'timestamp' fields.. Option D is correct because Firestore requires a composite index when a query includes both an equality filter on one field (status) and an order by clause on a different field (timestamp). Without this composite index, Firestore cannot efficiently satisfy both the filter and the sort order in a single index scan, leading to suboptimal performance or query failure. Creating a composite index on (status, timestamp) allows Firestore to use a single index to match the filter and return results in the requested order.

Answer

Create an index exemption to improve query performance.

Answer

Use a collection group query to bypass index requirements.

Answer

Rely on automatic single-field indexes; they will cover the query.

Question 10

A company uses Cloud Spanner and needs to export a full database to Cloud Storage in a format that preserves both the schema and data for long-term archival. The export must be restorable into a new Spanner database later. Which export method should they use?

Accepted Answer

Use gcloud spanner databases backup create to create a backup that is stored in Cloud Storage.. Cloud Spanner provides managed database-level backups that export the database to Cloud Storage in Avro format with a Protobuf schema. This format preserves schema and data and can be restored via the backup restore feature. Import/export using Dataflow is slower and more complex for full database archival. The gcloud spanner databases export command uses Dataflow and is not the recommended approach for backups. Cloud console also initiates the backup process.

Answer

Use gcloud spanner databases export to export the data to Cloud Storage in CSV format.

Answer

Use Dataflow to read the Spanner tables and write them to Cloud Storage as Parquet files.

Answer

Use gcloud spanner databases restore to copy the database to a new instance.

Question 11

An engineer needs to monitor the replication lag of a Cloud SQL read replica. Which metric should they use in Cloud Monitoring?

Accepted Answer

replication_lag. The `replication_lag` metric in Cloud Monitoring directly measures the time delay between a primary Cloud SQL instance and its read replica, reported in seconds. This is the standard metric for monitoring how far behind the replica is in applying changes from the primary, which is critical for ensuring read-after-write consistency and data freshness.

Answer

sent_bytes_count

Answer

disk_bytes_used

Answer

cpu_utilization

Question 12

A company uses Bigtable and wants to change the storage type from HDD to SSD to improve read performance. What must the engineer do to implement this change?

Accepted Answer

Create a new cluster with SSD, replicate data to it, then delete the old cluster.. Bigtable does not support in-place conversion of a cluster's storage type from HDD to SSD. The correct approach is to create a new cluster with SSD storage, configure replication to replicate data from the existing HDD cluster to the new SSD cluster, and then delete the old HDD cluster. This ensures zero downtime and data consistency during the migration.

Answer

Use gcloud bigtable clusters update with the --storage-type flag.

Answer

Modify the cluster settings and change the storage type to SSD.

Answer

Export the data to Cloud Storage, create a new SSD cluster, and import the data.

Question 13

A company uses Cloud SQL for MySQL with automated backups enabled. They want to ensure they can recover their database in the event of a regional outage. Which TWO actions should they take? (Choose two.)

Accepted Answer

Create a cross-region read replica.. Option B is correct because a cross-region read replica in Cloud SQL for MySQL provides a standby copy of the database in a different region, which can be promoted to a primary instance during a regional outage, enabling recovery with minimal data loss. Option C is correct because cross-region backup replicas (also known as cross-region backup copies) allow backups to be stored in a separate region, ensuring that backup data survives a regional disaster and can be used to restore the database in another region.

Answer

Increase the backup retention period to 30 days.

Answer

Enable point-in-time recovery with a 7-day retention.

Answer

Schedule more frequent on-demand backups.

Question 14

A company uses Memorystore for Redis and wants to achieve high availability with automatic failover. They also need to periodically back up the data to Cloud Storage for disaster recovery. Which TWO features should they use? (Choose two.)

Accepted Answer

Standard Tier (replication). Standard Tier (replication) provides a primary-replica architecture with automatic failover, ensuring high availability. The Export functionality allows you to manually or scheduled export Redis data to Cloud Storage, enabling point-in-time recovery for disaster recovery purposes.

Answer

Redis Cluster

Answer

AOF persistence

Answer

Basic Tier

Question 15

A company uses Cloud Spanner and needs to capture real-time changes from a table for downstream processing. They want to avoid writing custom application code to track changes. Which THREE components should they use? (Choose three.)

Accepted Answer

Pub/Sub. Spanner Change Streams capture real-time data changes (inserts, updates, deletes) and publish them to a Pub/Sub topic. The engineer can then create a Pub/Sub subscription to receive the changes. Dataflow can be used to process the change stream messages for further transformations or loading into other systems. The Cloud Spanner API is not needed for this purpose; Change Streams already leverage the API. Bigtable is not involved. Dataflow is optional but commonly used.

Answer

Cloud Bigtable

Answer

Cloud Spanner API