PCDE Plan and manage database infrastructure Practice Test 2 — 15 Questions

Question 1

A team is designing a disaster recovery plan for a Cloud SQL for PostgreSQL instance. The RPO is 5 minutes and RTO is 1 hour. Which configuration meets these requirements?

Accepted Answer

Configure a cross-region read replica with point-in-time recovery (PITR) to enable failover. Meets the RPO of 5 minutes and RTO of 1 hour because a cross-region read replica with point-in-time recovery (PITR) allows you to failover to a replica in another region, minimizing data loss to within seconds (PITR can recover to any point in time within the retention window) and promoting the replica typically completes within minutes, well under the 1-hour RTO. This configuration provides both disaster recovery across regions and granular recovery to meet the strict RPO.

Answer

Schedule daily exports to Cloud Storage and import in another region

Answer

Enable automatic failover within the same region using HA configuration

Answer

Create a read replica in the same region and promote it during disaster

Question 2

A database engineer is configuring a Memorystore for Redis instance for a session store application. The application requires persistent storage to survive node failures. Which tier and configuration should be used?

Accepted Answer

Standard tier with AOF persistence enabled. The Standard tier in Memorystore for Redis provides a replicated architecture with a primary and read replica, ensuring high availability and automatic failover. Enabling AOF (Append-Only File) persistence writes every write operation to an AOF file, which is stored on persistent disk and can be replayed to restore data after a node failure. This combination meets the session store requirement for data durability across failures, as the Basic tier lacks replication and cannot guarantee data survival.

Answer

Standard tier with no persistence

Answer

Basic tier with RDB persistence enabled

Answer

Basic tier with AOF persistence enabled

Question 3

A company runs a BigQuery data warehouse with many scheduled queries and materialized views. They notice that materialized view refreshes are taking longer than expected, causing delays in downstream reports. What is the most effective optimization?

Accepted Answer

Partition and cluster the base table on columns used in the materialized view. Partitioning and clustering the base table on columns used in the materialized view (D) is the most effective optimization because it allows BigQuery to perform incremental refreshes using only the changed partitions, significantly reducing scan and recomputation overhead. Without proper partitioning, the materialized view refresh must scan the entire base table, which becomes increasingly costly as data grows. Clustering further improves efficiency by co-locating related data, minimizing the data processed during aggregation or join operations in the refresh.

Answer

Manually refresh materialized views outside peak hours

Answer

Increase the refresh interval to reduce frequency

Answer

Disable automatic refresh and use scheduled queries to rebuild the materialized view

Question 4

A developer needs to store JSON documents that are frequently accessed by key but rarely updated. The data size is under 10 GB initially but expected to grow to 500 GB. Which database service is most suitable?

Accepted Answer

Firestore (Datastore mode) with document keys. Firestore in Datastore mode is ideal because it provides a fully managed, scalable NoSQL document database with automatic sharding and strong consistency for key-based lookups. It handles growth from 10 GB to 500 GB seamlessly without manual partitioning, and its document keys enable efficient point reads for frequently accessed, rarely updated JSON data.

Answer

Cloud Bigtable with row keys as document IDs

Answer

Memorystore for Redis with JSON data type

Answer

Cloud SQL for PostgreSQL with JSONB column

Question 5

A team is migrating an Oracle database to Cloud Spanner. They have a large table with an auto-increment primary key. Which key design strategy should they use to avoid hot spots?

Accepted Answer

Use a composite primary key with a hash prefix of the auto-increment value. Using a composite primary key with a hash prefix of the auto-increment value distributes writes across multiple splits in Cloud Spanner. Cloud Spanner uses range-based sharding; a monotonically increasing primary key (like an auto-increment value) would cause all new writes to land on the same split, creating a hot spot. By hashing the auto-increment value and prepending it to the primary key, you randomize the key distribution, ensuring writes are spread evenly across the table's splits.

Answer

Use the auto-increment value as the primary key

Answer

Use interleaved tables to colocate related data

Answer

Use UUID as the primary key

Question 6

