An oral-history ingestion job normally appends each interview transcript's metadata to a Lakehouse table the same day it's recorded, but a batch of transcripts from a rural branch arrives three weeks late because of a slow network connection, and some of those late records are corrections to transcripts that were already partially loaded earlier. Which approach correctly handles this late-arriving, partly-overlapping data?
Select an answer to reveal the explanation.
Short Explanation
Think of it like a librarian receiving a stack of index cards weeks late, some of which correct cards already filed. The right move isn't to refile everything as brand new — it's to swap in the corrected cards and only file the truly new ones.
Full Explanation
An upsert, typically implemented as a MERGE keyed on a natural identifier like interview ID, correctly reconciles late-arriving data against what's already loaded: rows that already exist get updated with the corrected values, and rows that represent genuinely new transcripts get inserted, all in one pass. That directly handles the described mix of corrections and new content. Rejecting the entire batch discards legitimate new transcripts along with the corrections, which throws away real data purely because it arrived outside the usual window. A plain append using on-time-load logic ignores the stated overlap entirely, creating duplicate rows for every transcript that was already partially loaded and corrupting downstream counts and reports. Waiting indefinitely for every branch to be on time isn't a data-handling technique at all — it simply delays the whole pipeline and doesn't scale, since some branch will always be the slow one. Before running the merge, confirm the natural key used for matching is actually unique per interview across branches, since a weak key could quietly merge two unrelated transcripts together.