PCDE Practice Exam 63 — 20 Questions

Question 1

A logistics company runs a Cloud SQL for MySQL instance that supports a route-planning application. The database is 2 TB, and the business requires a recovery point objective of 5 minutes and a recovery time objective of 1 hour after an accidental table drop. The team wants the least operational overhead while meeting these objectives. What should they do?

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

Accepted Answer

Enable automated backups and binary logging, and use point-in-time recovery to restore to a time before the drop. Automated backups plus binary logging enable point-in-time recovery in Cloud SQL, allowing restoration to a timestamp just before the accidental drop. This satisfies the five-minute recovery point objective and, for a 2 TB instance, can plausibly meet the one-hour recovery time objective with minimal operational overhead. Read replicas and high availability replicas replicate the logical error, and nightly dumps are too coarse.

Answer

Enable high availability with a regional failover replica and rely on automatic failover after the drop

Answer

Configure a cross-region read replica and promote it after the table drop

Answer

Schedule a nightly mysqldump export to Cloud Storage and restore the full dump after the drop

Question 2

You are monitoring a Cloud Bigtable instance that serves a read-heavy workload. You notice that read latency has increased. Which TWO actions should you take to diagnose the issue? (Choose two.)

Accepted Answer

Check the CPU utilization of the Bigtable cluster.. To diagnose increased read latency in Bigtable, you should monitor CPU utilization to assess cluster load and review row key design to identify hotspots. These steps help determine if the issue is due to insufficient resources or inefficient data distribution. Increasing nodes, enabling Data Boost, or checking replication lag are not diagnostic actions for this scenario.

Answer

Increase the number of nodes in the Bigtable cluster.

Answer

Enable Bigtable Data Boost for the instance.

Answer

Check the replication lag between clusters.

Question 3

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is designed for exactly this use case — petabyte-scale, low-latency (single-digit ms), high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is optimised for analytics (seconds-to-minutes latency), Cloud SQL is for OLTP (limited to tens of thousands of QPS), and Firestore is for document data with hierarchical structure.

Answer

Firestore

Answer

Cloud Spanner

Answer

BigQuery

Question 4

A retail company runs an on-premises Oracle database and is migrating its reporting workload to Google Cloud. The reporting team needs SQL access to the Oracle data with minimal changes and wants the data refreshed nightly. The company has no plans to rewrite the application. Which Google Cloud service should the database engineer recommend for the reporting layer?

Accepted Answer

Bare Metal Solution for Oracle, running the existing Oracle database on dedicated hardware in a Google Cloud region.. The requirement is to run existing Oracle SQL reporting with minimal changes in Google Cloud. Bare Metal Solution for Oracle provides Oracle-certified dedicated hardware in a Google Cloud region, preserving the Oracle engine and SQL dialect so the reporting team can continue using existing queries while data is refreshed nightly.

Answer

Cloud Bigtable, using the HBase API to expose the Oracle tables as column families.

Answer

Cloud Spanner, using its PostgreSQL interface to accept the Oracle SQL dialect.

Answer

Cloud SQL for SQL Server, because it is the closest managed relational engine to Oracle.

Question 5

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 6

A financial services company runs a Cloud SQL for PostgreSQL instance for transactional data and a Cloud Spanner instance for global inventory. They need a near-real-time, low-latency copy of changes from Cloud SQL to Cloud Spanner to power a unified reporting API. The solution must minimize operational overhead and support continuous replication without impacting source performance. What should they use?

Accepted Answer

Datastream with a Cloud SQL for PostgreSQL source and a Cloud Spanner destination. Datastream natively supports Cloud SQL for PostgreSQL as a source and Cloud Spanner as a destination, delivering continuous change data capture with low latency and minimal management. The other choices either target the wrong destination, use batch-oriented ETL, or require self-managed replication infrastructure that contradicts the low-overhead requirement.

Answer

Cloud Data Fusion with a PostgreSQL source and Spanner sink

Answer

Datastream for BigQuery with a Cloud SQL PostgreSQL source and a Spanner destination

Answer

Cloud SQL logical replication with a custom subscriber writing to Spanner

Question 7

An organization is migrating a 2 TB MongoDB Atlas cluster to Firestore in Native mode. They require a one-time snapshot migration with minimal operational overhead and do not need continuous replication. The source cluster uses MongoDB 4.4 and has a single replica set. Which migration approach should they use?

Accepted Answer

Use the Google-provided mongodb-to-firestore migration tool or a custom Dataflow pipeline to transform and load documents.. Migrating MongoDB to Firestore in Native mode requires a tool that understands BSON and Firestore document structures. Google's migration tool or a custom Dataflow pipeline handles the transformation and can process large datasets efficiently. DMS does not target Firestore, and BigQuery Data Transfer Service lacks MongoDB support. The managed import/export feature is only for Firestore-native formats, making the dedicated tool or Dataflow the correct choice.

