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

Question 1

A financial services company runs a mission-critical Cloud SQL for PostgreSQL instance in the us-central1 region. The compliance team requires that all data be encrypted with a customer-managed encryption key (CMEK) and that the key rotation is managed by the company. They also need to ensure that if the CMEK key is disabled or destroyed, the Cloud SQL instance becomes inaccessible. Which configuration should they implement?

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

Accepted Answer

Enable CMEK on the Cloud SQL instance by specifying a Cloud KMS key in the same region as the instance, and grant the Cloud SQL service account the Cloud KMS CryptoKey Encrypter/Decrypter role.. To meet the compliance requirement, the company must configure CMEK with a Cloud KMS key in the same region as the Cloud SQL instance and grant the Cloud SQL service account the CryptoKey Encrypter/Decrypter role. This ensures the instance uses the customer-managed key for encryption and becomes inaccessible if the key is disabled or destroyed.

Answer

Enable CMEK on the Cloud SQL instance by specifying a Cloud KMS key in a different region, and grant the Cloud SQL service account the Cloud KMS CryptoKey Encrypter/Decrypter role.

Answer

Enable CMEK on the Cloud SQL instance by specifying a Cloud KMS key in the same region, and grant the Cloud SQL service account the Cloud KMS Admin role.

Answer

Enable CMEK on the Cloud SQL instance by specifying a Cloud KMS key in the same region, and grant the Cloud SQL service account the Cloud KMS Viewer role.

Question 2

A media company uses Cloud Bigtable to store user event data. They need to design a row key that avoids hotspots and supports efficient queries for the most recent events for a given user. The application queries data by user_id and needs the latest events first. What row key design should they use?

Accepted Answer

Use user_id as the row key prefix, followed by a reversed timestamp.. Using user_id as the prefix groups events by user, and a reversed timestamp sorts them newest-first within that user. This supports efficient range scans for a user's recent events and distributes writes across many users, avoiding hotspots. Other designs either lose per-user grouping, cause write hotspots, or require full scans, failing to meet the performance and query requirements.

Answer

Use a hash of user_id as the row key prefix, followed by a timestamp.

Answer

Use a reverse timestamp as the row key prefix, followed by user_id.

Answer

Use a timestamp as the row key prefix, followed by user_id.

Question 3

You need to migrate a 500 GB on-premises PostgreSQL database to Cloud SQL for PostgreSQL with minimal downtime. The database experiences a steady write workload. Which migration approach should you use?

Accepted Answer

Use the Database Migration Service (DMS) to perform a continuous migration with minimal downtime.. Database Migration Service (DMS) provides continuous replication from an on-premises PostgreSQL database to Cloud SQL, enabling minimal downtime during migration. It performs an initial snapshot and then replicates ongoing changes, allowing a quick cutover. Other methods like dump and restore or import require downtime and are not suitable for large databases with active write workloads.

Answer

Set up a read replica from the on-premises database to Cloud SQL and promote it.

Answer

Perform a pg_dump and pg_restore while the application is offline.

Answer

Use the gcloud sql import command to import a CSV dump of the database.

Question 4

A startup runs a small PostgreSQL database for its internal HR tool on a single Compute Engine VM. The database is 20 GB, has low traffic, and the team wants to reduce operational overhead by moving to a managed service with minimal changes. They need automated backups and patching. Which Google Cloud service should they choose?

Accepted Answer

Cloud SQL for PostgreSQL. For a small, low-traffic PostgreSQL database that needs managed backups and patching with minimal application changes, Cloud SQL for PostgreSQL is the appropriate choice. It is compatible with standard PostgreSQL clients and handles operational tasks automatically, while the other services are either non-relational or designed for much larger scale.

Answer

Firestore

Answer

Bigtable

Answer

Cloud Spanner

Question 5

A startup is deploying a new application on Google Cloud. They need a fully managed, relational database that supports MySQL and requires automatic backups, high availability, and the ability to scale read traffic. They want to minimize operational overhead and have predictable pricing. Which Google Cloud service should they choose?

Accepted Answer

Cloud SQL for MySQL. Cloud SQL for MySQL is a fully managed service that supports MySQL, provides automated backups, high availability via regional instances, and read replicas for scaling reads. It minimizes operational overhead and offers predictable pricing based on instance size. Other options like Cloud Spanner, Bigtable, and Firestore are not MySQL-compatible and would require application changes or are not relational.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 6

A company runs a Cloud SQL for MySQL instance in us-central1 with 500 GB of data. Their compliance policy requires a daily full backup and point-in-time recovery to any second within the last 7 days. The current backup configuration retains only 7 automated backups and does not have binary logging enabled. They need to meet the compliance requirement with minimal operational overhead. What should they do?

Accepted Answer

