Support for List type in Variant datatype column for Snowflake Destination stage

Let's turn that ticket into a clear technical requirement and an implementation plan for engineering.

Summary / Engineering Approach

Currently, when a LIST (array) field is written to a Snowflake VARIANT column, the Snowflake Destination fails with the error "invalid type 'LIST', column type is 'VARIANT'". This happens because the stage only supports MAP (object) or scalar values for VARIANT.

Proposed Solution:
Enhance the Snowflake Destination to support writing LIST fields directly into VARIANT columns by serializing them as valid JSON arrays. StreamSets will internally call PARSE_JSON(?) when inserting, so arrays of primitives or nested objects are stored as native VARIANT JSON arrays — fully queryable with Snowflake JSON functions (e.g., variant_col[0], variant_col:key).

Scope:

• Support arrays of primitives and arrays of nested objects.

• Apply to both insert and upsert/merge modes.

• Preserve array structure (not stringify).

• Backward compatible with existing MAP/scalar → VARIANT behavior.

Benefits:
Allows customers to store and query complex JSON arrays natively in Snowflake, aligning with how VARIANT is designed to handle any valid JSON type.

We might still confirm with the customer:

• Whether they use Record Writer = JSON or AVRO (to ensure correct conversion pipeline).

• Whether their pipeline uses Field Type Converter before Snowflake.

• Size of arrays (for performance validation).

Customer inputs on the question from IBM StreamSets questions.