Unpivot

The Unpivot processor uses the Snowflake Unpivot function to rotate a table, converting the specified columns into rows. You can use this processor to convert a wide table into a narrower table.

When you configure the Unpivot processor, you indicate how to define the columns to unpivot. You can specify column names, strings included in the column names, or regular expressions that match the column names. You define a name for the new column that contains the names of the rotated columns, and a name for the new column that contains the values from the rotated columns.

The resulting data includes a separate row for each matching column.

Note: While you can use this processor to unpivot a pivoted table, the Unpivot processor does not undo aggregations performed by a Pivot processor.

For more information about the Snowflake Unpivot function, see the Snowflake documentation.

Example

Say you have a quarterly_sales table with the following data:

DEPT_ID DEPT Q1 Q2 Q3 Q4
1 tools 355 420 486 433
2 housewares 1088 980 866 945
3 garden 280 340 364 328
You want to rotate this data to show the quarterly sales by department without aggregating the results, so you configure the Unpivot processor as follows:
  • Column Match Criteria property: Column Name
  • Columns property: Q1, Q2, Q3, Q4
  • New Name Column property: QUARTER
  • New Value Column property: UNIT_SALES
The processor passes the following rows downstream:
DEPT_ID DEPT QUARTER UNIT_SALES
1 tools Q1 355
1 tools Q2 420
1 tools Q3 486
1 tools Q4 433
2 housewares Q1 1088
2 housewares Q2 980
2 housewares Q3 866
2 housewares Q4 945
3 garden Q1 280
3 garden Q2 340
3 garden Q3 364
3 garden Q4 328

Notice how the columns defined in the Columns property appear in the QUARTER column, and the value for each of the columns appears in the UNIT_SALES column. Also, each row from the original table generates four rows when unpivoted, one for each quarter.

Configuring an Unpivot Processor

Configure an Unpivot processor to rotate a table by converting specified columns into rows.

  1. On the General tab, configure the following properties:
    Table 1. General properties
    General property Description
    Name Stage name.
    Description Optional description.
    Cache Data Caches processed data.
  2. On the Unpivot tab, configure the following properties:
    Table 2. Unpivot properties
    Unpivot property Description
    Column Match Criteria Method to specify the columns to pivot:
    • Column Name - Select or specify individual column names.
    • Column Name Contains Text - Specify one or more strings in matching column names.
    • Column Name Prefix - Specify one or more prefixes in matching column names.
    • Column Name Suffix - Specify one or more suffixes in matching column names.
    • Column Name Matches Regex - Specify one or more regular expressions that match column names.
    Columns Columns in the incoming data to rotate into rows. Select or specify the columns to use.

    Available when specifying column names.
    Strings One or more strings to match with column names.

    When you enter multiple strings, the processor unpivots columns with names that include any of the specified strings.

    Available when matching using text.
    Prefixes One or more prefixes to match with column names.

    When you enter multiple prefixes, the processor unpivots columns with names that start with any of the specified prefixes.

    Available when matching using prefixes.
    Suffixes One or more suffixes to match with column names.

    When you enter multiple suffixes, the processor unpivots columns with names that end with any of the specified suffixes.

    Available when matching using suffixes.
    Regular Expressions One or more regular expressions to match with column names.

    When you enter multiple regular expressions, the processor unpivots columns with names that match any of the specified regular expressions.

    Available when matching using regular expressions.
    New Name Column Name for the new column that contains the names of the rotated columns.
    New Value Column Name for the new column that contains the values of the rotated columns.