Deltalake error- MERGE destination only supports Delta sources

Question

I am trying to implement scd-type-2 in delta lake and i am getting the following error- "MERGE destination only supports Delta sources".

Below is the snippet code i am executing.

MERGE INTO stageviews.employeetarget t
            USING (
                  -- The records from the first select statement, will have both new & updated records
                  SELECT id as mergeKey, src.*
                  FROM stageviews.employeeupdate src
                  UNION ALL
                  -- Identify the updated records & setting the mergeKey to NULL forces these rows to NOT MATCH and be INSERTED into target.
                  SELECT NULL as mergeKey, src.*
                  FROM stageviews.employeeupdate src JOIN stageviews.employeetarget tgt
                  ON src.id = tgt.id 
                  WHERE tgt.ind_flag = "1"
                  AND sha2(src.EmployeeName,256) <> sha2(tgt.EmployeeName ,256)
                  ) as s
ON t.id = s.mergeKey
WHEN MATCHED AND 
  ( t.ind_flag = "1" AND sha2(t.EmployeeName,256) <> sha2(s.EmployeeName ,256) ) THEN  
  UPDATE SET t.ind_flag = "0", t.eff_end_date = current_date()-1
WHEN NOT MATCHED THEN 
  INSERT(t.Id,t.EmployeeName,t.JobTitle,t.BasePay,t.OvertimePay,t.OtherPay,t.Benefits,t.TotalPay,t.TotalPayBenefits,t.Year,t.Notes,t.Agency,t.Status,t.ind_flag,t.create_date,t.update_date,t.eff_start_date,t.eff_end_date)
  values(s.Id,s.EmployeeName,s.JobTitle,s.BasePay,s.OvertimePay,s.OtherPay,s.Benefits,s.TotalPay,s.TotalPayBenefits,s.Year,s.Notes,s.Agency,s.Status,s.ind_flag,
  current_date(),current_date(),current_date(),to_date('9999-12-31'))

score 1 · Accepted Answer · answered Sep 11 '20 at 06:29

Unfortunately, Databricks only supports updates for delta (delta lake) tables.

The error message Error in SQL statement: AnalysisException: MERGE destination only supports Delta sources indicates that you try the update on a non-delta-table.

Merge a set of updates, insertions, and deletions based on a source table into a target Delta table.

Reference: Azure Databricks - Merge and SCD Type 2 using Merge.

Deltalake error- MERGE destination only supports Delta sources

1 Answers1