For rows in the target table that have keys that match rows in the incoming dataset, deletes matching rows from the existing table and replaces them. For incoming rows with new keys, inserts these normally. This ensures that updated records are fully replaced instead of partially updated.
This strategy helps when your unique key is not truly unique (multiple rows per key need to be
fully refreshed).
Parameters
| Parameter | Description |
|---|
| Unique Key | Column(s) used to match existing records in the target dataset. |
| Use Predicate | Lets you add conditions that specify when to apply the merge. |
| Use a condition to filter data or incremental runs | Enables applying conditions for filtering the incoming data into the table. |
| (Advanced) On Schema Change | Specifies 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: Refresh vehicle registry entries
If a vehicle already exists in the fleet, its old record is deleted and replaced with the incoming row. New vehicles are simply inserted.
Incoming table| VEHICLE_ID | TYPE | REGISTERED_AT |
|---|
| 201 | Train | 2024-01-15 |
| 203 | Bus | 2024-01-16 |
| VEHICLE_ID | TYPE | REGISTERED_AT |
|---|
| 201 | Train | 2023-12-01 |
| 202 | Tram | 2023-12-02 |
| VEHICLE_ID | TYPE | REGISTERED_AT |
|---|
| 201 | Train | 2024-01-15 |
| 202 | Tram | 2023-12-02 |
| 203 | Bus | 2024-01-16 |
201’s old record was completely replaced with the new data, vehicle 202 remained unchanged, and vehicle 203 was added as a new entry. 
