Contents x
    Construct Variant
      • Dark
        Light
      Contents

      Construct Variant

        • Dark
          Light
        Article Summary

        This article is specific to the following platforms - Snowflake.

        Construct Variant Component

        This component allows the user to create a variant-type column that collapses rows into arrays of key:value pairs.

        Each value in the new column takes the form of a Snowflake object, see OBJECT_CONSTRUCT for more information.


        Properties

        PropertySettingDescription
        NameStringA human-readable name for the component.
        Column MappingInput ColumnsSelect pairings for Source and Target columns. In the resulting Key:Value pairs, the values are taken from the Source columns and the Keys are the Target column names.
        Output SlotTextThe name of the outputted constructed variant column.

        Example

        In this example, we're going to be reformatting a table with many columns in 2 ways. Firstly, we want to collect some related columns together into small arrays. This can make the data a little easier to inspect and is generally best done with columns that you don't intend on regularly querying. Secondly, we will be collecting each row into a JSON format so that the data can be usefully exported into other tools expecting that format. For each of these tasks, we'll be using the Construct Variant component. The job layout is shown below.

        The original data is shown below. We'd like to group the columns YEAR, MONTH, DAYOFMONTH into a single column and DEPTIME, ARRTIME, ACTUALELAPSEDTIME into another column.

        We have a Create Variant component for each variant column we want to create. In this case, we're using Create Variant and Create Variant 2 named components from the canvas image above. They will give the new variant columns date and times as given in the Output Slot properties shown below.

        The columns to be brought into the new variant columns are specified in the Column Mapping property. The Source Column is name of the column in the data that will hold the variant values while the Target Column is the name of the key that value will be associated with.

        Sampling the Create Variant 2 component (being the last component in that branch of the job to affect the data) will show us the result of our transformations. Below we show the tidied up data.

        We also have another branch of our job that is transforming all of our data into a single variant column. This column will simply be a JSON-style record of the data on each row that can be exported and used by tools expecting data of that format. The sample is shown below.

        What's Next