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

Question 1

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.. Option A is correct because Cloud Spanner distributes data by key ranges across splits, and explicitly defining or managing split points (or designing keys that allow Spanner to split effectively) helps balance load across nodes and avoid hotspots. Option C is correct because monotonically increasing primary key values, such as sequential integers or timestamps at the start of a key, cause all new writes to land in the last split, creating a write hotspot; using hashed or reversed values, or high-cardinality non-sequential prefixes, distributes writes. Option D is correct because interleaved tables physically colocate child rows with their parent row, so parent-child joins and co-accessed data can be read with fewer network round trips and better locality. Option B is not appropriate because Cloud Spanner provides external consistency and does not require SELECT ... FOR UPDATE-style locking reads for all transactional reads; unnecessary locking reads increase contention and latency. Option E is not appropriate because indexing every column adds storage cost and write amplification, and Cloud Spanner's optimizer may not use redundant indexes, so indexes should be created selectively based on query patterns.

Answer

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

Answer

Create secondary indexes on every column to speed up queries.

Question 2

A developer runs the command shown in the exhibit and wants to verify that replication is enabled on the Bigtable instance. Where should they look for this information in the output?

Accepted Answer

View the 'clusters' list within the instance description.. The `gcloud bigtable instances describe` command returns a JSON representation of the instance, which includes a `clusters` list. Each cluster object in that list contains a `replication` field (e.g., `defaultStorageType` and `nodes`), and the presence of multiple clusters in the list indicates that replication is configured. Replication in Cloud Bigtable is enabled by adding more than one cluster to the instance, so examining the `clusters` list directly shows whether replication is active.

Answer

Examine the 'instanceType' field for 'MULTI_CLUSTER'.

Answer

Look for a 'replication' field in the JSON.

Answer

Check the 'state' field for 'REPLICATED'.

Question 3

A company is migrating their on-premises PostgreSQL database to Cloud SQL. They want to minimize downtime during the migration. Which approach should they use?

Accepted Answer

Use Database Migration Service (DMS) with continuous replication from an on-premises replica.. Database Migration Service (DMS) with continuous replication minimizes downtime by performing an initial full load of the database and then continuously replicating changes from the on-premises source to Cloud SQL. This allows the target to stay nearly synchronized with the source, so the final cutover can be completed in seconds or minutes rather than hours.

Answer

Export the database using pg_dump and import using psql in a single connection.

Answer

Use a VPN tunnel and set up Cloud SQL as a read replica of the on-premises primary.

Answer

Use Database Migration Service (DMS) with a one-time full dump and import.

Question 4

A Cloud Bigtable instance is experiencing hotspotting on a single node during heavy write traffic. The row keys are based on a timestamp prefix. Which change should they make to the row key design to distribute writes evenly?

Accepted Answer

Add a random prefix (salting) to the row key. Adding a random prefix (salting) to the row key distributes writes across multiple tablet servers by ensuring that consecutive timestamps do not all hash to the same node. This prevents hotspotting because Cloud Bigtable partitions rows lexicographically by row key; a monotonically increasing timestamp prefix causes all new writes to land on a single tablet server. Salting spreads the write load uniformly across the cluster.

Answer

Use a reverse timestamp (e.g., MAX_TIMESTAMP - timestamp)

Answer

Increase the number of nodes in the cluster

Answer

Enable replication across zones

Question 5

A mobile app stores user profiles in Firestore. Users are spread globally. Which data model ensures low latency reads and writes?

Accepted Answer

Subcollections under a geographic region collection (e.g., /regions/{region}/users/{user}). It uses geographic region as the top-level collection key, which enables Firestore to co-locate user documents within the same region. This minimizes latency by ensuring that reads and writes for users in the same geographic area are served from a nearby Firestore instance, leveraging Firestore's automatic multi-region replication and strong consistency within a location.

Answer

A single collection containing all user documents

Answer

One document per user in a single collection with composite indexes

Answer

One collection per geographic region

Question 6

A company uses Memorystore for Redis as a cache. They need to survive a zone failure. Which configuration should they choose?

Accepted Answer

Standard tier with replication. Standard tier with replication (Option D) provides a primary-replica pair in the same region but across two zones, ensuring automatic failover if one zone fails. This meets the requirement to survive a zone failure while maintaining low-latency access within a single region.

Answer

Cluster tier with multiple shards

Answer

Basic tier with a single node

Answer

Standard tier cross-region

Question 7

An e-commerce platform requires strong consistency across global regions. Which database service should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides strong consistency across global regions via synchronous replication and TrueTime, ensuring ACID transactions with external consistency. This meets the e-commerce platform's requirement for globally consistent reads and writes without eventual consistency trade-offs.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud SQL

Question 8

A Cloud Spanner instance is experiencing increased latency during peak hours. Monitoring shows CPU utilization nearing 70%. How should they scale?

Accepted Answer

Add more nodes.. Adding more nodes is the correct scaling approach for a Cloud Spanner instance experiencing high CPU utilization and latency. Cloud Spanner distributes data and query processing across nodes; each node provides a fixed amount of compute and storage capacity. Increasing the number of nodes directly increases the available CPU resources, reducing per-node utilization and improving query throughput and latency.

Answer

Change to a higher-tier machine type.

Answer

Increase the number of splits.

Answer

Add more processing units.

Question 9

Which TWO factors are important when selecting the location for a Cloud Spanner instance?

Accepted Answer

