PCDE Practice Exam 64 — 20 Questions

Question 1

A financial services company is migrating a 4 TB on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL. The database must remain available for read and write operations throughout the migration, with a cutover downtime of less than five minutes. They have a 1 Gbps dedicated interconnect and can provision a Cloud SQL instance with sufficient storage. Which migration approach should they use?

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

Accepted Answer

Use Database Migration Service (DMS) for PostgreSQL with continuous replication, then promote the destination instance during cutover.. Database Migration Service for PostgreSQL is designed for continuous, minimal-downtime migrations to Cloud SQL. It performs an initial snapshot and then replicates ongoing changes, allowing the source to remain fully operational. During cutover, the destination is promoted, and applications are redirected. This meets the requirement of less than five minutes of downtime for a 4 TB database.

Answer

Use the Cloud SQL import feature to load a CSV export of all tables, then apply binary logs for changes.

Answer

Set up a read replica in Cloud SQL for PostgreSQL, then promote it to a standalone instance after replication catches up.

Answer

Perform a pg_dump and pg_restore over the dedicated interconnect during a scheduled maintenance window.

Question 2

You are managing a Cloud SQL for MySQL instance that supports an e-commerce application. During peak hours, you notice that the number of active connections spikes, and the instance occasionally reaches the maximum connections limit, causing errors. You want to reduce the likelihood of hitting this limit and improve performance. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure a connection pooler such as ProxySQL or the Cloud SQL Auth Proxy with connection pooling.. To reduce connection spikes, implementing a connection pooler reduces the number of actual database connections by multiplexing client requests. Additionally, lowering idle timeouts ensures that unused connections are closed promptly, freeing up slots for active connections. Together, these actions directly mitigate the risk of hitting max_connections without over-provisioning resources.

Answer

Enable the slow query log and analyze queries to optimize them.

Answer

Increase the max_connections database flag to a higher value.

Answer

Increase the instance's vCPU count to handle more connections.

Question 3

A startup is designing a schema for a new application on Cloud SQL for PostgreSQL. They need to store user profiles with a unique email address and frequently query users by email. Which schema design should they implement to ensure data integrity and optimal query performance?

Accepted Answer

Create a UNIQUE constraint on the email column.. A UNIQUE constraint on the email column enforces data integrity by preventing duplicate emails and automatically creates a unique index that accelerates queries filtering by email. This is the most efficient and standard approach in PostgreSQL. Regular indexes lack uniqueness enforcement, CHECK constraints cannot enforce cross-row uniqueness, and using email as a primary key introduces maintenance challenges. Therefore, the UNIQUE constraint is the correct choice.

Answer

Create a primary key on the email column.

Answer

Create a regular index on the email column without a UNIQUE constraint.

Answer

Use a CHECK constraint to validate that email is unique.

Question 4

A logistics company runs AlloyDB for PostgreSQL in a single region and has a primary instance plus a read pool for reporting. During testing, the team found that when the primary zone becomes unavailable, the read pool does not automatically take over. They want the database to automatically fail over to another zone within the same region with the shortest possible recovery time and no manual promotion. What should the database engineer do?

Accepted Answer

Convert the primary instance to a highly available configuration so AlloyDB maintains a standby in a different zone.. AlloyDB provides automatic failover when the primary instance is configured for high availability, which maintains a synchronized standby in a separate zone that is promoted without operator action. Read pools serve read scaling only and are not promoted, and point-in-time recovery addresses logical data restoration rather than availability. Placing everything in one zone increases risk instead of reducing it.

Answer

Enable point-in-time recovery on the primary and restore to a new instance when a zone fails.

Answer

Place the primary and the read pool in the same zone to reduce replication lag.

Answer

Add a second read pool in a different zone and direct writes to it during an outage.

Question 5

A media company uses Cloud Bigtable to store user event data. They need to design a row key that avoids hotspots and supports efficient queries for the most recent events for a given user. The application queries data by user_id and needs the latest events first. What row key design should they use?

Accepted Answer

