PCDE Practice Exam 61 — 20 Questions

Question 1

A company is running a Cloud SQL for PostgreSQL instance that is used for both OLTP and reporting queries. The reporting queries are causing performance degradation for the OLTP workload. The database engineer wants to offload reporting queries to a read replica without affecting the primary instance. The reporting queries require near-real-time data. Which configuration should the engineer implement?

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

Accepted Answer

Create a read replica in the same region as the primary instance and configure the reporting application to connect to the read replica's IP address.. A same-region read replica provides low replication lag, ensuring near-real-time data for reporting. By directing reporting queries to the read replica, the primary instance's resources are freed for OLTP workloads. This improves overall performance without affecting data freshness significantly.

Answer

Enable high availability on the primary instance and direct reporting queries to the standby instance.

Answer

Create a read replica and enable cascading replication to further distribute the load.

Answer

Create a cross-region read replica and configure the reporting application to connect to it.

Question 2

A financial services firm runs a global trading platform on Cloud Spanner. They need to ensure that read-write transactions from different regions do not conflict, and that data is replicated across continents with low latency. They also require strong consistency for all reads. Which configuration should they use?

Accepted Answer

Deploy a multi-region Cloud Spanner instance with default leader and read-write replicas.. A multi-region Cloud Spanner instance with default leader and read-write replicas provides global replication, low-latency reads, and strong consistency. The leader handles writes, and read-write replicas can serve reads. This avoids write conflicts and meets the financial firm's requirements for a global trading platform.

Answer

Deploy multiple single-region Cloud Spanner instances and use application-level synchronization.

Answer

Use Cloud Spanner with a custom replication topology that includes only read-only replicas.

Answer

Deploy a single-region Cloud Spanner instance and use read replicas in other regions.

Question 3

A retail company runs a Cloud SQL for PostgreSQL instance that serves a customer-facing order processing application. The database must remain available for writes even if the primary zone fails, and the recovery time objective (RTO) is under 60 seconds. They also want to minimize cost while meeting these requirements. What should they do?

Accepted Answer

Enable high availability (HA) on the instance by configuring a regional instance with a standby in a different zone.. For Cloud SQL for PostgreSQL, high availability is achieved by configuring a regional instance with a standby in a different zone. This provides automatic failover within seconds, satisfying the RTO. Read replicas, backups, or changing the engine do not deliver the required automatic failover and low RTO for a write-intensive application.

Answer

Migrate the database to a Cloud SQL for MySQL instance with binary logging enabled.

Answer

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

Answer

Use a single-zone instance and rely on automated backups with point-in-time recovery (PITR).

Question 4

A startup is deploying a new internal tool on Google Cloud. The workload is a small PostgreSQL database with unpredictable, bursty traffic and they want to avoid managing servers or capacity planning. They want a fully managed service with automatic storage growth and minimal administrative overhead. Which Google Cloud service should they choose?

Accepted Answer

Cloud SQL for PostgreSQL. Cloud SQL for PostgreSQL delivers a managed PostgreSQL experience with automated patching, backups, and optional automatic storage growth, which fits a small database with unpredictable traffic and a preference for minimal operations. The other options either target different data models, require manual administration, or are not managed PostgreSQL services.

Answer

Bare Metal Solution for Oracle

Answer

Compute Engine with a self-managed PostgreSQL installation

Answer

Cloud Bigtable

Question 5

A healthcare company is migrating a 10 TB on-premises Microsoft SQL Server 2019 database to Cloud SQL for SQL Server. The database has a high transaction rate, and the company requires near-zero downtime. They plan to use Database Migration Service (DMS) for SQL Server. Which configuration must they implement to support continuous replication?

Accepted Answer

Enable Change Data Capture (CDC) on the source database and ensure the SQL Server Agent is running.. Database Migration Service for SQL Server relies on Change Data Capture (CDC) to replicate ongoing changes. CDC must be enabled on the source database, and the SQL Server Agent service must be running to execute the capture jobs. This setup allows DMS to read change tables and apply them to the destination, achieving near-zero downtime. Without CDC and the Agent, continuous replication cannot function.

