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

Question 1

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?

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

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 2

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?

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

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 3

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 4

A retail company uses Bigtable for its product catalog. They need to store a large number of columns per row, but many columns are sparse (i.e., not all rows have values for all columns). They want to optimize storage and read performance. Which data modeling approach should they use?

Accepted Answer

Use a single column family and store only the columns that have values for each row, leveraging Bigtable's sparse nature.. Bigtable's sparse storage model means that only cells with data are stored; missing columns do not consume space. Therefore, the optimal approach is to use a single column family and write only the columns that have values for each row. This reduces storage overhead and improves read performance by avoiding unnecessary data retrieval.

Answer

Use a column family for each row and store all columns as qualifiers within that family.

Answer

Store each column as a separate column family to improve read performance.

Answer

Create a separate table for each column and join them at read time using row keys.

Question 5

A company runs a mission-critical Cloud SQL for MySQL instance with high availability enabled. The primary instance is in us-central1, and the standby is in us-central1 as well. The company wants to ensure that the database remains available even if an entire Google Cloud zone fails. What should the database engineer do?

Accepted Answer

Enable high availability, which automatically provisions a standby in a different zone.. Cloud SQL high availability creates a standby in a different zone within the same region and provides automatic failover if the primary zone fails. This ensures zone-level resilience with no data loss. Cross-region replicas are asynchronous and require manual promotion, so they do not meet the automatic failover requirement.

Answer

Configure the instance to use a cross-region read replica and promote it during a zone failure.

Answer

Create a read replica in a different region and configure it as a failover target.

Answer

Use a multi-region Cloud SQL instance configuration.

Question 6

A startup is building a mobile gaming application that needs a fully managed, serverless NoSQL database with real-time synchronization and offline support for mobile clients. The database must automatically scale to handle unpredictable traffic and integrate with Firebase. Which service should they use?

Accepted Answer

Cloud Firestore. Cloud Firestore is the only Google Cloud database that offers serverless NoSQL with real-time synchronization and offline support for mobile clients, along with Firebase integration. It automatically scales and is ideal for unpredictable traffic. Other options lack these mobile-centric features or require additional components.

Answer

Cloud Spanner

Answer

Cloud SQL for MySQL

Answer

Cloud Bigtable

Question 7

A company wants to host a MongoDB-compatible workload on Google Cloud. The application uses MongoDB wire protocol and requires a managed service with automatic backups and scaling. The team prefers a fully managed offering rather than running MongoDB on Compute Engine. Which Google Cloud service should they choose?

Accepted Answer

MongoDB Atlas on Google Cloud. MongoDB Atlas is a fully managed MongoDB service that runs on Google Cloud and supports the MongoDB wire protocol. It provides automated backups, scaling, and monitoring, so the existing application can connect without code changes. The other options are managed services but use different data models and protocols, which would require rewriting the application.

Answer

Bigtable

Answer

Firestore in Native mode

Answer

Cloud SQL for PostgreSQL

Question 8

A database engineer is setting up a new Cloud SQL for PostgreSQL instance. The security team requires that all connections to the instance be encrypted and authenticated using IAM. The engineer wants to minimize the number of firewall rules and avoid managing IP allowlists. Which connection method should the engineer use?

Accepted Answer

Use the Cloud SQL Auth Proxy to connect from the application.. The Cloud SQL Auth Proxy provides secure, encrypted connections with IAM-based authentication and eliminates the need for IP allowlists or firewall rules. It is the recommended method for connecting applications securely. Other methods either require IP allowlisting or do not integrate IAM authentication for database users.

Answer

Assign a private IP address to the instance and connect from a Compute Engine VM in the same VPC.

Answer

Configure the instance with a public IP and use SSL/TLS certificates for encryption.

Answer

Enable the instance's public IP and add authorized networks for the application's CIDR ranges.

Question 9

A company is deploying a new Cloud SQL for MySQL instance for an e-commerce application. The database engineer must ensure that the instance can support up to 10,000 transactions per second and that the data is protected against a zonal failure. The application requires low-latency reads and writes. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Configure the instance with high availability (HA) to provide a standby in a different zone.. To handle 10,000 transactions per second, the instance must be sized with a machine tier that provides sufficient vCPUs and memory. To protect against a zonal failure, high availability must be enabled, which creates a standby in a different zone and provides automatic failover. Together, these two actions meet the performance and availability requirements.

Answer

Create a read replica in a different region to distribute read traffic.

Answer

Enable point-in-time recovery (PITR) to protect against data corruption.

Answer

Configure the instance to use SSD storage with automatic storage increase.

Question 10

A company is planning to migrate an on-premises PostgreSQL database to Cloud SQL. The database is 5 TB and has a high write workload. They need to minimize downtime during the migration and ensure that the target instance can handle the write throughput. The database engineer must choose the appropriate migration strategy and instance configuration. (Choose two.)

Accepted Answer