Enable point-in-time recovery on the instance and set the retention period to 7 days.. Point-in-time recovery in Cloud SQL for MySQL uses binary logs to enable recovery to a specific timestamp. When enabled, it automatically manages binary logs and retention up to 7 days, satisfying the need for second-level recovery with minimal operational overhead. Other methods like mysqldump or manual binary logging require manual management and do not provide the same level of recovery granularity.

Answer

Enable binary logging on the instance and schedule a daily export using the Cloud SQL Admin API.

Answer

Configure a cron job to run mysqldump every hour and store the dumps in a Cloud Storage bucket with a 7-day lifecycle policy.

Answer

Create a read replica and use it for backups, then promote it to primary if recovery is needed.

Question 7

A company runs a Cloud SQL for MySQL instance with a read replica in a different region. They want to promote the read replica to a standalone primary instance to perform a regional failover. What should they do?

Accepted Answer

Use the gcloud sql instances promote-replica command on the read replica.. To promote a read replica to a standalone primary in Cloud SQL, the gcloud sql instances promote-replica command is used. This operation makes the replica writable and stops replication from the original primary, allowing it to serve as the new primary. Other commands like failover are for HA standby, and cloning or recreating does not promote the replica, so they are incorrect.

Answer

Use the gcloud sql instances clone command to create a new instance from the read replica.

Answer

Delete the primary instance and recreate it from the read replica's backup.

Answer

Use the gcloud sql instances failover command on the primary instance.

Question 8

A healthcare company runs a Cloud SQL for PostgreSQL instance that stores patient records. Compliance requires that all data at rest be encrypted with a customer-managed encryption key (CMEK) that the security team can rotate and revoke independently. The team also needs to verify that the key is actually being used for the instance's data. Which approach meets these requirements?

Accepted Answer

Create a Cloud KMS key in the same region as the instance and configure the instance to use that key for CMEK.. Configuring a Cloud SQL instance with a Cloud KMS key in the same region enables CMEK, giving the security team control over rotation and revocation and allowing verification through KMS audit logs. Google-managed keys do not provide customer control, CSEK is not supported for Cloud SQL, and application-layer encryption does not cover all data at rest.

Answer

Enable encryption at the application layer before writing data to the database.

Answer

Enable default encryption with Google-managed keys and rely on Cloud KMS audit logs.

Answer

Use a customer-supplied encryption key (CSEK) stored in Cloud Storage for the instance.

Question 9

A media company uses Cloud Bigtable to store user interaction events. The table has a row key that is a combination of a user ID and a timestamp, with the user ID as a prefix. The company needs to retrieve the most recent 100 events for a specific user. The data is stored in a single cluster in us-central1. The performance of these reads is critical and must be optimized. What should they do to ensure the lowest possible latency for these queries?

Accepted Answer

Use a row key that is a combination of user ID and reversed timestamp, and perform a scan with a limit of 100.. In Cloud Bigtable, data is sorted lexicographically by row key. To efficiently retrieve the most recent events for a user, the row key should be designed so that the latest events are stored together and can be read with a limited scan. Using a reversed timestamp as a suffix makes the most recent events appear first for that user, so a scan with a limit of 100 reads only the necessary rows. This minimizes latency and resource consumption.

Answer

Create a secondary index on the user ID and timestamp columns to speed up the lookups.

Answer

Use a row key that starts with the timestamp, followed by the user ID, and use a prefix scan.

Answer

Enable SSD storage on the Bigtable cluster to reduce read latency.

Question 10

A multinational corporation uses Cloud Spanner for a global inventory system. They need to ensure that the database can survive a regional outage without data loss and maintain strong consistency. The database is deployed in a multi-region configuration with two read-write regions and one witness region. During a planned failover test, they simulate the failure of one read-write region. What is the expected behavior of the Cloud Spanner instance?

Accepted Answer

The instance automatically fails over to the remaining read-write region, and writes continue with no data loss, but there may be a brief period of increased latency.. Cloud Spanner multi-region configurations with two read-write regions and one witness region are designed to survive the failure of a single read-write region. The witness region helps maintain quorum, and the surviving read-write region automatically takes over all read and write operations. Strong consistency is preserved, and no data is lost. There may be a brief latency increase during failover, but the instance remains available.

Answer

The instance continues to serve reads but rejects all writes until the failed region is restored, because the witness region cannot accept writes.

Answer

The instance becomes unavailable until the failed region is restored, because Cloud Spanner requires all read-write regions to be operational.

Answer

The instance automatically fails over to the witness region, which becomes the new read-write region, and writes continue with no data loss.

Question 11

A media company stores user-generated images in a Cloud Storage bucket and metadata in a Cloud SQL for MySQL instance. They need to run analytical queries that join the metadata with external data in BigQuery without moving the Cloud SQL data. Which approach should they use?

Accepted Answer

