Interview/Databricks

Databricks interview questions: Delta MERGE

Interactive Databricks interview questions on Delta MERGE. Practice with the matching lesson. MERGE INTO upserts a change set into a Delta table by rewriting only the files that the join key touches.

Lesson · Simulation

beginner
Delta MERGE

What does MERGE INTO do on Delta?

Answer it out loud, then reveal. Play steps through like the simulators.

Production scenario

Delta MERGE

Hourly MERGE, table is 4 million tiny files

Symptoms

  • Each MERGE added files
  • Next scan launches millions of tasks

All questions on this page

Indexed as FAQ. Open any item if you prefer a list to Play.

Showing 9 of 9 questions.

beginner

What does MERGE INTO do on Delta?

Delta MERGE · tap to open the answer

Say this first: Match keys, update/insert/delete, rewrite touched files, one atomic commit.

Why: It is not an in-place row edit on Parquet. Files that contain matched rows are rewritten. Tiny files after many MERGEs need OPTIMIZE.

Common mistake: Thinking MERGE updates a row in a single Parquet file like a database page.

Follow-up they will ask: What happens to unmatched target rows by default?

Lesson · Simulation

intermediate

Why did MERGE rewrite 40% of the table to change 0.1% of rows?

Delta MERGE · tap to open the answer

Say this first: Those rows lived in large files (or unclustered files) so whole files were copied.

Why: Partition / liquid clustering / Z-ORDER so a key lives in fewer files. MERGE ON a selective condition. Don't MERGE a full snapshot if you have change data.

Common mistake: OPTIMIZE after every MERGE in the same transaction as a religion without measuring file size.

Follow-up they will ask: How do you MERGE only yesterday's partition?

Lesson · Simulation

senior

Concurrent MERGEs fail with ConcurrentAppendException. What's the design change?

Delta MERGE · tap to open the answer

Say this first: Isolate partitions / rows so commits don't conflict, or serialize writers.

Why: Two jobs rewriting the same files. Partition the table by the merge boundary (date). One writer per partition. Retry is OK; 50 retries is a design bug.

Senior depth: When you replace a whole partition from a correct snapshot — cheaper than matching every key.

Common mistake: Turning off optimistic concurrency.

Follow-up they will ask: When is a replaceWhere overwrite better than MERGE?

Lesson · Simulation

beginner

A MERGE finished in 40 minutes and you want to know whether that was a lot of work or a lot of waste. Where do you look?

Delta MERGE · tap to open the answer

Say this first: DESCRIBE HISTORY on the target: the MERGE commit's operationMetrics tells you rows touched and files rewritten.

Remember these

  • DESCRIBE HISTORY exposes operationMetrics per commit
  • numTargetFilesRemoved is the rewrite cost, not rows updated
  • DESCRIBE DETAIL gives numFiles and sizeInBytes for context
  • Rows changed vs files rewritten is the amplification ratio

Why: Every Delta write records an entry in the transaction log, and DESCRIBE HISTORY surfaces it with an operationMetrics map. For a MERGE that map carries numSourceRows, numTargetRowsUpdated, numTargetRowsInserted, numTargetRowsDeleted, numTargetFilesRemoved and numTargetFilesAdded. The ratio you care about is rows changed against files removed: 900 rows updated and 4,000 files removed means the job copied most of the table to edit almost nothing. Pair it with DESCRIBE DETAIL, which gives numFiles and sizeInBytes, so you can turn files removed into an estimate of bytes rewritten. That single ratio is the difference between 'the MERGE is slow' and 'the file layout is wrong', and it is the number an interviewer wants you to name.

DESCRIBE HISTORY ops.silver.orders;
-- operationMetrics: numSourceRows, numTargetRowsUpdated,
-- numTargetFilesRemoved, numTargetFilesAdded
DESCRIBE DETAIL ops.silver.orders;

Common mistake: Reading only the wall clock from the job run and never opening the commit metrics.

Follow-up they will ask: If files removed is close to the table's total file count, what is your next move?

Lesson · Simulation

intermediate

Your nightly MERGE started failing with 'Cannot perform Merge as multiple source rows matched and attempted to modify the same target row'. What changed upstream, and how do you fix it?

Delta MERGE · tap to open the answer

Say this first: The source now contains more than one row per merge key, so Delta refuses to guess which one wins — collapse the source to one row per key first.

Remember these

  • At most one source row may match a given target row
  • CDC batches routinely contain several versions of one key
  • ROW_NUMBER over the key ordered by sequence desc collapses them
  • A loose ON clause produces the same error from the target side

