69. Apply a CDC Batch (MERGE Semantics)

Difficulty: Hard · Topics: Window Functions, Joins, Filtering & Selection

target is the current table (id, name, tier). changes is a batch of change events with id, op ('I' insert, 'U' update, 'D' delete), name, tier and an increasing sequence number seq. An id can change several times in one batch.

Apply the batch the way MERGE INTO would: for each id only its latest change counts; a delete removes the row, an insert or update writes the new values (an update for an unknown id is treated as an insert). Return the resulting table: id, name, tier.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

target

id	name	tier
1	Asha	free
2	Ben	pro
3	Chitra	free

changes

id	op	name	tier	seq
2	U	Ben	enterprise	1
3	D	null	null	2
4	I	Dev	free	3
4	U	Dev	pro	4

Expected output

id	name	tier
1	Asha	free
2	Ben	enterprise
4	Dev	pro

Hints

Hint 1

First reduce changes to the latest event per id: row_number over a window by id ordered by seq descending.

Hint 2

Rows of target with no change at all are kept as they are: a left_anti join finds them.

Hint 3

Union the untouched rows with the latest changes whose op is not 'D'.

Learn the concepts

MERGE INTO and upserts · 4 min read. Matched, not-matched and not-matched-by-source clauses, step by step, and the duplicate-source error.
Change Data Feed · 3 min read. Read the row-level inserts, updates and deletes made to a Delta table between two versions.
Copy-on-write vs merge-on-read · 3 min read. Two ways to apply updates and deletes, and which one fits a given workload.

PySpark functions you'll practise

Row Number
Window spec
join
left_anti
filter
select
withColumn
orderBy
unionByName

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · Learn · All problems