PCDE Practice Test 44 — 15 Questions

Question 1

A team is migrating a MongoDB application to Firestore. The data model includes embedded documents and references between collections. Which approach should they follow to maintain similar query performance?

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

Accepted Answer

Use subcollections for embedded data and references for relationships. Firestore's data model supports subcollections for hierarchical embedded data and document references for relationships, which mirrors MongoDB's embedded documents and references. Using subcollections for embedded data preserves the locality of related data, while references allow normalized relationships with efficient lookups. This approach maintains similar query performance by minimizing the need for expensive joins or full collection scans.

Answer

Flatten all data into a single document per entity

Answer

Replicate data across multiple collections to avoid reads

Answer

Use a single collection for all documents and rely on client-side joins

Question 2

A company wants to optimize costs for their Compute Engine instances. They have a mix of workloads: some are fault-tolerant batch jobs, and others are stateful services requiring consistent uptime. Which two strategies should they use to reduce costs? (Choose two.)

Accepted Answer

Use committed use discounts for the stateful services. Option A is correct because Committed Use Discounts (CUDs) provide significant discounts (up to 57% for 1-year and 70% for 3-year commitments) in exchange for committing to a consistent level of vCPU and memory usage in a specific region, which aligns perfectly with stateful services that require consistent, predictable uptime. Option C is correct because preemptible VMs (now also offered as Spot VMs) cost up to 60-91% less than standard instances and are ideal for fault-tolerant batch jobs that can tolerate interruptions, since preemptible instances can be terminated at any time with a 30-second notice. Option B is not correct because node auto-provisioning in GKE is a cluster scaling feature that adds or removes nodes based on workload demands, but it does not inherently reduce costs for Compute Engine instances and is not a cost-optimization strategy for the described mix of workloads. Option D is not correct because consolidating workloads onto larger machines does not necessarily reduce total cost and can reduce fault tolerance and flexibility, especially for stateful services requiring consistent uptime. Option E is not correct because CPU always on keeps instances running and billed even when idle, which increases rather than reduces costs.

Answer

Enable node auto-provisioning in GKE

Answer

Replace all instances with larger machines to reduce total instance count

Answer

Use CPU always on for all instances

Question 3

A data analyst needs to run ad-hoc SQL queries on a large dataset stored in Google Cloud Storage (CSV files). They do not want to manage any infrastructure. Which service should they use?

Accepted Answer

BigQuery. BigQuery is a fully managed, serverless data warehouse that can query external data directly from Google Cloud Storage using external tables or the BigQuery Storage API, with no infrastructure to manage. It supports standard SQL for ad-hoc analysis and scales automatically to handle large datasets. This makes it the correct choice for a data analyst who wants to query CSV files in GCS without provisioning or managing servers.

Answer

Dataproc

Answer

Cloud Spanner

Answer

Cloud SQL

Question 4

A company runs a critical application on Cloud SQL for PostgreSQL with a primary instance in us-central1 and a cross-region read replica in us-west1 for disaster recovery. The database engineer is responsible for ensuring that in the event of a regional outage in us-central1, the application can continue with minimal data loss and within 15 minutes of downtime. The application writes about 1000 transactions per second. The current setup has automated backups enabled with point-in-time recovery (7-day retention) and the cross-region replica is configured with asynchronous replication. Which action should the database engineer take to meet the recovery objectives?

Accepted Answer

Promote the cross-region read replica to a new primary and redirect application traffic.. Promoting the cross-region read replica to a primary is the correct DR action for Cloud SQL for PostgreSQL. Promotion converts the asynchronous replica into a standalone primary in us-west1, allowing the application to resume writes there. With asynchronous replication, some data loss (RPO) is possible, but the promotion itself can be completed in minutes, satisfying the 15-minute RTO.

Answer

Change the cross-region replica to synchronous replication and enable automatic failover.

Answer

Create a new instance from the latest backup in us-west1 and redirect traffic.

Answer

Increase automated backup frequency to every hour and ensure binary logging is enabled.

Question 5

An organization is planning a database migration from Oracle to PostgreSQL on Cloud SQL. They have a large number of stored procedures that use Oracle-specific PL/SQL features. Which tool should they use to automate the schema conversion, including conversion of PL/SQL to PL/pgSQL?

Accepted Answer

