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

Question 1

A company is deploying a Cloud Spanner instance for a global application. They need to ensure that the instance can handle high write throughput and provide low latency for users in North America and Europe. They also want to minimize network latency for writes. Which two factors should they consider when selecting the instance configuration? (Choose two.)

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

Accepted Answer

The number of nodes or processing units must be sufficient to handle the expected write throughput.. When selecting a Cloud Spanner instance configuration for a global application, you must ensure sufficient compute capacity (nodes or processing units) to handle write throughput. Additionally, choosing a multi-region configuration that includes regions in North America and Europe minimizes write latency by allowing writes to be served from a nearby region. Other options either misunderstand Cloud Spanner's replication model or are not valid considerations.

Answer

The instance should use SSD storage to ensure low latency for writes.

Answer

The instance should be configured with a regional configuration in us-central1 and use Cloud Interconnect to connect European users.

Answer

The instance should use a single region with read replicas in other regions to reduce write latency.

Question 2

A logistics company uses Cloud Bigtable to store real-time shipment tracking data. The table's row key is the shipment ID, which is a random UUID. They notice that read and write performance is inconsistent, and some tablet servers are hotspots. They want to improve performance without changing the application's data model. What should they do?

Accepted Answer

Pre-split the table using split keys based on a hash of the shipment ID.. Bigtable performance depends on even distribution of rows across tablet servers. A random UUID row key can still cause hotspots if the table is not pre-split, because the initial tablets may not cover the key space evenly. Pre-splitting with hashed keys ensures that writes are spread across multiple tablets from the start. This improves throughput and reduces latency without changing the application's row key or data model.

Answer

Change the row key to a sequential timestamp to improve write locality.

Answer

Enable replication on the Bigtable instance to distribute load across multiple clusters.

Answer

Increase the number of nodes in the Bigtable instance to add more tablet servers.

Question 3

A financial analytics team runs a 3-node Cloud Spanner instance in a single region. They need to add a read-only replica in a second region to serve low-latency reads for a new office, while keeping the existing nodes for writes. Which configuration change should they make?

Accepted Answer

Change the instance's replication configuration to a dual-region configuration that includes both regions.. Cloud Spanner replica placement is controlled by the instance configuration, not by individually adding replicas. A dual-region configuration provides read-write replicas in two regions plus read-only replicas, giving the second office local read latency while maintaining a single logical instance. Creating a second instance or changing endpoints does not produce the required topology.

Answer

Enable the regional endpoint for the second region and point the new office's application at it.

Answer

Add a read-only replica to the existing instance using the gcloud spanner instances update command with a --replica flag.

Answer

Create a separate Cloud Spanner instance in the second region and replicate data using a change stream.

Question 4

A media company uses Cloud Bigtable to store user interaction events. The table's row keys are currently designed as reverse timestamps (e.g., 20231015123456#user123). They are experiencing hotspots and uneven load distribution across nodes. They want to redesign the row key to improve performance and distribute load evenly. Which two strategies should they use? (Choose two.)

Accepted Answer

Use a row key that combines the user ID and a timestamp, with the user ID as the prefix.. To distribute load evenly in Cloud Bigtable, you should avoid sequential or timestamp-prefixed keys. Salting with a hash of the user ID randomizes the key space, while using the user ID as a prefix groups events by user and leverages the natural distribution of user IDs. Both strategies prevent hotspots from monotonically increasing timestamps. The other options either create hotspots or reduce query efficiency.

Answer

Use a row key that starts with the timestamp in reverse order to ensure the most recent events are stored together.

Answer

Use a row key that is a hash of the timestamp only, without any user identifier.

Answer

Use a sequential numeric row key that increments with each event.

Question 5

A financial services company runs a Cloud SQL for PostgreSQL instance in the us-central1 region to support a trading application. Regulatory requirements mandate that the database must be recoverable to any point in time within the last 14 days, and the recovery must be possible even if the primary instance is completely unavailable. The database administrator needs to configure the automated backup and recovery settings. What should the administrator do?

Accepted Answer

Enable automated backups with a 14-day retention period and point-in-time recovery (PITR), and ensure the instance has a regional configuration with a standby zone.. Point-in-time recovery (PITR) in Cloud SQL for PostgreSQL uses write-ahead logs to allow recovery to any moment within the configured retention period, up to 14 days. Automated backups provide a base backup for PITR. A regional instance maintains a standby in a different zone for high availability, ensuring the database remains accessible if the primary zone fails. Together, these settings meet both the recovery point objective and the availability requirement.

Answer

Enable point-in-time recovery (PITR) and configure transaction log retention to 14 days, then create a read replica in another zone for failover.

Answer

