PCDOE Design and Plan Database Solutions — Timed 25 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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

Question 12

You are planning a Cloud Bigtable cluster for a workload requiring 100,000 reads per second and 50,000 writes per second. The data will be stored on HDD. How many nodes are needed for the projected throughput? (Assume each node provides 10,000 QPS for reads or writes.)

Accepted Answer

10 nodes. Each Bigtable node can handle 10,000 QPS for reads or writes. For 100,000 reads/s, need 10 nodes. For 50,000 writes/s, need 5 nodes. The node count must satisfy both: max(10,5)=10 nodes. Also storage capacity may be a factor but the question focuses on throughput.

Answer

20 nodes

Answer

5 nodes

Answer

15 nodes

Question 13

A company is migrating a monolithic application to Google Cloud and needs to modernize the database layer. The application has both OLTP (high-volume transactions) and OLAP (complex reporting) workloads. The team wants to use a single database to simplify operations but with high performance for both. Which TWO Google Cloud database services support hybrid transactional/analytical processing (HTAP)? (Choose two.)

Accepted Answer

AlloyDB. AlloyDB with columnar engine and Spanner with analytics interface support HTAP. Cloud SQL and Bigtable do not natively support HTAP. BigQuery is purely analytical.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Cloud SQL

Question 14

You are designing a Cloud Bigtable row key for a social media feed where users see posts from friends. Queries are: get posts for a user (by user_id) ordered by timestamp most recent first, and get posts for a specific topic (by topic_id) ordered by timestamp. To support both access patterns efficiently, which TWO design strategies are appropriate? (Choose two.)

Accepted Answer

Create two tables: one with row key user_id#reverse_timestamp and another with topic_id#reverse_timestamp. To support multiple access patterns in Bigtable, you can either denormalize data into two tables with different row keys, or use a secondary index (but Bigtable doesn't support secondary indexes natively; you would create a separate table). The common approach is to create two tables: one with row key user_id#reverse_timestamp and another with topic_id#reverse_timestamp. Alternatively, you can use a single table with a composite key but scanning for topic would be inefficient. The question asks for strategies. Two correct strategies: create separate tables for each pattern, or use a row key that combines user_id and topic_id but then you need to scan, so not ideal. Actually, the best practice is to have two tables. So the correct answers are: 'Create two tables: one with row key user_id#reverse_timestamp and another with topic_id#reverse_timestamp' and 'Use row key design that includes both user_id and topic_id as a composite key'? The latter is not efficient. Let me think. For multiple access patterns, the standard Bigtable design is to duplicate data into multiple tables with different row keys. So the correct options are those that mention separate tables. Among the options: 'Create a single table with a row key that starts with a hash of user_id and topic_id' would scatter data, not good. 'Use a secondary index on the table' is not supported. 'Create two tables with different row keys' is correct. 'Use a row key with user_id and topic_id concatenated and then timestamp' would allow scanning for a user but not for a topic unless you do a full scan. So the best two are: create two tables, and maybe use a row key that allows scanning for both? But that's not possible with a single key. I'll set the correct answers to: 'Create two tables: one optimized for user queries and one for topic queries' and 'Use a row key that includes both user_id and topic_id as a composite key'? That would be inefficient for topic queries. I think the intended correct answers are the ones that mention duplication. Let me write plausible options. To be accurate: The correct ones are: 'Create two tables: one with row key user_id#reverse_timestamp and another with topic_id#reverse_timestamp' and 'Denormalize the data into a separate table for topic queries'. So I'll set those as correct.

Answer

Use a single table with a row key composed of user_id#topic_id#timestamp

Answer

Create a single table and use a secondary index on topic_id

Answer

Use a row key that starts with a hash of the user_id and then includes topic_id and timestamp

Question 15

You are designing a schema for Cloud Spanner and need to model a one-to-many relationship between Customers and Orders. Which THREE features or practices should you consider? (Choose three.)

Accepted Answer

Create a secondary index on Orders.customer_id to speed up queries. Spanner supports parent-child interleaving for efficient joins, and secondary indexes with STORING clause for covering queries. Foreign keys are not enforced. Using monotonically increasing keys is discouraged.

Answer

Use a foreign key constraint to ensure referential integrity

Answer

Use a monotonically increasing integer as the primary key for Orders

Question 16

A company is migrating an on-premises OLTP application to Google Cloud. The application requires high concurrency (up to 5,000 simultaneous connections) and uses a relational schema with strong transactional integrity. Which database service is the MOST suitable?

Accepted Answer

Cloud SQL. Cloud SQL supports up to 4,096 connections by default (configurable) and provides full transactional integrity. For 5,000 connections, Cloud SQL can be configured with appropriate instance sizing. Spanner also supports transactions but is overkill for a single-region OLTP migration. BigQuery is for analytics, Firestore is NoSQL.

Answer

Firestore

Answer

BigQuery

Answer

Cloud Spanner

Question 17

You need to design a Spanner schema for a social media application that stores user posts. Each post has a unique ID, author ID, timestamp, and content. The primary access pattern is querying all posts for a given author in reverse chronological order. Which schema design minimizes the risk of hotspotting?

Accepted Answer

Primary key: (AuthorId, Timestamp) with Timestamp stored in descending order. Using a composite primary key with author ID as the first part distributes writes across splits. Adding timestamp as the second key orders rows for efficient range scans. Monotonically increasing keys (like sequential post IDs) cause hotspots. Interleaving with user table would be appropriate, but the question focuses on hotspotting.

Answer

Primary key: (AuthorId, PostId) where PostId is generated using a UUID

Answer

Primary key: (AuthorId, HashOfPostId)

Answer

Primary key: (PostId) with a secondary index on AuthorId

Question 18

A data engineer needs to run complex analytical queries on terabytes of data with sub-second query latency. The data is stored in Google Cloud Storage and updated daily. Which database service should they use?

Accepted Answer

BigQuery. BigQuery is a serverless data warehouse designed for complex SQL queries on large datasets with fast query execution. It can query data directly from Cloud Storage using external tables. Cloud SQL and Spanner are for OLTP, Bigtable for real-time low-latency lookups.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Cloud Spanner

Question 19

An e-commerce platform runs on Cloud SQL and expects heavy write traffic during a flash sale. The database instance currently has 8 vCPUs and 32 GB RAM. Based on the max_connections formula (max_connections = RAM_MB/16), what is the current maximum number of connections, and what change would increase it?

Accepted Answer

2048 connections; increase RAM to 64 GB. With 32 GB = 32768 MB, max_connections = 32768/16 = 2048. To increase connections, you need to increase RAM (e.g., 64 GB gives 4096). vCPU does not directly affect max_connections per the formula.

Answer

2048 connections; increase vCPU to 16

Answer

4096 connections; increase vCPU to 16

Answer

4096 connections; increase RAM to 64 GB

Question 20

You are designing a Bigtable schema for a time-series application that records sensor readings every second. Queries always filter by device ID and time range. To avoid hotspotting and ensure recent data is retrieved quickly, which row key design is MOST effective?

Accepted Answer

Row key: (hash(deviceID)#deviceID#reverseTimestamp). Salting (hash prefix) distributes writes across nodes, avoiding hotspots from sequential timestamps. Field promotion (device ID first) allows efficient prefix scans. Reverse timestamp ensures most recent data appears first when scanning.

Answer

Row key: deviceID#timestamp

Answer

Row key: (reverseTimestamp#deviceID)

Answer

Row key: timestamp#deviceID