Arrays & Nested Structures

How DataPancake handles object arrays, primitive arrays, and nested structures, including foreign key relationships and table generation.

Object Arrays

Definition: Arrays that contain objects (complex structures).

Characteristics:

• Each array element is an object with its own attributes

• Creates separate Dynamic Tables for array contents

• Supports foreign key relationships to parent tables

• Example: orders array containing order objects

Code Generation:

• Root table contains array reference

• Separate table created for array contents (e.g., orders_array_object)

• Foreign key relationship links array table to root table

Polymorphic Version:

• Named as {attribute_name}_array_object

• Example: orders_array_object

---

Primitive Arrays

Definition: Arrays that contain primitive values (strings, numbers, booleans).

Characteristics:

• Array elements are simple values, not objects

• Stored as VARIANT or array types in Snowflake

• May be flattened or kept as arrays depending on configuration

• Example: tags array containing string values

Array Primitive Type:

• Specifies the data type of primitive array elements

• Examples: string, number, boolean

Polymorphic Version:

• Named as {attribute_name}_array_primitive

• Example: tags_array_primitive

---

Polymorphic Arrays

Definition: Arrays that contain both objects and primitives.

Characteristics:

• Most complex array type

• Requires careful handling in transformations

• May need type checking in SQL expressions

• Both array_primitive and array_object versions are activated

Handling:

• DataPancake detects when an array contains both types

• Both polymorphic versions are activated

• Transformation expressions may need to handle both cases

Example:

{
  "items": [
    "simple string",
    {"name": "object item", "value": 100}
  ]
}

In this case, both items_array_primitive and items_array_object would be active.

---

Nested Structures

Nested Objects

Attributes can be nested within objects at any depth:

root
  └── customer (object)
      └── contact (object)
          └── email (string)

Each level creates separate attributes:

• customer (object)

• customer.contact (object)

• customer.contact.email (string)

Nested Arrays

Arrays can be nested within other arrays or objects:

root
  └── orders (array)
      └── items (array)
          └── product_id (string)

Creates attributes:

• orders (array)

• orders[0].items (array)

• orders[0].items[0].product_id (string)

Mixed Nesting

Complex structures combine objects and arrays:

root
  └── customer (object)
      └── orders (array)
          └── items (array)
              └── product (object)
                  └── name (string)

---

Array Configuration

Foreign Key Relationships

For object arrays, you can configure foreign key relationships:

• Links array table to parent table

• Specifies which columns form the foreign key

• Configures relationship type (many-to-one, one-to-one)

• Sets join type (left outer, inner)

Array Metadata

Arrays have additional metadata fields:

• Array Type - 'object', 'primitive', or 'primitive,object'

• Array Primitive Type - For primitive arrays, the element data type

• Array Relationship Name - Name of the relationship

• Array Relationship Description - Description of the relationship

Array polymorphism: when an array contains mixed element types, DataPancake activates both {attribute_name}_array_primitive and {attribute_name}_array_object polymorphic versions so downstream transformations can handle either case.