Skip to content

Used to convert short, wide data tables into long, narrow data tables. Selected columns are transposed, with the column names converted into values across multiple rows.

Perhaps the easiest example to understand is to think of a data table with months listed as column headers:

Melting this data table would convert all of the month columns into rows.

wide (before)ProductJanFebWidget1020Gadget58meltlong (after)ProductMonthValueWidgetJan10WidgetFeb20GadgetJan5GadgetFeb8
Melt (unpivot) turns wide into long — the Jan/Feb columns collapse into Month + Value rows, keyed by the columns you leave alone.

By specifying which columns to transpose and which columns to leave alone, this becomes a powerful tool. Making this conversion in other ETL tools could require a dozen more steps.

Table Melt Source Target

To establish the source and target, first select the data table to be extracted from the Source Table dropdown menu.

Target Table

Table Target

To establish the target table select either an existing table as the target table using the Target Table dropdown or click on the green “+” sign to create a new table as the target.

Table Creation

When creating a new table you will have the option to either create it as a View or as a Table.

Views:

Views are useful in that the time required for a step to execute is significantly less than when a table is used. The downside of views is they are not a useful for data exploration in the table Details mode.

Tables:

When using a table as the target a step will take longer to execute but data exploration in the Details mode is much quicker than with a view.

When the Step Runs

Table Extract, Table Lookup, Table Inner Join, Table Cross Join, Table Outer Join, Table Anti Join, Multi-Table Join, Table Union Distinct, Table Union All, Table Pivot, Table Melt, Table In-Place Delete, Table Upsert and Table Append check their source tables when they run, before they write anything. The check compares the query the step built with each source table as it is at that moment:

  • A column the step reads that the table doesn’t have, a join key the table doesn’t have or that isn’t in the step’s source columns, or a source table the step reads that hasn’t been built stops the step. One error names every problem, each with its own fix. A target table that already existed is left as it was, and one the step had to create stays empty.
  • A column the step lists but doesn’t read, and the table no longer has, adds a warning. The step runs without it.
  • Table Union Distinct and Table Union All are the exception for a source table that hasn’t been built: they leave it out, without a warning, and combine the rest, as they did before. If none of their source tables has been built, they finish with a warning instead of failing.

A step whose target is a view only creates the view, so the columns and join keys it lists are checked against its tables only where the warehouse would refuse to create that view anyway. Elsewhere, only a source table that hasn’t been built or a join key the step doesn’t list stops it, and no warning is added.

Where it can, the error names the step that builds a table, and when the table has a column whose name is close to a missing one, such as one that differs only in case, it names that column too.

Table Pre-Melt

This section is a bit different from the standard Table Data Selection. Basically this is used to specify which columns are to be used in the Melt operation. This includes ID columns and Variable/Value columns.

Saving with a source table chosen but no source or no target columns fills the empty side from the source, as Populate Both Mapping Tables would.

To allow for maximum flexibility, data filters are available on the source data and the target data. For larger data sets, it can be especially beneficial to filter out rows on the source so the remaining operations are performed on a smaller data set.

Any valid Python expression is acceptable to subset the data. Please see Expressions

for more details and examples.

Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples

To limit the data, simply check the Apply Row Slicer box and then specify the following:

  • Initial Rows to Skip: Rows of data to skip (column header row is not included in count)
  • End at Row: Last row of data to include. This is different from simply counting rows at the end to drop

Table Melt Layout

There is a Guess Layout button available to allow Analyze a first crack at specifying ID columns. By default, all text (data type of String) columns are placed in the Keys section. Numeric columns are not placed into Keys by default, but they are allowed to be there based on the model’s needs.

ID columns are the columns which remain in tact. These columns are effectively repeated for every instance of a variable/value combination. For a monthly table, this would result in 12 repetitions of ID columns.

ID columns can be added automatically or manually. To add the columns automatically, use the aforementioned Guess Layout button. To add additional columns manually, right click anywhere in the section and select either Insert Row or Append Row, to add a row prior to the currently selected row or to add a row at the end, respectively. Then, type the column name to use as an ID.

To remove a field from the IDs, simply right-click and select Delete.

There are 2 values to specify. Both of these values will become column names in the target data table.

  • Variable Column Name: As specified in the transform, The variable names are derived from the current source column names. Essentially, specify a column name which will represent the data originally represented in the source data table columns.
  • Value Column Name: Specify a column name to represent the data represented within the source data table. Typically this will be a numerical unit: Dollars, Pounds, Degrees, Percent, etc.

More Melt Options has these fields:

  • Also melt every other column the table has when the step runs (melt_all_columns) — also melt every column the source table has when the step runs that the step does not list and that is not an ID column, in the order the table has them.
  • Position Column Name (position_name) — also write a column of this name holding the 0-based position of each row’s value column among the value columns.
  • Type Column Name (type_name) — also write a column of this name holding the data type of each row’s value column, such as integer, numeric, text, boolean, date or timestamp.

In the abouve documentation.