Configure the Cloud SQL instance with a machine type that has sufficient vCPUs and memory for the write workload.. Database Migration Service with continuous migration minimizes downtime by replicating ongoing changes, and it is the managed solution for PostgreSQL migrations. Sizing the Cloud SQL instance with sufficient compute and memory ensures it can handle the high write workload. Manual replication and HA do not address the core requirements of minimal downtime and write throughput.

Answer

Use pg_dump and pg_restore for the initial load and then set up logical replication.

Answer

Enable high availability on the Cloud SQL instance to improve write performance.

Answer

Use a read replica to offload writes from the primary instance.

Question 11

A media company uses Firestore in Native mode for its mobile app. They need to ensure that the database can handle sudden spikes in read traffic without affecting performance. They also want to minimize costs. What should they do?

Accepted Answer

Use Firestore's automatic scaling, which handles read traffic spikes by distributing load across multiple servers.. Firestore in Native mode is a serverless, automatically scaling database that handles read traffic spikes without manual provisioning. It distributes load across multiple servers and charges based on operations, making it cost-effective for variable workloads. Read replicas and mode changes are not applicable or beneficial here.

Answer

Provision additional read replicas for the Firestore database to increase read throughput.

Answer

Migrate to Cloud Bigtable to handle high read throughput more cost-effectively.

Answer

Enable Firestore in Datastore mode to benefit from automatic scaling.

Question 12

A retail company runs a Bigtable instance with a single cluster in us-east1. They want to improve read performance for users in Asia without changing their application's row key design. They also want to ensure that if the us-east1 cluster becomes unavailable, the data remains accessible. What should they do?

Accepted Answer

Add a cluster in an Asian region to the same instance and configure application profiles for the Asian users.. Bigtable replication works by adding clusters to the same instance; data is automatically replicated across clusters. An Asian cluster reduces read latency for local users, and application profiles route traffic appropriately. If one cluster fails, others continue serving. Creating a separate instance, using a nonexistent read replica API, or changing row keys do not satisfy the latency and availability needs without major drawbacks.

Answer

Create a new Bigtable instance in an Asian region and use a batch process to copy data nightly.

Answer

Enable a read replica in an Asian region using the Bigtable replication API.

Answer

Change the row key to include a region prefix and use a multi-cluster routing policy.

Question 13

A team is planning a Cloud Bigtable instance for a high-throughput analytics workload. They need to ensure the cluster can handle the expected read and write load. Which two factors should they consider when sizing the cluster? (Choose two.)

Accepted Answer

The number of nodes, because each node provides a fixed amount of read and write throughput.. Bigtable throughput scales with node count, and each node provides a fixed amount of read and write capacity. Row key design determines how evenly data and traffic are distributed across nodes, preventing hotspots that would otherwise waste capacity. Together, node count and row key design are the primary factors for sizing a cluster to handle a given load.

Answer

The number of column families, because each column family is stored on a separate node.

Answer

The number of tables in the instance, because each table requires a dedicated node.

Answer

The choice of SSD or HDD storage, because it changes the maximum number of nodes allowed.

Question 14

A company runs a Cloud SQL for MySQL instance in a project with a strict security policy. They need to encrypt the database with a customer-managed encryption key (CMEK) and rotate the key annually. Which action should they take?

Accepted Answer

Create a Cloud KMS key ring and key, grant the Cloud SQL service account the cloudkms.cryptoKeyEncrypterDecrypter role, and configure the instance to use the key.. To use CMEK with Cloud SQL, you create a Cloud KMS key, grant the Cloud SQL service account the cryptoKeyEncrypterDecrypter role, and configure the instance to use that key. This gives the instance permission to encrypt and decrypt data with the customer-managed key. Annual rotation is achieved by rotating the key in Cloud KMS, which creates a new version that Cloud SQL uses.

Answer

Create a Cloud KMS key and grant the project owner the cloudkms.cryptoKeyEncrypterDecrypter role, then enable CMEK on the instance.

Answer

Use a Cloud External Key Manager (EKM) key stored in a third-party HSM and reference it directly in the instance configuration.

Answer

Enable default encryption at rest, which automatically uses a customer-managed key from the project.

Question 15

A team is designing a new Cloud Spanner schema for an order management system. They want to avoid hotspotting during heavy insert traffic and keep interleaved child rows physically close to their parent for efficient reads. Which two schema design choices should they make? (Choose two.)

Accepted Answer

Use a UUID or a bit-reversed sequence as the leading component of the primary key for the orders table.. Randomizing the leading primary key component with UUIDs or bit-reversed sequences spreads insert load across splits, and interleaving order items in the parent orders table colocates related rows for efficient parent-child reads. Together these choices address both hotspotting on inserts and physical locality for child rows.

Answer

Store order items as a repeated STRUCT column inside the orders table instead of a child table.

Answer

Add a secondary index on the order timestamp column and query through that index for all reads.

Answer

Use a monotonically increasing integer as the first part of the primary key for the orders table.