Microsoft Certified: Fabric Analytics Engineer AssociatePlan and implement data analytics solutions (10-15%)Hard
A financial institution is implementing a Microsoft Fabric Lakehouse for its transactional data. They need to ensure that deleted records from source systems are also removed from the corresponding Delta tables in the Lakehouse to maintain data synchronization. This process must be idempotent and handle potential late-arriving deletes. Which Spark SQL command or Delta Lake operation is most appropriate for this scenario?
- A`UPDATE ... WHERE ...`
- B`INSERT INTO ... SELECT ...`
- C`TRUNCATE TABLE ...`
- D`MERGE INTO ... USING ... WHEN MATCHED THEN DELETE ...`
Show answer & explanationAnswer & explanation
Correct answer: D. `MERGE INTO ... USING ... WHEN MATCHED THEN DELETE ...`
The `MERGE INTO` command in Delta Lake is specifically designed for upserting, updating, and deleting records in a target table based on a source. The `WHEN MATCHED THEN DELETE` clause precisely handles the requirement to remove records from the Delta table that no longer exist in the source, ensuring data synchronization and idempotency.
Why the other options are wrong
- A. `UPDATE` modifies existing records but does not remove records that are no longer present in the source.
- B. `INSERT INTO` only adds new records and does not handle deletions or updates to existing ones.
- C. `TRUNCATE TABLE` removes all records from a table, which is not suitable for selectively deleting specific records to synchronize with a source.
Delta Lake MERGE INTO
The `MERGE INTO` command in Delta Lake performs `UPSERT` operations (insert, update, and delete) on a target Delta table based on changes from a source dataset, enabling efficient and idempotent data synchronization.
- Combines INSERT, UPDATE, and DELETE into a single atomic transaction.
- Supports complex match conditions.
- Ensures idempotency for data synchronization workflows.
Memory trick: MERGE INTO syncs your Delta data, no record left behind (or in excess).