PCDE Practice Exam 68 — 20 Questions

Question 1

A Cloud SQL for PostgreSQL instance is running a reporting workload. The database engineer notices that the instance's memory is nearly exhausted and that the operating system is swapping heavily, causing query latency to spike. The instance has 8 GB of RAM, and pg_stat_activity shows many concurrent connections running large sorts and hash joins. Which action should the engineer take first to relieve the memory pressure?

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

Accepted Answer

Reduce the work_mem flag and add a connection pooler in front of the instance.. On a memory-constrained Cloud SQL for PostgreSQL instance, per-session work_mem allocations for sorts and hash joins accumulate across concurrent connections. Lowering work_mem forces larger operations to spill to disk, and a connection pooler bounds the number of concurrent backends, together reducing total memory demand. These changes address the root cause without resizing the instance.

Answer

Enable the pg_stat_statements extension and analyze the slowest queries.

Answer

Increase the max_connections flag so more sessions can run concurrently.

Answer

Increase the shared_buffers flag to 6 GB so more data is cached in memory.

Question 2

A healthcare company is designing a Cloud Spanner schema for patient records. The primary access pattern is to retrieve a patient's record by patient_id. They also need to query patients by last name and by date of birth for analytics. The patient_id is a random UUID. Which schema design should they use to optimize for these access patterns and avoid hotspots?

Accepted Answer

Use patient_id as the primary key and create secondary indexes on last_name and date_of_birth.. A random UUID primary key distributes writes evenly, avoiding hotspots. Secondary indexes on last_name and date_of_birth allow efficient queries for those columns. Spanner's secondary indexes are automatically maintained and can be used for lookups, making this the best design for the given access patterns.

Answer

Use a composite primary key of last_name and patient_id.

Answer

Use patient_id as the primary key and create a single interleaved table for analytics.

Answer

Use last_name as the primary key and create a secondary index on patient_id.

Question 3

A media company stores user activity events in Cloud Bigtable. The table uses a single column family and a row key that is the reverse of a timestamp. Read latency is low, but a nightly batch scan of the full table is extremely slow because it must read every row. They want to add a second column family that stores a pre-aggregated daily count per user, updated on each write, so the batch job can read only the aggregate column family. Which change should they make?

Accepted Answer

Create a separate aggregate table keyed by user ID and day, and increment the counter using an atomic increment operation in a single row mutation.. The batch job is slow because it scans the entire event table. Moving the aggregate to a separate table keyed by user and day allows the job to read only the small aggregate table. Atomic increments via ReadModifyWriteRow keep counts accurate under concurrent writers without a read-before-write, which would otherwise add latency and race conditions.

Answer

Add a second column family to the existing table and use a conditional mutation that reads the current value in the same row before writing.

Answer

Create a new column family and write the aggregate value using a read-modify-write on every event, accepting higher write latency.

Answer

Enable a Cloud Bigtable replication cluster in a second region and direct the batch job to the replica to offload reads.

Question 4

A media analytics company runs a Cloud SQL for PostgreSQL instance in us-central1. Their nightly batch job loads 800 GB of data into a reporting table, causing the instance's storage to grow rapidly. They want to reduce storage costs without losing the ability to query historical data for at least one year. They also need to keep the primary instance's storage from growing indefinitely. What should they do?

Accepted Answer

Export historical data to Cloud Storage and use BigQuery external tables to query it.. Moving historical data out of the operational database into Cloud Storage and querying it with BigQuery external tables separates storage cost from query capability. The primary instance no longer holds the full history, so its disk growth is bounded, while the exported data remains accessible for analytics. Automatic storage increase, read replicas, and PITR all leave the data on the primary or duplicate it, failing the cost and growth constraints.

Answer

Enable automatic storage increase and set a high storage limit on the primary instance.

Answer

Create a read replica in a cheaper region and run all queries against the replica.

Answer

Enable point-in-time recovery and increase the transaction log retention period.

Question 5

A startup is building a new application on Firestore in Native mode. They need to store user profiles and frequently query users by their `email` address. They also want to minimize read costs and ensure low-latency lookups. What is the most efficient schema design for this requirement?

Accepted Answer

Store each user profile as a document in a `users` collection, using the `email` as the document ID.. The most efficient design is to use the email as the document ID in a `users` collection. This allows direct document reads by email, which are fast and cost-effective. It also ensures uniqueness of email addresses without needing additional indexes or queries. This approach aligns with Firestore best practices for natural keys.

Answer

Use a subcollection under each user document, with the email as the subcollection ID.

Answer

Store each user profile as a document in a `users` collection with an auto-generated document ID, and create a composite index on `email`.

Answer

Store all user profiles in a single document as a map, with email as the key.

Question 6

A financial services company runs its transactional workload on Cloud Spanner and its analytics workload on Cloud Bigtable. They need to change the schema of a Cloud Spanner table that is replicated across multiple regions while ensuring that the Cloud Bigtable cluster remains available for reads and writes. The Spanner schema change must not cause any downtime for the transactional application. What should they do?

Accepted Answer

Use Cloud Spanner schema update with online DDL, and independently update the Cloud Bigtable schema as needed without taking the Bigtable cluster offline.. Cloud Spanner online DDL allows schema changes without downtime, even in multi‑region configurations. Cloud Bigtable schema modifications, such as adding column families, are also performed without taking the cluster offline. Performing each change independently on its respective database technology meets the no‑downtime requirement for both the transactional and analytics workloads.

Answer

Take the Cloud Spanner instance offline, apply the schema change, bring it back online, and then update Cloud Bigtable.

Answer

Create a new Cloud Spanner instance with the updated schema, migrate data using Dataflow, and then update Cloud Bigtable after the Spanner migration completes.

Answer

Use a Cloud Spanner backup and restore to a new instance with the updated schema, then point the application to the new instance, and update Cloud Bigtable separately.

Question 7

A gaming company is deploying Cloud Spanner for a new global leaderboard service. The database engineer must choose options that improve write availability and read latency without breaking strong consistency. The team plans to use a multi-region instance configuration. Which TWO design choices are appropriate? (Choose two.)

Accepted Answer

Place read-write replicas across multiple regions so that a quorum survives the loss of one region.. Spanner multi-region availability depends on having read-write replicas in enough regions to maintain a write quorum after a regional failure, while read-only replicas placed near users lower read latency without affecting that quorum. A single read-write replica cannot form a quorum, consistency cannot be disabled, and splitting into separate instances sacrifices the transactional guarantees the design relies on.

Answer

Configure the instance with a single read-write replica and many read-only replicas to reduce write latency.

Answer

Use eventual consistency reads by disabling strong reads at the client so all reads can be served from any replica.

Answer

Replace the multi-region instance with separate regional instances and synchronize them with application-level merges.

Question 8

A retail analytics team is building a BigQuery dataset to store point-of-sale transactions for ad-hoc analysis. Analysts frequently filter by `transaction_date` and `store_id`, and the data is append-only. The team wants to minimize query cost and maximize scan efficiency. Which design decision should the database engineer recommend?

Accepted Answer

Partition the table by `transaction_date` and cluster by `store_id`.. BigQuery charges by bytes scanned, so aligning the physical layout with query filters is the primary cost lever. Partitioning by `transaction_date` enables partition pruning for date-range filters, and clustering by `store_id` sorts data within partitions so store filters skip blocks. Together they minimize scanned bytes for the team's common query patterns on append-only transaction data.

Answer

Create the table with no partitioning and rely on BigQuery's automatic storage optimization.

Answer

Partition by `store_id` using integer range partitioning and cluster by `transaction_date`.

Answer

Create a materialized view over the base table and query the view instead.

Question 9

A healthcare company is designing a database solution on Google Cloud to store patient records. The data must be strongly consistent, support ACID transactions, and be highly available across multiple regions. The records include structured data with complex relationships, and the company expects moderate write throughput with heavy read traffic. They also need to run SQL queries for reporting. Which database solution should they choose?

Accepted Answer

Cloud Spanner with a multi-region configuration. Cloud Spanner is the only Google Cloud database that combines strong consistency, ACID transactions, multi-region high availability, and SQL support. It is designed for relational data with complex relationships and can handle moderate write throughput with heavy read traffic. Other options either lack multi-region strong consistency, SQL support, or ACID transactions.

Answer

Cloud SQL for PostgreSQL with cross-region read replicas

Answer

Cloud Bigtable with a multi-region configuration

Answer

Cloud Firestore in Datastore mode

Question 10

A retail company is migrating a 3 TB on-premises MongoDB 4.4 replica set to Firestore in Native mode. They need to preserve document structure and minimize transformation effort. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Map MongoDB collections to Firestore collections and convert ObjectId values to a supported Firestore type during the transfer.. The supported route is to use the Dataflow MongoDB to Firestore template, which streams documents from MongoDB and writes them into Firestore while preserving nested structures. Because Firestore lacks an ObjectId type, identifiers and other BSON-specific values must be mapped to supported types, typically strings. Together these actions deliver the migration with minimal custom transformation code.

