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

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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

Question 9

A financial services company is migrating a legacy on-premises OLTP application to Google Cloud. The application requires high transaction rates (thousands per second), strict ACID compliance, and the ability to scale horizontally across multiple regions with strong consistency. Which Google Cloud database service should the company choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is a globally distributed, horizontally scalable relational database service that provides ACID transactions and strong consistency across regions. It is ideal for OLTP workloads that require high throughput, consistency, and global scalability.

Answer

Bigtable

Answer

Cloud SQL for PostgreSQL

Answer

Firestore

Question 10

A team is designing a schema for Cloud Spanner to store user profiles. The primary access pattern is to read a user's profile by their unique user ID. To avoid write hotspots, which primary key design strategy should the team use?

Accepted Answer

Use a UUID (universally unique identifier) as the primary key. Monotonically increasing keys (e.g., sequential integers or timestamps) cause all writes to hit the same tablet, creating hotspots. Using a random UUID distributes writes evenly across the cluster, avoiding hotspots.

Answer

Use the user's email address as the primary key

Answer

Use a monotonically increasing integer as the primary key

Answer

Use a timestamp as the primary key

Question 11

A company uses Cloud Bigtable to store time-series data from IoT devices. Each device sends a reading every minute. The row key currently is: device_id + timestamp (e.g., 'device123#2024-01-01T00:00:00Z'). Write throughput is lower than expected. Which row key modification would MOST improve write distribution?

Accepted Answer

Add a hash prefix of the device_id (e.g., hash(device_id) + device_id + timestamp). Adding a hash prefix (salting) to the row key distributes writes across multiple tablet servers. The current row key starts with device_id, which may cause hotspots if many writes target the same device. A hash prefix ensures uniform distribution.

Answer

Reverse the timestamp: timestamp + device_id

Answer

Use only device_id as the row key

Answer

Remove the device_id and use only timestamp

Question 12

A startup needs a database for its new web application that will serve a global user base. The application requires low-latency reads and writes (single-digit milliseconds), strong consistency, and the ability to handle high concurrency (thousands of transactions per second). Which Google Cloud database service should the startup choose?

Accepted Answer

Cloud Spanner. Cloud Spanner offers global distribution, strong consistency, and low-latency transactions at high concurrency. It is designed for globally scaled OLTP workloads.

Answer

Cloud SQL for MySQL

Answer

Bigtable

Answer

Firestore

Question 13

A company is designing a schema for Cloud Bigtable to store user sessions. Access patterns: (1) read all sessions for a given user ID, and (2) read a specific session by session ID. The row key should support both patterns efficiently. Which row key design is MOST appropriate?

Accepted Answer

Use user_id#session_id as the row key. Using 'user_id#session_id' as the row key allows prefix scans on user_id to retrieve all sessions for a user, and exact lookups on the full key for a specific session. This is a common pattern for Bigtable.

Answer

Use session_id as the row key and store user_id as a column

Answer

Use a hash of user_id as row key prefix and session_id as suffix

Answer

Use user_id as the row key and store multiple session columns

Question 14

An engineer is configuring a Cloud SQL for PostgreSQL instance for an OLTP workload. The instance has 30 GB of RAM and expects up to 2000 concurrent connections. The default max_connections is 100. The engineer needs to set max_connections appropriately based on Google's recommendation. What should the max_connections be set to?

Accepted Answer

1920. Google Cloud SQL recommends setting max_connections = RAM_MB / 16. With 30 GB RAM = 30720 MB, that gives 1920 connections. This balances connection overhead and available memory.

Answer

1500

Answer

3072

Answer

2000

Question 15

A company uses Cloud Spanner for its global inventory system. The current schema has a table 'Orders' with a primary key of OrderID (UUID). The team wants to add a secondary index to support queries filtering by 'status' and 'order_date'. Which type of index should they create and how should they define it to ensure the index covers the query without needing to read the base table?

Accepted Answer

Create a secondary index on (status) STORING (order_date). A secondary index with a STORING clause includes additional columns (like order_date) in the index, allowing the index to cover queries that reference only the indexed and stored columns, avoiding a back-join to the base table.

Answer

Create a secondary index on (status, order_date)

Answer

Create a global secondary index on status, and let the query join with the base table for order_date

Answer

Create a local secondary index on status with order_date stored in the index