Data Warehouse Cloud data flow - Check target table for duplicate entries

[SchinniC]

Hello experts,

currently we are trying to insert data into a table via DWC data flow. Now we want to prevent that the data flow creates duplicate entries in the target table. Problem is that the field we have to check is NOT a primary key.

EDIT: The primary key UUID is also used saved on other tables ad referenced via association.

The db table itself has a UUID as primary key and a second data field "example" for a string 100. We need to check if there is already a entry in the table (e.g. "Foobar" in data field "example").

Which approach would you recommend inside the data flow?

BR,

Christian

[SchinniC]

Hello Sven,

thank you for your answer. I forgot to mention that the primary key UUID is also saved on other tables and connected via association. If we delete the original entry we also have to update the associated entries. If possible we would rather avoid doing this.

Thanks in advance,

Christian

[Sven_Knöpfler]

Hello Christian,

I am not sure if I understand the scenario completely. Could you maybe explain it with an example ?

Thanks a lot and kind regards

Sven

[Sven_Knöpfler]

But if you dont want to overwrite the existing record in case its already there, you could left join the target table with the source table on the key field and make a "is null"-filter on the left table (target) after the join.

[SchinniC]

Hello Sven,

maybe i can show you the (simplyfied) scenario:

We need to add additional data to the table "Test1". Before that we need to check if there is already an existing entry for "Example" (e.g. "TestExampleEntry"). We can´t delete the entry from "Test1" because the primary key "UUID" is used at the table "Test2".

The solution with the left join did come to our mind, but we unsure if that´s the best practice.

Thank you!

[Sven_Knöpfler]

Hello Christian,

thank you for your example. So the left join approach would be feasible from my point of view, but it would be interesting, what the other experts think about that.

Kind regards,

Sven

[Sven_Knöpfler]

Hello Christian,

you could use the delete mode in the data flow:

1. First you would delete all the records in the target that come from the source with the same field value in this one field.

2. After that you would upsert the new records in the target.

https://help.sap.com/viewer/c8a54ee704e94e15926551293243fd1d/cloud/en-US/ae4e3b9c121e4bcc9ec46ccbc98...

Does that work for you?

Kind regards

Sven