Answer

Use BigQuery Data Transfer Service to load MongoDB data into BigQuery, then export to Firestore.

Answer

Use Database Migration Service (DMS) with a MongoDB source and Firestore destination, configured for continuous replication.

Answer

Use the Firestore managed import/export feature with a JSON export from MongoDB.

Question 8

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 9

A company is deploying a new application on Google Cloud that requires a relational database with automatic failover, point-in-time recovery, and the ability to scale reads. They want to minimize administrative overhead. They expect moderate write throughput and need strong consistency. Which database solution should they choose?

Accepted Answer

Cloud SQL for PostgreSQL with high availability configuration and read replicas.. Cloud SQL for PostgreSQL with high availability and read replicas provides a managed relational database with automatic failover, point-in-time recovery, and read scaling. It offers strong consistency and minimizes administrative overhead, making it the best fit for moderate write throughput and relational requirements.

Answer

Cloud Spanner with regional configuration.

Answer

Bigtable with a relational schema emulation layer.

Answer

Firestore in Datastore mode with multi-region replication.

Question 10

A retail analytics team ingests clickstream events into Cloud Bigtable for real-time dashboards. The table uses a row key of reverse-timestamp plus session ID. Query latency for the latest 5 minutes of data has grown from 20 ms to over 400 ms, and hotspotting is suspected. You want to diagnose whether the row key design is causing uneven tablet load. Which tool should you use?

Accepted Answer

Cloud Bigtable Key Visualizer. Key Visualizer is purpose-built to visualize Bigtable access patterns per row-key range and is the correct tool to confirm hotspotting. Tablet metrics, cbt reads, and Cloud Trace provide useful but indirect signals that cannot map traffic to key ranges, so they cannot definitively diagnose the row-key design issue.

Answer

Cloud Trace with Bigtable client instrumentation

Answer

Cloud Bigtable cbt CLI with the read command

Answer

Cloud Monitoring Bigtable tablet load metrics

Question 11

A startup is using Cloud Firestore in Native mode for a mobile application. They need to run analytical queries on the data using BigQuery without impacting the performance of the Firestore database. They want to set up a real-time sync of data changes to BigQuery. What should they do?

Accepted Answer

Use the Firestore to BigQuery extension to export data in real time.. The Firestore to BigQuery extension is designed to stream Firestore data changes to BigQuery in real time. It uses Cloud Functions to listen to Firestore changes and write them to BigQuery, enabling analytics without affecting Firestore performance. This is the recommended solution for real-time synchronization between Firestore and BigQuery.

Answer

Use the Firestore to BigQuery export feature in the Google Cloud console.

Answer

Enable BigQuery federation to query Firestore directly.

Answer

Set up a Cloud Function triggered by Firestore changes to write to BigQuery.

Question 12

A logistics company uses Cloud Spanner for its shipment tracking system. The Shipments table has a primary key of (shipment_id, created_at). The company frequently runs queries to find all shipments with a specific status (e.g., 'IN_TRANSIT') that were created in the last 24 hours. These queries are currently slow because they scan the entire table. The team wants to improve query performance without changing the primary key. What should they do?

Accepted Answer

Create a secondary index on (status, created_at) and include shipment_id as a stored column.. Creating a secondary index on (status, created_at) with shipment_id stored is the optimal solution. It allows Spanner to quickly find shipments by status and time range without scanning the base table, and it respects the constraint of not altering the primary key. This is a standard indexing strategy in Spanner.

Answer

Add a new column for a hash of the status and use it as a prefix in the primary key.

Answer

Use a SQL query hint to force the use of a specific index.

Answer

Create a separate table that stores only shipments with status 'IN_TRANSIT' and query that table.

Question 13

A company is designing a schema for a new Cloud SQL for PostgreSQL database that will store customer orders. The `orders` table will have a foreign key to the `customers` table. The application frequently runs queries that join `orders` and `customers` on `customer_id` and filters by `order_date`. The team wants to ensure referential integrity and optimize join performance. Which two actions should they take? (Choose two.)

Accepted Answer

Create a foreign key constraint on `orders.customer_id` referencing `customers.customer_id`.. To enforce referential integrity between orders and customers, a foreign key constraint on orders.customer_id is required. To optimize join performance, an index on the foreign key column is necessary because PostgreSQL does not automatically index foreign key columns. Together, these two actions ensure data consistency and efficient lookups when joining the tables, which is critical as the orders table grows.

Answer

Create an index on `orders.order_date` to optimize filtering by date.

Answer

Partition the `orders` table by `order_date` to improve join performance.

Answer

Use a `CHECK` constraint to validate that `customer_id` exists in the `customers` table.

Question 14

