Merge (upsert) operations in Apache Spark

Introduction

Merge (upsert) operations help you combine two datasets by updating existing records and adding new ones in one step.

When you want to update a database table with new and changed data from another source.

When you need to keep your data warehouse tables fresh by merging daily updates.

When combining customer records from different systems without losing any data.

When syncing data between two tables where some records match and others are new.

When you want to avoid duplicates but keep all relevant information updated.

Syntax

Apache Spark

deltaTable.alias("target")
  .merge(
    sourceData.alias("source"),
    "target.id = source.id"
  )
  .whenMatchedUpdate(set = {"col": "source.col"})
  .whenNotMatchedInsert(values = {"id": "source.id", "col": "source.col"})
  .execute()

Use merge on a Delta table to combine data from a source DataFrame.

The whenMatchedUpdate clause updates existing rows that match the condition.

Examples

Update user names if userId matches, insert new users if no match.

Apache Spark

deltaTable.alias("t")
  .merge(
    updatesDF.alias("s"),
    "t.userId = s.userId"
  )
  .whenMatchedUpdate(set = {"name": "s.name"})
  .whenNotMatchedInsert(values = {"userId": "s.userId", "name": "s.name"})
  .execute()

Update all columns for matched rows and insert all columns for new rows.

Apache Spark

deltaTable.alias("target")
  .merge(
    sourceDF.alias("source"),
    "target.key = source.key"
  )
  .whenMatchedUpdateAll()
  .whenNotMatchedInsertAll()
  .execute()

Sample Program

This program creates a Delta table with two users, then merges updates that change one name and add a new user. Finally, it shows the updated table.

Apache Spark

from pyspark.sql import SparkSession
from delta.tables import DeltaTable

spark = SparkSession.builder.appName("MergeExample")\
  .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")\
  .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog")\
  .getOrCreate()

# Create initial data
initialData = spark.createDataFrame([
  (1, "Alice"),
  (2, "Bob")
], ["id", "name"] )

# Write initial data as Delta table
initialData.write.format("delta").mode("overwrite").save("/tmp/delta-table")

deltaTable = DeltaTable.forPath(spark, "/tmp/delta-table")

# New data with one update and one new record
updates = spark.createDataFrame([
  (2, "Robert"),  # Update Bob to Robert
  (3, "Charlie")  # New record
], ["id", "name"])

# Perform merge (upsert)
deltaTable.alias("target")
  .merge(
    updates.alias("source"),
    "target.id = source.id"
  )
  .whenMatchedUpdate(set = {"name": "source.name"})
  .whenNotMatchedInsert(values = {"id": "source.id", "name": "source.name"})
  .execute()

# Show final table
finalDF = spark.read.format("delta").load("/tmp/delta-table")
finalDF.orderBy("id").show()

Important Notes

Make sure the Delta Lake library is installed and configured in Spark.

Merge operations are atomic and efficient for large datasets.

Use aliases to clearly distinguish source and target tables in the merge.

Summary

Merge (upsert) lets you update existing rows and insert new rows in one step.

It is useful for syncing and updating data efficiently.

Delta Lake provides easy syntax to perform merge operations in Spark.

Practice

1. What is the main purpose of a merge (upsert) operation in Apache Spark with Delta Lake?

easy

A. To update existing rows and insert new rows in one step

B. To delete all rows from a table

C. To create a new Spark session

D. To read data from a CSV file

Solution

1. Step 1: Understand the merge operation concept

   Merge (upsert) combines update and insert actions to keep data synchronized.

2. Step 2: Identify the main use case in Spark with Delta Lake

   It updates existing rows and inserts new rows efficiently in one command.

3. Final Answer:

   To update existing rows and insert new rows in one step -> Option A

4. Quick Check:

   Merge = update + insert [OK]

Hint: Merge means update existing and insert new rows together [OK]

Common Mistakes:

• Confusing merge with delete operation
• Thinking merge only inserts data
• Assuming merge creates new tables

2. Which of the following is the correct syntax to start a merge operation in Spark SQL with Delta Lake?

easy

A. deltaTable.select(sourceDF).merge()

B. deltaTable.join(sourceDF).update()

C. deltaTable.insert(sourceDF).update()

D. deltaTable.merge(sourceDF, condition).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

Solution

1. Step 1: Recall the merge syntax in Delta Lake

   The merge starts with deltaTable.merge(sourceDF, condition) followed by update and insert clauses.

