PCDE Mock Exam 80 — 25 Questions

Question 1

A company wants to migrate a 100 GB MySQL database to Cloud SQL with minimal application changes. Which migration tool should they use?

Accepted Answer

Database Migration Service. Database Migration Service (DMS) is the correct tool because it is designed specifically for migrating databases to Cloud SQL with minimal downtime and minimal application changes. DMS uses a combination of initial snapshot and continuous change data capture (CDC) to replicate the source MySQL database to Cloud SQL, allowing the application to point to the new database with only a connection string update.

Answer

mysqldump

Answer

BigQuery Data Transfer Service

Answer

Storage Transfer Service

Question 2

A company is designing a database schema for a global e-commerce platform. Orders are created with high frequency, and order status updates occur frequently. The team needs to choose a primary key strategy for the orders table in Spanner. Which approach minimizes hot-spotting?

Accepted Answer

Use a universally unique identifier (UUID) as the primary key. In Spanner, monotonically increasing or time-ordered primary keys cause hot-spotting because all new writes are directed to the same tablet server, creating a single point of contention. UUIDs are randomly distributed, ensuring writes are spread evenly across the entire key space, which minimizes hot-spotting and maximizes write throughput.

Answer

Use a monotonically increasing integer (e.g., auto-increment)

Answer

Use a timestamp as the primary key

Answer

Use a composite key with user_id and order_date

Question 3

A Cloud Spanner instance is experiencing high CPU utilization (above 80%) on multiple nodes. The database is used for an e-commerce application with a high volume of read-write transactions. The application uses the googlesql dialect and runs typical OLTP queries. You have already reviewed the query performance and found that most queries are efficient. Which initial step should you take to reduce CPU utilization?

Accepted Answer

Use the INFORMATION_SCHEMA.INDEXES view to identify and drop unused or redundant secondary indexes.. High CPU utilization in Cloud Spanner often stems from excessive secondary index maintenance during write operations. Dropping unused or redundant indexes reduces the write amplification and CPU overhead per transaction, directly lowering CPU usage without compromising query performance, as the queries are already efficient.

Answer

Adjust the application to use staleness of 5 seconds for reads to reduce CPU for read-write transactions.

Answer

Increase the number of nodes in the Spanner instance to spread the CPU load.

Answer

Create a separate read-only replica pool to offload read traffic.

Question 4

A company uses BigQuery materialized views to pre-aggregate sales data for a BI dashboard. The dashboard requires near-real-time data, but the materialized view currently reflects data up to 30 minutes old. What is the most effective way to reduce the refresh interval without significantly increasing costs?

Accepted Answer

Reduce the max_staleness parameter of the materialized view.. Reducing the `max_staleness` parameter directly controls the maximum acceptable age of the data in a BigQuery materialized view. By lowering this value, you force the view to refresh more frequently, achieving near-real-time data without incurring the cost of a full manual refresh or additional streaming infrastructure. This parameter is designed to balance freshness against cost, making it the most effective and efficient solution.

Answer

Disable automatic refresh and schedule a manual refresh every minute.

Answer

Use a streaming buffer with the base table to reduce latency.

Answer

Create additional materialized views with overlapping time windows.

Question 5

Refer to the exhibit. What is the effect of the partition_expiration_days option?

Accepted Answer

Partitions older than 365 days are automatically deleted. The `partition_expiration_days` option in BigQuery automatically drops partitions that are older than the specified number of days, reducing storage costs and simplifying lifecycle management. When set to 365, any partition with a date older than 365 days from the current date is deleted by BigQuery's background maintenance process.

Answer

The table's storage cost is reduced by 365%

Answer

Queries that reference data older than 365 days will fail

Answer

The table will be partitioned into 365 partitions

Question 6

A financial services company runs a high-frequency trading application that requires strong consistency, horizontal scalability, and low-latency transactions across multiple regions. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, strongly consistent, horizontally scalable relational database service designed for mission-critical applications like trading. Cloud SQL is not multi-region, Bigtable does not support SQL/ACID, and Firestore is not relational.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 7

A company has a BigQuery table that stores JSON data in a single column. They want to allow BI analysts to query nested fields using standard SQL. What is the best approach to make the data more query-friendly for BI tools?

Accepted Answer