Why: MERGE requires that each target row match at most one source row, because an UPDATE with two candidate payloads has no defined result. A change-data feed breaks this as soon as a key is modified twice inside one batch window, which happens the first time a customer edits an order and then cancels it in the same hour. The fix is in the USING clause: rank the source by the key, ordered by the sequence or commit timestamp, and keep only the latest row. The other cause is an ON clause that is too loose, for example joining on email instead of the primary key, so one source row legitimately matches several target rows. Check which of the two it is by counting the source grouped by the key before you change anything — if the source is already unique, the ON clause is the bug.

MERGE INTO ops.silver.orders t
USING (SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq DESC) rn FROM changes
) WHERE rn = 1) s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *

Common mistake: Adding DISTINCT to the source, which keeps both versions of the key whenever any other column differs.

Follow-up they will ask: If two source rows for a key are identical except for the sequence number, which one should win and why?

Lesson · Simulation

intermediate

Walk me through the difference between WHEN MATCHED THEN UPDATE SET * and listing every column by hand. When has SET * bitten you?

Delta MERGE · tap to open the answer

Say this first: SET * expands to the source's columns matched by name, so it is convenient until the source's shape changes or it overwrites columns the target owns.

Remember these

  • SET * matches source to target columns by name, not position
  • A new source column fails the MERGE unless evolution is enabled
  • WITH SCHEMA EVOLUTION opts in per statement instead of per session
  • Explicit assignments protect target-owned audit columns

Why: SET * is resolved at analysis time into an assignment per source column, matched to target columns by name rather than position, so reordering the source is harmless but renaming or dropping a column is not. The first trap is target-owned columns: if silver carries ingested_at, processed_by or a surrogate key that the source does not have, SET * leaves them alone, but if the source happens to carry a column of the same name it will overwrite them with whatever the vendor sent. The second trap is a new source column, which fails the MERGE unless schema evolution is enabled — either through the session setting spark.databricks.delta.schema.autoMerge.enabled or by writing MERGE WITH SCHEMA EVOLUTION INTO. Explicit column lists are more typing and they are also a contract: a new upstream column cannot change silver without someone editing the statement. For bronze-to-silver plumbing SET * is reasonable; for a table a finance team reads, name the columns.

MERGE WITH SCHEMA EVOLUTION INTO ops.silver.orders t
USING changes s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Common mistake: Enabling automatic schema merge globally on the cluster so every pipeline silently widens its tables.

Follow-up they will ask: How would you detect that a MERGE added a column to silver last night?

Lesson · Simulation

advanced

Your MERGE joins on t.id = s.id and rewrites about 2 TB a night. A colleague adds AND t.event_date >= current_date() - INTERVAL 3 DAYS to the ON clause and it drops to 60 GB. Explain the mechanism — and the risk.

Delta MERGE · tap to open the answer

Say this first: The extra predicate lets Delta refute most target files from their min/max statistics before the join, so far fewer files are read and rewritten.

Remember these

  • Phase one of MERGE prunes candidate files from log statistics
  • An equality on a high-cardinality key prunes nothing
  • A range predicate on the clustering key is refutable, so files drop out
  • Too narrow a window turns late updates into duplicate inserts

Why: MERGE runs in two phases: find the target files that could contain a matching row, then rewrite those files with the merged result. The first phase pushes the ON clause down against the per-file statistics in the transaction log. An equality on a high-cardinality id proves nothing — any file could hold that id — so every file becomes a candidate. A range predicate on the partition or clustering column is refutable, so files whose event_date range falls outside the window are eliminated without being opened, and the rewrite shrinks with them. You can watch the effect in numTargetFilesRemoved in the commit metrics and in the scan's file-pruning counters in the query profile. The risk is correctness: a change that arrives for a row older than three days now matches nothing, so WHEN NOT MATCHED either skips it or inserts a duplicate key. Only narrow the ON clause to a window you can prove bounds late arrivals, and keep a wider periodic reconciliation.

MERGE INTO ops.silver.orders t
USING changes s
   ON t.id = s.id
  AND t.event_date >= current_date() - INTERVAL 3 DAYS
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Senior depth: The ON clause is the only part of a MERGE that can eliminate files, so anything you want pruning on belongs there. I would cluster or partition on the window column, keep the window at the upstream's measured p99 lateness plus margin, and add a weekly full-key reconciliation MERGE so a straggler cannot live as a duplicate forever. The number I would bring to the review is numTargetFilesRemoved per run before and after, because that is what the DBUs track.

Common mistake: Putting the date filter in a WHEN MATCHED AND ... clause instead of the ON clause, which filters rows after the files were already read.

