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

Question 1

A company is migrating an on-premises OLTP application to Cloud SQL. The current database runs on a server with 64 GB RAM and 16 vCPUs, and handles thousands of write-heavy transactions per second. The team wants to size the Cloud SQL instance appropriately. Which sizing approach is correct for estimating max_connections?

Accepted Answer

Set max_connections = RAM_MB / 16.. Cloud SQL for MySQL and PostgreSQL uses the formula `max_connections = RAM_MB / 16` as a default heuristic to prevent memory exhaustion from connection overhead. Each connection consumes approximately 16 MB of RAM for buffers and state, so dividing total RAM by 16 provides a safe upper bound. This approach directly ties connection limits to available memory, which is critical for write-heavy OLTP workloads that may open many concurrent connections.

Answer

Set max_connections = 8 * vCPU_count.

Answer

Set max_connections = 10 * IOPS.

Answer

Set max_connections = vCPU_count * 1000.

Question 2

An engineer is designing a global inventory system that requires strong consistency across continents, with the ability to handle write conflicts and ensure ACID transactions. The system expects millions of reads and writes per second. Which Google Cloud database service meets these requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides ACID transactions and strong consistency across globally distributed regions, using TrueTime and synchronous replication to handle write conflicts at scale. It is designed for millions of reads and writes per second while maintaining external consistency, making it ideal for a global inventory system.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 3

