GCP Professional Data Engineer Practice Question 4: Nested Repeated Warehouse Model
Google Cloud Professional Data Engineer · Test 1 · question 4 of 10
A retailer lifted its on-premises third-normal-form warehouse into BigQuery unchanged. Nearly every analytical query joins the 20-billion-row orders table to the 90-billion-row order_items child table on order_id, and those joins dominate both slot time and query latency. The parent-child relationship is strictly one-to-many and items are never queried independently of their order.
How should you remodel the data to make these queries faster?
Show answer and explanation
Answer:
BigQuery's guidance for a one-to-many relationship that is always queried together is to denormalize using nested and repeated fields rather than to preserve the relational join. Each order becomes one row carrying its items as an ARRAY of STRUCT, so the join disappears entirely and with it the shuffle that was consuming the slot time. Because storage stays columnar down into the struct fields, a query that touches only two item attributes still reads only those two columns; the denormalization costs far less than the row-oriented equivalent would.
Option A adds work without removing the join. Region tables plus UNION ALL multiply table management, and the expensive orders-to-order_items join is still executed exactly as before.
Option B applies an acceleration layer to the wrong problem. BI Engine is an in-memory cache sized by reservation, aimed at low-latency BI dashboards over a working set; it cannot hold a 110-billion-row join and does not change the shape of the data.
Option C helps filtering, not joining. Clustering sorts blocks so that predicates prune data, but a join across two large clustered tables still shuffles rows across workers; the join remains the dominant cost.
Reference: Use nested and repeated fields