BenchMark Quizzes by MDFT Pro

Implement Incremental Loading with Merge Insert

You work for MDFT Pro, a well-known training agency that tracks student course progress. Claire, a Data Engineer at MDFT Pro, manages a Fabric lakehouse named StudentProgressLakehouse that contains a table named EnrollmentStatus_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 EnrollmentStatus_Source with the same columns, which is used to populate EnrollmentStatus_Target with incremental updates. In a notebook named EnrollmentSyncNotebook, Claire loads EnrollmentStatus_Source to a DataFrame named sourceDF and EnrollmentStatus_Target to a DataFrame named targetDF. Claire needs to implement an incremental loading pattern using EnrollmentSyncNotebook that will add new student enrollment records. The solution must meet the following requirement: Insert all the records that exist in EnrollmentStatus_Source that do NOT exist in EnrollmentStatus_Target (new enrollments that haven’t been captured yet).

How should Claire complete the merge statement?

(targetDF
    .merge(sourceDF, "sourceDF.Key = targetDF.Key")
    ._______________ (
        values = {
            "targetDF.Key": "sourceDF.Key",
            "targetDF.LastModified": "sourceDF.LastModified",
            "targetDF.Status": "sourceDF.Status"
        }
    )
.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, they already exist in the target table, so you can only update or delete them, not insert them. Insert operations are only valid for non-matching records.
whenMatchedUpdate is incorrect. The whenMatchedUpdate method updates records that already exist in both source and target tables (matching on the join condition). The requirement is to insert new records that exist in the source but not in the target, which is a different operation than updating existing matched records.
whenNotMatchedInsert is correct. The whenNotMatchedInsert method is correct for inserting new records during a merge operation. It handles records that exist in ProgressStatus_Source but do NOT exist in ProgressStatus_Target (based on the Key column). The values parameter specifies which columns to populate in the new rows and their source values. This enables incremental loading by adding only new student enrollment records that haven't been captured in the target table yet.
whenNotMatchedUpdate is incorrect. There is no whenNotMatchedUpdate method in Delta Lake merge operations. When records don't exist in the target table (not matched), the only valid operation is to insert them using whenNotMatchedInsert. Update operations require records to already exist in the target table.
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 can only perform update or delete operations, not inserts.
whenNotMatchedBySourceUpdate is incorrect. The whenNotMatchedBySourceUpdate method updates records that exist in the target table but NOT in the source table. The requirement is to insert records from the source that don't exist in the target, which is the opposite scenario. This method would incorrectly modify existing target records that are missing from the source.

Learn more about upserting into Delta Lake tables using merge:

Delta Lake Merge Operations

Practice Exam

Implement Incremental Loading with Merge Insert

Discuss this question on social media: