PDE Ingesting and Processing the Data Practice Question
A team uses dbt on BigQuery to transform data in their data warehouse. They have a large table with nested and repeated fields (arrays and structs). The transformation needs to normalize this data into a star schema. Which dbt feature and BigQuery SQL feature should they use together?
⚠ Common exam trap
A common pitfall in this question is confusing the purpose of dbt features: hooks (automation), snapshots (SCD type 2), and seeds (CSV loading) are not designed for flattening nested structures. The correct approach is to use dbt models with BigQuery's UNNEST and CROSS JOIN to expand arrays into rows.
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
✓
dbt models with BigQuery UNNEST and CROSS JOIN
To normalize nested and repeated fields (arrays and structs) into a star schema, you need to flatten the arrays into separate rows. BigQuery's UNNEST operator, when used with CROSS JOIN, expands each array element into its own row, effectively denormalizing the nested structure. dbt models (SQL SELECT statements) are the correct dbt feature to define these transformations as version-controlled, reusable SQL files. Together, they allow you to write a dbt model that uses CROSS JOIN UNNEST to produce dimension and fact tables from a single nested table.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
dbt hooks with BigQuery STRUCT access
Why it's wrong here
Hooks run SQL or macros before or after a model, for tasks such as grants and indexing; they perform no transformation themselves. STRUCT access reads individual fields from a struct but cannot expand repeated arrays into rows. Hooks would be correct for post-run administrative actions, not for unnesting nested data into a star schema.
- ✓
dbt models with BigQuery UNNEST and CROSS JOIN
Why this is correct
UNNEST flattens BigQuery arrays and structs into individual rows, while CROSS JOIN multiplies each parent row across its unnested elements. Together inside dbt models they convert nested, repeated source data into the normalised fact and dimension tables of a star schema.
- ✗
dbt snapshots with BigQuery JSON functions
Why it's wrong here
Snapshots implement type-2 slowly changing dimension history by comparing timestamps, not flattening arrays or structs. JSON functions parse JSON strings, whereas the stem's nested and repeated fields are native ARRAY and STRUCT types. Snapshots would be correct for tracking changes to existing dimension rows over time, not for normalising nested source data.
- ✗
dbt seeds with BigQuery ARRAY_AGG
Why it's wrong here
Seeds load static CSV reference data into the warehouse; they cannot transform existing nested tables at all. ARRAY_AGG also moves the opposite direction, collapsing rows into arrays rather than unnesting them. The tempting fit is that seeds suit small lookup tables, which a star schema needs, but normalisation here requires dbt models with BigQuery UNNEST.
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.