POST_START
<h1>Upserting Data with Delta MERGE</h1>

<p>
I'm working on a project to keep our customer data up to date in the Delta table <code>training.sales.customers</code>. Every day, we receive new data in the <code>training.sales.customer_updates</code> table, and we need to make sure that any changes to existing customers are reflected, while new customers are added. I remember that Delta Lake has a powerful <code>MERGE</code> operation that allows us to do both updates and inserts in a single statement. So I decided to use the <code>MERGE INTO</code> command to handle this upsert scenario.
</p>

<h2>Understanding the Merge Operation</h2>

<p>
I started by reviewing the schema of the two tables. The <code>training.sales.customers</code> table contains all our customer records, including fields like <code>customer_id</code>, <code>name</code>, and <code>email</code>. The <code>training.sales.customer_updates</code> table has the same columns, but it only contains the latest updates for specific customers. I needed to make sure that if a customer exists in both tables, their data is updated, and if they don't, they are inserted into the main table.
</p>

<p>
I recalled that the <code>MERGE INTO</code> statement in Delta Lake allows us to perform both <code>UPDATE</code> and <code>INSERT</code> operations in one go. The syntax is straightforward, and it's designed to handle the upsert pattern efficiently.
</p>

<h2>Writing the Merge Statement</h2>

<p>
I wrote the following SQL statement to perform the upsert operation:
<pre><code>
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 *
</code></pre>
</p>

<p>
This statement uses the <code>training.sales.customer_updates</code> table as the source and <code>training.sales.customers</code> as the target. The <code>ON</code> clause defines the condition for matching rows—here, it's based on the <code>customer_id</code> field. When rows match, the <code>UPDATE SET *</code> clause updates all columns in the target table with the values from the source. If there's no match, the <code>INSERT *</code> clause adds the new customer records to the target table.
</p>

<h2>Running the Merge Statement</h2>

<p>
I executed the <code>MERGE</code> statement in my Databricks notebook. I made sure to use the correct database and table names, and I double-checked the <code>ON</code> condition to ensure it correctly identifies matching customer records. I also verified that the source table contains only the updated records and no duplicates.
</p>

<p>
As the statement ran, I monitored the progress through the notebook's output and the Databricks UI. I noticed that the operation was efficient and completed in a matter of seconds, even though the source table had hundreds of records. Delta Lake's optimization for upserts made this possible.
</p>

<h2>Verifying the Results</h2>

<p>
Once the merge operation was complete, I ran a few <code>SELECT</code> statements to verify that the data in the <code>training.sales.customers</code> table was updated correctly. I checked for existing customers and confirmed that their data had been updated with the new values from the source table. I also verified that any new customers from the <code>customer_updates</code> table were successfully inserted into the main table.
</p>

<p>
I also ran a <code>JOIN</code> between the target and source tables to ensure that all rows were processed correctly. I noticed that there were no duplicate entries, and the merge operation had handled all the updates and inserts as expected.
</p>

<h2>Conclusion</h2>

<p>
Using the <code>MERGE INTO</code> operation in Delta Lake has been a game-changer for my workflow. It allows me to handle upserts in a single, efficient statement without having to write separate <code>UPDATE</code> and <code>INSERT</code> operations. I've learned that this approach not only simplifies the code but also improves performance and reduces the risk of data inconsistencies.
</p>

<p>
Going forward, I'll continue to use this method for any data synchronization tasks that require upserts. It's a powerful feature of Delta Lake that I'm glad to have in my toolkit.
</p>