PCDE Design and implement database schemas Practice Question
A company is using Cloud Spanner to manage financial transactions. The current schema has a single table 'Transactions' with a composite primary key (account_id, transaction_timestamp). The company frequently queries the latest transaction for each account. This query pattern is causing full table scans. Which schema design change would most improve query performance?
⚠ Common exam trap
Google Cloud often tests the misconception that a secondary index with DESC ordering can efficiently retrieve the latest row per group, but in Cloud Spanner, secondary indexes do not support 'top-N per group' without scanning all index entries for each group.
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 a separate 'LatestTransaction' table keyed by account_id, and update it whenever a new transaction occurs
It eliminates the need to scan the entire Transactions table to find the latest transaction per account. By maintaining a separate LatestTransaction table keyed by account_id, each account's latest transaction can be retrieved with a single point read. This is a classic denormalization pattern in Cloud Spanner that avoids the overhead of scanning or sorting large datasets for 'latest per group' queries.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a secondary index on (account_id, transaction_timestamp DESC)
Why it's wrong here
Still requires an index scan for each account to find the latest.
- ✗
Change the primary key to (transaction_timestamp, account_id) and use interleaving
Why it's wrong here
Interleaving does not help find max timestamp per account.
- ✓
Create a separate 'LatestTransaction' table keyed by account_id, and update it whenever a new transaction occurs
Why this is correct
Enables direct point reads for the latest transaction.
- ✗
Add a 'is_latest' boolean column to the Transactions table and index it
Why it's wrong here
Requires maintaining the boolean, and scanning the index still may be inefficient.
Go deeper
Related to this question
About these practice questions
This PCDE question is part of Courseiva's 1,446-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way 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. A financial services company is designing a Cloud Spanner schema for a trading system. They have two main entities: 'accounts' and 'transactions'. Each account has many transactions, and queries almost always retrieve transactions for a specific account. Which TWO schema design strategies should they employ?
hard- A.Use a secondary index on transactions.account_id.
- ✓ B.Ensure the primary key of transactions includes the account_id as the first part.
- C.Define a foreign key constraint from transactions to accounts.
- D.Store transactions as a JSON array of repeating fields within the account record.
- ✓ E.Use an interleaved table hierarchy with accounts as parent and transactions as child.
Why B: Cloud Spanner distributes rows across splits based on the primary key prefix. By making `account_id` the first part of the transactions table primary key, all transactions for a given account are co-located, enabling efficient range scans and point lookups without cross-node shuffling.
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.