Unnest the JSON into multiple columns using a persistent table with a flattened schema.. Flattening the JSON into a persistent table with a normalized schema eliminates the need for runtime parsing, allowing BI tools to query nested fields directly with standard SQL. This approach improves query performance by avoiding repeated JSON function calls and enables the use of indexed columns, which is critical for interactive BI workloads.

Answer

Use BigQuery's automatic schema detection to infer the structure.

Answer

Create a view that uses JSON_QUERY and JSON_VALUE functions to expose nested fields as columns.

Answer

Use the EXTRACT function to parse JSON fields in each query.

Question 8

A startup is building a mobile app and needs a fully managed database that scales automatically for unpredictable workloads. They expect moderate read/write traffic with occasional spikes. They want minimal operational overhead and do not need global distribution. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Firestore in native mode. Cloud Firestore is fully managed, serverless, auto-scales, and ideal for mobile apps with moderate traffic. Cloud SQL requires manual scaling. Bigtable is overkill and complex for this workload. Spanner is designed for global scale and is more complex than needed.

Answer

Cloud SQL for PostgreSQL with HA

Answer

Cloud Spanner regional

Answer

Cloud Bigtable

Question 9

A company is planning disaster recovery for Bigtable. They have two clusters in different regions with replication enabled. They want to automate failover using Cloud DNS. Which THREE components are required for this automation? (Choose 3)

Accepted Answer

Cloud HTTP(S) health check for the Bigtable cluster endpoints.. To automate Bigtable failover, you need a Cloud HTTP(S) health check to monitor the primary cluster, a Cloud DNS routing policy (e.g., weighted round robin) that can be updated, and a mechanism (e.g., Cloud Functions or an external tool) to update the DNS record when the health check fails.

Answer

Bigtable replication routing policy set to any-replica.

Answer

Cloud Bigtable AppProfile with single-cluster routing.

Question 10

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 11

A gaming company uses Cloud Spanner for a global leaderboard. They need to add a column to an existing table and create a secondary index on that column. The database must remain fully available during these changes. Which THREE statements are true?

Accepted Answer

The new column must have a default value if it is defined as NOT NULL.. In Cloud Spanner, when adding a NOT NULL column to an existing table, the column must have a DEFAULT value. This ensures that existing rows, which will be populated with the default value, satisfy the NOT NULL constraint without requiring a full table scan or blocking writes.

Answer

The new column cannot be added if the table has existing data.

Answer

CREATE INDEX will block writes on the table until the index is built.

Question 12

A developer needs to store JSON documents that are frequently accessed by key but rarely updated. The data size is under 10 GB initially but expected to grow to 500 GB. Which database service is most suitable?

Accepted Answer

Firestore (Datastore mode) with document keys. Firestore in Datastore mode is ideal because it provides a fully managed, scalable NoSQL document database with automatic sharding and strong consistency for key-based lookups. It handles growth from 10 GB to 500 GB seamlessly without manual partitioning, and its document keys enable efficient point reads for frequently accessed, rarely updated JSON data.

Answer

Cloud Bigtable with row keys as document IDs

Answer

Memorystore for Redis with JSON data type

Answer

Cloud SQL for PostgreSQL with JSONB column

Question 13

In Cloud Spanner, a table 'Orders' has a primary key (OrderId INT64) and is frequently updated. The application often queries for orders placed in the last hour. To reduce read latency, you decide to add a column to store the commit timestamp. Which approach should you use?

Accepted Answer

Define the column with the `allow_commit_timestamp` option and set it to 'true'. Cloud Spanner's `allow_commit_timestamp` option, when set to 'true' on a column of type `TIMESTAMP`, automatically populates that column with the exact commit timestamp of the transaction. This enables efficient time-based queries (e.g., orders placed in the last hour) without requiring application-managed timestamps or additional writes, reducing read latency by leveraging Spanner's built-in commit-time visibility.

Answer

Create an interleaved table with the timestamp

Answer

Use a generated column with expression to get current_timestamp

Answer

Add a secondary index on a user-managed timestamp column

Question 14

An engineer is migrating a workload from a relational database to Bigtable. The current schema has a Customers table (1M rows) and an Orders table (100M rows) with a foreign key. Queries often fetch all orders for a customer. What is the best row key design for the Bigtable orders table?

Accepted Answer

