Question

A retail analytics team is designing a BigQuery table to store customer orders. Each order has one customer and multiple line items (product, quantity, price). Analysts run frequent aggregations across orders and line items, and query cost and performance are top priorities. The team is deciding how to model the relationship between orders and their line items in BigQuery. What is the recommended schema design?

Accepted Answer

Create a single orders table with line items stored as a nested, repeated RECORD field within each order row.

Answer

Create separate normalized orders and line_items tables and JOIN them at query time using an order_id foreign key.

Answer

Store each line item as a separate row and duplicate all order-level columns across every line-item row in one flat table.

Answer

Store line items as a JSON string column and parse them with JSON functions at query time.