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

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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, PostId) where PostId is generated using a UUID. Using a composite primary key with AuthorId as the first part distributes writes across splits. However, adding Timestamp as the second key causes monotonically increasing writes for a single author, leading to hotspotting on the last split. Instead, a random component like a UUID (as in option A) distributes writes evenly across splits, minimizing hotspot risk. Option A's UUID provides sufficient randomness to avoid sequential writes while still enabling efficient queries per author via the prefix.

Answer

Primary key: (AuthorId, HashOfPostId)

Answer

Primary key: (AuthorId, Timestamp) with Timestamp stored in descending order

Answer

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

Question 12

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 13

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 14

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

Question 15

Which Google Cloud database service is designed for hybrid transactional and analytical processing (HTAP) with a built-in columnar engine?

Accepted Answer

AlloyDB for PostgreSQL. AlloyDB for PostgreSQL is a fully managed database with a columnar engine that accelerates analytical queries on transactional data. Cloud Spanner offers an analytics interface, but AlloyDB explicitly mentions a columnar engine for HTAP.

Answer

BigQuery

Answer

Cloud SQL

Answer

Cloud Spanner