Microsoft Certified: Fabric Analytics Engineer AssociatePlan and implement data analytics solutions (10-15%)Hard
A data engineer is designing a data ingestion solution for a Microsoft Fabric Lakehouse. The source system is an Azure SQL Database, and the goal is to perform an 'upsert' operation: insert new records and update existing ones based on a primary key, to keep the Lakehouse table synchronized with the source. Which Delta Lake command is specifically designed for this type of operation?
- ADELETE FROM
- BMERGE INTO
- CUPDATE SET
- DINSERT INTO
Show answer & explanationAnswer & explanation
Correct answer: B. MERGE INTO
The MERGE INTO command in Delta Lake is specifically designed to perform upsert operations (insert, update, delete) based on a specified condition, making it ideal for synchronizing a target table with a source table by handling both new and existing records in a single atomic transaction.
Why the other options are wrong
- A. DELETE FROM removes records and cannot insert or update.
- C. UPDATE SET only modifies existing records and cannot insert new ones.
- D. INSERT INTO only adds new records and would lead to duplicates if existing records are present.
Delta Lake MERGE INTO
The Delta Lake MERGE INTO command allows for conditional insertion, updating, and deletion of records in a Delta table based on a source table or DataFrame, providing robust upsert capabilities.
- Performs upsert (update or insert) operations.
- Can also include delete functionality.
- Ensures atomicity for complex data synchronization tasks.
Memory trick: Merge your data, new or old, a consistent story to be told!