Table Anti Join

Description

Table Anti Join provides the unmatched set of items between two tables. This will return the list of items in the first table without matches in the second table. This can be quite useful for determining which records are present in one table but not another.

This operation could be accomplished by using outer joins and filtering on null values for the join; however, the Anti Join transform will perform this in a more efficient and obvious way.

This is a left-excluding join, not a left join

Table Anti Join keeps the rows of source A that have no match in source B. Its behavior is fixed by the step type, so there is no join-type selector. If you want a left join — every row of source A, with source B’s columns filled in where the keys match — use the Table Outer Join step and set its Join Type to Left. Because an anti-join returns only unmatched source A rows, its output is drawn from source A only; source B contributes the join keys but no output columns.

Anti join — keeps only the rows of source A with no match in source B (a left-excluding join). Output columns come from A only; B supplies the keys to test against.

Table Data Selection

Table Source

Specify the source data table by selecting it from the dropdown menu.

Source Columns

Specify any columns to be included here. Selecting the Inspect Source and Populate Source Mapping Table buttons will make these columns available for the join operation.

Select Subset of Source Data

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

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.

Table Output

Target Table

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.

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.

Join Map

Specify join conditions. Guess fills the Join Map from the first of these that the two tables’ columns can take:

• the keys of another join step in this project that joins the same two tables
• the keys of an approved metric that joins them
• the key columns set on one of the tables, matched by name in the other
• the columns with the same name in Table 1 and Table 2
• columns whose names differ only in capitals or underscores, or an id column matched to a column named for its table, such as customer_id for a table named Customers

A line under the map says which it used, until you change the map. Guess reads the tables’ columns, not their rows, so check the keys before you save. Inspect Source fills an empty Join Map the same way.

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 names to match from Table 1 to Table 2. To remove a field from the Join Map, simply right-click and select Delete.

Target Output Columns

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.

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

Stale Columns

A Join Map or Target Output Columns row can name a column that the step’s Source Columns tab no longer lists, for example after a source’s columns were 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 tells you which source’s list is missing the column.

A Target Output Columns row with a Constant, an Expression or a Count (including nulls) summary is never flagged, because the step doesn’t read the row’s source column. Nothing is checked against a source whose list is empty, for example one that was never inspected.

When you save a step that still has flagged rows, a Stale Columns Found dialog lists them by table 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 that source’s list on the Source Columns tab, or pick a column the list contains.

When you save, each source table is also checked as it is now. A column that a source’s list holds but its table doesn’t have opens a Missing Source Columns dialog naming the table and the column, with Save Anyway or Cancel. When rows are flagged as well, both appear together in the Stale Columns Found dialog. See Columns Missing From the Source Table.

An anti join outputs columns from source A only, so a Target Output Columns row taken from source B is flagged too, even when source B lists the column.

Output 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.