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. The Join Expression property defines the matching condition, When Matched defines the action on matched rows, Update Mapping maps which columns to overwrite, and Include Not Matched optionally inserts unmatched rows.
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

string
required
A human-readable name for the component.
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.
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.
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.
drop-down
required
The table you want to update.
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.
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.
column editor
required
The condition used to match rows in the source data to rows in the target table, determining which rows to update. This is equivalent to the ON clause in a SQL MERGE INTO statement. Each expression is evaluated as true or false—rows where the condition is true are considered a match.Reference columns using the aliases set in Source Alias and Target Alias. Keys must uniquely identify rows and must not be NULL, otherwise you’ll get unexpected results.Examples:
  • Simple key match: "input"."customer_id" = "target"."customer_id"
  • Date range match: "input"."event_date" BETWEEN "target"."start_date" AND "target"."end_date"
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.
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.
boolean
required
If Yes, allow non-matched data to continue through to the output. The columns this data is written to are defined in the Insert Mapping property.
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.