Transformations
Update Strategy
Adds a column telling a database load what to do with each row: I to insert, U to update, D to delete.
When to use it
Use it before a database output or the CDC Apply node, when the load must know per row whether it is new, a change or a removal — for example upserting a daily extract by customer id.
In a pipeline
File InputUpdate StrategySQL Server Output
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| Strategy | select | — | insert |
| Key columns | select | — | Select key columns… |
| Strategy column name | text | — | Strategy |
Example
Upsert by customer
- Strategy=Upsert — first row of a key I, later rows U
- Key columns=CustomerId
| CustomerId | City |
|---|---|
| C-7 | Leeds |
| C-9 | York |
| C-7 | Hull |
| CustomerId | City | Strategy |
|---|---|---|
| C-7 | Leeds | I |
| C-9 | York | I |
| C-7 | Hull | U |
Worth knowing
- The tokens are always I, U and D — the CDC Apply node reads exactly these from this column.
- Upsert compares rows within this run only: the first row of each key is I, later rows of the same key are U.
- To mark deletions, filter the rows to delete first (Filter Rows or Minus Rows) and run them through an Update Strategy set to Delete.