Table Update

Description

Updates the rows that match your filter and writes the result to a target table. Every source row reaches the target — the rows matching the filter with your new values, and every other row exactly as it was. The row count of the target always equals the row count of the source.

The filter chooses which rows are changed, not which rows come back. That is what separates this step from Table Extract, where the filter removes the rows that don’t match.

This step no longer changes the source table

It used to update the source table in place. It now leaves the source untouched and writes to a separate target, so the original data stays available for comparison and the step runs on every warehouse — including those that don’t allow a table to be changed in place. Steps saved before this change need a target table selected before they will run, and any step that read the source expecting to see the updated rows must be pointed at the new target instead.

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

The target must be a different table from the source — this step reads every source row and writes it out, so pointing it back at its own source would append the result to the data it just read.

The source must be a table, not a view

Views can be chosen as the target (and are a good choice here — a view stores no data and re-evaluates against the source each time it is read), but not as the source: this step takes the target’s column list from the source table’s own schema, which a view does not carry.

The target’s columns always mirror the source’s, so you never choose them. Columns you don’t update are carried through unchanged, including any column added to the source after this step was last opened.

Columns to Update

On the Table Data Selection tab, add a row for each column you want to change and set exactly one of:

• Constant — a fixed value.
• Expression — a calculation, which may reference other columns of the same row.
• Set Null — clears the column.

Columns you don’t list are left alone.

Expressions read the row's original values

An expression sees the row as it was before the update, so two columns can safely swap values in a single step.

Data Filters for Update

On the Data Filters for Update tab, enter the condition identifying the rows to change. Leave it empty to update every row.

A row whose condition cannot be evaluated — most often because a column it tests is null — is treated as not matching, and passes through unchanged. This matches how a database evaluates the same condition.

Examples

In this example the Account is set to 41000 where the Version equals “Actual” in “Ledger Value to be allocated”. Rows with any other Version reach the target with their original Account.

The Table Data Selection tab, with Account set to a constant:

The Data Filters for Update tab, holding the condition that picks the rows to change: