Nested Data Structures

For decades, the First Normal Form (1NF) stood as an unbreakable rule in database design. It dictated that every column must contain atomic values, meaning a single cell could not hold a list or a set of attributes. To model a one-to-many relationship, such as an e-commerce order containing multiple line items, you were required to create two separate tables and join them via a foreign key.

Modern cloud data warehouses have fundamentally changed this requirement. By supporting semi-structured data types, specifically Arrays and Structs, systems like Google BigQuery, Snowflake, and Databricks allow you to denormalize one-to-many relationships directly into a single table without duplicating the parent row. This approach, often referred to as "schema-on-write" with nested structures, offers a powerful mechanism to improve query performance by eliminating expensive joins.

The Mechanics of Nested Data

To understand when to use nested structures, we must first define the two primary complex data types available in most analytical engines.

Arrays (Repeated Fields) An array is an ordered list of values sharing the same data type. In a standard relational model, if you wanted to store a list of product tags (e.g., "Electronics", "Sale", "New") for a single item, you would create a ProductTags bridge table. With arrays, you simply define a column of type ARRAY<STRING>. The database stores these values contiguously with the parent row.

Structs (Records/Objects) A struct is a container that groups related fields together under a single name. It functions similarly to a JSON object or a dictionary. For instance, instead of having separate columns for shipping_street, shipping_city, and shipping_zip, you can create a single shipping_address struct containing these sub-fields.

The true power of these data types emerges when they are combined. An Array of Structs allows you to nest entire rows from a child table inside the parent table.

Consider the structure of an e-commerce database. In a normalized design, an Orders table joins to an OrderItems table. In a nested design, the OrderItems are embedded directly within the Order row.

Comparison of a normalized one-to-many relationship versus a nested structure where child records reside within the parent row.

Performance Implications

The primary motivation for using nested data in an analytical context is I/O reduction. In a distributed database, joining two massive tables (like Orders and LineItems) usually triggers a "shuffle." The engine must move data across the network to align matching keys on the same compute node. This operation is network-intensive and slow.

By nesting the line items inside the order, you enforce data locality. The child data is physically stored adjacent to the parent data on the disk. When you query the orders, the line items are pre-loaded without requiring a join or network shuffle.

However, this does not mean the database stores a raw JSON string (which would be slow to parse). Modern formats like Parquet and Capacitor (used by BigQuery) shred these nested structures into separate sub-columns.

If you have a table Orders with a nested field items.price, the storage engine creates a specific column for items.price. If your query calculates the total revenue, the engine scans only the items.price sub-column. It skips the items.product_name or items.description sub-columns entirely.

We can express the efficiency gain of avoiding a join in terms of data access. In a normalized join, the cost function $C$ roughly correlates to the size of both relations plus the shuffle overhead:

$C_{join} \approx \text{Scan}(R) + \text{Scan}(S) + \text{Shuffle}(R, S)$

With a nested structure, the cost is reduced to the scan of the specific sub-columns required, with zero shuffle overhead:

$C_{nested} \approx \text{Scan}(R_{parent\_cols}) + \text{Scan}(R_{nested\_subcols})$

Querying Nested Data

While nesting improves storage and read performance, it increases the complexity of writing SQL. You cannot simply select a column that contains an array; you must "unnest" or "flatten" it to interact with the individual elements.

When you UNNEST an array, the query engine temporarily creates a cartesian product between the parent row and the array elements for the duration of the query. This mimics the result of a join but happens entirely in memory on a single node, maintaining high performance.

For example, to filter for orders containing a specific item, you might write logic that looks inside the array structure. This requires familiarity with syntax specific to your data warehouse (e.g., CROSS JOIN UNNEST in BigQuery or LATERAL FLATTEN in Snowflake).

Strategic Usage of Nesting

Nesting is not a universal replacement for star schemas. It is a specific optimization tool. You should evaluate the trade-offs based on your access patterns.

When to use Nested Structures

1. Parent-Child Locality: The child data is almost always accessed in the context of the parent. For example, you rarely analyze LineItems without knowing which Order they belong to.
2. High Cardinality Joins: The join between the parent and child is massive and creates performance bottlenecks.
3. Immutable Event Logs: The data represents a fixed event, such as a web log with repeated parameters or an application error trace.

When to Avoid Nesting

1. High Volatility: Updating a single element in an array (e.g., changing the quantity of one line item) requires rewriting the entire parent partition or file. This is inefficient for frequent updates.
2. Many-to-Many Relationships: If a child entity (like a "Product") is shared across many parents and has its own attributes that change (like "Product Name"), nesting it duplicates data. If the product name changes, you would have to update every order that contains that product.
3. BI Tool Compatibility: Some legacy Business Intelligence tools handle flat tables well but struggle to parse or visualize nested arrays without creating a custom SQL view to flatten them first.

The following chart highlights the decision boundary based on data relationship complexity and update frequency.

Scenarios with low update frequency and high query coupling benefit most from nesting, while frequently updated, loosely coupled data retains better performance in normalized tables.

By intelligently applying nested data structures, you can reduce the complexity of your join logic and significantly lower the latency of analytical queries. This technique serves as a bridge between the flexibility of document stores and the analytical power of columnar warehouses.