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

Question 1

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 2

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 3

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 4

An organization is migrating a relational OLTP database to Cloud Spanner. The existing database uses auto-increment primary keys. The team is concerned about potential hotspots. Which approach should they take to redesign the primary keys to avoid hotspots while preserving the ability to perform point lookups efficiently?

Accepted Answer

Use a UUID (randomly generated) as the primary key. Using a UUID as the primary key distributes writes evenly but may cause poor join performance. For Spanner, using a combination of a hash-bucket prefix and the original auto-increment ID (or a UUID) is common. However, the simplest and most effective approach is to use a UUID as the primary key, which ensures uniform distribution and supports point lookups via the UUID.

Answer

Use a hash of the auto-increment value as the primary key

Answer

Use the auto-increment value as a secondary index and a random UUID as the primary key

Answer

Keep the auto-increment primary key; Spanner handles hotspots automatically

Question 5

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 6

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 7

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 8

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

Question 9

A company is running analytical queries on large datasets (terabytes) that involve aggregations, joins, and window functions. The data is updated daily via batch loads. The queries must complete in seconds to minutes. Which Google Cloud database service is BEST suited for this workload?

Accepted Answer

BigQuery. BigQuery is a serverless data warehouse designed for large-scale analytics with fast SQL queries. It handles terabytes to petabytes, charges by query usage, and is ideal for OLAP workloads.

Answer

Cloud SQL for MySQL

Answer

Cloud Spanner

Answer

Bigtable

Question 10

A team is designing a Cloud Spanner schema for an e-commerce platform. They have 'Customer' and 'Order' tables and want to ensure that queries for all orders of a specific customer are efficient. Which schema design approach should they use?

Accepted Answer

Interleave the 'Order' table under the 'Customer' table with CustomerID as the first part of Orders' primary key. Spanner supports interleaving tables, where child rows are stored physically near the parent row. Interleaving Orders under Customers using the CustomerID as the first part of the primary key in Orders makes queries for all orders of a customer efficient by colocating related data.

Answer

Use a single table with denormalized customer and order data

Answer

Use a secondary index on CustomerID in the Order table

Answer

Create a separate 'Order' table with a foreign key to Customer

Question 11

A company is using Cloud Spanner and needs to add a new column to an existing table that has billions of rows. The column must have a default value of 0. The team is concerned about downtime. Which approach should they take to add the column with zero downtime?

Accepted Answer

Use ALTER TABLE ADD COLUMN with DEFAULT 0; the operation is online and non-blocking. Cloud Spanner supports online schema changes that are non-blocking. Adding a column with a DEFAULT value is an online operation that does not block reads or writes, and does not require copying data. The new column is added with the default value for existing rows at read time, not by backfilling.

Answer

Take the application offline, run ALTER TABLE to add the column, then bring the application back online

Answer

Create a new table with the column, copy data using Dataflow, then rename the tables

Answer

Add the column without a default, then run a batch update to set the default value

Question 12

An organization needs to run both transactional (OLTP) and real-time analytical (OLAP) queries on the same dataset without data duplication. The dataset is moderately large (a few terabytes). Which Google Cloud database service is MOST appropriate for this HTAP workload?

Accepted Answer

AlloyDB. AlloyDB is a fully managed PostgreSQL-compatible database designed for hybrid transactional and analytical processing (HTAP). It includes a built-in columnar engine that accelerates analytical queries on transactional data without separate ETL or duplication.

Answer

Bigtable

Answer

BigQuery

Answer

Cloud SQL for PostgreSQL

Question 13

A company is designing a Cloud Bigtable schema for time-series data. The data is written by millions of devices every second. The query patterns are: (1) retrieve the most recent reading for a specific device, (2) retrieve all readings for a device in a time range. Which TWO row key design techniques should the team use to optimize for these patterns? (Choose two.)

Accepted Answer

Use field promotion: put device_id as the first component of the row key. Using a reverse timestamp allows the most recent data to be at the beginning of the row key, making scans for the latest reading efficient. Field promotion ensures that device_id comes first in the row key, enabling efficient prefix scans for all readings of a device. Salting is not needed because device_id already distributes writes if there are many devices.

Answer

Use salting (hash prefix) to distribute writes

Answer

Use a monotonically increasing timestamp as the first part of the row key

Answer

Store the entire row as a single column family to reduce overhead

Question 14

A company is migrating a monolithic application to a microservices architecture. The existing relational MySQL database has a large table with sensitive PII data that must be encrypted at rest and accessed only by authorized services. The team wants to use a managed Google Cloud database service that supports IAM integration for fine-grained access control. Which THREE services meet these requirements? (Choose three.)

Accepted Answer

AlloyDB. AlloyDB is a fully managed PostgreSQL-compatible database service that supports IAM integration for fine-grained access control and provides encryption at rest by default using Google-managed or customer-managed encryption keys (CMEK). It meets the requirements for a managed relational database with strong security controls, making it suitable for migrating from MySQL while handling sensitive PII data.

Answer

Bigtable

Answer

Firestore

Question 15

An organization is designing a Cloud Spanner schema for a social media application. The application frequently queries for all posts by a specific user, and also updates the number of likes on a post. To ensure high performance and avoid hotspots, which TWO schema design principles should the team apply? (Choose two.)

Accepted Answer

Interleave the Post table under the User table using UserID as the first part of the primary key. Interleaving the Post table under the User table colocates posts with their user, making queries for a user's posts efficient by reducing distributed reads. Using a UUID for the post ID ensures writes are distributed across the cluster, avoiding hotspots from sequential keys like timestamps.

Answer

Use a secondary index on the Post table for UserID queries

Answer

Denormalize the like count into the User table to avoid joins

Answer

Use a monotonically increasing integer as the post ID to simplify indexing