2. Step 2: Match the full correct method chain

   Correct chain is whenMatchedUpdateAll(), whenNotMatchedInsertAll(), then execute().

3. Final Answer:

   deltaTable.merge(sourceDF, condition).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute() -> Option D

4. Quick Check:

   Correct merge syntax = deltaTable.merge(sourceDF, condition).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute() [OK]

Hint: Merge uses .merge().whenMatchedUpdateAll().whenNotMatchedInsertAll().execute() [OK]

Common Mistakes:

• Using join instead of merge
• Calling update before merge
• Missing execute() at the end

3. Given the following Spark code snippet, what will be the output count of rows in targetTable after the merge?

val sourceDF = spark.createDataFrame(Seq((1, "A"), (3, "C"))).toDF("id", "value")
val deltaTable = DeltaTable.forName("targetTable")
deltaTable.merge(sourceDF, "target.id = source.id")
  .whenMatchedUpdateExpr(Map("value" -> "source.value"))
  .whenNotMatchedInsertExpr(Map("id" -> "source.id", "value" -> "source.value"))
  .execute()
val count = spark.table("targetTable").count()

Assuming targetTable initially has rows with ids 1 and 2.

medium

A. 2

B. 3

C. 4

D. 1

Solution

1. Step 1: Analyze initial data in targetTable

   targetTable has ids 1 and 2 initially.

2. Step 2: Apply merge logic with sourceDF

   Source has ids 1 and 3. Id 1 matches and updates value. Id 3 does not match and inserts new row.

3. Step 3: Calculate final row count

   Rows after merge: ids 1 (updated), 2 (unchanged), 3 (inserted) -> total 3 rows.

4. Final Answer:

   3 -> Option B

5. Quick Check:

   Initial 2 + 1 inserted = 3 rows [OK]

Hint: Count = initial rows + new inserts after merge [OK]

Common Mistakes:

• Counting only updated rows
• Ignoring inserted rows
• Assuming rows are deleted

4. Identify the error in the following merge operation code snippet:

deltaTable.merge(sourceDF, "target.id = source.id")
  .whenMatchedUpdate(Map("value" -> "source.value"))
  .whenNotMatchedInsert(Map("id" -> "source.id", "value" -> "source.value"))
  .execute()

medium

A. The update and insert methods should be whenMatchedUpdateExpr and whenNotMatchedInsertExpr

B. The condition string should use backticks around column names

C. The execute() method is missing

D. The source DataFrame is not registered as a temp view

Solution

1. Step 1: Check method names for update and insert

   In Delta Lake merge, update and insert with expressions require whenMatchedUpdateExpr and whenNotMatchedInsertExpr.

2. Step 2: Verify other parts of the code

   Condition string and execute() are correct; sourceDF does not need temp view for merge.

3. Final Answer:

   The update and insert methods should be whenMatchedUpdateExpr and whenNotMatchedInsertExpr -> Option A

4. Quick Check:

   Use whenMatchedUpdateExpr and whenNotMatchedInsertExpr [OK]

Hint: Use whenMatchedUpdateExpr and whenNotMatchedInsertExpr for expression maps [OK]

Common Mistakes:

• Using whenMatchedUpdate instead of whenMatchedUpdateExpr
• Forgetting execute()
• Miswriting condition string syntax

5. You have a Delta table users with columns user_id and email. You receive a new DataFrame updatesDF with updated emails and new users. How do you write a merge operation to update emails for existing users and insert new users, ensuring no duplicates and preserving existing users without changes?

hard

A. Use filter to remove duplicates before inserting

B. Use join and then insert only new users

C. Use merge with condition on user_id, update email when matched, insert all columns when not matched

D. Overwrite the entire users table with updatesDF

Solution

1. Step 1: Define merge condition on user_id

   Match rows where users.user_id = updatesDF.user_id to identify existing users.

2. Step 2: Specify update and insert actions

   Update email for matched users, insert all columns for unmatched (new) users.

3. Step 3: Preserve existing users without changes

   Rows not matched remain unchanged automatically in merge.

4. Final Answer:

   Use merge with condition on user_id, update email when matched, insert all columns when not matched -> Option C

5. Quick Check:

   Merge = update matched + insert unmatched [OK]

Hint: Merge on user_id, update email if matched, insert if not [OK]

Common Mistakes:

• Overwriting entire table losing data
• Using join without update logic
• Not handling unmatched rows