Upsert Row

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

Parameter	Description
Unique Key	Column(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 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.
Merge Columns	Specifies which columns to update during the merge. If empty, the merge includes all columns.
Exclude Columns	Defines columns that should be excluded from the merge operation.
(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: Upsert vehicle types

A vehicle registry uses vehicle_id as the merge key. The merge column is set to type, so matching vehicles have their type updated. Vehicles that do not exist in the target table are inserted as new rows.

Existing target table

VEHICLE_ID	TYPE	REGISTERED_AT
101	Bus	2023-12-01
102	Train	2023-12-02
103	Bus	2023-12-03

Incoming dataset

VEHICLE_ID	TYPE
101	Tram
102	Bus
104	Bus

Resulting target table

VEHICLE_ID	TYPE	REGISTERED_AT
101	Tram	2023-12-01
102	Bus	2023-12-02
103	Bus	2023-12-03
104	Bus	null

Because the merge column is set to TYPE, Prophecy updates only that column for matching rows. Columns that are not included in the merge remain unchanged. Rows that do not match the merge key are inserted as new rows. In this example, vehicles 101 and 102 have updated TYPE values, vehicle 103 remains unchanged, and vehicle 104 is inserted with a null value for REGISTERED_AT because that column was not included in the incoming dataset.

Overview

Sources and targets

Data processing

Reporting

Advanced

Parameters

Example: Upsert vehicle types

​Parameters

​Example: Upsert vehicle types

Parameters

Example: Upsert vehicle types