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

Question 1

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 2

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 3

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 4

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. Option B is correct because exporting historical time-series data to Cloud Storage (e.g., as Parquet/Avro files) moves cold data off Bigtable's SSD-backed storage, directly reducing the storage cost of the Bigtable instance while keeping the most recent 30 days in Bigtable for fast query performance. Option D is correct because configuring a garbage collection policy with an age-based rule (maxage=30d) automatically deletes cells older than 30 days, so Bigtable only stores and bills for the recent 30 days of data, which is exactly the retention window required. Option A is not appropriate because adding cluster nodes increases compute cost and does not reduce storage cost, and compaction is managed automatically by Bigtable. Option C is wrong because replication actually multiplies storage and compute costs across clusters, and deleting data from one cluster does not reduce the cost of the remaining cluster. Option E is wrong because reducing cluster nodes lowers compute capacity and can degrade performance, but it does not reduce the storage footprint of the 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 5

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 6

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 7

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 8

A company is planning to migrate a large on-premises MySQL database (5 TB) to Cloud SQL. They need to choose the appropriate Cloud SQL edition. Which two factors should they consider? (Choose two.)

Accepted Answer

Maximum storage capacity supported by each edition.. Option C is correct because Cloud SQL editions differ in the maximum storage they support, and a 5 TB database requires verifying that the chosen edition can accommodate that data volume plus growth. Option D is correct because the editions also differ in performance limits such as maximum IOPS and throughput, which directly affect whether the migrated MySQL workload can meet its performance requirements. Options A, B, and E are not edition-specific differentiators: regional high availability, automated backups, and VPC network integration are Cloud SQL features available across editions rather than factors that determine which edition to select.

Answer

Availability of regional high availability.

Answer

Automated backup capabilities.

Answer

Integration with VPC networks.

Question 9

A company stores log files in Cloud Storage buckets. The logs are accessed frequently for the first 30 days, then rarely for the next 6 months, after which they must be archived for 7 years. They want to minimize storage costs. Which two actions should they take? (Choose two.)

Accepted Answer

Set a lifecycle rule to change storage class to Nearline after 30 days.. Option B is correct because Nearline storage is designed for data accessed less than once per month, so transitioning objects to Nearline after the initial 30-day frequent-access period matches the "rarely accessed for the next 6 months" requirement while lowering cost compared to Standard. Option E is correct because Archive storage is the lowest-cost class intended for data retained for long periods (minimum 365 days) and accessed at most once a year, which fits the 7-year archival requirement after the 6-month Nearline window. Option A is wrong because keeping objects in Standard for the entire retention period ignores the colder access patterns and fails to minimize storage costs. Option C is wrong because deleting objects after 30 days violates the requirement to retain the logs for 7 years. Option D is wrong because Autoclass manages transitions automatically based on access patterns and does not give the deterministic, scheduled 30-day and 6-month transitions the scenario requires.

Answer

Keep objects in Standard storage class for the entire retention period.

Answer

Set a lifecycle rule to delete the objects after 30 days.

Answer

Enable Autoclass to automatically transition objects to colder storage classes.

Question 10

A company notices that their Cloud SQL for PostgreSQL instance, as shown in the exhibit, frequently runs out of storage, causing downtime. They have set up automated backups with point-in-time recovery. What is the most likely cause of the storage issue?

Accepted Answer

Transaction logs for point-in-time recovery are consuming disk space.. Cloud SQL for PostgreSQL uses transaction logs (WAL files) to enable point-in-time recovery (PITR). These logs accumulate on the disk until they are automatically removed, but if the rate of log generation exceeds the cleanup rate or if the backup retention period is long, the logs can fill the disk, causing storage exhaustion and downtime.

Answer

The activation policy is set to ALWAYS, causing continuous writes.

Answer

The instance tier (db-custom-4-15360) is too low for the workload.

Answer

The data disk type (PD_SSD) is not suitable for PostgreSQL.

Question 11

A team is reviewing IAM permissions on a Cloud Storage bucket. The exhibit shows the bucket's IAM policy. A developer is using the service account sa-1 and reports that they cannot delete objects in the bucket. What is the likely reason?

Accepted Answer

The service account sa-1 does not have the storage.objects.delete permission.. The IAM policy shown in the exhibit grants the `objectViewer` role to service account `sa-1`, which includes the `storage.objects.get` and `storage.objects.list` permissions but does not include `storage.objects.delete`. Without the `storage.objects.delete` permission, the developer cannot delete objects in the bucket, even if they can view them. The correct answer is B because the service account lacks the necessary delete permission.

Answer

The etag value must be updated before any delete operation.

Answer

A condition is attached to the objectViewer role that prevents deletion.

Answer

The policy only allows deletion by the service account sa-2.

Question 12

A company has a parent-child relationship in Cloud Spanner. They want to minimize cross-table join latency. What should they use?

Accepted Answer

Interleaved tables. Interleaved tables in Cloud Spanner physically co-locate child rows with their parent rows, which minimizes latency for joins between parent and child tables. This is the recommended approach for parent-child relationships to optimize join performance.

Answer

Cloud Functions

Answer

Stored procedures

Answer

Secondary indexes

Question 13

A production Cloud SQL for PostgreSQL instance needs to handle increased read traffic and provide automatic failover in case of a zone outage. Which architecture satisfies both requirements?

Accepted Answer

Enable high availability (regional) and create a read replica in the same region.. Enabling high availability (regional) for Cloud SQL for PostgreSQL creates a primary and standby instance in different zones within the same region, providing automatic failover during a zone outage. Adding a read replica in the same region offloads read traffic from the primary instance, satisfying the increased read traffic requirement while the HA configuration ensures high availability.

Answer

Enable high availability only.

Answer

Use a CMEK key and enable binary logging.

Answer

Create multiple read replicas without HA.

Question 14

A Cloud SQL instance stores financial data. They need to meet a 1-hour RPO and 30-minute RTO. What backup configuration should they use?

Accepted Answer

Automatic backups plus binary logging to enable point-in-time recovery (PITR).. Automatic backups combined with binary logging enable point-in-time recovery (PITR), which allows restoring the database to any point within the backup retention period. This configuration meets the 1-hour RPO by recovering transactions committed within the last hour, and the 30-minute RTO by using the most recent full backup plus binary logs to restore quickly.

Answer

Export once a day to Cloud Storage.

Answer

Enable high availability.

Answer

Automatic backups with a schedule of 1 hour.

Question 15

Which TWO considerations are critical when migrating from Cassandra to Cloud Bigtable?

Accepted Answer

Denormalizing data to avoid joins. Option B is correct because Cloud Bigtable does not support joins, so data that was normalized across Cassandra tables must be denormalized into a single wide row or column family structure to preserve query access patterns. Option D is correct because Bigtable performance depends heavily on row key design; keys must be chosen to distribute reads and writes evenly across tablet servers and avoid hotspots, unlike Cassandra where partition keys and clustering columns handle distribution differently. Option A is incorrect because Cloud Bigtable does not support CQL; it uses the HBase API, Google Cloud CLI, or client libraries, so CQL queries must be rewritten. Option C is incorrect because Bigtable provides strong consistency for single-row operations, not the tunable eventual consistency model used in Cassandra. Option E is incorrect because Bigtable does not support secondary indexes; efficient filtering must be achieved through row key design or by scanning and filtering in the application.

Answer

Using CQL for queries

Answer

Maintaining eventual consistency model

Answer

Using secondary indexes for efficient filtering