topic Re: merge into deletes from SOURCE in Data Engineering

merge into deletes from SOURCE

gilo12 — Fri, 12 May 2023 20:42:22 GMT

I am using the following query to make an upsert:

MERGE INTO my_target_table AS target
USING (SELECT MAX(__my_timestamp) AS checkpoint FROM my_source_table) AS source
  ON target.name = 'some_name'
  AND target.address = 'some_address'
WHEN MATCHED AND source.checkpoint IS NOT NULL THEN
  UPDATE SET checkpoint = source.checkpoint
WHEN NOT MATCHED THEN
  INSERT (name, address, checkpoint)
  VALUES ('some_name', 'some_address', source.checkpoint)

Whenever it does 'insert', it also deletes from *my_source_table*. Any explanation why it deletes from *my_source_table* and can I avoid it, so the logic will stay the same, without anything being deleted from the source

Re: merge into deletes from SOURCE

Ryan_Chynoweth — Fri, 12 May 2023 21:36:36 GMT

Can you provide the table history of your source table? Your logic appears correct. The source history should tell us if a delete is actually happening.

Or at least a before and after state of your table

Re: merge into deletes from SOURCE

gilo12 — Sat, 13 May 2023 01:46:46 GMT

I was using a view for my_source_table, once I changed that to be a table the issue stoped.

That unblocked me, but I think Databricks has a bug with using MERGE INTO from a VIEW

Re: merge into deletes from SOURCE

Ryan_Chynoweth — Fri, 09 Jun 2023 16:44:34 GMT

It should work the same whether it is a view of a table. But I am confused on how it was deleting data from a view.

In any case, happy this is resolved! And you are unblocked. If you believe there is a bug please provide more details on how we can replicate the issue and we can look into it.