POST_START

Upserting Data with Delta MERGE

Introduction

I recently needed to synchronize customer data between two tables in my Delta Lake environment. One table, called customers, was the source of truth, and the other, customer_updates, contained new or modified records. The challenge was to efficiently update existing records and insert new ones in a single operation. I learned that Delta Lake’s MERGE command is the perfect tool for this kind of upsert operation.

Understanding the Problem

I started by examining the structure of the customers table. It contained customer IDs, names, and contact information. The customer_updates table had the same structure, but with some records that had been modified and others that were entirely new. My goal was to ensure that the customers table reflected the latest data from customer_updates.

I realized that using UPDATE and INSERT statements separately would be inefficient and could lead to data inconsistencies. I needed a way to handle both operations in one go. That’s when I remembered that Delta Lake supports the MERGE command, which allows for upsert operations in a single statement.

Executing the MERGE Operation

I decided to use the MERGE command to update existing records and insert new ones. Here’s the SQL I ran:

MERGE INTO training.sales.customers AS target 
USING training.sales.customer_updates AS source 
ON target.customer_id = source.customer_id 
WHEN MATCHED THEN UPDATE SET * 
WHEN NOT MATCHED THEN INSERT *;

I saw a representative result like this:

This output confirmed that the operation was successful. I noticed that the MERGE command matched rows based on the customer_id and applied the updates. For rows that didn’t match, it inserted the new records into the customers table.

Verifying the Results

To ensure the data was correct, I ran a few SELECT queries on the customers table. I checked for existing records and confirmed that their values had been updated. I also verified that new records from customer_updates were present in the customers table.

I noticed that the MERGE command preserved the data types and structure of the original table, which was important for maintaining data integrity. It also handled all the necessary operations in a single step, which improved the performance and reliability of the data synchronization process.

Conclusion

Using Delta Lake’s MERGE command allowed me to efficiently upsert data between tables. This approach saved time and reduced the risk of data inconsistencies. I now understand that MERGE is a powerful feature for maintaining data accuracy and synchronization in a Delta Lake environment.