A data architect is migrating an enterprise e-commerce reporting workload from a normalized relational database to BigQuery. The existing relational model contains a parent Orders table and a child Order_Items table related by an order_id foreign key (a 1-to-many relationship). The analytical workload frequently aggregates line items per order, but queries that join these multi-billion-row tables across compute slots suffer from significant data shuffling and high slot consumption. The architect has the following requirements: Preserve the logical hierarchical relationship between each order and its line items without duplicating parent order attributes across multiple storage rows. Eliminate the computational overhead and network data shuffling caused by relational JOIN operations. Maintain columnar read efficiency so queries accessing only order-level fields do not scan line item data. How should the architect model this schema in BigQuery?

Create a single `Orders` table with a column defined as an `ARRAY` of `STRUCT` (repeated `RECORD`) types to contain the line item details.: What BigQuery ARRAY of STRUCT is In BigQuery, combining the ARRAY (repeated mode) and STRUCT (RECORD type) data types allows you to store multiple structured child objects directly inside a single parent row. This pattern creates a semi-normalized or nested schema that natively represents one-to-many parent-child relationships. How it meets the requirements Preserves logical hierarchical relationships: It encapsulates line items under their parent order in a single entity row, avoiding data anomalies without needing artificial surrogate mapping tables. Eliminates shuffle JOIN overhead: Because child records are physically colocated with the parent row in BigQuery's underlying Capacitor columnar storage format, queries access nested line items like pre-joined records, executing localized joins without cross-slot network shuffling. Maintains columnar storage efficiency: Capacitor separates each sub-field of a STRUCT into its own storage column. Queries referencing only order headers (e.g., order_date, customer_id) bypass the nested line item columns completely, minimizing bytes scanned and reducing query costs. Avoids row-level data duplication: Unlike a flat denormalized table where order metadata must repeat for every line item, an array keeps the order metadata in exactly one row while expanding arrays only when unnested. Key advantages Optimized for Dremel execution: Aligns schema architecture directly with BigQuery's internal Dremel execution model. Reduced I/O and query costs: Minimizes data scanned by reading only referenced nested leaf fields. High query performance: Converts distributed hash joins into fast, localized, in-memory array operations. Clean SQL semantics: Easily unnested in standard SQL using LEFT JOIN UNNEST() or comma cross joins. Why this is optimal This approach eliminates the performance bottlenecks of normalized relational joins in distributed MPP environments while preventing the storage bloat and duplicate aggregation errors associated with flat denormalization.