Answer: MERGE

For Type 2 slowly changing dimensions (SCD) in Azure Databricks with Delta Lake tables, the MERGE operation is the optimal choice because it supports complex conditional logic required for SCD Type 2 implementations. Type 2 SCD requires maintaining historical data by inserting new rows for changed records while keeping the old versions, rather than updating existing rows. The MERGE operation can handle multiple conditions in a single atomic transaction: - **WHEN MATCHED** clauses can update existing records (e.g., setting end dates for previous versions) - **WHEN NOT MATCHED** clauses can insert new records for changed dimensions - **WHEN NOT MATCHED BY SOURCE** clauses can handle deletions or archival This provides ACID transaction guarantees essential for data consistency in dimension tables. Other operations are less suitable: - **CREATE**: Only creates new tables, cannot update existing data - **UPDATE**: Only modifies existing rows, cannot insert new records needed for Type 2 SCD history - **ALTER**: Modifies table structure, not the data content The MERGE operation's ability to handle multiple data modification operations in a single statement makes it ideal for the complex requirements of Type 2 SCD implementations in Delta Lake environments.

Author: LeetQuiz Editorial Team