Loading with merge, insert, and append

Azure Databricks uses Delta Lake's ACID-compliant operations—INSERT, MERGE, and DataFrame append writes—to load data into tables incrementally or as upserts. Choosing the right operation depends on whether you need simple appends, full overwrites, or conditional updates/inserts (upserts) based on matching keys. Understanding transaction guarantees and schema handling is essential for reliable, idempotent data loading pipelines.

1 · Learn the must-know

• 1MERGE INTO performs upserts in a single atomic transaction, allowing matched rows to be updated or deleted and unmatched rows to be inserted.
• 2INSERT INTO appends new rows to a Delta table without touching existing data, while INSERT OVERWRITE replaces the entire table or targeted partitions.
• 3DataFrame writes using .mode("append") add rows to an existing table or path, and Delta enforces schema-on-write by default, rejecting mismatched schemas unless mergeSchema is enabled.
• 4COPY INTO is idempotent and tracks previously loaded source files via table metadata, making it safe to rerun for incremental, append-only ingestion without creating duplicates.
• 5MERGE performance depends heavily on the selectivity of the join/match condition and partition pruning; poorly chosen merge keys can force expensive full table scans.
• 6Schema evolution during append or overwrite operations requires explicitly setting the mergeSchema or overwriteSchema option, since Delta Lake otherwise blocks incompatible schema changes to protect data integrity.

2 · Check your understanding