BenchMark Quizzes by MDFT Pro

Implement Incremental Loading with Merge Update

You work for MDFT Pro, a well-known training agency that tracks student course progress. Mark, a Data Engineer at MDFT Pro, manages a Fabric lakehouse named StudentProgressLakehouse that contains a table named ProgressStatus_Target with the following columns:

Key (student course enrollment ID)
Status (enrollment status)
LastModified (last update timestamp)

The data source system contains a table named ProgressStatus_Source with the same columns, which is used to populate ProgressStatus_Target with incremental updates. In a notebook named ProgressUpdateNotebook, Mark loads ProgressStatus_Source to a DataFrame named sourceDF and ProgressStatus_Target to a DataFrame named targetDF. Mark needs to implement an incremental loading pattern using ProgressUpdateNotebook that will update existing student progress records. The solution must meet the following requirement: For all matching records that have the same value of Key (indicating the same student enrollment), update the value of LastModified in ProgressStatus_Target to reflect the latest timestamp from ProgressStatus_Source.

How should Mark complete the merge statement?

(targetDF
    .merge(sourceDF, "sourceDF.Key = targetDF.Key")
    ._____________ (
        set = {"targetDF.LastModified": "sourceDF.LastModified"}
    )
.execute()
)

Choose the correct answer from the options below.

Explanations for each answer:

whenMatchedInsert is incorrect. There is no whenMatchedInsert method in Delta Lake merge operations. When records match based on the join condition, you can only update or delete them, not insert them (since they already exist). Insert operations are only valid for non-matching records using whenNotMatchedInsert.
whenMatchedUpdate is correct. The whenMatchedUpdate method is correct for updating matched records during a merge operation. When records have the same Key value in both source and target tables, whenMatchedUpdate updates the specified columns in the target table with values from the source table. The set parameter defines which columns to update and their new values, such as setting targetDF.LastModified to sourceDF.LastModified.
whenNotMatchedInsert is incorrect. The whenNotMatchedInsert method handles records that exist in the source but not in the target table, inserting new rows. Since the requirement is to update matching records (rows with the same Key value), this method addresses a different scenario than what's needed.
whenNotMatchedUpdate is incorrect. There is no whenNotMatchedUpdate method in Delta Lake merge operations. When records don't match, you can only insert them into the target table using whenNotMatchedInsert. Update operations are only valid for matching records using whenMatchedUpdate or whenMatchedDelete.
whenNotMatchedBySourceInsert is incorrect. There is no whenNotMatchedBySourceInsert method in Delta Lake. The whenNotMatchedBySource methods (whenNotMatchedBySourceUpdate and whenNotMatchedBySourceDelete) handle records that exist in the target but not in the source, and these methods can only update or delete, not insert.
whenNotMatchedBySourceUpdate is incorrect. The whenNotMatchedBySourceUpdate method updates records that exist in the target table but not in the source table. The requirement is to update matching records (rows with the same Key), not records that are missing from the source, making this method inappropriate for this scenario.

Learn more about upserting into Delta Lake tables using merge:

Delta Lake Merge Operations

Practice Exam

Implement Incremental Loading with Merge Update

Discuss this question on social media: