PDE Preparing and Using Data for Analysis Practice Question
You are designing a BigQuery schema for a table that will store user profile data. The data includes a unique user ID, a list of email addresses (each with a type and address), and a timestamp of last update. You need to support efficient queries that retrieve all email addresses for a given user and also filter users by email type. Which schema design should you use?
⚠ Common exam trap
The trap here is normalizing into separate tables, which is common in relational databases but can degrade performance in BigQuery due to joins and increased data shuffling.
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
✓
Store emails as a repeated RECORD with fields 'type' and 'address' within the user table, using dot notation and UNNEST to query.
Using a repeated RECORD for emails allows BigQuery to store multiple emails per user with type and address fields. Queries can UNNEST the array to filter by email type and use dot notation to access fields. This denormalized design is efficient, avoids joins, and aligns with BigQuery's strengths for nested data.
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 emails as a single STRING column with a delimiter, and use SPLIT and REGEXP_CONTAINS to query.
Why it's wrong here
Storing emails as a delimited string loses type information and makes filtering by email type difficult. It requires string parsing, which is less efficient and more error-prone. This approach does not support efficient queries for filtering by type and is not recommended for structured data with multiple attributes.
- ✓
Store emails as a repeated RECORD with fields 'type' and 'address' within the user table, using dot notation and UNNEST to query.
Why this is correct
A repeated RECORD allows storing multiple emails per user while preserving the nested structure. Queries can use UNNEST to flatten the array and filter by email type, and dot notation to access fields. This denormalized approach is efficient in BigQuery, avoiding joins and enabling fast retrieval of all emails for a user.
- ✗
Store emails as a JSON STRING column and use JSON functions to extract values.
Why it's wrong here
While JSON functions can parse the string, this approach does not provide the same performance and schema enforcement as native RECORD types. Filtering by email type would require JSON path expressions, which are less efficient than directly querying nested fields. It also lacks type safety and can lead to errors if the JSON structure changes.
- ✗
Create a separate table for emails with a foreign key to the user table, and join when needed.
Why it's wrong here
Normalizing into separate tables requires joins, which can be less efficient for retrieving all emails for a user, especially at scale. BigQuery performs best with denormalized schemas for analytical queries. While joins are supported, they add complexity and may increase query cost and latency, so this is not optimal for the given access patterns.
Go deeper
Related to this question
About these practice questions
This PDE question is part of Courseiva's 747-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 →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Google Cloud exam blueprint
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.