Apply Daily Upserts — Solution & ExplanationProblems / Apply Daily Upserts / EditorialApproach

Stack, don't join. UNION ALL of base and updates puts every known version of every product in one pile. A join-based merge needs three separate cases (matched, unmatched-left, unmatched-right); stacking needs none.

Latest-per-key. ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC), keep rn = 1. Untouched products have one row (their base row wins trivially); updated products have several (newest update wins); new products exist only in the updates pile.

Project and order.

Key InsightMERGE/upsert logic *feels* like a join problem — match rows, update matches, insert non-matches — but the join formulation multiplies edge cases (multiple updates per key being the one that breaks naive COALESCE(u.col, b.col) merges). Recasting state as "all versions, then keep the latest" turns it into the familiar dedup-by-recency idiom, handles any number of update rounds unchanged, and is exactly how append-only lakehouse tables materialize current state from a change log.Hints

Upsert = 'update or insert', and both cases collapse into one rule once the tables are stacked: latest updated_at per product_id wins. Stack first, decide second.

UNION ALL, not UNION: the rows aren't duplicates to be removed — they're versions to be ranked. (Spark's DataFrame .union() already behaves like UNION ALL.)

A product with two updates must surface the newest one, which is why the ranking runs over the stacked set rather than joining base to updates row-by-row.

ROW_NUMBER ... ORDER BY updated_at DESC, keep rn = 1 — the same latest-row-per-key idiom as deduplication, applied across two sources.

Solve Apply Daily Upserts yourself →