Use customer ID + order ID as the row key (e.g., cust123#ord456).. Using customer ID + order ID as the row key ensures that all orders for a single customer are stored in contiguous rows, enabling efficient range scans. Bigtable orders rows lexicographically by row key, so a prefix scan on the customer ID retrieves all related orders in a single read operation, avoiding expensive joins or scatter-gather patterns.

Answer

Use the order ID as the row key and store customer ID as a column.

Answer

Use a hash of the customer ID as the row key.

Answer

Use a random UUID as the row key.

Question 15

You need to set up a Cloud Monitoring alert for a Cloud Spanner instance to notify when the CPU utilization exceeds a threshold that could indicate performance degradation. What is the recommended CPU utilization threshold for Cloud Spanner?

Accepted Answer

65%. The recommended CPU utilization threshold for Cloud Spanner is 65%. This value is based on Google's best practices, as sustained CPU usage above 65% can lead to increased latency and performance degradation due to queuing and contention. Setting the alert at 65% provides a proactive warning before the instance reaches a critical state, allowing time for scaling or optimization.

Answer

90%

Answer

40%

Answer

80%

Question 16

A company is migrating their on-premises Oracle OLTP workload to Cloud SQL for PostgreSQL. The database currently supports 500 concurrent connections and has a working set of 8 GB. What is the minimum memory required for the Cloud SQL instance based on the max_connections formula (max_connections = RAM_MB/16)?

Accepted Answer

8 GB. Using the formula, RAM = max_connections * 16 = 500 * 16 = 8000 MB = 8 GB. However, this is only for connection overhead; additional memory is needed for buffer pool and working set. The question asks for minimum memory based on the formula alone, so 8 GB is the answer.

Answer

16 GB

Answer

4 GB

Answer

32 GB

Question 17

A Cloud SQL for PostgreSQL instance is experiencing high CPU usage due to many short-lived connections. Which configuration change can help without application changes?

Accepted Answer

Enable connection pooling with pgBouncer. Enabling connection pooling with pgBouncer reduces the CPU overhead of creating and tearing down many short-lived connections. Option A (read replicas) helps with read load, not CPU from connection churn. Option B (increase max_connections) allows more concurrent connections but does not reduce per-connection overhead and can increase CPU usage. Option C (increase tier to more vCPUs) adds resources but does not address the root cause of connection churn and may be cost-inefficient.

Answer

Use read replicas

Answer

Increase the max_connections parameter

Answer

Increase the tier to more vCPUs

Question 18

Refer to the exhibit. A BI analyst runs a query to get total sales for the last 7 days. The query filters on sale_date BETWEEN '2023-01-01' AND '2023-01-07'. What is the primary benefit of the partitioning defined in the table?

Accepted Answer

It reduces the amount of data scanned by pruning partitions.. Partitioning in BigQuery (and similar data warehouses) physically divides the table into segments based on the partition column (sale_date). When the query filters on sale_date BETWEEN '2023-01-01' AND '2023-01-07', the query engine can perform partition pruning, scanning only the partitions that match the date range instead of the entire table. This dramatically reduces the amount of data read, lowering query cost and improving performance.

Answer

It automatically creates indexes on sale_date.

Answer

It allows the query to use clustering.

Answer

It enables streaming inserts.

Question 19

A company's Cloud Spanner database currently uses a regional configuration in us-central1. Due to growth, the database must support global reads with low latency and maintain strong consistency. The database engineer is evaluating options. Which THREE considerations should the engineer include in the design? (Choose three.)

Accepted Answer

Select a configuration that places the leader region close to the majority of write traffic.. In a multi-region Cloud Spanner configuration, the leader region handles all writes and must be placed close to the majority of write traffic to minimize write latency. This ensures strong consistency, as all reads are served from the same leader region by default, and global reads with low latency require careful leader placement to avoid cross-region round trips.

Answer

Use interleaved tables to reduce the number of reads required for hierarchical data.

Answer

Add read replicas in remote regions to serve reads with eventual consistency.

Question 20

When designing a schema for a data warehouse in BigQuery, which table type is most cost-effective for storing raw event data that will be queried by date range filters?

Accepted Answer

A partitioned table partitioned by date column. Partitioned tables in BigQuery allow you to divide a table into segments based on a date, timestamp, or integer column. When querying with a date range filter, BigQuery can prune partitions, scanning only the relevant data rather than the entire table. This dramatically reduces the amount of data processed, lowering query costs and improving performance, making it the most cost-effective choice for storing raw event data that is frequently queried by date.

Answer

A table with integer range partitioning on an ID column

Answer

A regular table with no partitioning

Answer

A regular table clustered on timestamp