Merge (upsert) operations in Apache Spark - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a merge operation on the target DataFrame.

Apache Spark

deltaTable = DeltaTable.forName(spark, [1])

Drag options to blanks, or click blank then click option'

A"update_table"

B"source_table"

C"target_table"

D"merge_table"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the source table name instead of the target table name.

Forgetting to put the table name in quotes.

The DeltaTable.forName method requires the target table name as a string to perform merge operations.

2fill in blank

medium

Complete the code to specify the merge condition between target and source DataFrames.

Apache Spark

deltaTable.alias("t").merge(source.alias("s"), [1])

Drag options to blanks, or click blank then click option'

A"t.name = s.name"

B"t.id = s.id"

C"t.value > s.value"

D"t.date < s.date"

Attempts:

3 left

💡 Hint

Common Mistakes

Using inequality or comparison operators instead of equality.

Matching on non-unique columns.

The merge condition should match rows based on a unique key, usually an ID column.

3fill in blank

hard

Fix the error in the merge update clause to set the target's name to source's name.

Apache Spark

whenMatchedUpdate = deltaTable.whenMatchedUpdate(set = [1])

Drag options to blanks, or click blank then click option'

A{"name": source.name}

B{"name": "source.name"}

C{"name": "s.name"}

D{"name": s.name}

Attempts:

3 left

💡 Hint

Common Mistakes

Putting source column names in quotes, making them strings.

Using the wrong alias for source.

The set parameter expects a dictionary mapping target columns to source columns without quotes around the source column variable.

4fill in blank

hard

Fill both blanks to complete the merge operation with update and insert actions.

Apache Spark

deltaTable.merge(source.alias("s"), "t.id = s.id")
  .whenMatchedUpdate(set = [1])
  .whenNotMatchedInsert(values = [2])
  .execute()

Drag options to blanks, or click blank then click option'

A{"name": "s.name", "age": "s.age"}

B{"name": s.name, "age": s.age}

C{"id": s.id, "name": s.name, "age": s.age}

D{"id": "s.id", "name": "s.name", "age": "s.age"}

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around source column variables.

Missing required columns in insert values.

For update, the set dictionary maps target columns to source columns without quotes around source variables. For insert, the values dictionary must include all columns with source variables.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension for updating only columns with non-null source values.

Apache Spark

update_dict = {col: s[col] for col in source.columns if s[col] [1] None and col != [2]
deltaTable.merge(source.alias("s"), "t.id = s.id")
  .whenMatchedUpdate(set = update_dict)
  .whenNotMatchedInsert(values = [3])
  .execute()

Drag options to blanks, or click blank then click option'

Ais not

B"id"

C{"id": s.id, "name": s.name, "age": s.age}

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' instead of 'is not' for None checks.

Including 'id' in update dictionary.

Incorrect insert values dictionary.

The dictionary comprehension filters out columns where the source value is None and excludes the 'id' column from update. The insert values dictionary includes all columns.

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