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

Question 1

A media company uses Cloud Firestore in Native mode to store user profiles and activity feeds. They need to ensure that the database can handle a sudden spike in read traffic from a global user base while maintaining low latency. They also want to minimize costs. What should they do?

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

Accepted Answer

Choose a multi-region location for the Firestore database and implement client-side caching.. Firestore in Native mode offers multi-region replication for low-latency global reads. Client-side caching reduces read operations, which directly lowers costs and improves performance during traffic spikes. There is no manual read replica configuration or global query cache in Firestore. Datastore mode is not suitable for real-time client applications.

Answer

Enable multi-region replication for the Firestore database and use a global query cache.

Answer

Create a read replica in each region and configure the application to read from the nearest replica.

Answer

Use Firestore in Datastore mode to leverage automatic scaling and multi-region replication.

Question 2

A healthcare company stores patient records in Cloud Firestore in Native mode. They need to ensure that the database can be restored to any point in time within the last 30 days in case of accidental deletion or corruption. They also want to minimize the recovery time objective (RTO). Which feature should they enable?

Accepted Answer

Use Firestore managed backups combined with scheduled exports to Cloud Storage, and restore from the most recent backup.. Cloud Firestore does not offer point-in-time recovery. The closest alternative is to use managed backups, which can be scheduled, and optionally combine with exports to Cloud Storage for additional recovery points. This approach allows restoration to the most recent backup, minimizing RTO as much as possible within Firestore's capabilities. It does not provide continuous PITR but is the correct feature set for this scenario.

Answer

Enable Firestore's built-in point-in-time recovery (PITR) feature.

Answer

Enable point-in-time recovery (PITR) with a 30-day retention period.

Answer

Schedule daily managed backups with a 30-day retention period.

Question 3

A financial services firm runs a Cloud Spanner instance with a regional configuration in europe-west1. They need to add a second region, europe-west2, for disaster recovery while keeping the same database. They want to minimize application changes and ensure that reads and writes continue to work if europe-west1 fails. What should they do?

Accepted Answer

Reconfigure the existing instance to a multi-region configuration that includes europe-west1 and europe-west2.. Cloud Spanner instance configurations can be changed from regional to multi-region, which automatically replicates data across the chosen regions and provides transparent failover. Applications keep using the same instance and database, so no code changes are needed. Change streams, read-only replicas, and export/import either require custom replication, are not supported, or cause data loss, making them unsuitable for this disaster recovery scenario.

Answer

Create a new Cloud Spanner instance in europe-west2 and use Cloud Spanner change streams to replicate data.

Answer

Export the database to Cloud Storage and import it into a new instance in europe-west2 on a schedule.

Answer

Create a read-only replica in europe-west2 and promote it if europe-west1 fails.

Question 4

A healthcare company runs a Cloud Spanner instance with a regional configuration in us-central1. They need to ensure that the database can survive a zonal failure without any data loss and with minimal downtime. What should they do?

Accepted Answer

Verify that the instance is using a regional configuration, which replicates data across three zones in the region.. Cloud Spanner regional configurations are designed for high availability within a region by replicating data across three zones. This provides automatic failover and zero data loss (RPO=0) in case of a zonal failure. No additional steps are required as long as the instance is regional, which is the default and only option for regional configurations.

Answer

Ensure the instance is configured with at least three nodes to handle zonal failure.

Answer

Enable point-in-time recovery (PITR) on the database to protect against zonal failures.

Answer

Change the instance configuration to a multi-region configuration, such as nam3.

Question 5

A company is planning to deploy a Cloud SQL for PostgreSQL instance for a critical application. They need to ensure high availability and data durability. They want to configure the instance to automatically fail over to another zone in case of a zone outage, and they want to minimize data loss during failover. Which two actions should they take? (Choose two.)

Accepted Answer

Enable high availability (HA) configuration for the instance.. To achieve automatic failover and minimize data loss, the instance must be configured for high availability, which creates a standby in a different zone within the same region. This is done by setting the availability type to REGIONAL. Other options like cross-region synchronous replication or read replica promotion do not provide automatic failover with no data loss.

Answer

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

Answer