Answer

Enable Change Tracking on the source database and configure DMS to use it.

Answer

Enable Always On Availability Groups and use the secondary replica as the DMS source.

Answer

Configure transactional replication from the source to the destination and point DMS to the distributor.

Question 6

A company is deploying a new application on Google Cloud and needs a fully managed, relational database that supports global transactions and horizontal scaling. They want to minimize operational overhead and ensure strong consistency. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that combines relational semantics, horizontal scaling, strong consistency, and global transactions with minimal operational overhead. Cloud SQL does not scale horizontally, Bigtable is NoSQL, and Firestore is a document database without relational support.

Answer

Firestore

Answer

Cloud Bigtable

Answer

Cloud SQL for PostgreSQL

Question 7

A company runs Cloud SQL for MySQL and wants to improve the availability and durability of its production database. They are evaluating options for handling zone failures and data corruption. Which TWO actions should they take? (Choose two.)

Accepted Answer

Configure automated backups with point-in-time recovery enabled to protect against data corruption.. High availability protects against zonal failures by maintaining a synchronous standby that is promoted automatically. Automated backups with point-in-time recovery protect against data corruption and logical errors by allowing restoration to a specific point in time. Cross-region replicas, larger storage, and binary logging alone do not satisfy both requirements.

Answer

Create a cross-region read replica and promote it during a zonal outage.

Answer

Increase the storage size of the instance to improve write durability.

Answer

Enable binary logging and rely on it as the sole recovery mechanism for the instance.

Question 8

A healthcare SaaS provider runs AlloyDB for PostgreSQL in a single region. They want to add read capacity for reporting workloads without affecting the primary instance's performance, and they need the read pool to stay within the same region for data residency. What should they do?

Accepted Answer

Add a read pool to the AlloyDB cluster in the same region and direct reporting queries to the read pool endpoint.. AlloyDB read pools provide additional read-only compute that shares the cluster's storage, so reporting queries run without impacting the primary and without replication lag. Placing the read pool in the same region satisfies data residency. Vertical scaling and cross-region replicas do not isolate reporting workloads while meeting the residency requirement.

Answer

Increase the primary instance's vCPU count and memory to handle both transactional and reporting queries.

Answer

Create a cross-region read replica and point the reporting tools at it.

Answer

Configure a Cloud SQL federated query from the AlloyDB primary to a separate reporting database.

Question 9

A small e-commerce company is building a new application on Google Cloud. They need a fully managed relational database that supports MySQL and provides automatic backups, patch management, and replication. They want to minimize administrative effort. Which service should they use?

Accepted Answer

Cloud SQL for MySQL. Cloud SQL for MySQL is the best fit because it is a fully managed MySQL-compatible relational database with automatic backups, patching, and replication. It minimizes administrative effort while providing the relational features needed for an e-commerce application. The other options are either NoSQL, not MySQL-compatible, or overkill for a small business.

Answer

Firestore in Native mode

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 10

A healthcare company uses Cloud Bigtable to store patient monitoring data. They need to ensure that the data is highly available across multiple zones and can survive a zone failure without data loss. They also want to minimize write latency for their application, which is running in the same region. Which Cloud Bigtable configuration should they use?

Accepted Answer

Create a multi-cluster instance with clusters in different zones within the same region and configure multi-cluster routing.. A multi-cluster instance with clusters in different zones within the same region ensures high availability and zone-failure resilience. Multi-cluster routing directs requests to the nearest cluster, minimizing write latency for the application in the same region. This configuration is the standard way to achieve high availability in Cloud Bigtable while maintaining low latency.

Answer

Create a single-cluster instance and use Cloud Storage to replicate data to another zone.

Answer

Create a multi-cluster instance with clusters in different regions and use a global load balancer to route traffic.

Answer

Create a single-cluster instance in the same region and enable automatic failover.

Question 11

