Arrays & Nested Structures

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

Object Arrays

Definition: Arrays containing objects (ARRAY_TYPE = 'object').

Characteristics:

• Each array element is an object with its own attributes

• Separate Dynamic Table created for array contents

• Foreign key relationships link array table to parent

• Polymorphic version: {attribute_name}_array_object (e.g., orders_array_object)

Code generation:

• Root table contains array reference

• Array table named using CREATE_OBJECT_NAME_ALIAS_NAME or default {root_table_name}_array_object

• Foreign keys configured in Foreign Keys section

---

Primitive Arrays

Definition: Arrays containing primitive values (ARRAY_TYPE = 'primitive').

Characteristics:

• Array elements are simple values (not objects)

• Stored as VARIANT or ARRAY types in Snowflake

• May be flattened into separate table depending on configuration

• ARRAY_PRIMITIVE_TYPE specifies element data type ('str', 'int', 'float', 'bool')

• Polymorphic version: {attribute_name}_array_primitive (e.g., tags_array_primitive)

---

Polymorphic Arrays

Definition: Arrays containing both objects and primitives (ARRAY_TYPE = 'primitive,object').

Characteristics:

• Both array_primitive and array_object polymorphic versions are activated

• Most complex array type; requires careful handling in transformations

• Transformation expressions may need type checking to handle both cases

Example:

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

Both items_array_primitive and items_array_object are active.

---

Nested Structures

Nested objects: Attributes nested at any depth create separate attributes at each level:

• customer (object, level 0)

• customer.contact (object, level 1)

• customer.contact.email (string, level 2)

Nested arrays: Arrays nested within arrays create indexed paths:

• orders (array)

• orders[0].items (array)

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

Mixed nesting: Objects and arrays can be combined at any depth. Each level tracked via ATTRIBUTE_LEVEL and PARENT_OBJECT/PARENT_ARRAY.

---

Array Configuration

Foreign key relationships: For object arrays, configure in Foreign Keys section:

• Links array table to parent table

• Specifies foreign key columns (ATTRIBUTE_FOREIGN_KEY_COLUMNS)

• Relationship type: 'many_to_one' or 'one_to_one' (ARRAY_RELATIONSHIP_TYPE)

• Join type: 'left_outer' or 'inner' (ARRAY_RELATIONSHIP_JOIN_TYPE)

Array metadata fields:

• ARRAY_TYPE - 'object', 'primitive', or 'primitive,object'

• ARRAY_PRIMITIVE_TYPE - Element data type for primitive arrays

• ARRAY_RELATIONSHIP_NAME - Relationship name

• ARRAY_RELATIONSHIP_DESCRIPTION - Relationship description

• INCLUDE_IN_CODE_GEN - Controls array table generation