PDE Storing the Data Practice Question
An e-commerce company uses Cloud Spanner for order processing. They need to query orders by customer ID and retrieve all order items. Which schema design pattern should they use for optimal performance?
⚠ Common exam trap
A common pitfall in Google PDE exams is assuming interleaved tables automatically optimize any parent-child query. Interleaving only provides physical co-location when the query uses the parent's primary key prefix; queries on non-key columns like customer_id still need a secondary index and can incur additional lookups. For read patterns that always fetch the full child set with the parent, a repeated field is often the better denormalization choice.
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
✓
Denormalize by storing order items as a repeated field in the orders table.
For this access pattern, the optimal Spanner design is to denormalize order items as a repeated field in the Orders table. A repeated field stores child rows inline with the parent row, so a query by customer_id (with a secondary index on customer_id) retrieves the order and all its items in a single lookup without a join or extra network round-trip. Interleaved tables co-locate child rows with the parent, but they only help when the query uses the parent's primary key prefix; querying by customer_id would still require a secondary index and then a join-like lookup to fetch interleaved children, so option A is not the best fit here.
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 interleaved tables where Orders is the parent and OrderItems is an interleaved child table with the same primary key prefix.
Why it's wrong here
Interleaving co-locates child rows with their parent, enabling efficient joins and strong consistency.
- ✗
Store all data in a single table with nullable columns for order item attributes.
Why it's wrong here
This is poor schema design leading to sparsity and inefficiency.
- ✓
Denormalize by storing order items as a repeated field in the orders table.
Why this is correct
Spanner is a relational database; repeated fields are not supported. Denormalization would break relational integrity.
- ✗
Create two separate tables with a secondary index on customer_id in the orders table and a secondary index on order_id in the order_items table.
Why it's wrong here
This leads to cross-table lookups and slower queries compared to interleaving.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PDE question from scratch — 747 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 →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PDE 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 PDE exam.