Merge with a recency guard

You meet it when#

Rows update rather than only arriving — customers, accounts, subscriptions, anything with a lifecycle. You need "insert if new, update if changed", and you need it to survive being run out of order.

The pattern#

merge into customers as target
using staging_customers as source
   on target.customer_id = source.customer_id

when matched and source.updated_at > target.updated_at then update set
    email      = source.email,
    plan       = source.plan,
    updated_at = source.updated_at

when not matched then insert (customer_id, email, plan, updated_at)
    values (source.customer_id, source.email, source.plan, source.updated_at);

The clause that earns its place#

and source.updated_at > target.updated_at.

Without it, a backfill that processes March after April overwrites April's values with March's. The job succeeds. The table is now wrong, and it is wrong in a way that no row count, null check or freshness test will find — the rows are all present, correctly typed, and stale.

This is the single most common bug in hand-rolled upserts, and it appears specifically during recovery, when you are least able to notice.

Deduplicate the source first#

MERGE errors — or worse, picks arbitrarily — when the source has more than one row per key. Rank first:

using (
    select * from staging_customers
    qualify row_number() over (partition by customer_id
                               order by updated_at desc, _loaded_at desc) = 1
) as source

Same deduplicate by rank pattern, doing load-bearing work.

When not to use it#

If you also need to know what the row looked like before, a merge is the wrong shape — it destroys history. That is a type-2 dimension, which inserts a new version rather than updating in place.

Ask which question the business will ask: "what is this customer's city?" or "what was it in February?" Only the second needs history, and building it later from data you overwrote is impossible.