Bigquery Nested Repeated Fields Schema Design

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.