CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
dcrdev
dcrdev

Reputation: 137

Merging deltas with duplicate keys

I'm trying to perform a merge into a target table in our Snowflake instance where the source data contains change data with a field denoting the at source DML operation i.e I=Insert,U=Update,D=Delete.

The problem is dealing with the fact the log (deltas) source might contain multiple updates for the same record. The merge I've constructed bombs out complaining about duplicate keys.

I'm struggling to think of a solution without going the likes of GROUP BY and MAX on the updates. I've done a similar setup with Oracle and the AND clause on the MATCH was enough.

MERGE INTO "DB"."SCHEMA"."TABLE" t
USING (
    SELECT * FROM "DB"."SCHEMA"."TABLE_LOG" 
    ORDER BY RECORD_TIMESTAMP ASC
) s ON t.RECORD_KEY = s.RECORD_KEY
WHEN MATCHED AND s.RECORD_OPERATION = 'D' THEN DELETE
WHEN MATCHED AND s.RECORD_OPERATION = 'U' THEN UPDATE
    SET t.ID=COALESCE(s.ID,t.ID),
        t.CREATED_AT=COALESCE(s.CREATED_AT,t.CREATED_AT),
        t.PRODUCT=COALESCE(s.PRODUCT,t.PRODUCT),
        t.SHOP_ID=COALESCE(s.SHOP_ID,t.SHOP_ID),
        t.UPDATED_AT=COALESCE(s.UPDATED_AT,t.UPDATED_AT)
WHEN NOT MATCHED AND s.RECORD_OPERATION = 'I' THEN
    INSERT (RECORD_KEY, ID, CREATED_AT, PRODUCT,
            SHOP_ID, UPDATED_AT)
    VALUES (s.RECORD_KEY, s.ID, s.CREATED_AT, s.PRODUCT,
            s.SHOP_ID, s.UPDATED_AT);

Is there a way to rewrite the above merge so that it works as is?

Upvotes: 1

Views: 3711

Answers (1)

Rich Murnane
Rich Murnane

Reputation: 2920

The Snowflake docs show the ability for the AND case predicate during the match clause, it sounds like you tried this and it's not working because of the duplicates, right?

https://docs.snowflake.net/manuals/sql-reference/sql/merge.html#matchedclause-for-updates-or-deletes

There is even an example there which is using the AND command:

merge into t1 using t2 on t1.t1key = t2.t2key
when matched and t2.marked = 1 then delete
when matched and t2.isnewstatus = 1 then update set val = t2.newval, status = t2.newstatus
when matched then update set val = t2.newval
when not matched then insert (val, status) values (t2.newval, t2.newstatus);

I think you are going to have to get the "last record" per key and use that as your update, or process these serially which will be pretty slow...

Another thing to look at would be to try to see if you can apply the last_value( ) function to each column, where you order by your timestamp and partition over your key. If you do that in your inline view, that might work.

I hope this helps, I have a feeling it won't help much...Rich

UPDATE:

I found the following: https://docs.snowflake.net/manuals/sql-reference/parameters.html#error-on-nondeterministic-merge

If you run the following command before your merge, I think you'll be OK (testing required of course):

ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE=false;

Upvotes: 1

Related Questions