A startup runs Cloud SQL for MySQL and wants a low-cost way to survive a zonal failure in the primary zone without manual intervention, while keeping a single primary for writes. Which configuration should they choose?

Accepted Answer

Enable Cloud SQL high availability by configuring a standby instance in a different zone of the same region.. Cloud SQL high availability maintains a synchronous standby in a second zone, so a zonal outage causes an automatic failover with no data loss and no manual intervention. Writes continue through a single primary, which matches the scenario. Backups and replicas serve different purposes and do not provide automatic zonal failover.

Answer

Deploy three Cloud SQL instances and use an external load balancer to distribute writes across all three.

Answer

Enable point-in-time recovery and rely on automated backups to restore the instance after a zonal failure.

Answer

Create a cross-region read replica and promote it manually if the primary zone becomes unavailable.

Question 12

A healthcare SaaS provider runs AlloyDB for PostgreSQL in europe-west1. They need a highly available primary that automatically fails over if the primary zone becomes unavailable, and they want to minimize recovery time. Which AlloyDB feature should they configure?

Accepted Answer

AlloyDB high availability with a secondary instance in a different zone of the same region.. AlloyDB high availability provisions a secondary instance in a different zone that stays synchronized with the primary. When the primary zone fails, AlloyDB automatically promotes the secondary and updates the instance endpoint, giving a short recovery time. This is the intended mechanism for zone-level automatic failover and matches the healthcare provider's availability requirement.

Answer

Continuous backups with point-in-time recovery enabled on the primary instance.

Answer

A read pool with two nodes in the same region.

Answer

A cross-region replica in europe-west4 for disaster recovery.

Question 13

You are designing a Cloud Spanner schema for a social networking application. The main entity is a 'User' table. Each user has a unique username, and you need to efficiently retrieve a user by username. Additionally, you want to interleave a 'Post' table with 'User' to co-locate posts with their author for efficient joins. Which primary key design for the 'User' table best meets these requirements and optimizes for Cloud Spanner's architecture?

Accepted Answer

Use the username as the primary key.. The username as the primary key directly supports efficient retrieval by username and enables interleaving of 'Post' with 'User' using the same key, co-locating related data for efficient joins. While random UUIDs distribute writes, they require a secondary index for username lookups. Composite keys add unnecessary complexity. Monotonically increasing integers cause hotspots. Thus, using the username is the best balance for the given requirements.

Answer

Use a composite primary key of (username, user_id).

Answer

Use a monotonically increasing integer as the primary key.

Answer

Use a randomly generated UUID as the primary key.

Question 14

A startup is migrating a small 50 GB MySQL 8.0 database from a virtual machine in Google Compute Engine to Cloud SQL for MySQL. The database can tolerate a brief downtime of up to 30 minutes. They want a simple, cost-effective migration with minimal setup. Which method should they use?

Accepted Answer

Perform a mysqldump export, transfer the dump file to Cloud Storage, and import it into Cloud SQL.. For a small database with acceptable downtime, mysqldump combined with Cloud Storage import is the simplest and most cost-effective approach. It requires no continuous replication setup and can be completed within the allowed 30-minute window. DMS and replication methods add unnecessary complexity for this scenario.

Answer

Use Database Migration Service (DMS) for MySQL with continuous replication.

Answer

Use the Cloud SQL import feature to directly import from the source database over a private IP.

Answer

Set up a replica from the Compute Engine VM to Cloud SQL and promote it during cutover.

Question 15

You are designing a Cloud Spanner schema for an IoT telemetry platform. The main table stores sensor readings with columns: device_id (STRING), event_timestamp (TIMESTAMP), reading_value (FLOAT64), and metadata (JSON). Queries almost always filter by device_id and a time range, and you want to minimize storage and improve scan efficiency. The primary key must support these queries while avoiding hotspots. What should you do?

Accepted Answer

