Answer: When the target table cannot contain duplicate records

The MERGE INTO command (also known as UPSERT) is specifically designed for handling scenarios where you need to update existing records and insert new ones in a single operation. This is particularly important when the target table cannot contain duplicate records, as MERGE INTO allows you to: 1. **Update existing records** when a match is found based on specified conditions 2. **Insert new records** when no match is found 3. **Delete records** (optional) when certain conditions are met **Why other options are incorrect:** - **A**: Changing data location is typically handled by ALTER TABLE or CREATE TABLE AS SELECT commands, not MERGE INTO. - **B**: External tables can be targets for both INSERT INTO and MERGE INTO operations. - **C**: Source table deletion is not a determining factor for choosing MERGE INTO vs INSERT INTO. - **E**: MERGE INTO works with various source types, not just Delta tables. **Key takeaway**: Use MERGE INTO when you need to handle upsert operations (update existing records and insert new ones) to maintain data integrity and prevent duplicates in the target table.

Author: Keng Suppaseth