yoklainterview sim
ProgressHistory

Data Engineer Pe Incremental Cdc Merge Mechanics Interview Questions

75 verified Data Engineer Pe Incremental Cdc Merge Mechanics interview questions — solve with answers, learn from explanations, test yourself in a real simulation.

Try the real simulation →

Sample questions

Pe Incremental Cdc Merge MechanicsDifficulty 1
In a Spark SQL MERGE INTO statement, what does a WHEN MATCHED THEN UPDATE clause do?
  • aInserts a new row for every row present only in the source table
  • bUpdates a matching target row per the merge condition✓
  • cDeletes every row in the target table regardless of the merge condition
  • dRewrites the entire target table from scratch on every run
Explanation:WHEN MATCHED THEN UPDATE applies only to target rows that satisfy the merge condition against a source row; unmatched source rows are handled by a separate WHEN NOT MATCHED clause.
Pe Incremental Cdc Merge MechanicsDifficulty 2
In a Delta Lake MERGE INTO, what is the purpose of a WHEN NOT MATCHED THEN INSERT clause?
  • aIt marks source rows that failed validation for later review
  • bIt removes target rows that no longer appear in the source
  • cIt updates target rows whose values differ from the source
  • dIt inserts a new target row for an unmatched source key✓
Explanation:WHEN NOT MATCHED fires when a source row's key does not exist in the target, which is exactly the condition for inserting it as a new row — this is the mechanism that turns a merge into an upsert.
Pe Incremental Cdc Merge MechanicsDifficulty 1
A Debezium change event for a deleted source row looks like this:
{"before": {"id": 42, "status": "active"}, "after": null, "op": "d", "ts_ms": 1699999999000}

What does "op": "d" tell a consumer of this event?
  • aThe row was deleted; before shows its last state, after is null✓
  • bThe source row was duplicated into a new row with the same primary key
  • cThe event is part of the initial snapshot load, not a live change
  • dThe source database rejected the delete due to a foreign key constraint
Explanation:Debezium's op field marks the operation type; "d" is a delete. For deletes, after is null because the row no longer exists, while before still carries the row's state just prior to the delete.
Pe Incremental Cdc Merge MechanicsDifficulty 2
Why is 'the source side of a MERGE condition matches more than one row for the same target row' considered the most common MERGE INTO failure in practice?
  • aBecause most SQL engines silently ignore extra source rows instead of erroring
  • bBecause it only occurs when the target table has no primary key defined
  • cBecause Delta Lake rejects it when an update/delete clause is present✓
  • dBecause it triggers a full table scan on every subsequent query, not an error
Explanation:Delta Lake and similar engines raise an explicit error (e.g. UnsupportedOperationException) when the merge condition maps multiple source rows onto a single target row and a WHEN MATCHED update/delete clause exists, because it would be ambiguous which source row should win — and duplicate keys in a CDC or extract batch are a routine occurrence.
Pe Incremental Cdc Merge MechanicsDifficulty 3
Before running a MERGE INTO where the source might contain more than one row per key, which pre-processing step avoids the multiple-match error?
  • aSorting the source by key and relying on the sort order for correctness
  • bDeduplicating the source with ROW_NUMBER() per key✓
  • cAdding a LIMIT 1 clause to the MERGE INTO statement itself
  • dIncreasing spark.sql.shuffle.partitions before the merge runs
Explanation:Sorting alone doesn't remove duplicate rows, LIMIT 1 on a MERGE INTO statement isn't valid, and shuffle partition count has nothing to do with duplicate keys. Windowing the source by key, ordering by a recency column, and keeping only the top row per key guarantees at most one source row per key before the merge condition is evaluated.
Pe Incremental Cdc Merge MechanicsDifficulty 2
What does a Spark INSERT OVERWRITE TABLE t PARTITION (dt='2026-08-15') SELECT ... statement do to the target partition?
  • aIt replaces the entire partition's contents with the SELECT result✓
  • bIt appends the SELECT result to whatever rows already exist in that partition
  • cIt merges the SELECT result with existing rows on a primary key
  • dIt only updates columns that changed, leaving unchanged rows untouched
Explanation:INSERT OVERWRITE on a specific partition discards the existing content of that partition and replaces it wholesale with the query result — there is no row-level matching involved, unlike MERGE INTO.

By specialization

Pipelines / ETL 450Streaming 450Warehousing 450

By seniority

Data Engineer JuniorData Engineer MidData Engineer Senior

Other fields

BackendFrontendFullstackDevOps / CloudAI EngineerDatabaseMobileSecurityQA / Test AutomationGame DevelopmentML Engineer

Test yourself against the 1950-question Data Engineer bank.

Start interview