Use a composite primary key with device_id as the first column and event_timestamp as the second column.. The correct design uses a composite primary key with device_id first and event_timestamp second. This ordering supports the dominant query pattern—filtering by device_id and a time range—by allowing efficient range scans. It also avoids hotspots because device_id is high-cardinality and not monotonically increasing, so writes spread across the key space. Secondary indexes or synthetic keys add overhead and are unnecessary here.

Answer

Use a synthetic primary key generated by a bit-reversed sequence, and create a secondary index on device_id and event_timestamp.

Answer

Use event_timestamp as the sole primary key and rely on Cloud Spanner's interleaving to group data by device_id.

Answer

Use a composite primary key with event_timestamp as the first column and device_id as the second column.

Question 16

You are responsible for a Cloud Bigtable cluster that handles a high volume of random reads and writes. Users report increased latency during peak hours. You observe that the cluster's CPU utilization is around 85%, and the disk utilization is low. You want to optimize performance without over-provisioning. What should you do first?

Accepted Answer

Review the row key design for hotspots.. Before scaling the cluster, you should analyze the row key design. Hotspots from sequential keys cause uneven load, driving up CPU on specific nodes. Redesigning the key to distribute reads evenly can alleviate CPU pressure and reduce latency without additional cost. This is a fundamental optimization step for Bigtable performance.

Answer

Increase the cluster's storage capacity.

Answer

Increase the number of nodes in the cluster.

Answer

Enable SSD storage for the cluster.

Question 17

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 18

A startup is using Firestore in Native mode for a mobile application. They need to ensure that the database can scale automatically to handle sudden spikes in traffic without manual intervention. They also want to minimize costs during periods of low usage. Which Firestore feature should they rely on?

Accepted Answer

Automatic scaling. Firestore in Native mode is a serverless, fully managed database that automatically scales to handle traffic spikes and scales down during low usage. This automatic scaling ensures that the application can handle sudden increases in load without manual intervention, and the pay-as-you-go pricing model minimizes costs when usage is low. Other options like multi-region replication or manual indexing do not provide these benefits.

Answer

Multi-region replication

Answer

Manual index configuration

Answer

Datastore mode

Question 19

A media company uses Firestore in Native mode for its mobile app's user profiles. A nightly batch job must export all documents to Cloud Storage for archival and then import them into BigQuery for analytics. The export must be consistent as of a specific point in time. Which managed export mechanism should the team use?

Accepted Answer

Use the Firestore managed export feature to export all documents to a Cloud Storage bucket.. Firestore's managed export is the only option that produces a consistent, point-in-time snapshot of all documents and writes it to Cloud Storage in a format suitable for later import into BigQuery. Client-side scripts lack snapshot consistency, BigQuery cannot natively query Firestore, and Datastore mode tooling does not apply to a Native mode database.

Answer

Run a client-side script that queries all documents and writes them to a Cloud Storage bucket as JSON.

Answer

Enable Firestore's built-in BigQuery export and let BigQuery query the Firestore database directly.

Answer

Use Datastore mode's backup schedule to export the database to Cloud Storage on a nightly basis.

Question 20

A financial services firm stores trade records in Cloud Bigtable. Compliance requires that every write be durably replicated to a second cluster in a different region, and that the application continue accepting writes if the primary region fails. The application uses a single Bigtable client configured with a project, instance, and table ID. Which configuration should you implement?

Accepted Answer

Create a multi-cluster routing instance with clusters in both regions and set the application profile to use multi-cluster routing.. Bigtable replication is configured by creating a multi-cluster instance and adding clusters in each region. The application profile determines how clients route requests: multi-cluster routing spreads traffic and fails over automatically, while single-cluster routing pins traffic. To meet both replication and write availability with an unchanged client configuration, the multi-cluster routing profile on a multi-cluster instance is required.

Answer

Create a multi-cluster routing instance with clusters in both regions and set the application profile to use single-cluster routing.

Answer

Create two separate Bigtable instances, one per region, and have the application write to both using a dual-write pattern.

Answer

Create a single-cluster instance in the primary region and enable a read replica in the secondary region for disaster recovery.