Databricks Certified Data Engineer - Associate

Explanation

The correct answer is C. When the source table can be deleted.

Why MERGE INTO vs INSERT INTO:

1. MERGE INTO (also known as UPSERT) is used when you need to:

   • Update existing records
   • Insert new records
   • Delete records
   • Perform conditional operations based on whether records exist

2. INSERT INTO is used when you simply want to append new data to a table without checking for existing records.

Analysis of Options:

• A. When the location of the data needs to be changed - This is incorrect. Changing data location is typically handled by ALTER TABLE commands or by creating new tables, not by MERGE INTO.

• B. When the target table is an external table - This is incorrect. Both MERGE INTO and INSERT INTO can work with external tables in Databricks. The table type doesn't determine which command to use.

• C. When the source table can be deleted - This is CORRECT. MERGE INTO is particularly useful when you need to handle scenarios where:

  • You want to delete records from the target table that don't exist in the source (using DELETE clause)
  • You're performing incremental updates where some source records might be removed
  • You need to synchronize two tables completely

Key Use Cases for MERGE INTO:

1. Incremental data loading - Update existing records and insert new ones
2. Data synchronization - Keep two tables in sync
3. Slowly Changing Dimensions (SCD) - Handle type 1 or type 2 SCD patterns
4. Data deduplication - Update existing duplicates

Example Scenario:

If you have a customer table and receive daily updates, you would use MERGE INTO to:

• Update existing customer records
• Insert new customers
• Optionally delete customers who are no longer in the source

This is why option C is correct - when source data might be deleted or removed, MERGE INTO with DELETE clause is the appropriate command to handle such scenarios.