Create a federated query in BigQuery that connects to Cloud SQL using the Cloud SQL connector.. BigQuery federated queries allow querying Cloud SQL for MySQL directly using the Cloud SQL connector, so the metadata remains in Cloud SQL while being joined with BigQuery data. This avoids data movement and duplication, satisfying the requirement for live analytical queries.

Answer

Export the Cloud SQL data to a CSV file in Cloud Storage and load it into BigQuery.

Answer

Use Cloud SQL's built-in BigQuery export feature to schedule daily exports.

Answer

Set up a Datastream pipeline from Cloud SQL to BigQuery and query the replicated tables.

Question 12

A healthcare company stores patient records in Cloud Spanner. Compliance requires that all data be encrypted with customer-managed encryption keys (CMEK) that the company rotates every 90 days, and that access to the keys be auditable. The company also needs to ensure that if a key is disabled, the database becomes unavailable. Which configuration should the database engineer implement?

Accepted Answer

Create a Cloud KMS key ring and key in the same region as the Spanner instance, grant the Spanner service account the cloudkms.cryptoKeyEncrypterDecrypter role, and specify the key when creating the instance.. Cloud Spanner CMEK requires a Cloud KMS key in the same region as the instance. The Spanner service account must have the cloudkms.cryptoKeyEncrypterDecrypter role to encrypt and decrypt data encryption keys. This setup enables customer-controlled key rotation and audit logging, and disabling the key renders the data inaccessible, satisfying compliance and availability requirements.

Answer

Enable CMEK by using a Cloud External Key Manager (Cloud EKM) key hosted in a third-party key management system.

Answer

Create a Cloud KMS key in a different region, grant the Compute Engine default service account the cloudkms.cryptoKeyEncrypterDecrypter role, and specify the key when creating the instance.

Answer

Use Google-managed encryption keys and enable Cloud Audit Logs for the Spanner instance.

Question 13

A company is planning to deploy a Cloud SQL for PostgreSQL instance for a mission-critical application. They need to ensure that the database can survive a zone failure and that they can recover data to a specific point in time within the last 7 days. They also want to minimize downtime during maintenance. Which two actions should they take? (Choose two.)

Accepted Answer

Enable point-in-time recovery (PITR) and set the transaction log retention period to 7 days.. To survive a zone failure with minimal downtime, configure the instance as regional, which maintains a standby in another zone and provides automatic failover. To enable point-in-time recovery within 7 days, enable PITR and set the transaction log retention to 7 days. These two actions together meet the high availability and recovery requirements.

Answer

Schedule manual backups to run every hour and store them in a multi-region Cloud Storage bucket.

Answer

Create a read replica in a different region and promote it if the primary fails.

Answer

Enable binary logging and set the binary log retention to 7 days, and create a failover replica.

Question 14

A gaming company uses Cloud Bigtable to store player state and leaderboard data. They need to design a row key that allows efficient retrieval of the top 100 players by score for each game mode. The leaderboard is updated frequently, and queries must return results quickly. Which row key design should they use?

Accepted Answer

Reverse the score and prefix with game mode, e.g., game_mode#reverse_score. Cloud Bigtable row keys are sorted lexicographically. To get the top scores efficiently, you need the highest scores to be adjacent and sorted first. Reversing the score (e.g., using a fixed-width numeric string and subtracting from a maximum) makes higher scores sort earlier. Prefixing with game mode allows you to scan only the relevant rows for that mode. This design supports fast retrieval of the top 100 players by reading the first 100 rows for a given game mode.

Answer

Use the player ID as the row key

Answer

Use a timestamp as the row key

Answer

Use a random UUID as the row key

Question 15

A financial services company runs a Cloud SQL for SQL Server Enterprise edition instance in us-central1. The compliance team requires that all data at rest be encrypted with a customer-managed encryption key (CMEK) that the company controls. The security team creates a Cloud KMS key ring and key in the same region. Which configuration step is required to meet this requirement?

Accepted Answer

Grant the Cloud SQL service account the Cloud KMS CryptoKey Encrypter/Decrypter role on the key and configure the instance to use the CMEK.. For Cloud SQL to use a customer-managed encryption key, the instance's service account must have the Cloud KMS CryptoKey Encrypter/Decrypter role on the key, and the instance must be configured with that key. This combination ensures that the database encrypts data at rest with the customer-controlled key, meeting compliance requirements. Other options either omit required permissions or reference invalid configurations.

Answer

Enable CMEK by setting the instance's disk encryption to 'customer-managed' and provide the key directly in the instance configuration without additional IAM permissions.

Answer

Enable CMEK by creating a customer-managed key in Cloud KMS and then enabling the Cloud SQL Admin API, which automatically grants the necessary permissions.

Answer

Use the gcloud sql instances patch command with the --disk-encryption-key flag pointing to the KMS key resource name, and rely on default service account permissions.