PDE Storing the Data Practice Question
A data engineer needs to design a schema in BigQuery for a dataset that contains customer orders. Each order has a header and multiple line items. Queries frequently need to retrieve the entire order including line items. Which schema design is MOST performant and cost-effective?
⚠ Common exam trap
Google often tests the misconception that normalization (Option C) is always the best practice for relational databases, but in BigQuery's distributed, columnar architecture, denormalization with nested and repeated fields is the recommended pattern for performance and cost efficiency.
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
✓
Use nested and repeated fields (orders table with line items as REPEATED RECORD)
BigQuery is optimized for denormalized schemas using nested and repeated fields (REPEATED RECORD). Storing line items as a repeated record within the orders table avoids expensive JOIN operations, reduces data shuffling, and allows BigQuery to scan only the necessary columns, making queries that retrieve entire orders with line items both faster and more cost-effective.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Store all data in a flat table with repeated order info per line item
Why it's wrong here
Storing all data in a flat table with repeated order info per line item forces BigQuery to scan and store redundant header fields for every line item, inflating both storage costs and query slot consumption when retrieving entire orders. This approach is tempting because it mirrors a denormalised star-schema pattern often used in OLAP systems to avoid joins, and would be correct if queries never needed the full order structure but only aggregated metrics per line item.
- ✓
Use nested and repeated fields (orders table with line items as REPEATED RECORD)
Why this is correct
Nested repeated RECORDs store line items inside the parent order row, so retrieving a full order needs one read rather than a join. This eliminates shuffle and join costs, satisfying the frequent whole-order retrieval requirement while cutting bytes scanned.
- ✗
Normalize into separate orders and line_items tables, join on order_id
Why it's wrong here
Normalising into two tables forces a join on order_id for every whole-order query, adding shuffle and slot cost that nested repeated records avoid. Separate tables suit independent line-item analytics, such as product-level aggregation, rather than retrieving complete orders together.
- ✗
Use a partitioned table on order date
Why it's wrong here
Partitioning on order date prunes scans by date range, but it cannot co-locate a header with its line items; retrieving a whole order still requires joining or scanning separate rows. Partitioning suits large fact tables filtered by time, not nested parent-child retrieval.
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.