Issue with MERGE INTO for first batch

[Joe1912]

I have source data with multiple rows and columns, 1 of column is city. 

I want to get unique city into other table by stream data from source table. So I trying to use merge into and foreachBatch with my merge function.  My merge condition is : On source.city = target.city. But then I realized that, in first batch, I will always get duplicated data becauce there is no data on target and duplicated city on first batch of source.

How can I resolve this ? thank you for supporting

[Kaniz · Accepted solution]

Hi @Joe1912 , 

Certainly, you can handle this issue by:

1. Create a temporary staging table for your streaming data.

2. In the first batch, load data into the staging table, including duplicates.

3. After each batch, remove duplicates from the staging table.

4. Use mergeInto to merge the source and deduplicated data in the staging table.

This approach ensures that the initial duplicates are removed before merging the data.