Bigquery Nested Repeated Fields Schema Design

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

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 JSON type column to hold line item objects, and query the items using JSON_EXTRACT_ARRAY.

Flatten both tables into a single wide denormalized table where each row represents an individual line item and repeats parent order attributes.

Create a single Orders table with a column defined as an ARRAY of STRUCT (repeated RECORD) types to contain the line item details.

Maintain two separate normalized tables (Orders and Order_Items) and cluster both tables on order_id to optimize relational JOIN operations.

Professional Cloud Data Engineer

Bigquery Nested Repeated Fields Schema Design

Related questions