A data engineering team is building a large Delta Lake table and uses a MERGE operation nightly to upsert records from a daily CDC feed. The table is partitioned by event_date. The MERGE is running slowly despite the source data only touching records from the last 7 days. Which approach BEST improves MERGE performance in this scenario without changing the partitioning scheme?
Answer & explanation
Correct answer: B
WHY B: The Delta Lake documentation identifies 'reduce the search space for matches' as the primary technique to speed up MERGE. When a table is partitioned (e.g., by event_date) and the source data is known to touch records from a bounded time window, adding the partition filter to the match condition (on the target, and on the source as well) makes Delta scan those partitions rather than the whole table. Databricks documents this pattern directly: 'Adding the following condition makes the query faster, as it looks for matches only in the relevant partitions.' This avoids a full-table scan during the merge join phase, dramatically reducing I/O for large tables.
WHY NOT A: spark.sql.shuffle.partitions does control the number of tasks during the MERGE shuffle phase, but raising it does not reduce the amount of DATA read during the match phase, and a very high value can produce excessive small output files. The bottleneck is reading the whole table to find matches: a search space problem, not a shuffle parallelism problem.
WHY NOT C: INSERT OVERWRITE replaces a partition's data wholesale, not individual records, so it cannot implement selective upsert logic. A for-loop is also an anti-pattern for distributed workloads, introduces driver-side bottlenecks, and cannot reproduce the record-level change semantics that MERGE provides.
WHY NOT D: Databricks documents that Spark caching should NOT be used with Delta Lake tables. Caching a Delta table loses data skipping benefits, can return stale data if the table is updated concurrently or accessed via a different identifier, and does not help with the MERGE match-phase scan.