Flatten Variant Component

Flatten Variant Component

This article is specific to the following platforms - Snowflake.

Flatten Variant Component

'Flattens' (explodes) compound values into multiple rows. When an input column (of Variant type) contains many values, Flatten Variant can seperate those values, giving each their own column.

For this component to be useful, the input data must be condensed in such a way that a single column contains multiple columns worth of data. The expected format is as follows:

{ "<ColumnName>": <Value>, "<ColumnName2>": <Value>", ... }

For example:

{ "Name": "John Bigs", "Age": 32, "Married": TRUE } 

In the example above, this single column can be seperated into 3 columns named 'Name', 'Age' and 'Married' with respective types VARCHAR, NUMBER, BOOLEAN.

This component can be used to flatten nested arrays and expose their variables. For a guide on how this works, see the Flattening Nested Arrays page.



Property Setting Description
Name Text The name of the component on the canvas.
Column Mapping Column/Property/Type/Alias Choose an input column and a property from it to take. Each property taken creates a column in the output and each row contains values from each property from its respective row in the input data.
Column Flattens Column/Property/Alias Take a property from an input column and flatten it such that its values constitute another input column. For example, an array value held as a single string in an input column can be flattened, so long as it adheres to the formatting outlined at the top of this article. Columns defined here can be used in the 'Column Mapping' property.


In this example, we tackle a problem whereby we possess a dataset that is unfortunately in the wrong format to perform further action on. In the job below, a Table Input component takes data and passes it to a Flatten Variant component. Finally, the data is passed to a Rewrite Table component to overwrite the undesirable data.

If we sample the incoming data we can see it is problematic. Every row is essentially 7 columns (each representing a single property) merged into a single column. Also note that each of the 7 properties comes paired with a value. A sample of this data is shown below.

In order to fix this, we can use the Flatten Variant component. Of particular interest is the 'Column Mapping' property.

Since we only have a single input column, the 'Column' property is always the same. However, we know that each contains many properties which are now seperated out in this list. We designate each property a type and let the component populate this new table layout by expanding out the original single column.

Finally we can check a sample from the Flatten Variant property to ensure the expansion has worked correctly.

This data is now ready to overwrite the original input.