Table Extract

Description

Used to extract data from an existing Analyze data table into another data table. Examples include, but are not limited to, the following:

• Sort
• Group
• Summarization
• Filter/Subset Rows
• Drop Extra Columns
• Math Operations
• String Operations

Note

There is no functions exclusive to this transform. All sorting, grouping, filtering, etc. can be performed in any other transform with the Table Data Selection and Data Filters tabs.

Extract Parameters

Source and Target

To establish the source and target tables, first select the data table to be extracted from using the Source Table dropdown menu. Next, 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.

Note

Use tables for key steps in your workflows where data validation or the ability to perform ad-hoc analytics will be necessary. For all other steps use views to decrease the overall workflow calculation time. It’s possible to change a table to a view and vice versa so you can always update the table target type at a later date.

Table Data Selection

Data Mapper Configuration

The Data Mapper is used to map columns from the source data to the target data table.

Inspection and Populating the Mapper

Using the Inspect Source menu button provides additional ways to map columns from source to target:

• Populate Both Mapping Tables: Propagates all values from the source data table into the target data table. This is done by default.
• Populate Source Mapping Table Only: Maps all values in the source data table only. This is helpful when modifying an existing workflow when source column structure has changed.
• Populate Target Mapping Table Only: Propagates all values into the target data table only.

If the source and target column options aren’t enough, other columns can be added into the target data table in several different ways:

• Propagate All will insert all source columns into the target data table, whether they already existed or not.
• Propagate Selected will insert selected source column(s) only.
• Right click on target side and select Insert Row to insert a row immediately above the currently selected row.
• Right click on target side and select Append Row to insert a row at the bottom (far right) of the target data table.

Danger

Selecting Propagate All will duplicate columns if they already exist in the target list

Deleting Columns

To delete columns from the target data table, select the desired column(s), then right click and select Delete.

Source Columns That Are No Longer Listed

A target row can name a Source Column that the source data table no longer lists, for example after the source was inspected again. The step never removes such a row for you. Instead, the column name is shown in red behind a ⚠ warning sign, and hovering over it says the column is not in the source columns. In a step with several tables, such as a union, each table is checked against its own source columns.

A row with a Constant, an Expression or a Count (including nulls) summary is never flagged, because the step doesn’t read its source column. Nothing is flagged while the source data table lists no columns, for example before the source is first inspected.

When you save a step that still has flagged rows, a Stale Columns Found dialog lists them under the tab or table they are on, and offers three choices:

• Remove All and Save — remove the flagged rows, then save.
• Save Anyway — save the step as it is.
• Cancel — go back to the step without saving.

To keep a row and clear its warning, add the column back to the source data table, or pick a column the source data table lists.

Columns Missing From the Source Table

When you save, the source data table itself is checked as it is now. A column the step lists in its source columns but the table doesn’t have, for example one an earlier step renamed or dropped, means the step’s columns are out of date, and a step that reads such a column fails when it runs. Saving then opens a Missing Source Columns dialog that names each table and the columns it lacks, with two choices:

• Save Anyway — save the step as it is.
• Cancel — go back to the step without saving.

Nothing is removed for you, because the table may be what is wrong, and it can change again before the step runs. When rows are also flagged as no longer listed, both appear together in the Stale Columns Found dialog, and Remove All and Save removes only the flagged rows. While the source columns list is empty, the columns the step maps are checked instead, except rows with a Constant, an Expression or a Count (including nulls) summary, which read no column.

A source named with a variable, such as {year}/Sales, is checked as the variable stands when you save, and the dialog names the table it checked, such as 2026/Sales (from {year}/Sales). A variable that a loop sets holds the value from its last iteration, so a loop over tables of different shapes can warn about a table that the next run won’t read. A table that doesn’t exist yet, for example one an earlier step in the workflow creates, isn’t checked, and neither is a source whose variable has no value, a table the step finds by searching when it runs, or a table that can’t be read at the moment.

Column Data Type

Every target column carries a Type. A column propagated from the source keeps the source column’s type; a row you add by hand starts as Text, and you set Type to say what it should actually be — a column you intend to sum or sort needs its numeric or date type, because text sorts and sums as text.

A step saved with a column that has no type at all still runs. The type is worked out when the step starts, from the source column that column reads. Where there is nothing to read it from, it is treated as text: an Extract SQL step’s columns come from the query you wrote rather than from a mapped source, and where several sources carry the same column name under types that disagree, text is used rather than guessing one of them — a wrong guess produces a conversion the warehouse can reject, and anything converts to text.

Caution

Multi-Table Join is the exception, deliberately. It refuses to run and names the column instead, which is more useful than a guess in a step joining several tables. Set that column’s Type and re-save.

Setting Type yourself is still how you say what a column should be. Leaving it unset means accepting whatever the source column happens to be.

Changing Column Order

To rearrange columns in the target data table, select the desired column(s). You can use either:

• Bulk Move Arrows: Select the desired move option from the arrows in the upper right
• Context Menu: Right clikc and select Move to Top, Move Up, Move Down, or Move to Bottom.

Reduce Result to Distinct Records Only

To return only distinct options, select the Distinct menu option. This will toggle a set of checkboxes for each column in the source. Simply check any box next to the corresponding column to return only distinct results.

Depending on the situation, you may want to consider use of Summarization instead.

The distinct process retains the first unique record found and discards the rest. You may want to apply a sort on the data if it is important for consistency between runs.

Danger

Selecting all columns to determine distincriveness might make it appear as if it isn’t being applied. Select only the columns you feel define the distictivness of the data.

Aggregation and Grouping

To aggregate results, select the Summarize menu option. This will toggle a set of select boxes for each column in the target data table. Choose an appropriate summarization method for each column.

When Inspect Source fills the columns, a number column starts as Group By rather than Sum if its name reads as an identifier, such as id, customer_id, Order No or CustomerKey, if its table sets it as a key, or if the join uses it as a key.

• Group By
• Sum
• Min
• Max
• First
• Last
• Count
• Count (including nulls)
• Mean
• Standard Deviation
• Sample Standard Deviation
• Population Standard Deviation
• Variance
• Sample Variance
• Population Variance
• Advanced Non-Group_By

Note

When using aggregation, all columns must have a summarization type specified

For advanced data mapper usage such as expressions, cleaning, and constants, please see the Advanced Data Mapper Usage

Columns Named at Run Time

Select Also carry columns named at run time to pass through, unchanged, every source column the step doesn’t list, as the table has them when the step runs. Use it when an earlier step creates columns whose names depend on the data, such as a pivot, so you can’t list them when you build the step. Leave it off to write only the columns you list.

Data Filters

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.

Select Subset Of Data

This filter type provides a way to filter the inbound source data based on the specified conditions.

Apply Secondary Filter To Result Data

This filter type provides a way to apply a filter to the post-transformed result data based on the specified conditions. The ability to apply a filter on the post-transformed result allows for exclusions based on results of complex calcuations, summarizaitons, or window functions.

Final Data Table Slicing (Limit)

The row slicing capability provides the ability to limit the rows in the result set based on a range and starting point.

Note

For consistency, results that are sliced should have a sort specified

Filter Syntax

The filter syntax utilizes Python SQLAlchemy which is the same syntax as other expressions.

View examples and expression functions in the Expressions area.

Examples