A company uses Bigtable for time-series analytics and needs to query the most recent data points first. The row key currently consists of a user ID followed by a timestamp (e.g., user123#2024-01-15T10:30:00). However, frequent queries filter by time range across all users. Which row key design change would optimize query performance for this access pattern?

Accepted Answer

Reverse the timestamp and place it at the beginning of the row key (e.g., 2024-01-15T10:30:00_rev#user123).. Option C is correct because reversing the timestamp and placing it at the beginning of the row key ensures that the most recent data points are stored first in lexicographic order. Bigtable stores rows sorted by row key, so queries filtering by a time range across all users can now scan a contiguous range of rows without needing to skip over user ID prefixes. This design avoids the hotspotting and inefficient scans that occur when the timestamp is not the leading part of the key for time-range queries.

Answer

Use the user ID as the only row key and store timestamps as column qualifiers.

Answer

Use a monotonically increasing integer as the row key.

Answer

Use a hash of the user ID as a prefix (salting) to distribute writes evenly.

Question 4

A data engineer is designing a schema for Cloud Spanner to store a hierarchy of customers and their orders. Customers have many orders, and queries often retrieve orders for a specific customer. To optimize performance and reduce cross-node reads, which schema design pattern should the engineer use?

Accepted Answer

Use a parent-child interleaved table structure where Orders are interleaved in Customers.. Spanner's interleaved tables allow storing child rows (orders) physically co-located with their parent row (customer) using the same primary key prefix. This enables efficient joins and reduces cross-node reads. A secondary index with STORING is useful for other access patterns but not for hierarchical queries. Denormalization is not recommended in Spanner.

Answer

Denormalize by embedding order details as a repeated field within the customer row.

Answer

Normalize customers and orders into separate tables without any relationship.

Answer

Create a secondary index with STORING on the order ID.

Question 5

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).. Option A is correct because 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 6

A startup needs to run complex analytical queries on large datasets (10+ TB) with sub-second to a few seconds latency. The data is structured and updated daily in batch. Which Google Cloud service is best suited for this use case?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse designed for petabyte-scale analytics with fast SQL queries using columnar storage and a distributed query engine. It supports sub-second to few-second latency on structured data via features like clustering, partitioning, and BI Engine acceleration, and it handles daily batch updates efficiently through batch loading or scheduled queries.

Answer

Cloud Bigtable

Answer

AlloyDB

Answer

Cloud SQL

Question 7

A company runs a MySQL database on Cloud SQL for an e-commerce platform. They need to add a new column to a table with millions of rows without causing downtime. What is the recommended approach?

Accepted Answer

Use a tool like pt-online-schema-change to perform the change with minimal impact.. Option B is correct because pt-online-schema-change (or gh-ost) creates a shadow table with the new schema, incrementally copies rows using triggers or binary log replay, and then atomically swaps the tables. This avoids holding any locks on the original table, preventing downtime for an e-commerce platform with millions of rows. Cloud SQL's InnoDB does not support true online DDL for all ALTER TABLE operations, especially on large tables, making a dedicated online schema change tool the safest approach.

Answer

Use 'ALTER TABLE ... ADD COLUMN ... ALGORITHM=INPLACE, LOCK=NONE'.

Answer

Create a new table with the column, copy data manually, then swap tables.

Answer

Use 'ALTER TABLE ... ADD COLUMN' directly; Cloud SQL handles it online.

Question 8

An engineer is designing a Cloud Spanner schema for a chat application where users send messages. Messages are ordered by timestamp per conversation. The primary key chosen is (ConversationId, MessageId) where MessageId is a monotonically increasing integer. What potential issue might arise with this key design?

Accepted Answer

Writes will be concentrated on a single split causing hotspots.. Option B is correct because using a monotonically increasing integer as the second part of the primary key (MessageId) in Cloud Spanner causes all new writes to be concentrated on a single split (tablet) that handles the highest key range. This creates a hotspot, degrading write throughput and latency, as Cloud Spanner splits data by key range and sequential inserts target the same node.

Answer

Queries by conversation will be slow.

Answer

The table cannot be interleaved with another table.

Answer

The table will not support secondary indexes.

Question 9

A company needs a fully managed, relational database with strong consistency and global distribution for a travel booking application that supports high concurrency and ACID transactions. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it is a fully managed, globally distributed relational database that provides strong consistency and ACID transactions across regions, making it ideal for high-concurrency travel booking applications that require global distribution and transactional integrity.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Firestore

Question 10

A company is using Cloud Bigtable to store user events for real-time analytics. The current row key is userId_timestamp (e.g., user123_20240115103000). However, writes to the table are unevenly distributed, causing hotspotting on a few nodes. Which row key modification can best distribute writes evenly?

Accepted Answer

Add a hash prefix (e.g., 2-byte hash of userId) at the beginning.. Adding a hash prefix (e.g., a 2-byte hash of the userId) at the beginning of the row key ensures that writes are distributed across all Bigtable nodes by randomizing the initial portion of the key. This prevents hotspotting because sequential or clustered userId values no longer concentrate writes on a single tablet server, as Bigtable splits and distributes rows based on the row key prefix.

Answer

Increase the number of nodes to handle the load.

Answer

Reverse the timestamp and prepend it to the key.

Answer

Move userId to the end of the row key.

Question 11

A data engineer is migrating a legacy on-premises Oracle data warehouse to Google Cloud. The source schema uses star schemas and advanced Oracle features like materialized views. The target must support real-time data from streaming sources and run complex SQL joins over 50 TB of data with low latency. Which architecture is most appropriate?

Accepted Answer

Migrate to BigQuery and use streaming inserts for real-time data.. BigQuery is the most appropriate target because it supports real-time streaming inserts, can handle complex SQL joins over 50 TB of data with low latency via its columnar storage and distributed query engine, and can replace Oracle materialized views with logical views or scheduled queries. It also natively integrates with streaming sources like Pub/Sub, making it ideal for the real-time data requirement.

Answer

Migrate to AlloyDB and use columnar engine for analytics.

Answer

Migrate to Cloud Spanner and use its analytics interface.

Answer

Migrate to Cloud SQL for PostgreSQL and use read replicas for analytics.

Question 12

A company runs a high-traffic event logging system on Cloud Bigtable. Each event has a timestamp, severity level, and a message. Queries often filter by severity and time range. To optimize for this access pattern, which field should be placed first in the row key?

Accepted Answer

Severity level. Severity level should be placed first in the row key because Cloud Bigtable stores rows in lexicographic order by row key. By placing severity first, all rows with the same severity are stored contiguously, making range scans over time within a severity highly efficient. This directly supports the query pattern of filtering by severity and time range without requiring a full table scan.

Answer

Random salt

Answer

Timestamp (reversed)

Answer

Message ID

Question 13

An engineer is designing a Cloud Spanner schema for a social media application. The database will have a User table and a Post table. Users have many posts, and the application frequently queries all posts for a user, ordered by timestamp. Which two schema design choices will improve performance? (Choose two.)

Accepted Answer

Interleave the Post table within the User table.. Option A is correct because interleaving the Post table within the User table physically co-locates rows for a user and their posts on the same split, enabling fast, low-latency queries for all posts by a user. This design avoids cross-node lookups and leverages Cloud Spanner's hierarchical storage to reduce read overhead.

Answer

Create a secondary index on PostId.

Answer

Use a monotonically increasing integer for PostId.

Answer

Use a UUID for PostId.

Question 14

A company is migrating a monolithic application to a microservices architecture and plans to use multiple Google Cloud databases. The application has the following workloads: (1) user profiles with high read/write concurrency, (2) product catalog with complex queries, (3) session data that requires low-latency access. Which three Google Cloud databases should be used? (Choose three.)

Accepted Answer

Firestore. Firestore is correct for user profiles with high read/write concurrency because it is a NoSQL document database that provides real-time synchronization, automatic multi-region replication, and strong consistency for high-throughput workloads. Its ability to scale horizontally and handle millions of concurrent connections makes it ideal for user profile data that requires low-latency reads and writes.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 15

An engineer is designing a Bigtable schema for a weather data application. The data is written by thousands of sensors, each generating a reading every minute. Queries typically retrieve all readings for a sensor in a time range. The row key should be designed to avoid hotspots and support these queries. Which two row key components are recommended? (Choose two.)

Accepted Answer

Reversed timestamp. Option C is correct because using a reversed timestamp (e.g., Long.MAX_VALUE - timestamp) as part of the row key spreads writes across Bigtable tablets, avoiding hotspots that occur when sensors write sequentially in natural time order. This design also supports efficient range scans for a sensor's data over a time range when combined with a sensor ID prefix.

Answer

Sensor ID (raw) as the only key

Answer

Sensor location as a column

Answer

Timestamp in natural order