Home / DE Professional practice test / Data Modelling / Question

Databricks Data Engineer Professional · free practice question

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…

Exam-style question on Data Modelling from the free DE Professional practice test, with the answer and a full explanation below.

Updated November 16, 2026

Data Modelling Easy

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?

  1. ARaise spark.sql.shuffle.partitions to a high value such as 2000 to give the MERGE shuffle more tasks and spread each partition's write across more files.
  2. BAdd a date filter to the MERGE condition (events.event_date >= current_date() - INTERVAL 7 DAYS) to restrict the search space to the relevant partitions.
  3. CReplace the MERGE with an INSERT OVERWRITE of each affected partition, driven by a for-loop, to avoid the overhead of the MERGE match evaluation.
  4. DCache the target Delta table with spark.table('events').cache() before running MERGE, keeping the rows to be matched in memory for the join phase.
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.

Take the full DE Professional practice test free →

Would you pass today?

This question is one of 60 in the free, timed Databricks Data Engineer Professional practice test. Take it and get a section-by-section score.

Start the free test →