Create a manual backup every day and export it to a Cloud Storage bucket with a 14-day lifecycle policy, then use those exports for recovery.

Answer

Configure high availability by adding a failover replica and enable binary logging with a 14-day retention period.

Question 6

A financial analytics team needs a relational database on Google Cloud that can scale horizontally to handle a global trading ledger with strong consistency. The ledger has a primary key composed of account_id (string), trade_date (date), and trade_id (string). Writes are distributed across all accounts and dates, and the team wants to avoid hotspots. They also require automatic sharding and replication across regions. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner with a primary key using a hash of account_id. Cloud Spanner is the only Google Cloud relational database that provides horizontal scaling, strong consistency, and automatic sharding across regions. To avoid hotspots, the primary key should distribute writes evenly. Hashing account_id ensures that writes are spread across many splits, preventing a single split from becoming a bottleneck. This design supports the global trading ledger with high write throughput and low latency.

Answer

Cloud Spanner with a primary key starting with account_id

Answer

Cloud Spanner with a primary key starting with trade_date

Answer

Cloud SQL for PostgreSQL with read replicas in multiple regions

Question 7

A logistics company uses Cloud Bigtable to store IoT sensor data. They need to design the row key to optimize for queries that retrieve the latest reading for a specific sensor and for range scans across sensors in a specific geographic region. They also want to avoid hotspots. Which row key design should they use?

Accepted Answer

Use a row key that concatenates region, sensor_id, and a reversed timestamp, in that order.. Using region as the first part of the row key enables efficient range scans for a geographic area. Within a region, sensor_id groups readings per sensor, and a reversed timestamp ensures the latest reading is first. This design balances read efficiency and write distribution.

Answer

Use a row key that concatenates a random hash, region, sensor_id, and timestamp, in that order.

Answer

Use a row key that concatenates timestamp, region, and sensor_id, in that order.

Answer

Use a row key that concatenates sensor_id and a reversed timestamp, with sensor_id as the prefix.

Question 8

A financial analytics firm runs an operational MySQL database on a Compute Engine VM in europe-west1. The database is 800 GB, and the team wants to move it to Cloud SQL for MySQL with less than 10 minutes of application downtime during the final cutover. They can tolerate a short read-only period. Which approach should they use to meet the downtime requirement?

Accepted Answer

Use Database Migration Service with a continuous CDC-based migration and promote during a maintenance window.. Database Migration Service is designed for minimal-downtime migrations to Cloud SQL. It performs a consistent initial load and then applies ongoing changes via binary log replication, so the target stays current until promotion. A brief write freeze during promotion is enough to meet the 10-minute downtime requirement, while dump/import and unsupported replication topologies cannot.

Answer

Configure Cloud SQL as an external replica of the on-premises server using the Cloud SQL Auth Proxy, then fail over to it.

Answer

Export the database with mysqldump, upload the dump to a Cloud Storage bucket, and import it into Cloud SQL during a weekend outage.

Answer

Create a Cloud SQL read replica of the on-premises primary by configuring native MySQL replication manually, then promote the replica.

Question 9

A financial services company runs Cloud Spanner with a multi-region configuration (nam-eur-asia1). They need to store a new table of customer transaction records that will be queried by customer_id for point lookups and by transaction_date for monthly reporting. They want the best performance for both access patterns. What should they do?

Accepted Answer

Create a composite primary key with customer_id as the first column and transaction_date as the second, and create a secondary index on transaction_date.. The optimal schema uses customer_id as the primary key prefix for point lookups and a secondary index on transaction_date for reporting queries. This leverages Cloud Spanner's ability to seek efficiently on key prefixes and index scans, avoiding full table scans and minimizing latency and cost for both patterns. Other designs either miss one access pattern or introduce unnecessary index overhead.

Answer

Create a composite primary key with customer_id as the first column and transaction_date as the second column.

Answer

Use transaction_date as the sole primary key and create a secondary index on customer_id.

Answer

Use a generated UUID as the primary key and create two secondary indexes: one on customer_id and one on transaction_date.

Question 10

A retail company uses Cloud Bigtable for a user activity tracking system. The table has a row key that is the user ID followed by a timestamp. The team notices that reads for the most recent activity for a specific user are slow because they must scan many versions. They want to optimize the schema to retrieve the latest activity quickly. What should they do?

Accepted Answer

Use a row key that is the user ID followed by a reversed timestamp.. In Cloud Bigtable, rows are sorted lexicographically by row key. By using a reversed timestamp as a suffix, the most recent timestamp (which becomes the smallest when reversed) sorts first within the user's row. This enables fast retrieval of the latest activity with a single read. The user ID prefix preserves the ability to query by user efficiently. This design optimizes the specific access pattern without requiring changes to the application's query logic beyond the key construction.