Ora2Pg. Ora2Pg is an open-source tool specifically designed to automate the migration of Oracle databases to PostgreSQL, including the conversion of Oracle-specific PL/SQL stored procedures into PL/pgSQL. It handles schema objects, data types, and procedural code, making it the correct choice for this scenario where PL/SQL conversion is a key requirement.

Answer

pglogical

Answer

pg_dump

Answer

Database Migration Service (DMS)

Question 6

A database administrator notices that a Cloud SQL for MySQL instance is experiencing high CPU usage during peak hours. The instance has 4 vCPUs and 15 GB of memory. The query patterns are mostly read-intensive with occasional writes. Which action should the DBA take first to address the high CPU usage?

Accepted Answer

Analyze slow query log and optimize queries. High CPU usage in a read-intensive Cloud SQL for MySQL instance is most often caused by inefficient queries that consume excessive CPU cycles. Analyzing the slow query log allows the DBA to identify and optimize these queries, addressing the root cause directly. Increasing resources or changing configuration without understanding the workload can mask the problem and lead to unnecessary costs.

Answer

Increase the max_connections flag to allow more concurrent connections

Answer

Enable read pool to offload read queries

Answer

Increase the machine type to 8 vCPUs

Question 7

What is the maximum backup retention period for Cloud Spanner backups?

Accepted Answer

365 days. Cloud Spanner backups have a maximum retention period of 365 days. This limit is enforced by the service, meaning you cannot configure a backup to be retained longer than one year. The retention period is set when the backup is created and can be extended up to this maximum, but not beyond.

Answer

730 days

Answer

90 days

Answer

30 days

Question 8

A Cloud Database Engineer is designing a schema for an e-commerce application on Cloud Spanner. The application requires high read throughput for product queries by category and price range, and must support global scale with strong consistency. The team is considering primary key design and interleaved tables. Which TWO design considerations should the engineer apply? (Choose TWO.)

Accepted Answer

Use a primary key that starts with the category column to colocate product data for efficient queries by category.. Option D is correct because in Cloud Spanner the primary key prefix determines data locality, so starting the key with the category column colocates products of the same category in contiguous key ranges, enabling efficient range scans for category-based product queries. Option E is correct because interleaved tables physically co-locate child rows (product variants) with their parent product row, and since variants are always accessed with the parent, this eliminates expensive joins and reduces latency for those reads. Option A is wrong because secondary indexes in Spanner are themselves keyed by the base table's primary key, so primary key design cannot be ignored; poor key design leads to hotspots even with indexes. Option B is wrong because a leading timestamp key creates monotonically increasing keys that concentrate writes on the last split, causing hotspots rather than efficient partitioning. Option C is wrong because interleaving unrelated entities that are not accessed together adds write contention and storage coupling without query benefit, and interleaving is not a substitute for joins in general.

Answer

Define secondary indexes on price and category columns to support range queries without considering the primary key design.

Answer

Use a timestamp as the first part of the primary key to enable time-based partitioning and efficient range scans.

Answer

Define interleaved tables for all related entities, even if they are not always accessed together, to reduce joins.

Question 9

A multinational retailer is designing a Google Cloud database tier for a new order-capture service. The service must survive the loss of an entire Google Cloud region with no data loss and automatic failover, while still enforcing ACID transactions across order and inventory tables. The team wants to minimize operational overhead and avoid manual database administration. Which database service should they choose?

Accepted Answer

Cloud Spanner with a multi-region instance configuration. The requirement for surviving a full region loss with zero data loss and automatic failover, combined with ACID transactions across relational tables, points to a globally distributed, strongly consistent, fully managed relational database. Cloud Spanner multi-region configurations provide synchronous cross-region replication, automatic failover, and external consistency, which together satisfy all constraints without manual database administration.

Answer

Bigtable with a multi-cluster routing policy across regions

Answer

Firestore in Native mode with multi-region location

Answer

Cloud SQL for PostgreSQL with a cross-region read replica promoted manually during an outage

Question 10

You have a Memorystore for Redis instance used as a session store. You notice that the instance is experiencing high eviction rates. What is the best first step to take?

Accepted Answer

Increase the instance size or set a TTL policy on session keys.. High eviction rates in Memorystore for Redis indicate that the instance is running out of memory and the Redis `maxmemory-policy` is actively removing keys. The best first step is to either increase the instance size to provide more memory or set a TTL (Time-To-Live) policy on session keys so that expired sessions are cleaned up proactively, reducing memory pressure and evictions.

