I have a delta table in Azure and a dataframe. The dataframe has duplicates in it and want it so that those duplicates aren't added to the delta table when they are merged. I know that I can remove the duplicates before the merger, but I want to know if it's possible to do it during the merger. I have an example below.
Org Delta Table
1, "abc", 45678
2, "def", 89076
3, "lop", 90678
New incoming data - dataframe
4, "abc", 98067 --- partition 1
4, "abc", 98067 --- partition 2
4, "abc", 98067 --- partition 3
Merge two tables as shown
Final table
1, "abc", 45678
2, "def", 89076
3, "lop", 90678
4, "abc", 98067 --- THIS ROW SHOULD EXIST ONLY ONCE
Edit: I have tried omitting the “whenMatched” when merging but it keeps the duplicates and when i add “when MatchedDelete” it deletes the whole table.