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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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. Using a private IP address for Cloud SQL ensures that the database instance is not exposed to the public internet, reducing the attack surface. This is a fundamental security best practice for database migrations, as it restricts network access to within a Virtual Private Cloud (VPC) and requires traffic to traverse Google's internal network, which is more secure than public IP routing.

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 3

A company runs a Bigtable instance for time-series data. They need to reduce storage costs without compromising query performance for the most recent 30 days. Which two strategies should they implement?

Accepted Answer

Use Cloud Storage as a cold storage tier for historical data. The question asks for two strategies to reduce storage costs without compromising query performance for the most recent 30 days. The correct strategies are Option B (using Cloud Storage as a cold storage tier for historical data) and Option D (setting garbage collection to delete data older than 30 days). Option B offloads old data to cheaper storage while keeping recent data in Bigtable for fast access. Option D automates deletion of stale data, reducing storage footprint without affecting recent queries. The other options are incorrect: increasing nodes (A) raises costs, replication with deletion (C) risks data loss, and reducing nodes (E) would degrade performance for recent data.

Answer

Increase the number of cluster nodes to improve compaction

Answer

Enable Bigtable replication and delete data from one cluster

Answer

Reduce the number of cluster nodes to save costs

Question 4

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. Point-in-time recovery (PITR) with a 10-minute recovery period is correct because it allows restoring Cloud Spanner data to any point within the last 10 minutes, meeting the RPO of 10 minutes. PITR provides versioned data retention without requiring manual backups, enabling recovery within seconds to minutes, which satisfies the RTO of 5 minutes when combined with a multi-region configuration.

Answer

Deploy a cross-region read replica

Answer

Configure automated backups

Answer

Use database versioning to failover

Question 5

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 6

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 7

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 8

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 9

A data engineer needs to grant a service account read-only access to a Cloud Storage bucket containing sensitive data. The service account is used by a Compute Engine instance. What is the most secure way to assign the permissions?

Accepted Answer

Grant the service account the Storage Object Viewer role on the specific bucket.. Granting the Storage Object Viewer role at the bucket level applies the principle of least privilege, restricting the service account's read-only access to only that specific bucket. This avoids granting broader permissions at the project level, which would inadvertently allow access to all buckets in the project. Using IAM roles is more secure and manageable than legacy bucket ACLs.

Answer

Set bucket ACLs to allow read access for the service account.

Answer

Make the bucket public and rely on network restrictions.

Answer

Grant the service account the Storage Object Viewer role at the project level.

Question 10

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 11

A financial institution requires all data stored in Cloud Spanner to be encrypted using customer-managed encryption keys (CMEK) stored in Cloud KMS. The security team mandates that the key be in a separate project from the Spanner instance. How should the database engineer configure this?

Accepted Answer

Grant the Cloud Spanner service account the Cloud KMS CryptoKey Encrypter/Decrypter role on the key.. Cloud Spanner uses a service account to access Cloud KMS keys. To enable customer-managed encryption keys (CMEK) from a separate project, the Cloud Spanner service account must be granted the Cloud KMS CryptoKey Encrypter/Decrypter role on the specific key. This allows Spanner to encrypt and decrypt data using the key without granting broader permissions.

Answer

Grant the Cloud Spanner service account the Editor role in the key project.

Answer

Specify the key in the Spanner instance creation and provide the user's credentials for KMS access.

Answer

Grant the Spanner instance's service account the Cloud KMS Admin role on the key.

Question 12

A company wants to store and analyze data with BigQuery. They have customers in Europe and need to comply with GDPR data residency requirements. What should they do to ensure data stays within the European Union?

Accepted Answer

Create the dataset with the location set to 'EU'.. BigQuery enforces data residency at the dataset level. When you create a dataset with the location set to 'EU', all tables, views, and data within that dataset are physically stored in a Google Cloud region within the European Union. This ensures compliance with GDPR data residency requirements, as data will not be moved or replicated outside the EU without explicit user action.

Answer

Use the default dataset location and rely on BigQuery's automatic compliance.

Answer

Set the query job location to 'EU' for all queries.

Answer

Choose a dataset location of 'us-central1' for performance.

Question 13

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 14

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.

Question 15

A database engineer is designing a schema for a Cloud Spanner database. Which three practices should they follow to ensure good performance? (Choose three.)

Accepted Answer

Use split points to distribute data across nodes.. Explicitly defining split points in Cloud Spanner allows you to control how data is distributed across nodes, which can prevent hot spots and improve read/write throughput. By specifying split boundaries, you ensure that frequently accessed data is spread evenly, avoiding performance bottlenecks.

Answer

Use locking read (SELECT ... FOR UPDATE) for all transactional reads.

Answer

Create secondary indexes on every column to speed up queries.