Use user_id as the row key prefix, followed by a reversed timestamp.. Using user_id as the prefix groups events by user, and a reversed timestamp sorts them newest-first within that user. This supports efficient range scans for a user's recent events and distributes writes across many users, avoiding hotspots. Other designs either lose per-user grouping, cause write hotspots, or require full scans, failing to meet the performance and query requirements.

Answer

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

Answer

Use a reverse timestamp as the row key prefix, followed by user_id.

Answer

Use a timestamp as the row key prefix, followed by user_id.

Question 6

A financial analytics team is designing a BigQuery schema for a table that will store daily stock trades. The table will contain columns: trade_id (STRING), symbol (STRING), trade_date (DATE), price (FLOAT64), and volume (INT64). The table will be queried frequently for aggregations by symbol and trade_date, and also for full table scans to export raw data. The data volume is about 10 TB per year, and the team wants to minimize query costs. They plan to partition the table. What partitioning strategy should they use?

Accepted Answer

Partition by trade_date using daily partitions, and cluster by symbol.. Partitioning by trade_date with daily partitions enables partition pruning for date filters, significantly reducing bytes scanned. Clustering by symbol further optimizes queries that filter or aggregate by symbol within each partition. This combination is the most cost-effective for the given query patterns and data volume, as it leverages BigQuery's ability to prune both partitions and clusters.

Answer

Do not partition the table, but cluster by symbol and trade_date.

Answer

Partition by symbol using integer range partitioning, and cluster by trade_date.

Answer

Partition by trade_date using monthly partitions, and cluster by trade_id.

Question 7

Your organization uses BigQuery for analytics. A daily ETL job loads data into a partitioned table. Users report that queries scanning the last 7 days of data are slow. You examine the query and see that it filters on a timestamp column. The table is partitioned by ingestion time. What should you do to improve query performance?

Accepted Answer

Recreate the table with partitioning by the timestamp column instead of ingestion time.. Partitioning the table by the timestamp column used in the filter enables BigQuery to perform partition pruning, scanning only the partitions that contain the relevant data. This reduces the amount of data processed and speeds up queries. Ingestion-time partitioning does not align with the filter, so it cannot prune effectively. Recreating the table with the correct partitioning scheme is the most direct and effective solution.

Answer

Use a wildcard table to query only the relevant daily tables.

Answer

Create a clustered table on the timestamp column used in the filter.

Answer

Enable the query cache to avoid scanning the same data repeatedly.

Question 8

A company runs a Cloud SQL for SQL Server instance in a single zone and wants to improve availability and read scaling. They plan to add a cross-region read replica and enable high availability. Which TWO statements about this configuration are correct? (Choose two.)

Accepted Answer

Enabling high availability creates a standby instance in a different zone within the same region as the primary.. High availability in Cloud SQL creates a synchronized standby in a different zone of the same region and provides automatic failover for zonal failures. Cross-region read replicas scale reads and can serve as a disaster recovery target, but promotion to primary is manual. Together they address in-region availability and cross-region recovery, each with distinct failover behavior.

Answer

The cross-region read replica shares the same IP address as the primary instance.

Answer

Enabling high availability and adding a cross-region read replica both require the instance to be restarted and cause downtime.

Answer

The cross-region read replica automatically becomes the new primary if the primary region fails.

Question 9

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 10

A financial analytics team is designing a BigQuery schema for a fact table that stores millions of transactions per day. Queries often filter on transaction_date and customer_id, and aggregate on amount. The team wants to minimize query costs and maximize performance. What schema design should they use?

Accepted Answer

Partition the table by transaction_date and cluster by customer_id.. Partitioning by transaction_date allows BigQuery to prune partitions when queries filter on that column, reducing bytes scanned and cost. Clustering by customer_id then sorts data within each partition, making filters and aggregations on customer_id more efficient. This design directly addresses the query patterns and is optimal for large fact tables in BigQuery.

Answer

Use a partitioned table with ingestion-time partitioning and cluster by customer_id.

Answer

Partition the table by customer_id and cluster by transaction_date.

Answer

Cluster the table by transaction_date and customer_id without partitioning.

Question 11

A financial analytics firm runs a mission-critical Cloud Spanner database in a single region. The compliance team mandates that the database must tolerate a full regional outage with zero data loss and no application changes. Which configuration should you implement?