Answer

Export the MongoDB data with mongoexport in JSON format and import the files into Firestore using the managed import feature.

Answer

Enable the MongoDB wire protocol compatibility mode on Firestore so existing drivers can write directly without changes.

Answer

Shard the destination Firestore database into multiple named databases to parallelize the 3 TB write load.

Question 11

A retail company runs a customer-facing web application on a single Cloud SQL for MySQL instance in the us-central1 region. The application requires automatic failover to a standby instance in a different zone within the same region to minimize downtime if the primary zone fails. Which configuration should the company implement?

Accepted Answer

Enable high availability by configuring the instance with a regional persistent disk and a standby instance in a different zone.. Cloud SQL high availability creates a standby instance in a different zone within the same region, using a regional persistent disk for synchronous replication. Failover is automatic and typically completes within 60 seconds. This is the only option that provides automatic failover within the same region, minimizing downtime without manual intervention.

Answer

Enable point-in-time recovery and schedule frequent backups to a multi-region Cloud Storage bucket.

Answer

Deploy the application across two zones and use a global load balancer to route traffic to the healthy zone.

Answer

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

Question 12

A gaming company runs Cloud SQL for PostgreSQL with a primary instance and several read replicas. During a regional event, the primary becomes unavailable and the team must promote a replica. They want to minimize data loss and ensure the promoted instance can accept writes with a consistent connection endpoint. What should they do first?

Accepted Answer

Identify the replica with the least replication lag and promote it, then update the application to use the promoted instance's connection name.. Read replicas in Cloud SQL replicate asynchronously, so the replica with the least lag holds the freshest data. Promoting it converts it into a standalone primary that accepts writes and minimizes data loss. Because promotion changes the instance role and connection endpoint, applications must be updated to use the promoted instance. Deleting the primary, restoring a backup, or assuming an HA standby exists do not achieve the same low-loss, prompt recovery.

Answer

Fail over to the high-availability standby and then reconfigure the read replicas to follow the standby.

Answer

Restore the most recent automated backup of the primary to a new instance and redirect the application.

Answer

Delete the primary instance so the replica with the most recent transaction log position is promoted automatically.

Question 13

A healthcare analytics team runs Cloud Bigtable to store time-series sensor data. They have a single cluster in us-east1 and want to add a second cluster in europe-west1 for lower-latency reads for European analysts while keeping a single logical table. The analysts run scan queries that read large contiguous key ranges. After enabling replication, the team notices that European read latency did not improve for these scans. What is the most likely cause?

Accepted Answer

The application is using a single-cluster routing policy or a client that pins requests to the original cluster, so reads are not being served locally in europe-west1.. With Cloud Bigtable replication, every cluster holds a full replica of the data, so local reads are possible from any cluster. Whether a read is actually served locally depends on the client's routing policy and app profile. A single-cluster or pinned route forces all traffic to one cluster, which explains why European analysts saw no latency improvement despite the new cluster.

Answer

The second cluster must be created in the same region as the first because Cloud Bigtable only supports intra-region replication for latency improvements.

Answer

Large contiguous key-range scans are always served from the cluster that owns the tablet, and tablet ownership cannot be influenced by routing.

Answer

Cloud Bigtable replication is asynchronous, so European reads always return data from us-east1 until a manual sync is triggered.

Question 14

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?

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 15

A financial services firm runs a Cloud SQL for PostgreSQL instance in us-central1. Their compliance team mandates that all data at rest be encrypted with a customer-managed encryption key (CMEK) that the firm can rotate and revoke independently. The database currently uses Google-managed encryption. They want to enable CMEK with minimal disruption. What should they do first?

Accepted Answer

Create a Cloud KMS key ring and crypto key in the same region as the Cloud SQL instance, grant the Cloud SQL service account the Cloud KMS CryptoKey Encrypter/Decrypter role, then reconfigure the instance to use the CMEK.. To enable CMEK on an existing Cloud SQL instance, you must first create a Cloud KMS key in the same region, grant the Cloud SQL service account the CryptoKey Encrypter/Decrypter role, and then reconfigure the instance to use that key. This approach avoids data migration and minimizes disruption.

Answer

Export the database to a Cloud Storage bucket encrypted with a CMEK, then import it into a new Cloud SQL instance configured with that key.

Answer

Enable CMEK by editing the instance and pasting the key resource name; Cloud SQL automatically grants the required IAM permissions to its service account.

Answer

Create a new Cloud SQL instance with CMEK enabled and use Cloud SQL replica promotion to switch over, then delete the old instance.

Question 16

A healthcare analytics team runs Cloud Bigtable with a single cluster in us-central1. They must add a second cluster in us-east1 so that reads can be served locally in both regions and the instance can continue serving traffic if one region becomes unavailable. Writes must be accepted in either region. Which replication configuration should the database engineer choose?

Accepted Answer

Configure the instance with multi-cluster routing and the multi-cluster routing policy so requests are distributed across the clusters in both regions.. Cloud Bigtable replication uses clusters in multiple regions that are all read-write, and the multi-cluster routing policy on an application profile distributes requests across those clusters, sending traffic to the nearest healthy cluster. This delivers local reads in both regions, spreads load, and lets requests fail over to the surviving cluster during a regional outage, while writes are accepted in either region as required.

Answer

Enable multi-cluster routing but set the application profile to use a single-cluster routing policy with the us-east1 cluster as the target.

Answer

Configure multi-cluster routing with a single-cluster routing policy so all requests go to the nearest cluster.

Answer

Configure the second cluster as a read-only replica and keep all writes directed to the us-central1 cluster only.

Question 17

A retail company is migrating its on-premises MySQL database to Google Cloud. The database is used for order processing and requires high availability with automatic failover. The company wants to minimize downtime during the migration and ensure data consistency. Which solution should they use?

Accepted Answer

Cloud SQL for MySQL with high availability configuration. Cloud SQL for MySQL with high availability is the most compatible and least disruptive option for migrating an on-premises MySQL database. It provides automatic failover and data consistency, and Database Migration Service can minimize downtime. The other options require significant schema and application changes, which would increase downtime and risk.

Answer

Cloud Bigtable

Answer

Firestore in Datastore mode

Answer

Cloud Spanner

Question 18

A media company stores user activity events in Cloud Bigtable and wants the data to survive the loss of an entire zone. The team wants the lowest-latency reads for users concentrated in a single metropolitan area and does not need the data replicated to other continents. They want a configuration that provides zone-level fault tolerance with minimal write latency. What should the database engineer recommend?

Accepted Answer

Create a multi-cluster instance with two clusters in different zones of the same region.. Zone-level fault tolerance in Cloud Bigtable comes from a multi-cluster instance whose clusters are placed in different zones. Keeping both clusters within one region preserves low write latency for a locally concentrated user base, while automatic replication ensures the data survives a zone failure. Single-cluster deployments and intercontinental replication do not satisfy the combined availability and latency requirements.

Answer

Create a multi-cluster instance with clusters in zones of two different continents.

Answer

Create a single-cluster instance and enable daily backups.

Answer

Create a single-cluster instance and configure a read replica in another zone.

Question 19

A media company uses Cloud Bigtable to store user interaction events. They need to query events for a specific user within the last 7 days, sorted by timestamp descending, and also retrieve the most recent event for each user. The row key currently is a reverse timestamp followed by user_id. What change should they make to optimize for these access patterns?

Accepted Answer

Change the row key to user_id followed by reverse timestamp.. The optimal row key for these access patterns is user_id followed by reverse timestamp. This groups events by user and orders them from newest to oldest, enabling efficient retrieval of recent events and the latest event. Bigtable does not support secondary indexes, so the row key must be designed to support the queries directly.

Answer

Use a composite row key of user_id and event_type, and store timestamp as a column.

Answer

Change the row key to user_id followed by timestamp, and reverse the timestamp in the application.

Answer

Keep the row key as reverse timestamp followed by user_id and add a secondary index on user_id.

Question 20

A startup is building a new mobile game that requires a globally distributed database with low-latency reads and writes for player profiles and game state. They expect rapid growth and want to minimize database administration. They also need the ability to run SQL queries for analytics. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner with multi-region configuration. Cloud Spanner offers a globally distributed, strongly consistent relational database with SQL support and minimal administration. It provides low-latency reads and writes across regions, making it ideal for a globally distributed mobile game. The other options either lack SQL support, are not designed for global low-latency writes, or require more operational overhead. Cloud Spanner meets all the startup's requirements.

Answer

Cloud SQL for PostgreSQL with read replicas in multiple regions

Answer

Cloud Bigtable with multi-cluster routing

Answer

Cloud Firestore in Native mode with multi-region locations