Data residency compliance. Option B (Data residency compliance) is correct because Cloud Spanner instance location determines where data is stored and replicated, and many regulations (e.g., GDPR, HIPAA, or industry-specific rules) require data to remain within specific geographic boundaries, so the chosen region or multi-region configuration must satisfy those legal constraints. Option C (Proximity to users) is correct because placing a Spanner instance close to the application's users reduces network round-trip latency and improves read/write performance, which is a primary design consideration when choosing a region or multi-region configuration. Option A is not a key selection factor because the number of other Spanner instances in a region does not affect your instance's performance, capacity, or compliance. Option D is not a primary factor because Cloud Spanner node pricing is generally consistent across regions and is not the deciding criterion for location selection. Option E is not relevant because Cloud VPN is a networking service for connecting on-premises networks to GCP, not a factor in choosing a Spanner instance location.

Answer

Number of other Spanner instances in the region

Answer

Cost of Cloud Spanner nodes in the region

Answer

Availability of Cloud VPN

Question 10

Which THREE actions can reduce read latency for a globally distributed Cloud Spanner database?

Accepted Answer

Use read-only replicas (follower reads). Option A is correct because read-only replicas (follower reads) let a client read from a nearby replica instead of routing the request to the leader region, which directly cuts network round-trip latency for globally distributed reads. Option B is correct because a multi-region configuration places replicas in multiple geographic regions, so reads can be served from a replica close to the user, reducing latency compared with a single-region instance. Option E is correct because secondary indexes allow queries to be satisfied by an index scan rather than a full table scan, reducing the amount of data read and thus lowering read latency. Option C (leader reads) is not correct because leader reads must be served by the leader replica, which can be far from the client and increases latency. Option D (interleaved tables) is not correct because interleaving is a schema design for co-locating parent and child rows to improve join performance, not a mechanism for reducing global read latency.

Answer

Use leader reads

Answer

Use interleaved tables

Question 11

A company is migrating their on-premises PostgreSQL database to Cloud SQL. They want to minimize downtime during the migration. Which approach should they use?

Accepted Answer

Use Database Migration Service with continuous replication. Database Migration Service (DMS) with continuous replication is the correct approach because it uses change data capture (CDC) to replicate ongoing transactions from the source PostgreSQL database to Cloud SQL, enabling a near-zero downtime migration. DMS handles schema conversion, data validation, and automated failover, which minimizes the cutover window to seconds or minutes.

Answer

Use pg_dump and pg_restore

Answer

Export data to CSV and import into Cloud SQL

Answer

Use a third-party ETL tool

Question 12

You need to export data from Cloud Spanner for archival purposes. Which method is most cost-effective?

Accepted Answer

Use gcloud spanner databases export. `gcloud spanner databases export` uses Cloud Spanner's built-in managed export feature, which directly exports data to Cloud Storage in Avro format without incurring compute costs from Dataflow or Cloud Functions. This is the most cost-effective method for archival purposes as it leverages Spanner's internal export infrastructure, which is optimized for bulk data movement and has no additional per-operation compute charges beyond the storage and network egress costs.

Answer

Use a Cloud Function to copy data

Answer

Use Dataflow to read and write to Cloud Storage

Answer

Use gcloud spanner databases execute-sql

Question 13

You are building a globally distributed leaderboard application that requires strongly consistent reads with latency under 10 ms and high write throughput. Which Google Cloud database service is most suitable?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides strongly consistent reads across globally distributed regions with latency under 10 ms, while also supporting high write throughput. It uses TrueTime and synchronous replication to ensure ACID transactions and global consistency, meeting the exact requirements of a globally distributed leaderboard application.

Answer

Cloud Bigtable

Answer

Memorystore

Answer

Firestore

Question 14

You are designing a highly available Cloud SQL for MySQL architecture. Which TWO components are essential?

Accepted Answer

High availability (HA) configuration with a standby zone. Option A is correct because enabling HA configuration on Cloud SQL for MySQL provisions a standby instance in a different zone within the same region, and automatic failover to that standby is the core mechanism that keeps the database available during a zone-level outage. Option B is correct because Cloud SQL HA uses a regional persistent disk that synchronously replicates data across two zones in the region, so the standby instance always has an up-to-date copy of the data to take over from. Option C is not essential for HA because PITR is a backup/recovery feature that restores data to a past point in time, not a mechanism for automatic failover. Option D is not essential because read replicas serve read scaling and are not automatically promoted for HA failover in Cloud SQL. Option E is not essential because cross-region replication addresses disaster recovery across regions, not high availability within a region.

Answer

Point-in-time recovery (PITR)

Answer

Read replicas in the same region

Answer

Cross-region replication

Question 15

You are planning capacity for a Cloud Spanner instance. Which TWO factors directly affect the number of nodes required?

Accepted Answer

Read throughput in queries per second (QPS). Options B and D are correct because Cloud Spanner node count is driven primarily by compute load, which is measured in terms of read QPS and write QPS (along with CPU utilization); higher read throughput (B) and higher write throughput (D) both increase the CPU work the instance must perform, so more nodes are needed to sustain them within latency targets. The number of users (A) is not a direct sizing factor—what matters is the workload those users generate in QPS and CPU, not the user count itself. The number of indexes (C) affects write amplification and storage, but it is not one of the two primary direct drivers of node count. Storage size in GB (E) is a separate consideration: Spanner requires enough nodes to hold the data, but storage capacity is not the throughput-based factor that directly determines node count in this capacity-planning context.

Answer

Number of users

Answer

Number of indexes

Answer

Storage size in GB