A Cloud SQL for MySQL instance is experiencing performance degradation. You run the 'SHOW ENGINE INNODB STATUS' command and notice a high number of 'History list length' and 'Pending reads' in the InnoDB status output. What is the most likely cause of the performance issue?

Accepted Answer

Long-running transactions causing excessive undo log growth.. The history list length in InnoDB represents the number of undo log records that are waiting to be purged. A high value typically indicates that there are long-running transactions that are preventing the purge process from cleaning up old versions. This can lead to increased I/O and performance degradation. Pending reads may further indicate I/O bottlenecks. The primary cause is long-running transactions.

Answer

Insufficient buffer pool size causing frequent disk reads.

Answer

Too many concurrent connections overwhelming the server.

Answer

Improper indexing causing full table scans.

Question 15

A financial analytics team uses BigQuery. They need to design a schema for a table that stores transaction records. The table will be queried with filters on customer_id, transaction_date, and amount. The team wants to minimize query costs and improve performance. They also need to enforce that customer_id is never null and that amount is always positive. Which schema design should they use?

Accepted Answer

Create a partitioned table on transaction_date, cluster by customer_id, and use NOT NULL constraints on customer_id and a CHECK constraint for amount > 0.. Partitioning by transaction_date enables partition pruning for date filters, and clustering by customer_id improves performance for customer-specific queries. Together, they reduce the amount of data scanned and lower query costs. BigQuery supports NOT NULL and CHECK constraints to enforce data integrity, ensuring customer_id is never null and amount is positive. This combination best meets the requirements.

Answer

Create a partitioned table on transaction_date, cluster by amount, and use NOT NULL constraints on customer_id and a CHECK constraint for amount > 0.

Answer

Create a partitioned table on customer_id, cluster by transaction_date, and use NOT NULL constraints on customer_id and a CHECK constraint for amount > 0.

Answer

Create a non-partitioned table with NOT NULL constraints on customer_id and a CHECK constraint for amount > 0, and rely on BigQuery's automatic optimization.

Question 16

A company is designing a BigQuery schema for a dataset that will store clickstream events. The events have a nested structure: each event has a user_id, timestamp, and an array of product interactions, where each interaction includes product_id, action, and duration. The team needs to write queries that analyze user behavior, such as finding all users who viewed a specific product. They also want to minimize query costs. Which TWO schema design considerations should they apply? (Choose two.)

Accepted Answer

Use a repeated RECORD field for product interactions to keep related data together and avoid joins.. Using a repeated RECORD for product interactions keeps related data together and allows efficient querying of nested fields without joins. Partitioning by timestamp reduces data scanned for time-based queries, lowering costs. Together, these design choices optimize both storage and query performance in BigQuery.

Answer

Use a STRING field to store product interactions as JSON, and parse it at query time.

Answer

Flatten the product interactions into separate rows in a single table, duplicating user_id and timestamp for each interaction.

Answer

Create a separate table for product interactions and join it with the events table on user_id and timestamp.

Question 17

You manage a Cloud SQL for PostgreSQL instance that supports an online order-processing application. During peak hours, users report slow response times. You observe that the instance's CPU utilization is consistently around 95%, and the number of active connections is at the maximum. You want to quickly identify the top SQL statements contributing to CPU load. Which Cloud SQL feature should you use?

Accepted Answer

Enable and review Query Insights for Cloud SQL.. Query Insights for Cloud SQL is the native tool for monitoring query performance on Cloud SQL instances. It automatically collects and aggregates query metrics, highlighting the most resource-intensive statements. By using it, you can rapidly identify the queries causing high CPU utilization and take corrective action, such as adding indexes or rewriting queries, without manual overhead.

Answer

Enable pg_stat_statements and query the view manually.

Answer

Enable the slow query log and analyze the log entries.

Answer

Use Cloud Monitoring to create a custom dashboard for CPU utilization.

Question 18

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.

Question 19

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 20

A company is migrating a small on-premises MySQL 5.7 database to Cloud SQL for MySQL using a one-time dump and load. They want to use the simplest method that requires minimal configuration. Which tool should they use?

Accepted Answer

mysqldump with the --single-transaction option, then import using the Cloud SQL import feature.. For a small one-time MySQL migration to Cloud SQL, mysqldump with --single-transaction and the Cloud SQL import feature is the simplest and most straightforward method. It requires minimal configuration, avoids ongoing replication setup, and is well-suited for databases where downtime is acceptable. DMS and replication are more complex and intended for larger or minimal-downtime scenarios. The non-existent wizard is not a valid option.

Answer

Use the Google Cloud Console's 'Migrate MySQL' wizard that automatically handles the migration.

Answer

MySQL replication by configuring the source as a master and Cloud SQL as a replica.

Answer

Database Migration Service (DMS) with continuous replication.