A Cloud SQL for SQL Server instance has been running for months. Recently, the database size grew significantly and now query performance has degraded. The DBA checks the query execution plan and sees index scans. The current storage is 500GB SSD. What is the most likely cause and solution?

Accepted Answer

Index fragmentation; rebuild or reorganize indexes. Index fragmentation occurs over time as data is inserted, updated, or deleted, causing indexes to become inefficient. The query execution plan showing index scans (instead of seeks) is a classic symptom of fragmented indexes. Rebuilding or reorganizing the indexes will defragment them, restoring query performance without requiring additional storage or infrastructure changes.

Answer

Increase storage capacity to 1TB SSD

Answer

Enable query insights to analyze performance

Answer

Enable read replicas to offload queries

Question 7

A database administrator is planning to migrate an on-premises MySQL database to Cloud SQL. Which two steps are required to ensure a secure migration?

Accepted Answer

Configure Cloud SQL to use a private IP address. Option A is correct because configuring Cloud SQL with a private IP address keeps database traffic on the internal VPC network, preventing exposure to the public internet and reducing the attack surface during and after migration. Option E is correct because the Cloud SQL Auth Proxy provides secure authentication via IAM and encrypts connections with TLS 1.3, allowing clients to connect without manually managing SSL certificates or opening firewall rules. Options B, C, and D are not required: authorized networks for all client IPs would actually weaken security by broadly exposing the instance, CMEK encryption at rest is an optional compliance feature rather than a migration security requirement since Cloud SQL encrypts data at rest by default, and while SSL/TLS is good practice, the Cloud SQL Auth Proxy in option E already handles encrypted connections and is the recommended secure method.

Answer

Add authorized networks for all client IPs

Answer

Ensure the database is encrypted at rest using CMEK

Answer

Enable SSL/TLS for all connections

Question 8

A team is designing a disaster recovery plan for a Cloud Spanner instance. They want an RPO of 10 minutes and RTO of 5 minutes. Which two features should they use?

Accepted Answer

Enable point-in-time recovery (PITR) with a 10-minute recovery period. Option A is correct because Cloud Spanner point-in-time recovery (PITR) lets you recover a database to any timestamp within a configurable retention window (up to 7 days), so setting a 10-minute recovery period directly supports the 10-minute RPO by allowing restoration to a point just before data loss. Option E is correct because a multi-region instance configuration synchronously replicates data across regions and can automatically fail over to a healthy region, providing the low RTO (5 minutes) and strong consistency needed for disaster recovery. Options B, C, and D are not the intended answers: a cross-region read replica is not a Cloud Spanner disaster-recovery feature in the same way and does not by itself guarantee the required RPO/RTO, automated backups are not a native Cloud Spanner capability (PITR and multi-region configs are the relevant mechanisms), and database versioning is not a Cloud Spanner failover feature.

Answer

Deploy a cross-region read replica

Answer

Configure automated backups

Answer

Use database versioning to failover

Question 9

What is the correct way to create a Spanner instance with 2 nodes?

Accepted Answer

Remove the --processing-units flag. `--processing-units` and `--nodes` are mutually exclusive flags in `gcloud spanner instances create`. To create a 2-node instance, you must use only `--nodes=2` and omit `--processing-units`. The `--nodes` flag is the correct way to specify the number of nodes; `--num-nodes` is not a valid flag.

Answer

Use --num-nodes=2 instead of --nodes

Answer

Set --processing-units=2000 and remove --nodes

Answer

Set --nodes=2 and --processing-units=0

Question 10

A company runs a production Cloud SQL for PostgreSQL instance. They need to ensure high availability with automatic failover in case of a zone failure. Which configuration should they use?

Accepted Answer

Enable high availability (HA) configuration during instance creation.. Enabling the high availability (HA) configuration during Cloud SQL for PostgreSQL instance creation automatically provisions a standby instance in a different zone within the same region. This synchronous replication setup ensures automatic failover with minimal data loss (typically under 1 second RPO) in the event of a zone failure, meeting the requirement for high availability.

Answer

