Skip to main content
If a row with the same key exists, it is updated. Otherwise, a new row is inserted. You can also limit updates to specific columns, so only selected values are changed in matching rows.

​
Parameters

ParameterDescription
Unique KeyColumn(s) used to match existing records in the target dataset. In many cases, the unique key will be equivalent to your table’s primary key, if applicable.
Use PredicateLets you add conditions that specify when to apply the merge.
Use a condition to filter data or incremental runsEnables applying conditions for filtering the incoming data into the table.
Merge ColumnsSpecifies which columns to update during the merge. If empty, the merge includes all columns.
Exclude ColumnsDefines columns that should be excluded from the merge operation.
(Advanced) On Schema ChangeSpecifies how schema changes should be handled during the merge process.
  • ignore: Newly added columns will not be written to the model. This is the default option.
  • fail: Triggers an error message when the source and target schemas diverge.
  • append_new_columns: Append new columns to the existing table.
  • sync_all_columns: Adds any new columns to the existing table, and removes any columns that are now missing. Includes data type changes. This option uses the output of the previous gem.

​
Example: Update vehicle type only

When an incoming VEHICLE_ID matches an existing one, vehicle type information is updated while registration dates remain unchanged. The merge column is explicitly set to TYPE.
Incoming table
VEHICLE_IDTYPE
101Tram
102Bus
104Bus
Existing table
VEHICLE_IDTYPEREGISTERED_AT
101Bus2023-12-01
102Train2023-12-02
103Bus2023-12-03
Updated table
VEHICLE_IDTYPEREGISTERED_AT
101Tram2023-12-01
102Bus2023-12-02
103Bus2023-12-03
104Busnull
Notice that only the TYPE column was updated for vehicles 101 and 102 while the REGISTERED_AT values remained unchanged. Additionally, note that vehicle 103 remained unchanged since it didn’t match a vehicle in the incoming data. Finally, vehicle 104 was inserted, and no data was added to the REGISTERED_AT column.