Enable point-in-time recovery (PITR) to allow recovery to a specific point before failover.

Answer

Configure the instance to use synchronous replication to a standby in another region.

Question 6

A startup is deploying a new Cloud SQL for MySQL instance for a development environment. They want to minimize cost and do not need high availability or read replicas. They also want to avoid unexpected charges from storage growth. What should they do?

Accepted Answer

Use a shared-core machine type and enable automatic storage increase.. For a development environment, a shared-core machine type provides the lowest compute cost, and automatic storage increase prevents the instance from becoming read-only when storage fills. High availability, dedicated cores, and disabling automatic storage increase all either raise cost or introduce downtime risk, making them poor choices for this scenario.

Answer

Enable high availability and set a small storage size.

Answer

Disable automatic storage increase and set a storage limit.

Answer

Use a dedicated-core machine type with a single vCPU and disable backups.

Question 7

A logistics company uses Firestore in Native mode to store shipment tracking data. The application performs a high volume of small read and write operations on individual documents. The database engineer needs to optimize the Firestore instance for cost and performance while ensuring strong consistency for all reads. Which action should the engineer take?

Accepted Answer

Design the data model to minimize the number of indexes and avoid hotspots by distributing writes across documents.. Firestore performance and cost are optimized by designing a data model that minimizes indexes and distributes writes to avoid hotspots. Unnecessary indexes increase write latency and storage costs, while hotspots can degrade performance. This approach maintains strong consistency and improves efficiency for high-volume small operations.

Answer

Enable Firestore's built-in caching layer by using the client SDK's offline persistence.

Answer

Increase the number of read replicas to improve read throughput and reduce latency.

Answer

Use Firestore in Datastore mode to reduce costs for the high volume of small operations.

Question 8

A financial services company runs a Cloud SQL for SQL Server instance in us-east1 that supports a trading dashboard used only during business hours (08:00–18:00 ET, Monday–Friday). The database is 1.5 TB and idle periods are predictable. The operations team wants to reduce compute cost without changing application code or affecting availability during trading hours. What should they do?

Accepted Answer

Enable the Cloud SQL instance's scheduled start and stop using the Cloud SQL Admin API or gcloud sql instances patch with --activation-policy ALWAYS/NEVER on a Cloud Scheduler cron.. Cloud SQL instances can be stopped via the activation policy and restarted through the same API, which halts compute billing while keeping storage, backups, and the connection identity intact. Pairing that control with Cloud Scheduler on a cron aligned to the 08:00–18:00 ET window removes idle compute cost while preserving availability during trading hours.

Answer

Lower the machine type to a shared-core tier during idle periods and raise it again before 08:00.

Answer

Create a read replica in us-east1 and direct all traffic to it during idle periods, then fail back to the primary at 08:00.

Answer

Export the database to Cloud Storage nightly and delete the instance, then re-import each morning before trading begins.

Question 9

A healthcare company is designing a Cloud Spanner database for a patient records system. They need to ensure strong consistency for reads and writes across regions, and they want to minimize write latency for users in both North America and Europe. They also need to support a high volume of small transactions. Which TWO factors should they consider when choosing the instance configuration? (Choose two.)

Accepted Answer

The type of quorum (read-write vs. read-only) used by the configuration.. Instance configuration determines the placement of read-write replicas, which directly affects write latency and fault tolerance. The quorum type indicates whether writes require acknowledgment from multiple regions, impacting latency. Together, these factors let you choose a configuration that provides strong consistency and low write latency for users in both North America and Europe. Column limits, storage class, and backup retention do not influence cross-region write performance.

Answer

The default backup schedule retention period for the configuration.

Answer

The maximum number of columns allowed per table in the configuration.

Answer

The storage class of the underlying persistent disks in each region.

Question 10

A company runs a Cloud SQL for SQL Server instance with a 1 TB database. They need to retain daily backups for 30 days and also keep transaction logs for point-in-time recovery. Which backup configuration should they use?

Accepted Answer

