cswork / libraryOpen practice workspace

Articles

Delta Lake MERGE Interview Questions: Duplicate Matches, Schema Changes, and Retry Safety

Practice Delta Lake MERGE interview questions with duplicate-match examples, runtime-specific schema rules, deterministic source cleanup, and retry checks.

Delta Lake MERGE interview questions test whether you can explain what happens to individual rows when the input is messy or a job runs twice. Start by establishing four things: one intended source state per key, a correct matching condition, an explicit schema policy, and a repeatable update rule.

MERGE conditionally updates, inserts, or deletes target rows by comparing them with a source relation. It does not, by itself, define which conflicting business event should win. For broader platform context, use the Databricks Data Engineer guide.

Evidence boundary: official documentation supplies the labeled behavior and runtime limits below. Our datasets, queries, and recommendations are original practice. Candidate report: Manasi Kulkarni’s account mentions MERGE internals in a Deloitte interview. That self-report does not establish these exact edge cases or their frequency. Sources were checked September 21, 2026.

Why do duplicate matches sometimes fail and sometimes insert?

Official behavior: Databricks’ MERGE guide distinguishes ambiguous matches against existing target rows from duplicates inside new source data. The latter still require source deduplication. It also documents a runtime boundary: 16.0 and later consider both ON and WHEN MATCHED conditions when identifying duplicate matches; 15.4 LTS and earlier consider ON alone.

Original exercise: the target contains order 7 at sequence 10. Your source has sequences 11 and 12 for order 7. Both attempt an update under WHEN MATCHED AND s.seq > t.seq. The engine does not choose sequence 12 merely because it is newer. Your source preparation must make that decision.

Now run the same two source rows against an empty target. Neither finds an existing order 7, so both can enter the insert branch. A job can therefore succeed while violating your intended one-row-per-order contract.

Do not turn this into “multiple matches always fail.” The Databricks SQL reference allows multiple matches for an unconditional matched delete, which has no competing update value. State the action and runtime before predicting an error.

For a version-sensitive follow-up, let one source row satisfy an update condition and the other satisfy none. The duplicate check differs across the documented runtime boundary. Avoid treating a runtime upgrade as a substitute for a clear source-key contract.

How should you prepare the MERGE source?

Assume a latest-state order table with non-null order_id, seq, and amount_cents. Sequence numbers come from the producer and increase per order. Equal sequences must describe identical states. This contract is part of our exercise, not a Delta-generated guarantee.

The target starts with (7, 10, 10000) and (9, 5, 2000). The incoming batch is:

order_idseqamount_centsInterpretation
71111000Earlier update
71212000Latest update
71212000Exact replay
815000New order
815000Exact replay of new order
941500Older than the target
NULL29900Invalid business key

Our policy quarantines invalid required fields, collapses identical states, and fails the batch if one key/sequence describes different payloads. Check the conflict before choosing a winner:

SELECT order_id, seq
FROM (
  SELECT DISTINCT order_id, seq, amount_cents
  FROM raw_batch
  WHERE order_id IS NOT NULL
    AND seq IS NOT NULL
    AND amount_cents IS NOT NULL
) valid_states
GROUP BY order_id, seq
HAVING COUNT(*) > 1;

The orchestrator must stop if this returns any rows. Logging the conflict and continuing would still feed ambiguous data into the merge. Include every state-bearing payload column in the comparison when extending this example.

This reduction assumes each row supplies a complete replacement state for the fields being maintained. If sequence 11 changes an address and sequence 12 changes only a phone number, keeping the second patch alone can lose the address change. Likewise, a batch of increments must not be reduced to its highest sequence and treated as a full balance. Establish whether the feed contains states, patches, or additive events before choosing the reduction.

Then prepare the source:

WITH distinct_states AS (
  SELECT DISTINCT order_id, seq, amount_cents
  FROM raw_batch
  WHERE order_id IS NOT NULL
    AND seq IS NOT NULL
    AND amount_cents IS NOT NULL
), ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY order_id ORDER BY seq DESC
  ) AS rn
  FROM distinct_states
)
SELECT order_id, seq, amount_cents
FROM ranked
WHERE rn = 1;

Official caution: ROW_NUMBER is nondeterministic when its ordering is not unique. Our conflict check and exact-state collapse make sequence ordering unique within each key. Adding an arbitrary filename as a tie-breaker could produce repeatable but unjustified business results.

We executed these source queries in DuckDB 1.5.2. They return orders (7,12,12000), (8,1,5000), and (9,4,1500), with one quarantined input. This verifies the source-selection queries, not Delta MERGE execution or Databricks runtime behavior.

Where should the freshness predicate go?

Use entity identity in ON and update eligibility in WHEN MATCHED. Our fixed-schema Delta SQL example is:

MERGE INTO orders AS t
USING prepared_batch AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.seq > t.seq THEN
  UPDATE SET seq = s.seq, amount_cents = s.amount_cents
WHEN NOT MATCHED THEN
  INSERT (order_id, seq, amount_cents)
  VALUES (s.order_id, s.seq, s.amount_cents);

Exercise inference: the expected target becomes (7,12,12000), (8,1,5000), and (9,5,2000). Order 9 remains unchanged because its incoming state is older. Running the same prepared batch again leaves those business columns unchanged.

Contrast that with this tempting match condition:

ON t.order_id = s.order_id AND s.seq > t.seq

