PCDE Practice Exam 62 — 20 Questions

Question 1

You are using Cloud Bigtable to store time-series data. The table has a single column family and uses a row key that is a combination of a sensor ID and a timestamp, with the sensor ID as a prefix. You notice that read latency for queries that fetch the latest data for a specific sensor is high. The queries use a row key range that starts with the sensor ID and ends with the sensor ID plus a high timestamp. What is the most effective way to improve read latency for these queries?

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

Accepted Answer

Change the row key to use a reversed timestamp as a prefix followed by the sensor ID.. The row key design with sensor ID as prefix and timestamp as suffix means that the latest data for a sensor is at the end of the row range. To fetch the latest data, the query must scan through all historical data for that sensor, causing high latency. Reversing the timestamp so that the latest timestamp has the smallest value (e.g., using Long.MAX_VALUE - timestamp) places the latest data at the beginning of the range, allowing the query to read only the first few rows. This dramatically reduces the number of rows scanned and improves latency.

Answer

Increase the number of nodes in the Bigtable cluster to improve throughput.

Answer

Enable a replication cluster in a different zone to distribute read load.

Answer

Use a filter to limit the number of versions returned per row.

Question 2

You are migrating an on-premises PostgreSQL 14 database to Cloud SQL for PostgreSQL using Database Migration Service. The database is 800 GB and the business requires under five minutes of write downtime during cutover. You have already completed the initial full dump and the destination is replicating continuously from the source. Which action should you take to complete the migration with the least downtime?

Accepted Answer

Stop application writes to the source, wait for the destination replica lag to reach zero, verify row counts, then promote the Cloud SQL instance.. Continuous migration keeps the destination caught up, but cutover still requires a controlled write freeze so no transactions are lost. Waiting for replication lag to reach zero confirms full synchronization, and promotion then flips the Cloud SQL instance to a standalone primary in a short window.

Answer

Increase the source database max_wal_senders and max_replication_slots, then keep the source writable permanently as a dual-write primary.

Answer

Run a second full dump with pg_dump while the destination is still replicating, then import it to overwrite the destination tables.

Answer

Promote the Cloud SQL instance immediately, then use logical replication slots to replay any transactions that the source received after promotion.

Question 3

A logistics company runs its shipment-tracking API on Cloud SQL for PostgreSQL in the us-central1 region. During a recent zonal outage, the primary instance became unavailable and the application experienced 12 minutes of downtime before an operator manually promoted a replica. The team wants the database to recover automatically from a zonal failure with minimal application changes. What should they do?

Accepted Answer

Enable high availability on the instance, which provisions a standby in a different zone and performs automatic failover.. Cloud SQL high availability creates a regional instance with a synchronous standby in a secondary zone and automatically fails over when the primary zone becomes unavailable. This matches the requirement for automatic recovery from a zonal failure with minimal application changes, whereas read replicas, vertical scaling, and exports do not provide automatic promotion or zero-touch failover.

Answer

Increase the instance's vCPU and memory so it can survive a zone failure through vertical scaling.

Answer

Schedule automated exports to Cloud Storage every five minutes and restore from the latest export after a failure.

Answer

Create a cross-region read replica and configure the application to write to it during a zonal outage.

Question 4

A healthcare company runs a Cloud SQL for MySQL instance with a read replica in another region for disaster recovery. They need to ensure that the read replica can be promoted to a standalone primary with minimal data loss in the event of a regional failure. They also want to monitor replication lag. What should they do?

Accepted Answer

Create a cross-region read replica, monitor replication lag using Cloud Monitoring metrics, and manually promote the replica if the primary fails.. Cross-region read replicas in Cloud SQL are intended for disaster recovery and require manual promotion. Monitoring replication lag is essential to estimate potential data loss. Automatic failover is only supported for same-region failover replicas, so the manual promotion approach is correct here.

Answer

Use Cloud SQL's built-in automatic failover to a cross-region replica by enabling the 'cross-region failover' option in the instance settings.

Answer

Set up a Cloud SQL replica in the same region as the primary and use it for failover, while using the cross-region replica only for read offloading.

Answer

Configure the read replica as a cross-region replica and enable automatic failover by setting the replica as a failover replica.

Question 5

A logistics company runs Cloud Spanner for a shipment tracking system. They need to run analytical queries that scan large portions of the database without affecting transactional latency. Which approach should they use?

Accepted Answer

Use a Data Boost processing unit with partitioned reads to run the analytics queries against the Spanner database.. Spanner Data Boost provides independent compute for read-only analytical and export workloads, so partitioned reads do not consume the instance's provisioned capacity. This isolates heavy scans from transactional traffic and preserves latency for the shipment tracking application. It avoids the staleness and complexity of exporting data to a separate analytical store.

Answer

Export the database to Cloud Storage and query the exported files with BigQuery external tables.

Answer

Create a read-only replica in a different region and route analytics queries to it using directed reads.

Answer

Increase the instance's processing units and enable automatic scaling with a higher maximum.

Question 6

A financial analytics firm uses Cloud BigQuery to store trade data in a table 'trades' with columns: 'trade_id' (STRING), 'trade_date' (DATE), 'symbol' (STRING), and 'details' (JSON). The 'details' column contains nested JSON with fields like 'price', 'quantity', and 'counterparty'. Analysts frequently run queries that filter on 'symbol' and extract 'price' from the 'details' JSON. The table is partitioned by 'trade_date' and clustered by 'symbol'. To optimize query performance and reduce costs, which schema change should you implement?

Accepted Answer

Add a new column 'price' of type FLOAT64 and populate it by extracting from the JSON 'details' column.. Extracting the frequently queried 'price' field from the JSON 'details' column into a dedicated FLOAT64 column allows BigQuery to scan only that column when queries filter on 'symbol' and retrieve 'price'. Because BigQuery is columnar, this reduces the amount of data read, lowering cost and improving performance. The existing partitioning by 'trade_date' and clustering by 'symbol' remain effective for pruning and filtering. Other options either do not reduce data scanned or add unnecessary complexity.

Answer

Create a materialized view that pre-computes the extracted 'price' for each trade.

Answer

Change the 'details' column from JSON to STRING and use JSON_EXTRACT functions in queries.

Answer

Partition the table by 'symbol' instead of 'trade_date' and cluster by 'trade_date'.

Question 7

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 8

A company is deploying a new application on Google Cloud that requires a relational database with automatic scaling, strong consistency, and the ability to handle millions of transactions per second. They want to minimize operational overhead and avoid managing servers. Which database service should they use?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that combines relational semantics, strong consistency, horizontal scalability, and automatic management. It can handle millions of transactions per second and scales seamlessly. Other options are either not relational, lack strong consistency at scale, or require significant operational effort to scale.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Bigtable

Answer

Firestore in Datastore mode

Question 9

A healthcare organization is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. They need to convert the schema and perform a one-time migration with minimal downtime. They have a 1 TB database and can tolerate up to 1 hour of downtime. Which combination of tools should they use?

Accepted Answer

Use Ora2Pg for schema conversion and Database Migration Service (DMS) for data migration.. Ora2Pg is specifically designed to convert Oracle schemas to PostgreSQL, handling tables, indexes, and some PL/SQL. DMS provides continuous data migration with minimal downtime. Together, they cover both schema conversion and data migration, meeting the 1-hour downtime requirement.

Answer

Use pg_dump and pg_restore for both schema and data migration.

Answer

Use Oracle GoldenGate for schema conversion and data migration.

Answer

Use Database Migration Service (DMS) for both schema conversion and data migration.

Question 10

A startup runs a Cloud SQL for PostgreSQL instance for its primary application and wants a managed, in-memory cache to reduce read load on the database. The cache must be reachable only from the application's Compute Engine instances in the same VPC network, with no public IP exposure. Which Google Cloud service should they use?

Accepted Answer

Cloud Memorystore for Redis with a private service access connection in the same VPC. Memorystore for Redis is the managed in-memory caching service on Google Cloud, and it can be attached to a VPC network using private service access so that only resources in that network can reach it. This keeps the cache private while delivering the low-latency reads that reduce load on the primary Cloud SQL for PostgreSQL instance.

Answer

Cloud Spanner with a read-only replica in the same region

Answer

Cloud SQL for PostgreSQL with a read replica in a second zone

Answer

Cloud Bigtable with an application profile for the application's Compute Engine instances

Question 11

A logistics company runs Cloud Spanner for shipment tracking. Their SLA requires 99.999% availability, and they must survive the loss of an entire Google Cloud region while continuing to serve reads and writes. Which Spanner configuration should the database engineer choose?

Accepted Answer

A multi-region instance configuration with read-write replicas in multiple regions.. Cloud Spanner achieves its highest availability with a multi-region instance, where read-write replicas live in several regions and a quorum-based protocol keeps the database writable after any single region is lost. A regional instance survives only zonal failures, and read-only replicas cannot accept writes, so only the multi-region configuration meets the regional-survival and 99.999% SLA requirements.

Answer

A regional instance plus a cross-region read-only replica for disaster recovery.

Answer

Two independent regional instances with application-level dual writes.

Answer

A regional instance with three read-write replicas across three zones in one region.

Question 12

You have a Cloud BigQuery dataset that is queried frequently. Users complain that queries scanning large tables are slow and expensive. You want to reduce the amount of data processed by common queries. What should you do?

Accepted Answer

Partition the table by a date column and cluster by commonly filtered columns.. Partitioning and clustering are the primary BigQuery features for reducing data scanned. Partitioning divides the table into segments based on a column (e.g., date), and clustering sorts data within partitions by specified columns. When queries filter on these columns, BigQuery can skip irrelevant partitions and blocks, minimizing bytes processed and improving performance.

Answer

Set a maximum bytes billed limit on the queries.

Answer

Use the cached results feature by enabling query caching.

Answer

Create a materialized view that pre-aggregates the data.

Question 13

A startup is deploying a new application on Google Cloud and needs a fully managed, globally distributed database that supports ACID transactions and SQL. They expect rapid growth and want to avoid operational overhead. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that combines global distribution, horizontal scalability, ACID transactions, and SQL support with a fully managed service. The other options lack either global distribution, SQL support, or ACID transactions at scale.

Answer

Firestore in Datastore mode

Answer

Cloud Bigtable

Answer

Cloud SQL for MySQL

Question 14

A retail company runs its order-processing system on Cloud SQL for PostgreSQL. The database team wants to offload read-heavy reporting queries to a separate instance without changing the application's connection string for writes. The reporting queries can tolerate a few seconds of replication lag. What should the team do?

Accepted Answer

Create a read replica of the Cloud SQL for PostgreSQL instance and point the reporting tool at the replica's IP address.. Read replicas in Cloud SQL for PostgreSQL are designed to offload read traffic by asynchronously replicating from the primary. Because the replication is asynchronous, reporting queries may see slightly stale data, which the scenario explicitly permits. The primary keeps its existing connection string for writes, and the reporting tool is simply directed to the replica's address, achieving isolation of read workloads.

Answer

Export the database to Cloud Storage and import it into a new Cloud SQL instance nightly.

Answer

Enable the Cloud SQL Auth Proxy on the primary and have the reporting tool connect through it.

Answer

Configure the primary instance with a higher tier and enable the high availability (HA) option.

Question 15

A media company uses Cloud Bigtable to store user interaction events. They have a single-cluster instance in us-central1. Due to business expansion, they need to serve reads and writes from both us-central1 and europe-west1 with low latency. They also need to ensure that data is replicated across regions for disaster recovery. Which configuration should they implement?

Accepted Answer

Convert the existing instance to a multi-cluster instance and add a cluster in europe-west1.. To serve reads and writes from both us-central1 and europe-west1 with low latency and ensure cross-region replication, you must use a multi-cluster Cloud Bigtable instance. Converting the existing instance to multi-cluster and adding a cluster in europe-west1 allows multi-cluster routing to direct requests to the nearest cluster and replicates data across regions for disaster recovery.

Answer

Set up a Cloud Bigtable read replica in europe-west1 using the gcloud bigtable instances update command.

Answer

Create a new Cloud Bigtable instance in europe-west1 and use Cloud Bigtable replication to synchronize data between the two instances.

Answer

Use Cloud Bigtable's change streams to replicate data to a Cloud Bigtable instance in europe-west1.

Question 16

A healthcare analytics team is designing a BigQuery solution for a dataset that ingests streaming vitals from hospital devices and must support both near-real-time dashboards and long-term historical cohort analysis. They need to control storage costs while keeping recent data fast to query. Which two design choices should they make? (Choose two.)

Accepted Answer

Use time-partitioned tables with partition expiration on the raw streaming table. Time-partitioned tables with partition expiration keep recent data fast to query while automatically removing old raw partitions to bound storage cost, and BigQuery long-term storage pricing automatically lowers the cost of retained historical data that is not modified for 90 days. Together these choices support near-real-time dashboards and long-term cohort analysis without manual data lifecycle management.

Answer

Load streaming data into Bigtable and query it with BigQuery federated queries for all dashboards

Answer

Configure the dataset default table expiration to 30 days and write all historical data into that same dataset

Answer

Store all historical data in a single unpartitioned table and rely on clustered columns only

Question 17

A startup is setting up a new Cloud SQL for MySQL instance for a development environment. They want to minimize costs and do not require high availability or read replicas. The instance will be used intermittently during business hours. Which configuration should they choose to optimize cost?

Accepted Answer

Use a single-zone instance with a shared-core machine type, set a fixed storage size, and schedule the instance to stop during non-business hours.. For a cost-sensitive development environment with intermittent usage, a single-zone instance with a shared-core machine type minimizes compute costs. Setting a fixed storage size prevents unexpected storage charges. Scheduling the instance to stop during non-business hours further reduces costs by eliminating charges when the instance is not needed. This meets the requirements without over-provisioning.

Answer

Use a single-zone instance with a shared-core machine type and enable automatic storage increase.

Answer

Use a single-zone instance with a dedicated-core machine type and disable automatic backups.

Answer

Use a regional instance with a shared-core machine type and enable high availability.

Question 18

A startup is building a new mobile application and needs a fully managed, serverless NoSQL database that automatically scales to handle unpredictable traffic spikes. The data model consists of user profiles and activity feeds, and the team wants to minimize operational overhead. Which Google Cloud database should they choose?

Accepted Answer

Cloud Firestore in Native mode. Cloud Firestore in Native mode is a serverless NoSQL document database that automatically scales and is designed for mobile and web applications. It provides real-time updates, offline support, and requires no provisioning, making it ideal for startups with unpredictable traffic. Other options either require provisioning, lack mobile-friendly features, or are not serverless.

Answer

Cloud Bigtable

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Question 19

A healthcare company runs AlloyDB for PostgreSQL for a patient records application in a single region. They want to increase read scalability and improve availability. They plan to add a read pool and configure high availability. Which TWO statements are correct about this AlloyDB deployment? (Choose two.)

Accepted Answer

A read pool in AlloyDB consists of read replicas that can serve read-only queries, and it can be scaled independently of the primary instance.. AlloyDB read pools provide independently scalable read replicas for read-only queries, and high availability creates an automatically failing-over standby in a different zone of the same region. Together they address read scalability and zone-level availability. Read pool nodes do not accept writes, HA is zone-level rather than regional, and read pools can be added online.

Answer

Adding a read pool requires recreating the primary instance and restoring from a backup, because AlloyDB does not support online addition of read replicas.

Answer

AlloyDB high availability replicates data to a standby in a different region by default, so a regional outage does not affect the primary.

Answer

Read pool nodes in AlloyDB can accept write transactions if the primary instance is unavailable, providing write availability during a zone failure.

Question 20

A media company is designing a Firestore in Native mode database for a mobile app that stores user-generated content. They need to ensure that queries are efficient and scalable. The app frequently queries for the most recent posts by a specific user, ordered by timestamp descending. They also need to retrieve a single post by its unique post ID. Which two actions should they take to optimize the Firestore data model? (Choose two.)

Accepted Answer

Use the post ID as the document ID in a root-level collection.. To optimize the Firestore data model, create a composite index on userId and timestamp descending to support the frequent query for recent posts by user. Additionally, use the post ID as the document ID in a root-level collection to enable fast direct lookups by post ID. These two actions ensure efficient and scalable queries.

Answer

Denormalize the user's most recent posts into the user document as an array.

Answer

Store each post as a subcollection under the user document.

Answer

Enable Firestore's automatic indexing for all fields to speed up queries.