PCDE Design innovative, scalable, and highly available cloud database solutions Practice Test 5 — 15 Questions

Question 1

An engineer is designing a Cloud SQL database for an e-commerce platform. They need to store product inventory and order history. Which schema normalization level is recommended for OLTP to avoid data anomalies?

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

Accepted Answer

Third Normal Form (3NF). Third Normal Form (3NF) eliminates transitive dependencies, reducing update anomalies in OLTP systems.

Answer

Second Normal Form (2NF)

Answer

First Normal Form (1NF)

Answer

Denormalized schema

Question 2

A company needs to perform real-time analytics on streaming data from IoT devices with millisecond latency for alerts, and also run complex historical analytics. Which Google Cloud database architecture supports both?

Accepted Answer

AlloyDB with columnar engine. AlloyDB with columnar engine handles real-time inserts and fast analytical queries on the same data, ideal for HTAP workloads.

Answer

Cloud Bigtable for real-time and BigQuery for analytics

Answer

Cloud SQL (read-only replica) for analytics

Answer

Cloud Spanner with interleaved tables

Question 3

An engineer is designing a Bigtable row key for global user events. They want to avoid hotspots and enable efficient queries by user_id and time range. Which row key design is best?

Accepted Answer

hash(user_id) + timestamp. Using a hash of user_id ensures distribution, and appending timestamp enables range scans by time within a user's events.

Answer

reverse(timestamp) + user_id

Answer

user_id + timestamp

Answer

timestamp + user_id

Question 4

A Cloud Spanner instance must handle 50,000 write mutations per second. You plan to use processing units (PUs). Each PU supports up to 2,000 mutations/second. What is the minimum number of PUs required?

Accepted Answer

25 PUs. 50,000 / 2,000 = 25 PUs. However, Spanner requires at least 1,000 PUs (or 1 node = 1,000 PUs) for production, but the question asks for minimum PUs based on throughput formula. The calculated value is 25, but since 1 node = 1,000 PUs, the actual minimum is 1 node (1,000 PUs). But the options likely include 25 if they ignore node minimum. To be consistent with GCP doc, the answer is 25 PUs if considering pure throughput, but note that minimum node is 1. Let's assume they want the calculated number.

Answer

100 PUs

Answer

10 PUs

Answer

50 PUs

Question 5

An organization needs a fully managed, globally distributed relational database with strong consistency and horizontal scaling for a multi-region application. Which service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner provides global distribution, strong consistency, horizontal scaling, and relational features.

Answer

Bigtable

Answer

Firestore

Answer

Cloud SQL

Question 6

A company is migrating from a relational database to Cloud Bigtable. They have a table with a 'user_id' and 'login_timestamp'. Queries often filter by user_id and time range. What should be the row key?

Accepted Answer

hash(user_id) + timestamp. hashing the user_id prevents hotspots, and appending timestamp enables range scans. 'user_id' alone may cause hotspots if sequential.

Answer

timestamp

Answer

user_id + timestamp

Answer

user_id

Question 7

An engineer needs to migrate a MySQL database to Cloud SQL with minimal downtime. Which TWO steps should be part of the migration plan? (Choose 2)

Accepted Answer

Verify that the source MySQL version is compatible with Cloud SQL. DMS provides continuous replication for minimal downtime. Verifying compatibility ensures a smooth migration. Cloud VPN is not required for connectivity if using public IP. mysqldump causes downtime. Using a read replica is not applicable.

Answer

Create a Cloud SQL read replica from the source

Answer

Perform a mysqldump and import the dump into Cloud SQL

Answer

Set up a Cloud VPN tunnel between on-premise and GCP

Question 8

A retail company uses Cloud Bigtable for real-time inventory. They want to improve read performance for queries that filter by product category and last_updated timestamp. Which THREE row key design strategies should they adopt? (Choose 3)

Accepted Answer

Use a reverse timestamp for time-based queries. Field promotion places frequently filtered attributes first. Salting with hash prefix distributes writes. Reverse timestamp ensures recent data is efficiently queried. A single monotonically increasing timestamp causes hotspots. Using a single column family doesn't affect row key.

Answer

Use a monotonically increasing timestamp as the first component

Answer

Store all data in a single column family

Question 9

A startup is building a ride-sharing application that requires globally distributed, strongly consistent transactions for ride matching and payments. The database must scale horizontally and provide low-latency reads and writes. Which Google Cloud database should they use?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that provides global strong consistency, horizontal scaling, and supports ACID transactions across regions.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 10

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 11

A data engineering team needs to run complex analytical queries on terabytes of data stored in Cloud Storage. The queries are ad-hoc and require scanning large portions of the dataset. The team needs a serverless solution that optimizes for cost by charging only for the data processed. Which Google Cloud service should they use?

Accepted Answer

BigQuery. BigQuery is a serverless data warehouse that uses columnar storage and charges for the data scanned by queries. It is ideal for ad-hoc analytical queries on large datasets.

Answer

Dataproc

Answer

Cloud SQL

Answer

Cloud Spanner

Question 12

An e-commerce platform uses Cloud Spanner as its database. The orders table has a monotonically increasing order_id as the primary key, and the team observes high write latency during peak hours. Which design change would BEST distribute write load across nodes?

Accepted Answer

Change the primary key to a UUID string. Using a UUID (or a hash prefix) as the primary key or as a leading part of a composite key prevents hotspotting on a single tablet server by distributing writes across the cluster.

Answer

Add a secondary index on order_id

Answer

Increase the number of nodes

Answer

Use interleaving with a parent table

Question 13

A company wants to store and analyze time-series metrics from thousands of servers. The data is write-heavy with occasional reads of recent data. They need low-latency writes and the ability to scan large ranges later. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Bigtable is designed for time-series data, high write throughput, and efficient range scans. It can handle large volumes of time-series data with low latency.

Answer

Firestore

Answer

Cloud Spanner

Answer

Cloud SQL

Question 14

A financial services company uses Cloud Bigtable to store transaction data. The row key is constructed as customer_id reversed timestamp. The team wants to retrieve the most recent 100 transactions for a specific customer quickly. Which row key design principle is being used to optimize this query?

Accepted Answer

Reverse timestamp. Reverse timestamp in the row key ensures that the most recent transactions for a given customer appear first when scanning rows with that customer prefix.

Answer

Field promotion

Answer

Salting

Answer

Composite key

Question 15

A team is migrating a relational database to Cloud Bigtable. They need to design a row key that distributes write traffic evenly across nodes. The original table had a composite primary key of customer_id and order_date. Which row key design is BEST for high write throughput?

Accepted Answer

hash(customer_id) # customer_id # order_date. Adding a hash prefix (salting) to the row key distributes writes across multiple tablet servers, avoiding hotspots. The customer_id alone may cause hotspots if some customers are more active.

Answer

customer_id # order_date

Answer

order_date # customer_id

Answer

customer_id only