On replay, order 7 has equal sequences, so that expression finds no match. If the statement still has an unrestricted insert branch, the existing order becomes eligible for another insert. The condition intended to reject an old update has instead changed the definition of identity.

Date predicates used for target pruning deserve the same scrutiny. If an existing key lies outside the match window, it can appear absent. Explain the business invariant that makes the restriction valid, and test a late correction outside that window.

Before writing, compare the prepared source with the target for equal sequences carrying different payloads. Reject those conflicts too: within-batch validation cannot catch a contradictory event arriving in a later batch. Under our single-writer assumption, this check remains valid until the merge; concurrent writers require stronger coordination.

Our replay model assumes the target already has unique, non-null keys and one coordinated writer. Source cleanup does not repair pre-existing target duplicates or establish a global uniqueness constraint. Validate the target separately.

What changes when the source schema evolves?

Official behavior: Databricks’ schema-evolution reference supports MERGE WITH SCHEMA EVOLUTION in Runtime 15.4 LTS and later. Eligible source columns can be added to the target. Under evolution, target-only columns remain unchanged with UPDATE SET * and become null with INSERT *. Per-operation evolution is recommended over a session-wide setting.

These are Databricks version numbers. Do not substitute them for open-source Delta Lake versions or assume every API exposes identical options.

Original schema exercise: extend the source with currency, while the target also has review_note. Before enabling evolution, answer these separate questions:

ChangeDecision to explain
Source adds currencyApprove the field, define its meaning, and choose explicit mapping or supported evolution.
Source omits review_noteDecide whether preservation on updates and null on inserts meet the contract.
amount_cents arrives as textValidate parsing and units before accepting it as the existing numeric field.

A source-only field does not necessarily make a wildcard merge fail: without evolution, an extra field can be ignored. Conversely, a missing required target field or incompatible assignment can fail. Inspect the actual action, schema, and configuration instead of assuming “schema drift means automatic addition.”

Our earlier statement deliberately maps three columns. Adding currency to its source does not make that statement write currency. Change and test the mapping or use a supported evolution variant intentionally.

Also re-run the equal-sequence conflict check with currency included. Two rows with the same sequence and amount but different currencies are different states. A schema change can invalidate your deduplication rule even when the write remains syntactically valid.

Is the MERGE safe to retry after a timeout?

Engineering definition: for fixed input S and target state T, a business-idempotent operation satisfies M(M(T,S),S) = M(T,S). The equality concerns the promised business state, not necessarily identical transaction-log history or operational metrics.

Our sequence-guarded assignment passes that model. An unconditional amount_cents = t.amount_cents + s.amount_cents does not: it adds the contribution again. A safe latest-state merge is not automatically a safe event-counting or payment-processing design.

Freeze the logical input used for a retry. Official behavior: open-source Delta documentation describes automatic source materialization from Delta 2.2 onward to stabilize multiple passes within a merge command. That does not establish that a later job rerun reads the same upstream data. Persist the prepared batch or identify a reproducible source version and cutoff.

Ten local checks covered source cleanup and Python models for replay, stale input, additive updates, conflicting states within a batch and against the target, and the incorrect freshness-in-ON alternative. The results support this exercise’s reasoning; runtime duplicate detection, schema evolution, and concurrent commits still require tests on the actual deployed engine.

When the client times out, the result may be unknown. Inspect target history and job evidence before classifying it as a failed write. If it committed, replaying a non-idempotent expression can corrupt valid state. A separately written audit record is useful evidence, but does not by itself make the audit update and target merge atomic.

What do foreachBatch and concurrency change?

Official behavior: Databricks documents at-least-once writes in foreachBatch. A callback can run again. The existence of a checkpoint does not remove the need to reason about a merge that committed before the callback’s progress was recorded.

Delta’s streaming reference describes txnAppId and txnVersion for idempotent DataFrameWriter writes. It also requires a new application ID when a deleted checkpoint restarts batch numbering. Do not assume these writer options automatically wrap arbitrary SQL MERGE statements or external side effects.

Official concurrency model: Delta concurrency control reads a snapshot, stages changes, then validates before committing; conflicting operations can fail. That transactional protection and the business-key rules above solve different problems.

Operational inference: distinguish three situations before retrying:

  • Unknown commit outcome: establish what reached the target, then use the same logical input and repeatable rule.
  • Known concurrency conflict: re-read the target and reevaluate against the preserved input. Do not blindly apply a precomputed stale result.
  • Changed source or schema contract: validate again; this is a new input or migration decision, not merely another delivery attempt.

Before releasing the result downstream, compare expected keys and values, rejected-row counts, and equal-sequence conflicts. Row counts alone cannot detect an old amount replacing a newer one. Include a retry after a successful commit in the test plan, not just a retry after a clean failure.

Delta Lake MERGE interview questions to practice

QuestionFocus for your answer
Design idempotent daily loads with dedupingPredict source cleanup, target changes, and rerun output.
Design an idempotent SQL ETL for late dataSeparate latest-state corrections from aggregate effects.
Deduplicate events and rank products with SQLDefend event identity and deterministic ranking.
Design SQL cleaning, mapping, dedupe, and keyingExplain normalization, keys, and conflicting payloads.
Explain ETL schema changes and ensure integrityShow how a new field changes validation and mappings.

To rehearse Delta Lake MERGE interview questions in your next Data Engineer practice session, trace order 7 through the correct match condition and the freshness-in-ON variant. Predict both reruns before explaining which invariant each statement preserves.

Sources and Further Reading