Enable automated backups with a retention period of 30 days and enable point-in-time recovery.. Cloud SQL automated backups support retention up to 365 days and are the standard way to meet daily backup retention. Point-in-time recovery retains transaction logs for a configurable period, allowing restoration to a specific timestamp. Together they satisfy both the 30-day daily backup requirement and the need for log-based recovery on SQL Server.

Answer

Enable automated backups with a retention period of 30 days and enable binary logging.

Answer

Enable automated backups with a retention period of 7 days and export daily backups to Cloud Storage with a 30-day lifecycle policy.

Answer

Create on-demand backups daily using a cron job and retain them for 30 days.

Question 11

A media analytics team stores clickstream events in Bigtable. A single row key is used for each session, and each session writes roughly 5,000 columns over its lifetime. During peak hours, write latency to this table spikes to several seconds and hotspotting appears on a few nodes. The team cannot change the application's write pattern. What should they do to reduce latency and distribute load?

Accepted Answer

Change the row key to prepend a hash or salt of the session ID so writes for a session spread across multiple rows and tablets.. Bigtable distributes data by row-key ranges, so a session that writes thousands of columns to a single row concentrates load on one tablet and node. Prepending a hash or salt to the row key breaks the session into many rows that map to different tablets, spreading writes across the cluster and reducing per-node pressure and latency.

Answer

Enable a column family garbage collection policy and reduce the number of columns per row.

Answer

Switch the table to a column family per day and set a TTL of 24 hours on each family.

Answer

Increase the number of nodes in the Bigtable cluster and enable SSD storage.

Question 12

A financial services company uses Cloud Spanner for transaction processing. They need to ensure zero downtime during a schema change that adds a new column with a default value to a large table. Which approach should the Database Engineer take?

Accepted Answer

Use ALTER TABLE to add the column; Spanner handles schema changes online.. Cloud Spanner supports online schema changes, including adding columns with default values, without requiring a maintenance window or causing downtime. Spanner applies schema updates asynchronously across all nodes while the database remains fully available for reads and writes, making it the ideal approach for zero-downtime requirements.

Answer

Create a new table with the new column, then use a fan-out pattern to write to both tables until the old table is deprecated.

Answer

Use an ALTER TABLE statement during a maintenance window.

Answer

Drop the table and recreate it with the new schema.

Question 13

A company runs a BigQuery data warehouse. They notice that query performance has degraded over time. The data is loaded daily from Cloud Storage using batch loads. Which action is most likely to improve query performance?

Accepted Answer

Partition and cluster tables based on common query filters.. Partitioning and clustering tables based on common query filters directly reduces the amount of data scanned per query by organizing data into physical segments. In BigQuery, this allows the query engine to prune entire partitions and clusters, significantly lowering I/O and improving performance without additional cost or complexity.

Answer

Increase the number of slots in the reservation.

Answer

Create materialized views for all frequent queries.

Answer

Migrate the data to Cloud SQL for better performance.

Question 14

A gaming company uses Memorystore for Redis to cache player session data. They need to ensure high availability with automatic failover in case of a zone failure. Which configuration should the Database Engineer choose?

Accepted Answer

Deploy a Standard tier Redis instance with replication across two zones.. Memorystore for Redis Standard tier provides cross-zone replication with automatic failover, ensuring high availability during a zone failure. The Standard tier uses a primary and replica instance in different zones, and if the primary fails, the replica is automatically promoted. This meets the requirement for automatic failover without manual intervention.

Answer

Deploy a Basic tier Redis instance with multiple read replicas.

Answer

Deploy a Memcached cluster with multiple nodes.

Answer

Deploy a Basic tier Redis instance in a single zone.

Question 15

A company is migrating their on-premises MySQL database to Cloud SQL. The database is 500 GB and they have a 1 Gbps network connection. They want to minimize downtime. Which THREE steps should they take?

Accepted Answer

Perform a test migration to validate the process.. Performing a test migration validates the entire process, including schema compatibility, data integrity, and application connectivity, before the actual cutover. This reduces the risk of unexpected failures during the production migration, which is critical for minimizing downtime. A test migration also helps estimate the time required for the final cutover and allows for tuning of the Database Migration Service settings.

Answer

Export the database to a SQL file and import after the cutover.

Answer

Schedule a maintenance window for the cutover.