PCDE Design innovative, scalable, and highly available cloud database solutions — Timed 20 Questions

Question 1

A company is designing a data warehouse for analytics. They need to store structured and semi-structured data, support SQL queries with sub-second performance on petabytes, and integrate with Data Studio. Which service should they consider?

20 minute time limit — choose an answer to begin.

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse that supports SQL queries on petabyte-scale datasets with sub-second performance and integrates with Data Studio. Cloud Bigtable is a NoSQL database and does not support SQL. Cloud Spanner is designed for OLTP workloads, not analytics. Dataproc is a managed Hadoop/Spark service, not a data warehouse. Cloud SQL is for smaller relational datasets and cannot handle petabytes.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Answer

Dataproc

Answer

Cloud SQL

Question 2

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 and supports efficient point lookups. While hashing the auto-increment value can also distribute writes uniformly, it introduces additional complexity and may hinder range queries; thus, UUIDs are preferred for simplicity.

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 3

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 4

You need to size a Bigtable cluster for a workload that requires 50,000 reads per second (QPS) and 20,000 writes per second. Each read is about 1 KB, each write is about 1 KB. The data volume is 5 TB and growing. You choose SSD storage. What is the minimum number of nodes?

Accepted Answer

5 nodes. Bigtable SSD nodes can handle approximately 10,000 read QPS per node for 1 KB reads and 10,000 write QPS per node for 1 KB writes. For 50,000 reads QPS, 5 nodes are needed (50,000 / 10,000 = 5). For 20,000 writes QPS, 2 nodes are sufficient, but the read requirement dominates. Each SSD node also provides up to 2.5 TB of storage, so 5 nodes provide 12.5 TB, more than the 5 TB data. With Bigtable’s minimum recommendation of 3 nodes for production, 5 nodes satisfy all requirements. Therefore, the minimum number of nodes is 5.

Answer

72 nodes

Answer

50 nodes

Answer

7 nodes

Question 5

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 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 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 8

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 9

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.. 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 10

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is designed for exactly this use case — petabyte-scale, low-latency (single-digit ms), high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is optimised for analytics (seconds-to-minutes latency), Cloud SQL is for OLTP (limited to tens of thousands of QPS), and Firestore is for document data with hierarchical structure.

Answer

Firestore

Answer

Cloud Spanner

Answer

BigQuery

Question 11

You are designing a schema for Cloud Spanner and need to avoid write hotspots. Which primary key design strategy is recommended?

Accepted Answer

Use a UUID or hash prefix as the first part of the primary key. Using a UUID or a hash prefix as the first part of the primary key ensures that writes are distributed across nodes, avoiding hotspots. Monotonically increasing keys like timestamps cause all writes to hit the same tablet, creating a hotspot.

Answer

Use the most frequently queried column as the primary key

Answer

Use a composite key with the leading column being a timestamp

Answer

Use a monotonically increasing timestamp as the primary key

Question 12

A company is migrating an on-premise PostgreSQL database to Cloud SQL. The current database has a connection pool with 200 connections and uses 64 GB RAM. According to Cloud SQL best practices, what is the maximum recommended max_connections setting?

Accepted Answer

4096. The formula for max_connections on Cloud SQL is RAM_MB / 16. 64 GB = 65536 MB. 65536 / 16 = 4096. However, the question asks for the maximum recommended setting based on RAM. Note: This formula is for Cloud SQL; the actual max_connections default may vary, but the formula is a best practice guide.

Answer

200

Answer

64

Answer

1024

Question 13

You need to design a Cloud Bigtable row key for a time-series application that records user activity. The most common queries filter by user_id and then by timestamp (most recent first). Which row key design is MOST appropriate?

Accepted Answer

user_id#reverse_timestamp. For time-series with frequent queries filtering by user_id and recent data, the recommended design is to put user_id first and then use a reverse timestamp. This ensures data for a user is co-located and the latest data appears first when scanning.

Answer

user_id#timestamp

Answer

reverse_timestamp#user_id

Answer

timestamp#user_id

Question 14

A company is running a financial application on Cloud Spanner and needs to ensure strong transactional consistency across regions. The application requires both high write throughput (2000 mutations/second) and read throughput (2000 reads/second). According to Spanner capacity planning, how many processing units (PUs) are needed for the combined workload?

