Table Append

Description

Adds rows from a source table to the end of an existing target table. Existing rows in the target are preserved; only new rows are inserted. Use this when you have data arriving incrementally (e.g., daily extracts, monthly load files) and want to grow the target over time rather than replacing it.

If you need to deduplicate as you append, follow with a Table Union Distinct step, or use Table Upsert if some rows update existing records by key.

Load 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 an existing table as the target table using the Target Table dropdown.

Table Data Selection

When configuring the Data Mapper the columns in the source table must be mapped to a column in the target table.

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

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.

What’s Checked When You Save

• The mapping fits an existing target table. When the target table already exists, the step can’t map a column the table doesn’t have, or give a column a different type from the table’s. Numeric and currency count as the same type. A table column the mapping leaves out is allowed, and is left empty in the appended rows. The refusal names each difference, for example The target columns must match the existing target table ‘sales’. Types differ: amount (table: numeric, step: text). A target table that doesn’t exist yet, or one named with a variable, is checked when the step runs.
• An empty mapping is filled for you. 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.

Examples