PCDOE Design and Plan Database Solutions Practice Test 5 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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

Question 8

An organization uses Cloud Spanner and needs to add a new column to an existing table without downtime. The table has billions of rows and is heavily used. What is the recommended approach to add the column?

Accepted Answer

Use ALTER TABLE ADD COLUMN statement. Cloud Spanner schema changes are online and non-blocking, so you can simply use ALTER TABLE to add the column; it will not cause downtime.

Answer

Create a new table with the column, then copy data and rename

Answer

Add the column using gcloud command and set a downtime window

Answer

Backup the database, add column, then restore

Question 9

A small business runs a MySQL OLTP database for their inventory management system. They need high availability with automatic failover and regional disaster recovery. Which Google Cloud database service meets these requirements with minimal operational overhead?

Accepted Answer

Cloud SQL with HA and cross-region read replicas. Cloud SQL for MySQL with high availability (HA) configuration provides automatic failover within a region and can be configured with cross-region replicas for disaster recovery.

Answer

Cloud Spanner

Answer

Cloud Bigtable

Answer

Compute Engine with self-managed MySQL

Question 10

A gaming company uses Cloud Bigtable to store player session data. The row key is player_id (UUID) and they have a column family 'sessions' with multiple columns per session. They want to query all sessions for a specific player in a given time range efficiently. Which row key design improvement should they consider?

Accepted Answer

Append a reverse timestamp to the row key. Including a timestamp in the row key after the player_id allows scanning rows with a prefix of player_id and then filtering or scanning within a time range. Using a reverse timestamp helps get recent data first.

Answer

Add a hash prefix to player_id

Answer

Create a secondary index on timestamp

Answer

Use a separate table for time-range queries

Question 11

A data analyst needs to run a one-time SQL query on a dataset stored in CSV files in Cloud Storage. The query will scan the entire dataset, and the analyst wants to minimize cost. Which Google Cloud service should they use?

Accepted Answer

BigQuery. BigQuery can query external data directly from Cloud Storage; using query-on-demand billing, they only pay for the data scanned. It is serverless and requires no infrastructure.

Answer

Cloud SQL

Answer

Dataproc

Answer

Cloud Spanner

Question 12

A company is planning to use Cloud Spanner for a new global application. They estimate a peak write throughput of 10,000 mutations per second. What is the minimum number of processing units (PUs) required, given that each PU supports up to 2000 mutations/second?

Accepted Answer

5 processing units. Each PU supports 2000 mutations/second. To get 10,000 mutations/second, you need 10,000 / 2000 = 5 PUs. However, note that Spanner also requires at least 1 node (1000 PUs) for production. But the question asks for PUs based on write throughput alone.

Answer

1 processing unit

Answer

100 processing units

Answer

10 processing units

Question 13

A company is designing a schema for Cloud Spanner for an order management system. They need to ensure efficient joins between Order and OrderItems tables. Which TWO design practices should they adopt? (Choose 2)

Accepted Answer

Use parent-child interleaving for Order and OrderItems. Parent-child interleaving places child rows physically near parent rows, enabling efficient joins. Using the OrderId as the primary key of Order and also as the first part of the primary key of OrderItems (with a line item identifier) allows for strongly consistent interleaved queries.

Answer

Use a monotonically increasing primary key for Order

Answer

Store OrderItems in a separate database

Answer

Denormalize OrderItems into an array column in Order

Question 14

A company is migrating a large relational database to Bigtable. The database has a table with columns: user_id (string), event_type (string), timestamp (timestamp), and details (JSON). The access patterns include retrieving all events for a user in a time range, and filtering by event_type. Which THREE row key design strategies should they apply? (Choose 3)

Accepted Answer

Include event_type as a column qualifier instead of row key. A row key like hash(user_id) + user_id + reverse_timestamp + event_type distributes writes (hash), allows user-level scans (user_id), orders by time (reverse_timestamp), and enables filtering on event_type by using it as a column qualifier or part of key.

Answer

Store all events for a user in a single row

Answer

Use a monotonically increasing timestamp as the row key

Question 15

A manufacturing company uses Cloud SQL for PostgreSQL for its inventory system. The database has grown and now experiences high read latency. The team wants to improve read performance without changing application code. Which THREE actions should they consider? (Choose 3)

Accepted Answer

Add read replicas. Adding read replicas spreads read load, appropriate indexing speeds up queries, and increasing instance memory improves cache hit ratio. Sharding would require application changes.

Answer

Migrate to Cloud Spanner

Answer

Implement database sharding