Accepted Answer

1 processing unit (PU). Spanner capacity: write throughput 2000 mutations/second per PU, read throughput 2000 reads/second per PU. For 2000 writes/s and 2000 reads/s, you need 1 PU for writes and 1 PU for reads, but the reads and writes share the same PU. Actually, the formula is: PUs needed = max(write_throughput/2000, read_throughput/2000). For 2000 each, that's max(1,1)=1 PU. However, the question states 'high write throughput (2000 mutations/second) and read throughput (2000 reads/second)'. So 1 PU is sufficient. But note: in practice, you might need more for other factors. The question tests the calculation. The answer is 1 PU.

Answer

2 processing units (PUs)

Answer

4 processing units (PUs)

Answer

0.5 processing units (PUs)

Question 15

You are designing a database schema for Cloud SQL (MySQL) for an OLTP application. Which normal form is typically recommended to avoid update anomalies?

Accepted Answer

Third Normal Form (3NF). Third Normal Form (3NF) is the standard for OLTP databases to reduce redundancy and avoid update, insert, and delete anomalies.

Answer

Denormalized form

Answer

Second Normal Form (2NF)

Answer

First Normal Form (1NF)

Question 16

A company is migrating a relational database to Cloud Bigtable. The source schema has a customers table and an orders table with a foreign key. Which data model approach is recommended for Bigtable?

Accepted Answer

Denormalize and store orders as a column family within the customer row. Bigtable is a wide-column NoSQL database, so denormalization is recommended to avoid joins. Embedding order data within customer rows (or using a single table with composite keys) is common. Referential integrity is not enforced.

Answer

Create a view to join the tables

Answer

Maintain two separate tables with foreign keys

Answer

Use Cloud Spanner to maintain relational structure

Question 17

You are running a Cloud Spanner instance and notice that a secondary index is causing performance issues for write operations. The index includes all columns of the table. Which Spanner feature can reduce the storage and write overhead of the index?

Accepted Answer

Use the STORING clause to include only the necessary columns. The STORING clause in Spanner allows you to include additional columns in the index without storing them in the index, reducing write overhead. This is used in 'covering indexes' but the STORING clause specifically stores the column in the index? Actually, STORING stores the column in the index so that queries don't need to read the base table. However, writing to the table requires updating the index, and if the index includes all columns, it's essentially a copy. To reduce overhead, you can use the STORING clause to only store necessary columns. The question asks to 'reduce the storage and write overhead' — using STORING with only needed columns reduces the index size, thus reducing write overhead. Alternatively, you could use a filtered index (partial index) but Spanner does not support filtered indexes. The correct answer is to use the STORING clause with only the columns needed.

Answer

Use a hash index instead of a secondary index

Answer

Drop the secondary index and rely on the primary key

Answer

Create a covering index without STORING

Question 18

A company needs to run complex analytical queries on large datasets (petabytes) with SQL support and high scalability. The data is stored in CSV files in Cloud Storage. Which Google Cloud service is MOST suitable?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable, and cost-effective data warehouse designed for running analytical queries on large datasets. It can query data directly in Cloud Storage using external tables.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Cloud Spanner

Question 19

You are designing a Cloud Bigtable schema for a time-series application where the most common write pattern is high-throughput writes (10,000 writes per second) and the row key starts with a timestamp. Write throughput is lower than expected. What is the most likely cause?

Accepted Answer

The row key uses a timestamp as the leading component, causing a hotspot. Using a timestamp as the first part of the row key causes all writes to hit a single tablet (hotspot), leading to poor write throughput. The recommendation is to salt the timestamp with a hash prefix.

Answer

The column family has too many columns

Answer

The row key is too long

Answer

The cluster has insufficient nodes

Question 20

A company uses Cloud SQL for PostgreSQL and needs to add a full-text search capability to a table with product descriptions. Which index type should be used?

Accepted Answer

GIN index. PostgreSQL supports full-text search using indexes created with the 'gin' index type on tsvector columns. The question asks for index type. In Cloud SQL for PostgreSQL, you can use the built-in full-text search with GIN indexes.

Answer

B-tree index

Answer

GiST index

Answer

Hash index