Follow-up they will ask: How would you prove three days is enough for this source?

Lesson · Simulation

advanced

Design SCD Type 2 on a Delta dimension using MERGE. Why can't a single MERGE statement do it?

Delta MERGE · tap to open the answer

Say this first: A matched row can only take one action per MERGE, and SCD2 needs two — close the current version and insert the new one — so you stage both or write twice.

Remember these

  • One action per matched row is the constraint that forces the pattern
  • Stage the changed key twice: real key expires, null key inserts
  • Compare a row hash so unchanged keys cause no rewrite
  • Two separate statements mean two commits and a gap on failure

Why: SCD Type 2 keeps history by expiring the current row and appending a new one, which is an UPDATE and an INSERT against the same business key. MERGE allows one action per matched target row, so the classic shape stages a source where a changed key appears twice: once with the real key so it matches and expires the current row, and once with a null merge key so it falls to NOT MATCHED and inserts the new version. Change detection is a row hash over the tracked attributes, so unchanged rows take no action and you do not churn files. The alternative is two statements in one job — a MERGE that sets is_current = false and valid_to, then an INSERT of the new versions — which is easier to read but is two commits, so a failure between them leaves a key with no current row. Declarative pipelines offer APPLY CHANGES ... STORED AS SCD TYPE 2 if you would rather not own this at all.

MERGE INTO dim_customer t
USING staged s ON t.customer_id = s.merge_key AND t.is_current
WHEN MATCHED AND t.row_hash <> s.row_hash THEN
  UPDATE SET t.is_current = false, t.valid_to = s.effective_ts
WHEN NOT MATCHED THEN INSERT *

Senior depth: The interesting part is not the SQL, it is the invariant: exactly one current row per key, no gaps and no overlaps in validity. I would assert that invariant as a scheduled check rather than trusting the merge, because the failure mode is silent fan-out in every downstream join. If the team does not have an appetite to own that, APPLY CHANGES with STORED AS SCD TYPE 2 maintains the start and end columns for you and handles out-of-order rows by the sequence column, which is a fair trade of control for correctness.

Common mistake: Matching on the business key without AND t.is_current, so the MERGE tries to modify every historical version of that key and errors on multiple matches.

Follow-up they will ask: Where does the clustering key go on an SCD2 dimension that is queried both by key and by validity date?

Lesson · Simulation

senior

A nightly sync uses WHEN NOT MATCHED BY SOURCE THEN DELETE against a vendor's full export. This morning 60% of the table is gone. What happened and what do you change?

Delta MERGE · tap to open the answer

Say this first: The export was partial, and NOT MATCHED BY SOURCE means 'delete every target row the source did not send' — the MERGE did exactly what it was told, atomically.

Remember these

  • NOT MATCHED BY SOURCE makes source completeness a correctness dependency
  • The condition on that clause may only reference target columns
  • RESTORE TABLE recovers it while the files are inside retention
  • Gate the job on a source row-count delta before the MERGE runs

Why: NOT MATCHED BY SOURCE turns a MERGE into a full-snapshot reconciliation, so the source's completeness becomes a hard correctness dependency. A truncated file, a partial upload still in flight, or a vendor filtering their export to one region all look identical to the statement: those keys are absent, so they are deleted, in one commit that readers see all at once. Recovery is the easy part — DESCRIBE HISTORY gives you the version before the MERGE and RESTORE TABLE puts it back, since the data files are still within the deleted-file retention window. The fix is a precondition, not a better MERGE: count the source and compare it against the previous run's count, and abort the job when it moves by more than a tolerance. After that, scope the delete clause with a target-side condition such as last_seen older than a week so a one-night gap cannot clear live rows, or switch to a soft delete flag and let a separate job physically remove rows later.

WHEN NOT MATCHED BY SOURCE
  AND t.last_seen < current_date() - INTERVAL 7 DAYS
  THEN DELETE

Senior depth: Any statement that can delete rows because data is absent needs a completeness check in front of it. I would land the export in bronze first, assert its row count and file manifest against the prior run, and only then run the reconciliation MERGE from bronze — which also gives you the raw file to argue with the vendor about. Then make the delete recoverable by default: soft-delete with a flag, physically purge on a lagging schedule, and keep deletedFileRetentionDuration long enough that RESTORE is a real option rather than a hope.

Common mistake: Treating the blast radius as a Delta problem and asking for backups, when the missing control is a row-count guard on the incoming export.

Follow-up they will ask: How would you detect this within minutes instead of at the next morning's standup?

Lesson · Simulation