Accepted Answer

Create a new Cloud Spanner instance with a multi-region configuration that includes the primary region and a second region.. A multi-region Cloud Spanner configuration replicates data synchronously across regions and automatically fails over to a healthy region, ensuring zero data loss and continuous availability. It requires no application changes because the same instance endpoint remains valid. Other options either do not provide automatic failover, introduce data loss, or require manual intervention.

Answer

Enable point-in-time recovery (PITR) on the existing instance and set the retention period to 7 days.

Answer

Export the database to Cloud Storage every hour and restore it to a new instance in a different region if an outage occurs.

Answer

Add a read-only replica in a second region and configure the application to redirect writes to the replica during a failure.

Question 12

A retail company runs its transactional workload on Cloud SQL for MySQL and needs to stream every committed change into BigQuery with sub-second latency for real-time dashboards. The team wants minimal operational overhead and no custom code. Which Google Cloud service should they use to replicate the data?

Accepted Answer

Datastream with a source connection profile for MySQL and a BigQuery destination. Datastream is the managed change data capture service that reads the MySQL binlog from Cloud SQL and streams inserts, updates, and deletes into BigQuery continuously. Because it is serverless and supports Cloud SQL as a source with BigQuery as a destination, it meets the low-latency and low-operations requirements without custom code or batch scheduling.

Answer

Cloud SQL read replica with federated queries from BigQuery

Answer

BigQuery Data Transfer Service for Cloud SQL

Answer

Cloud Data Fusion with a pipeline that reads from Cloud SQL and writes to BigQuery

Question 13

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 14

A retail company uses Firestore in Native mode for a product catalog. They need to support a query that finds all products with a price between $10 and $50, sorted by price ascending, and also filters by category. The catalog has millions of products. Which Firestore feature should they use to optimize this query?

Accepted Answer

Create a composite index on (category, price) and query with filters on category and price range, ordering by price.. Firestore requires composite indexes for queries that combine multiple filters with a sort order. A composite index on (category, price) allows the database to efficiently filter by category and a price range while returning results sorted by price. This is the standard way to optimize such queries and scale to millions of documents without performance degradation.

Answer

Enable Firestore in Datastore mode and use a composite index on (category, price).

Answer

Store the price as a string and use a range filter with lexicographic ordering.

Answer

Use a collection group query on a subcollection named 'prices' and filter by price range.

Question 15

A retail company runs Cloud SQL for SQL Server Enterprise edition in a single zone in us-east1. Their compliance team requires automatic failover to a standby in a different zone within the same region, no data loss for committed transactions, and minimal application changes. The application connects using a private IP address. Which configuration should the database engineer implement?

Accepted Answer

Enable high availability on the instance, which creates a standby in a different zone and uses a regional private IP that follows failover.. Enabling high availability on Cloud SQL for SQL Server creates a standby instance in a different zone and replicates synchronously, so committed transactions survive a zone failure with no data loss. Failover is automatic, and the regional private IP stays the same after failover, meaning the application's connection string does not change and no code modifications are needed.

Answer

Configure the instance with a failover replica in the same zone and rely on the zone's internal redundancy to handle failures.

Answer

Move the instance to a shared VPC and enable Private Service Connect so the application can reconnect to a new IP automatically.

Answer

Create a cross-region read replica in us-east4 and promote it manually if the primary zone fails.

Question 16

A healthcare company stores patient records in Cloud SQL for PostgreSQL and audit logs in Cloud Bigtable. A compliance report must list every patient record that has an associated audit log entry in the last 30 days. The team wants to run this report without extracting data into a new store. What should they do?

Accepted Answer

Use BigQuery federated queries to read Cloud SQL for PostgreSQL and Bigtable external tables in one SQL statement.. BigQuery federated queries allow a single SQL statement to combine Cloud SQL for PostgreSQL data with Bigtable external tables, so the compliance report can join patient records with audit logs without copying data. Both databases stay as systems of record, and the report runs in BigQuery. This is the simplest way to span the two database technologies while meeting the no-extraction requirement.

