Hii All, several time i got Cannot perform Merge as multiple source rows matched do anyone have better idea how to resolve this problem. i use below command but still getting error ,i don’t have any timestamp column

%%sql
MERGE INTO delta.`refine_data` v
USING delta.`raw_data` u
ON v.userID=u.userID
WHEN MATCHED AND (u.__op = "u" or u.__op = "d")
THEN DELETE

Did you get a chance to read the very detailed error message and referenced page?

Did you checked if your source table have duplicated rows?

Yes I have duplicates rows in raw table then how to drop any idea

Any distinct command or sth like that can help you to get rid of duplicated rows.

But I don’t have any time stamp functions to remove duplicate exam can’t use window function

But I don’t have any time stamp functions to remove duplicate exam can’t use window function

if all the rows in your

refine

with the same id are the same for the remaining fields, you can simply run

distinct

or

dropDuplicates

... if they aren't exactly the same it'll be up to you how to handle them and consolidate them into a single row...,

MERGE

cannot decide this for you, hence the error

if i delete duplicate data from raw zone does it impact on refine streaming

you don't actually need to change the

raw_data

table, it can still have the original data with the duplicates, you can deduplicate as a processing step before MERGE, something like:

%%sql

CREATE TEMPORARY VIEW processed_raw_data AS (
    SELECT ...(deduplicate logic)... FROM delta.`raw_data`
)

MERGE INTO delta.`refine_data` v
USING processed_raw_data u
ON v.userID=u.userID
WHEN MATCHED AND (u.__op = "u" or u.__op = "d")
THEN DELETE

Oh ok I was doing only one mistake I was not duduplicating data from raw, you can see my query above

yupp, that's what the error message as well as the article linked in the error message says.