Answer

Enable garbage collection policies to keep only the latest version of each cell.

Answer

Use a row key that is a hash of the user ID followed by a reversed timestamp.

Answer

Change the row key to timestamp followed by user ID.

Question 11

You are planning to deploy a Cloud SQL for MySQL instance for a high-traffic web application. The application requires low latency for reads and must be resilient to a zonal failure. You need to configure the instance for high availability and read scaling. (Choose two.)

Accepted Answer

Enable high availability (HA) to create a standby instance in a different zone.. Enabling high availability provides automatic failover for zonal failures, ensuring resilience. Creating read replicas distributes read traffic, improving performance and scalability. Together, they meet the requirements for low-latency reads and zonal resilience. Other options like PITR and cross-region replicas serve different purposes and do not directly address the need for high availability and read scaling within the region.

Answer

Configure the instance to use a regional persistent disk for data storage.

Answer

Set up a cross-region replica for disaster recovery.

Answer

Enable point-in-time recovery (PITR) to ensure data durability.

Question 12

A healthcare company stores sensitive patient data in Cloud SQL for MySQL. They must ensure that all data at rest and in transit is encrypted, and they want to manage the encryption keys themselves using a hardware security module (HSM) for compliance. They also need to audit key usage. Which Google Cloud service should they use to meet these requirements?

Accepted Answer

Cloud HSM. Cloud HSM provides a managed HSM service that integrates with Cloud SQL for customer-managed encryption keys (CMEK). It ensures that keys are protected by FIPS 140-2 Level 3 validated hardware, and key usage is logged in Cloud Audit Logs. This meets the compliance and auditing requirements for sensitive healthcare data.

Answer

Cloud External Key Manager (EKM)

Answer

Cloud Data Loss Prevention (DLP)

Answer

Cloud Key Management Service (KMS) with software keys

Question 13

A company is designing a Cloud Spanner instance for a global application with users in North America, Europe, and Asia. The application performs both reads and writes in all three regions and requires strong consistency. The team must choose a configuration that minimizes write latency for users worldwide while meeting the consistency requirement. (Choose two.)

Accepted Answer

Use a dual-region or multi-region configuration that places replicas on multiple continents.. A multi-continent configuration with a read-write leader in one region and read-only replicas in others gives local reads everywhere and coordinated writes, which is the Cloud Spanner design for global strong consistency. Continental configurations keep replication within one continent, and separate instances with Change Streams cannot provide strong consistency across regions.

Answer

Deploy three separate single-region instances, one per continent, and replicate data with Change Streams.

Answer

Use a single-region configuration in the region with the most users and rely on Cloud CDN for reads.

Answer

Use a multi-region instance configuration such as nam3 or eur3.

Question 14

A financial services company runs a Cloud SQL for PostgreSQL instance in us-central1. Their disaster recovery policy requires that, in the event of a regional outage, they can bring up a fully functional read/write instance in us-east1 with no data loss. Which configuration should they implement?

Accepted Answer

Create a cross-region read replica in us-east1 and promote it during a disaster.. A cross-region read replica in us-east1 can be promoted to a standalone read/write instance during a regional outage, providing a functional database in the DR region. Although replication is asynchronous and may lose a small amount of data, it is the only listed option that creates a usable instance in a different region.

Answer

Set up a Cloud SQL Auth Proxy connection to the primary from us-east1 and redirect application traffic.

Answer

Enable high availability on the primary instance with a standby in us-east1.

Answer

Configure a read replica in us-east1 and enable point-in-time recovery on the primary.

Question 15

A financial services company runs a Cloud SQL for PostgreSQL instance in us-central1 that contains 4 TB of transaction records. The database must be available continuously, but the company's disaster recovery policy requires a full copy of the data in a separate region (europe-west1) that can be promoted to a primary within 15 minutes if the primary region fails. The application cannot be modified and must continue to use the same connection endpoint after failover. Which approach should the database engineer implement?

Accepted Answer

Create a cross-region read replica in europe-west1 and promote it during a regional failure.. A cross-region read replica maintains an asynchronous copy of the primary in a different region and can be promoted to a standalone primary. Promotion is fast enough to meet a 15-minute RTO, and the promoted instance retains the same connection name, so the unmodified application reconnects automatically. Other options either stay within one region or introduce a new endpoint.

Answer

Configure a Cloud SQL federated query to read data from a Cloud Storage bucket in europe-west1.

Answer

Schedule automated backups with a retention policy and restore them to a new instance in europe-west1 when needed.

Answer

Enable high availability by adding a standby instance in a different zone within us-central1.