Skip to main content
The Table Update transformation component lets you update a set of existing records in a target table. The component attempts to match rows in your input dataset with rows in the target table, and updates selected columns in the target row wherever a match is found. The match is based on matching keys in the input dataset and the target tables. It’s very important that the keys uniquely identify the rows, and that the keys aren’t NULL. Successful validation of this component ensures the target table exists and the target columns have been found. However, data is only written to the table when the pipeline containing the table update is actually run. Most potential problems are avoided by a successful validation; however, run-time errors can still occur during execution. This component is equivalent to using the SQL MERGE INTO statement.
There are other components that can write data to a table and may better suit your use case:
  • To output data to a new table, use Rewrite Table.
  • To output data to an existing table, appending to or replacing the current contents, use Table Output.

​
Use case

Table Update is typically used in pipelines when you’re working with slowly changing dimensions, CDC (change data capture), or data harmonization. Some typical examples of use are:
  • Overwriting existing attributes in a table row when changes are detected. For example, when a customer updates their email address or phone number, you need to update that single field without overwriting the rest of the data.
  • Change data capture (CDC) handling. For example, ingesting change logs from an upstream system and reflecting those changes in your warehouse table.
  • Synchronizing reference or lookup tables. For example, updating a product description based on a trusted source feed.

​
Properties

​
Name
string
required
A human-readable name for the component. When you validate the component, this will take the name of the selected Target Table, but can be changed if required.
​
Warehouse
drop-down
required
The Snowflake warehouse used to run the queries. The special value [Environment Default] uses the warehouse defined in the environment. Read Overview of Warehouses to learn more.
​
Database
drop-down
required
The Snowflake database. The special value [Environment Default] uses the database defined in the environment. Read Databases, Tables and Views - Overview to learn more.
​
Schema
drop-down
required
The Snowflake schema. The special value [Environment Default] uses the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.
​
Target Table
drop-down
required
The table you want to update.
​
Target Alias
string
required
The alias for the target table. An alias allows a table to be referred to and is usually a name that is typically shorter and simpler than its actual name.
​
Source Alias
string
required
The alias for the source table. An alias allows a table to be referred to and is usually a name that is typically shorter and simpler than its actual name.
​
Join Expression
column editor
required
A list of expressions specifying how each join is performed. Each expression must be valid SQL and can use all the built-in Snowflake functions. There is exactly one expression for each JOIN, and the result of the expression is evaluated as true or false, which indicates whether the two records being compared ‘match’. Often this will be a simple ‘equality’ condition, but it could be more complex, for example, where a date falls within a start/end date range.
​
When Matched
column editor
Select a case as previously defined in the Join Expression property. Add as many rows to the editor as you need, one per case. Choose an operation for when the corresponding case occurs according to the join expression.
  • Delete: Completely remove the row from the output table if the case is matched.
  • Update: Output the data as expected if a match is found.
​
Update Mapping
column editor
required
The input column name from the matched input flow. Add as many rows to the editor as you need, one per input column. Property only available when a matched case results in an update.The name of the output column that the corresponding matched input is written to. This can be the same name as the input column if desired.
​
Include Not Matched
boolean
required
If Yes, allow non-matched data to continue through to the output. The columns this data is written to is defined in a new property, ‘Insert Mapping’, described above.
​
Insert Mapping
column editor
required
The input column name from the unmatched input flow. Add as many rows to the editor as you need, one per input column. Property only available when an unmatched case is included.The name of the output column that the corresponding unmatched input is written to. This can be the same name as the input column if desired.