<\/p>\n\n\n\n
This tutorial covers how to perform upserts (MERGE)<\/strong> in Delta tables on Databricks, with both hard deletes<\/strong> and soft deletes (using SCD1 style)<\/strong>.<\/p>\n\n\n\n In Delta Lake, the MERGE<\/strong> operation (also called UPSERT<\/strong>) lets you:<\/p>\n\n\n\n \ud83d\udc49 This is the same concept as Slowly Changing Dimension Type 1 (SCD1)<\/strong> in data warehousing.<\/p>\n\n\n\n We\u2019ll use an EMP<\/strong> table as the target.<\/p>\n\n\n\n This table will hold updates and new rows<\/strong>.<\/p>\n\n\n\n \ud83d\udc49 Now we have 3 updates + 1 new record.<\/p>\n\n\n\n Update existing employees, insert new ones.<\/p>\n\n\n\n \ud83d\udccc Results:<\/p>\n\n\n\n Check:<\/p>\n\n\n\n Suppose source doesn\u2019t have emp_id 103 and 105. \ud83d\udccc emp_id 103, 105 \u2192 deleted.<\/p>\n\n\n\n Check:<\/p>\n\n\n\n Instead of deleting, we mark records inactive<\/strong>.<\/p>\n\n\n\n \ud83d\udc49 All existing rows get \ud83d\udccc Behavior:<\/p>\n\n\n\n Check:<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" This tutorial covers how to perform upserts (MERGE) in Delta tables on Databricks, with both hard deletes and soft deletes (using SCD1 style). 1. \ud83d\udd39 Introduction In… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-783","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/783","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=783"}],"version-history":[{"count":1,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/783\/revisions"}],"predecessor-version":[{"id":784,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/783\/revisions\/784"}],"wp:attachment":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=783"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=783"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=783"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\n\n\n1. \ud83d\udd39 Introduction<\/h2>\n\n\n\n
\n
\n\n\n\n2. \ud83d\udd39 Setup: Create EMP (target) table<\/h2>\n\n\n\n
CREATE TABLE IF NOT EXISTS dev.bronze.emp (\n emp_id INT,\n emp_name STRING,\n dept_code STRING,\n salary DOUBLE\n);\n\n-- Insert initial data\nINSERT INTO dev.bronze.emp VALUES\n(101, 'Alice', 'D101', 10000),\n(102, 'Bob', 'D102', 12000),\n(103, 'Charlie', 'D101', 11000),\n(104, 'David', 'D103', 9000),\n(105, 'Eve', 'D104', 9500);\n\n-- Check data\nSELECT * FROM dev.bronze.emp;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n3. \ud83d\udd39 Create EMP_UPDATES (source) table<\/h2>\n\n\n\n
CREATE TABLE dev.bronze.emp_updates (\n emp_id INT,\n emp_name STRING,\n dept_code STRING,\n salary DOUBLE\n);\n\n-- Insert updates (existing employees with new salary)\nINSERT INTO dev.bronze.emp_updates VALUES\n(101, 'Alice', 'D101', 15000),\n(102, 'Bob', 'D102', 16000),\n(104, 'David', 'D103', 17000);\n\n-- Insert a new employee\nINSERT INTO dev.bronze.emp_updates VALUES\n(106, 'Rohan', 'D103', 10000);\n\n-- Check source data\nSELECT * FROM dev.bronze.emp_updates;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n4. \ud83d\udd39 MERGE for Upserts<\/h2>\n\n\n\n
MERGE INTO dev.bronze.emp AS e\nUSING dev.bronze.emp_updates AS u\nON e.emp_id = u.emp_id\n\nWHEN MATCHED THEN\n UPDATE SET e.salary = u.salary\n\nWHEN NOT MATCHED THEN\n INSERT *;\n<\/code><\/pre>\n\n\n\n\n
SELECT * FROM dev.bronze.emp;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n5. \ud83d\udd39 Hard Deletes (Remove missing records)<\/h2>\n\n\n\n
We want to delete them from target<\/strong>.<\/p>\n\n\n\nMERGE INTO dev.bronze.emp AS e\nUSING dev.bronze.emp_updates AS u\nON e.emp_id = u.emp_id\n\nWHEN NOT MATCHED BY SOURCE THEN\n DELETE;\n<\/code><\/pre>\n\n\n\nSELECT * FROM dev.bronze.emp;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n6. \ud83d\udd39 Soft Deletes (SCD1 style)<\/h2>\n\n\n\n
Step 1: Add column<\/h3>\n\n\n\n
ALTER TABLE dev.bronze.emp ADD COLUMNS (is_active STRING);\n<\/code><\/pre>\n\n\n\nNULL<\/code> by default.<\/p>\n\n\n\n
\n\n\n\nStep 2: Modify MERGE for soft delete<\/h3>\n\n\n\n
MERGE INTO dev.bronze.emp AS e\nUSING dev.bronze.emp_updates AS u\nON e.emp_id = u.emp_id\n\nWHEN MATCHED THEN\n UPDATE SET e.salary = u.salary, e.is_active = 'Y'\n\nWHEN NOT MATCHED THEN\n INSERT (emp_id, emp_name, dept_code, salary, is_active)\n VALUES (u.emp_id, u.emp_name, u.dept_code, u.salary, 'Y')\n\nWHEN NOT MATCHED BY SOURCE THEN\n UPDATE SET e.is_active = 'N';\n<\/code><\/pre>\n\n\n\n\n
SELECT * FROM dev.bronze.emp;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n7. \ud83d\udd39 Benefits of MERGE<\/h2>\n\n\n\n
\n
\n\n\n\n\u2705 Final Recap<\/h1>\n\n\n\n
\n
WHEN NOT MATCHED BY SOURCE THEN DELETE<\/code>).<\/li>\n\n\n\nis_active<\/code>) and mark inactive instead of removing.<\/li>\n<\/ul>\n\n\n\n
\n\n\n\n