BenchMark Quizzes by MDFT Pro

Implement Incremental Loading with Conditional Source Update

You work for MDFT Pro, a well-known training agency that manages student course enrollments. Mark, a Data Engineer at MDFT Pro, maintains a Fabric lakehouse named StudentEnrollmentLakehouse that contains a table named ActiveEnrollments_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 ActiveEnrollments_Source with the same columns, which tracks currently active enrollments. In a notebook named EnrollmentCleanupNotebook, Mark loads ActiveEnrollments_Source to a DataFrame named sourceDF and ActiveEnrollments_Target to a DataFrame named targetDF. Mark needs to implement an incremental loading pattern that identifies and marks stale enrollment records. The solution must meet the following requirement: Set the value of Status in ActiveEnrollments_Target to “inactive” for all records that were last modified more than seven days ago AND that do NOT exist in ActiveEnrollments_Source (indicating students who have withdrawn or completed their courses).

How should Mark complete the merge statement?

(targetDF
    .merge(sourceDF, "sourceDF.Key = targetDF.Key")
    .________________ (
        condition = "targetDF.LastModified < (current_date() - INTERVAL '7' DAY)",
        set = {"targetDF.Status": "'inactive'"}
    )
.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, they already exist in the target table, so you can only update or delete them. The requirement is to update records in the target that don't exist in the source, not records that match between source and target.
whenMatchedUpdate is incorrect. The whenMatchedUpdate method updates records that exist in both source and target tables (matching based on the join condition). The requirement is to update records that exist in the target but NOT in the source, which represents records that have been removed or expired from the source system.
whenNotMatchedInsert is incorrect. The whenNotMatchedInsert method inserts records that exist in the source but not in the target. The requirement is the opposite - to update records that exist in the target but not in the source, marking them as inactive when they haven't been updated recently.
whenNotMatchedUpdate is incorrect. There is no whenNotMatchedUpdate method in Delta Lake merge operations. When records don't match (exist in source but not target), you can only insert them using whenNotMatchedInsert. This method doesn't exist in the Delta Lake API.
whenNotMatchedBySourceInsert is incorrect. There is no whenNotMatchedBySourceInsert method in Delta Lake. The whenNotMatchedBySource methods handle records that exist in the target but not in the source, and can only perform update or delete operations (whenNotMatchedBySourceUpdate or whenNotMatchedBySourceDelete), not inserts.
whenNotMatchedBySourceUpdate is correct. The whenNotMatchedBySourceUpdate method is correct for updating records that exist in the target table but NOT in the source table. This handles the scenario where student enrollments have been removed or expired from the source system. The condition parameter filters for records last modified more than seven days ago, and the set parameter marks them as 'inactive'. This pattern is useful for soft-deleting stale records that are no longer present in the active source data.

Learn more about upserting into Delta Lake tables using merge:

Delta Lake Merge Operations

Practice Exam

Implement Incremental Loading with Conditional Source Update

Discuss this question on social media: