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

Question 1

A company uses Cloud Spanner. The backup service account 'sa-backup' needs to create and manage backups of the 'orders' database. However, backup creation fails with a permission error. What is the most likely cause?

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

Accepted Answer

The backup role must be granted at the instance level, not on the database.. Cloud Spanner backup permissions must be granted at the instance level, not on the database itself. The service account 'sa-backup' needs the `spanner.backups.create` permission on the instance resource to create backups, and assigning a role like `roles/spanner.databaseBackupAdmin` at the database level does not propagate the necessary instance-level permissions, causing the backup creation to fail with a permission error.

Answer

The service account lacks the spanner.databases.read permission.

Answer

The service account is assigned the role roles/spanner.databaseBackupAdmin at the database level, but this role only grants database-level permissions and does not include the spanner.backups.create permission required at the instance level.

Answer

The instance 'orders-db' is in a regional configuration, which does not support backups.

Question 2

Arrange the steps to create and connect to a Cloud SQL for PostgreSQL instance using the gcloud command-line tool.

Accepted Answer

Create Cloud SQL instance, then set the password, then configure network access, then connect via psql.. After creating the instance, you set the password, configure network access, then connect via psql.

Answer

Set the password, then create Cloud SQL instance, then configure network access, then connect via psql.

Answer

Create Cloud SQL instance, then configure network access, then set the password, then connect via psql.

Answer

Create Cloud SQL instance, then connect via psql, then set the password, then configure network access.

Question 3

Arrange the steps to configure high availability for a Cloud SQL for MySQL instance.

Accepted Answer

Step 1: Create a Cloud SQL for MySQL instance. Step 2: Enable high availability and configure the standby zone. Step 3: Verify high availability and test failover.. Create instance first, then enable HA and set standby zone, then verify and test.

Answer

Step 1: Enable high availability and configure the standby zone. Step 2: Create a Cloud SQL for MySQL instance. Step 3: Verify high availability and test failover.

Answer

Step 1: Create a Cloud SQL for MySQL instance. Step 2: Verify high availability and test failover. Step 3: Enable high availability and configure the standby zone.

Answer

Step 1: Enable high availability and configure the standby zone. Step 2: Verify high availability and test failover. Step 3: Create a Cloud SQL for MySQL instance.

Question 4

Arrange the steps to set up database encryption with Cloud KMS for Cloud SQL.

Accepted Answer

1. Create KMS key ring and key. 2. Grant Cloud SQL service account access to the KMS key. 3. Create Cloud SQL instance with CMEK. 4. Verify encryption and test.. First create KMS key, grant access, then create instance with CMEK, verify, test.

Answer

1. Grant Cloud SQL service account access to the KMS key. 2. Create KMS key ring and key. 3. Create Cloud SQL instance with CMEK. 4. Verify encryption and test.

Answer

1. Create Cloud SQL instance with CMEK. 2. Create KMS key ring and key. 3. Grant Cloud SQL service account access to the KMS key. 4. Verify encryption and test.

Answer

1. Create KMS key ring and key. 2. Create Cloud SQL instance with CMEK. 3. Grant Cloud SQL service account access to the KMS key. 4. Verify encryption and test.

Question 5

Match each Google Cloud database service to its primary use case.

Accepted Answer

Cloud SQL: Relational database for standard workloads. The correct matches are Cloud SQL for standard relational workloads, Cloud Spanner for globally distributed transactions, and Bigtable for analytical workloads. Firestore is for mobile/web apps, and Memorystore is for caching. Common mistakes include confusing the use cases of these services.

Answer

Firestore: Horizontally scalable relational database for global transactions

Answer

Memorystore: Relational database for standard workloads

Question 6

Match each database migration term to its description.

Accepted Answer

Homogeneous migration: Migrating a database from one source engine to the same target engine.. The correct matches are: Homogeneous migration = same engine, Heterogeneous migration = different engine, Database Migration Service = Google Cloud's database migration tool. Common confusions involve swapping the definitions of homogeneous and heterogeneous, or misattributing DMS to application migration.

Answer

Homogeneous migration: Migrating a database to a different database engine.

Answer

Heterogeneous migration: Migrating a database to the same database engine.

Answer

Database Migration Service (DMS): A tool for migrating applications to compute engines.

Question 7

Match each Cloud Spanner replication type to its purpose.

Accepted Answer

Read-write replica: Can serve reads and writes and participates in voting.. Cloud Spanner supports read-write, read-only, and witness replicas. Read-write replicas handle all operations and vote; read-only replicas scale reads without voting; witness replicas provide quorum without storing data. Common confusions include swapping the roles of read-only and witness replicas.

Answer

Read-write replica: Serves reads only and does not vote.

Answer

Read-only replica: Does not store data but participates in voting.

Question 8

A company is migrating a PostgreSQL database to Cloud SQL for PostgreSQL. They want to ensure minimal downtime during the migration. Which migration strategy should they use?

Accepted Answer

Use Database Migration Service (DMS) with continuous replication. Database Migration Service (DMS) with continuous replication is the correct strategy because it uses change data capture (CDC) to replicate ongoing transactions from the source PostgreSQL database to Cloud SQL with minimal lag. This allows the application to remain fully operational during the migration, and the cutover can be performed in seconds by stopping writes to the source and promoting the target, achieving near-zero downtime.

Answer

Set up application-level dual writes to both databases and switch over

Answer

Create a read replica in Cloud SQL and promote it

Answer

Export the database using pg_dump and import into Cloud SQL

Question 9

A database administrator notices that a Cloud SQL for MySQL instance is experiencing high CPU usage during peak hours. The instance has 4 vCPUs and 15 GB of memory. The query patterns are mostly read-intensive with occasional writes. Which action should the DBA take first to address the high CPU usage?

Accepted Answer

Analyze slow query log and optimize queries. High CPU usage in a read-intensive Cloud SQL for MySQL instance is most often caused by inefficient queries that consume excessive CPU cycles. Analyzing the slow query log allows the DBA to identify and optimize these queries, addressing the root cause directly. Increasing resources or changing configuration without understanding the workload can mask the problem and lead to unnecessary costs.

Answer

Increase the max_connections flag to allow more concurrent connections

Answer

Enable read pool to offload read queries

Answer

Increase the machine type to 8 vCPUs

Question 10

A company has a Spanner instance with 5 nodes serving a global application. They receive alerts that write latency has increased significantly during business hours in the Asia-Pacific region. The team confirms that no application changes have been made. What is the most likely cause and recommended action?

Accepted Answer

Writes are hitting a hot spot due to monotonically increasing keys; consider using a hash prefix or bit-reversed key. Monotonically increasing keys (e.g., timestamps or auto-increment IDs) cause all new writes to target the same tablet leader in Spanner, creating a hot spot. This increases write latency because the single node becomes a bottleneck, especially during peak business hours in the Asia-Pacific region. Using a hash prefix or bit-reversed key distributes writes evenly across nodes, resolving the contention.

Answer

CPU utilization is above 70%; enable Spanner fine-grained access control

Answer

Set up interleaved indexes to speed up writes

Answer

The instance is under-provisioned; increase the number of nodes

Question 11

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 12

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 13

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 14

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 15

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