Create a cross-region read replica and set it as failover target.

Answer

Use a zonal HA configuration by selecting a regional location.

Answer

Deploy a single Cloud SQL instance with multiple CPUs and high memory.

Question 11

A company is designing a Cloud Spanner database for a global financial application. They need to minimize latency for customer queries while handling write-heavy workloads. The current design uses a single-region instance in us-central1. Which approach should they take to reduce latency for users in Europe?

Accepted Answer

Reconfigure the instance to a multi-region configuration with default leader in europe-west1.. To reduce latency for users in Europe, the database must have a write-capable replica close to them. A multi-region configuration with the default leader in europe-west1 ensures that writes are committed in Europe, minimizing write latency for European users. Read-only replicas (Option C) cannot handle writes, so they do not reduce write latency.

Answer

Reconfigure the instance to a multi-region configuration with default leader in us-central1.

Answer

Add a read-only regional replica in europe-west1.

Answer

Increase the number of nodes to improve throughput and automatically reduce latency.

Question 12

A company uses Cloud Bigtable with replication across two clusters in us-east1 and us-west1. They have a critical application that requires strong consistency for all reads after writes. What configuration should they implement to meet this requirement?

Accepted Answer

Use single-cluster routing with single-row transactions.. Cloud Bigtable does not support strong consistency across clusters in a replicated setup; replication is eventually consistent. To guarantee strong consistency for reads after writes, you must use single-cluster routing with single-row transactions, which ensures that all reads and writes for a given row are processed by the same cluster, providing ACID semantics for that row.

Answer

Use cluster-group routing with multi-cluster routing.

Answer

Enable inter-cluster replication with strong consistency.

Answer

Use multi-cluster routing to automatically route to the nearest cluster.

Question 13

A company has a Cloud SQL for MySQL instance with automated backups enabled. They want to ensure they can recover to any point within the last 7 days with minimum storage cost. What should they do?

Accepted Answer

Keep the default automated backup configuration with 7-day retention and enable point-in-time recovery.. Cloud SQL for MySQL with automated backups enabled by default retains backups for 7 days. Enabling point-in-time recovery (PITR) uses the existing binary logs to allow recovery to any point within that retention period, without additional storage cost for the logs beyond the backup storage. This meets the requirement of 7-day recoverability with minimum storage cost.

Answer

Enable binary logging manually and store transaction logs in Cloud Storage.

Answer

Increase automated backup retention to 30 days and disable point-in-time recovery.

Answer

Disable automated backups and create manual backups daily.

Question 14

A company is migrating an application from Datastore to Firestore in Datastore mode. They need to ensure zero downtime during the migration. What is the recommended approach?

Accepted Answer

Upgrade the existing Datastore project to Firestore in Datastore mode using the Cloud Console.. Upgrading an existing Datastore project to Firestore in Datastore mode via the Cloud Console is a built-in, one-way migration that preserves the existing database name, indexes, and data without requiring any export/import or application code changes. This process is designed to be a live upgrade with no downtime, as Firestore in Datastore mode is fully backward-compatible with the Datastore API, allowing existing queries and transactions to continue uninterrupted during the transition.

Answer

Export all data from Datastore to Cloud Storage, then import into a new Firestore database.

Answer

Run both Datastore and Firestore in parallel, writing to both until migration is complete.

Answer

Create a new Firestore database and redirect traffic gradually.

Question 15

A company uses Cloud Memorystore for Redis as a cache for their web application. They want to ensure that cache data survives a failover event with minimal data loss. The current instance has a standard tier (with replication) and persistence disabled. What change should they make?

Accepted Answer

Enable persistence (AOF) on the instance.. Enabling AOF (Append-Only File) persistence on a Cloud Memorystore for Redis standard tier instance ensures that write operations are durably logged to disk. In the event of a failover, the promoted replica can replay the AOF to recover the most recent writes, minimizing data loss beyond what the default in-memory replication provides.

Answer

Switch to the basic tier without replication but with high memory.

Answer

Increase the instance memory size to hold more data.

Answer

Add a read replica to the instance.