Design innovative, scalable, and highly available cloud database solutions →hardMultiple SelectObjective-mapped
PCDE Practice Question: Design innovative, scalable, and highly available cloud database solutions
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.)
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use a single table with a row key composed of user_id#topic_id#timestamp
Why it's wrong here
This key would not support efficient topic queries because you would need to scan all users.
- ✓
Create two tables: one with row key user_id#reverse_timestamp and another with topic_id#reverse_timestamp
Why this is correct
Separate tables allow optimal row key for each access pattern.
- ✗
Create a single table and use a secondary index on topic_id
Why it's wrong here
Bigtable does not support secondary indexes.
- ✓
Denormalize the data: store posts in two different tables for each access pattern
Why this is correct
Denormalization into separate tables is a common pattern for multiple access paths.
- ✗
Use a row key that starts with a hash of the user_id and then includes topic_id and timestamp
Why it's wrong here
Hash would scatter data, making range scans impossible.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PCDE question from scratch — 1,446 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
2 more ways this is tested on PCDE
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are designing a Spanner schema for a global social media application that stores user posts. Each user can have millions of posts. The most common query is 'get the most recent 10 posts for a user'. Which table interleaving design minimizes latency?
easy- ✓ A.Interleave the Posts table under the Users table, with user_id as parent key and post_timestamp as the child ordering key.
- B.Use a secondary index on user_id in the Posts table.
- C.Create a single table with user_id and post_timestamp as a composite primary key.
- D.Store posts in a separate Cloud Bigtable table and use the user_id as part of the row key.
Why A: Interleaving the Posts table under the Users table in Spanner ensures that all posts for a given user are stored in the same split, co-located on the same tablet server. This allows the query for the most recent 10 posts to be served with a single, local range scan on the interleaved child table, using post_timestamp as the descending ordering key, minimizing cross-node communication and latency.
Variation 2. 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.)
medium- ✓ A.Interleave the Post table under the User table using UserID as the first part of the primary key
- B.Use a secondary index on the Post table for UserID queries
- C.Denormalize the like count into the User table to avoid joins
- D.Use a monotonically increasing integer as the post ID to simplify indexing
- ✓ E.Use a UUID as the post ID to distribute writes evenly
Why A: 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.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.