Answer

Monitor memory usage but take no action.

Answer

Add a read replica to offload read traffic.

Answer

Enable persistence (AOF or RDB) to reduce memory usage.

Question 11

A media company uses Cloud Bigtable to store user interaction events. The row key is currently a random UUID, which ensures even distribution but makes it impossible to query events for a specific user efficiently. They need to retrieve all events for a given user, sorted by time descending. What should they change the row key to?

Accepted Answer

Use the user ID as the row key prefix, followed by a reversed timestamp.. To efficiently retrieve events for a specific user sorted by time descending, the row key should start with the user ID to group all events for that user. Appending a reversed timestamp ensures that the most recent events appear first in the sorted order, allowing a simple range scan with a row limit.

Answer

Use a hash of the user ID as the row key prefix, followed by the timestamp.

Answer

Use a reversed timestamp as the row key prefix, followed by the user ID.

Answer

Use the timestamp as the row key prefix, followed by the user ID.

Question 12

A media analytics company ingests 200,000 JSON events per second into BigQuery. Each event has a globally unique `event_id` (UUID string) and a `timestamp`. Queries frequently filter by `event_id` for deduplication and by `timestamp` for time-range reports. The table is append-only, and storage cost is a concern because old data is rarely queried after 90 days. Which design meets the query patterns and cost requirements?

Accepted Answer

Partition the table by `timestamp` (day granularity) and cluster by `event_id`.. Partitioning by `timestamp` enables partition pruning for time-range reports and supports partition expiration to automatically delete data after 90 days, directly addressing storage cost. Clustering by `event_id` improves the deduplication lookups that filter on that column. The other designs either create too many partitions, lack expiration, or fail to optimize both access paths.

Answer

Partition the table by `event_id` and cluster by `timestamp`.

Answer

Cluster the table by `event_id` and `timestamp` without partitioning.

Answer

Create a materialized view that selects only the last 90 days of data and query the view instead.

Question 13

A healthcare company uses Cloud SQL for PostgreSQL to store patient records. The database is critical, and they need to ensure that in the event of a zone failure, the database remains available with minimal data loss and automatic failover. They also want to minimize downtime during planned maintenance. Which feature should they implement?

Accepted Answer

Enable high availability (HA) configuration for the Cloud SQL instance.. Enabling high availability in Cloud SQL for PostgreSQL provides a synchronous standby in a different zone. This ensures automatic failover with no data loss if the primary zone fails, and it allows for minimal downtime during maintenance. The other options involve manual processes or asynchronous replication, which do not meet the requirements.

Answer

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

Answer

Use Cloud SQL's built-in backup and restore feature to recover from failures.

Answer

Set up a cross-region read replica and use it for disaster recovery.

Question 14

You are designing a Cloud Bigtable schema for a time-series application where the most common write pattern is high-throughput writes (10,000 writes per second) and the row key starts with a timestamp. Write throughput is lower than expected. What is the most likely cause?

Accepted Answer

The row key uses a timestamp as the leading component, causing a hotspot. Cloud Bigtable sorts rows lexicographically by row key and splits them into tablets. If the row key starts with a timestamp, sequential writes all land at the end of the key space, concentrating traffic on a single tablet (a hotspot). This limits write throughput because a single tablet is served by one node. The fix is to prefix the row key with a high-cardinality field (e.g., a hash or device ID) to distribute writes.

Answer

The column family has too many columns

Answer

The row key is too long

Answer

The cluster has insufficient nodes

Question 15

A media company stores user-generated video metadata in Firestore in Native mode. The application reads individual documents by document ID millions of times per day and rarely runs queries across collections. The team wants the lowest-latency, most cost-effective reads for this access pattern. What should they do?

Accepted Answer

Perform direct document reads using the document ID path. Firestore direct document reads by document ID use the primary key path, which is the fastest and least expensive way to retrieve a single document. Since the application almost always reads by document ID and rarely queries across collections, adding composite indexes or routing reads through BigQuery would increase cost and latency without improving the dominant access pattern.

Answer

Use collection group queries with a filter on a metadata field

Answer

Export Firestore data to BigQuery and serve reads from BigQuery

Answer

Create a composite index on the metadata fields and query by those fields