Answer

Create a Cloud Spanner instance and migrate both the patient records and audit logs into it, then run the report there.

Answer

Configure Cloud SQL for PostgreSQL to replicate to Cloud Bigtable using Datastream, then query Bigtable for the report.

Answer

Export Cloud SQL patient records to Cloud Storage as CSV and load them into Cloud Bigtable, then run a scan for matching audit entries.

Question 17

A logistics company uses Bigtable to store real-time shipment tracking events. The row key is currently 'shipment_id#timestamp', where shipment_id is a random UUID and timestamp is the event time in milliseconds. The team needs to efficiently retrieve the latest event for a given shipment_id. However, they notice that reads for the latest event are slow because Bigtable must scan many versions. What should they do to improve read performance for the latest event?

Accepted Answer

Use a row key of 'shipment_id#reverse_timestamp' where reverse_timestamp is Long.MAX_VALUE minus the event timestamp.. The correct approach is to use a row key of 'shipment_id#reverse_timestamp'. This inverts the timestamp order so that the latest event has the smallest suffix and appears first in a range scan for that shipment. Reading with a limit of 1 then retrieves the latest event efficiently. Bigtable does not support secondary indexes, so row key design is critical.

Answer

Enable Bigtable's versioning feature to automatically return the latest version.

Answer

Create a secondary index on shipment_id and timestamp.

Answer

Change the row key to 'timestamp#shipment_id' to store events in time order.

Question 18

You manage a Cloud Spanner instance with a multi-region configuration (nam3). A read-write transaction that updates a single row in a table with a monotonically increasing primary key (e.g., timestamp) experiences high commit latency. What is the most likely cause?

Accepted Answer

The monotonically increasing primary key causes hotspots, as all writes go to the same split, overwhelming a single server.. Cloud Spanner partitions data by key ranges, and each partition is served by a specific server. When a table uses a monotonically increasing primary key, such as a timestamp, new inserts and updates concentrate on the last partition, causing a hotspot. This hotspot leads to increased latency and resource contention. To avoid this, keys should be designed to distribute writes evenly, for example by using a hash of the timestamp or a UUID.

Answer

The transaction is using a read-only lock mode, causing it to wait for a global snapshot.

Answer

The multi-region configuration replicates data synchronously across regions, and the commit must wait for quorum acknowledgment from all regions.

Answer

The transaction is not using an index, forcing a full table scan before the update.

Question 19

A company is migrating a 4 TB on-premises PostgreSQL 13 database to Cloud SQL for PostgreSQL. They require a one-time migration with less than 30 minutes of downtime. The database has a high volume of write transactions. Which migration approach should they use?

Accepted Answer

Use Database Migration Service (DMS) to perform a continuous migration with minimal downtime.. Database Migration Service (DMS) is designed for minimal-downtime migrations to Cloud SQL. It performs an initial snapshot and then continuously replicates changes from the source, allowing cutover with minimal downtime. For a 4 TB PostgreSQL database with high write volume, DMS handles the scale and keeps the destination nearly in sync, meeting the 30-minute downtime requirement.

Answer

Configure a read replica in Cloud SQL and promote it after replication catches up.

Answer

Use pglogical to set up logical replication between on-premises and Cloud SQL.

Answer

Perform a pg_dump and pg_restore while the application is offline.

Question 20

A startup is building a mobile app with Firestore in Native mode. They need to store user profiles and allow users to query by email address. The email field must be unique. What is the most appropriate schema design?

Accepted Answer

Store each user profile as a document in a 'users' collection, using the email address as the document ID.. In Firestore, document IDs are unique within a collection. Using the email address as the document ID naturally enforces uniqueness and enables direct document retrieval by email without needing a query. This approach is efficient, requires no additional indexes or collections, and aligns with Firestore's design for unique identifiers.

Answer

Store each user profile as a document in a 'users' collection, using a combination of email and a random suffix as the document ID to avoid collisions.

Answer

Store each user profile as a document with an auto-generated ID and create a single-field index on the email field.

Answer

Store each user profile as a document with an auto-generated ID and create a separate 'emails' collection mapping email to user ID.