Replication Flow Upsert Mode in SAP Datasphere

[PongsatornK]

There are two modes in the Replication Flow:

1. Initial

2. Initial & Delta

Due to the limitations of the source CDS view, we need to utilize the Initial Mode.

Refer to the doccument (SAP Datasphere > Replication Flow) 

The Truncate setting is relevant if the target structure already exists and contains data. Review the default setting and change it if required:

• If Truncate is marked for a database table, when you start the replication run, the system deletes the table content, but leaves the table structure intact and fills it with the relevant data from the source.

  If not, the system inserts new data records after the existing data in the target. For data records that already exist in the target and have been changed in the source, the system updates the target records with the changed data from the source using the UPSERT mode.

Therefore, we will not choose the truncate option and will utilize the upsert mode instead.

The first initial execution duration was 9 minutes and 28 seconds.

The duration of the second execution was 9 minutes and 34 seconds.

I have a few questions regarding this result.

1. Why did the duration of the second execution not decrease significantly compared to the first run?

2. Why was the initial data executed again during the second execution? Shouldn't the upsert mode have been utilized to only process the changed data? I noticed that the removal state was executed before the initial state."

Removal state (Second execution)

initial state (Second execution)

[Martin_Kuma]

Hi Pongsatorn, 

the UPSERT is SQL DML -> Do UPDATE, if fail, Do INSERT. Nothing to do with the logic of the TF. 

Only Delta will reduce the number of entries and thus time needed. In case of Initial, you will always fetch everything. 

As far as I know 🙂

Cheers,

Martin