Merge matches incoming rows to stored rows using unique_key. A matching order replaces its prior current state, while a new order inserts.
The key must identify the model grain. A user key is too broad for a subscription model when one user can own two subscriptions. Partial update batches expose that mistake because only one of those records may arrive.
The SELECT still decides what merge receives. A correction behind the stored maximum needs a documented overlap, and the overlapping rows need keyed replacement to make replay safe. Deduplicate before matching: a merge setting can't choose between competing source updates.
These tasks start with an existing target and then change the raw source. Your result must match an independent current-source query after both the update and a repeated command, including the older rows that stay outside a bounded batch.
{{ config(materialized='incremental', incremental_strategy='merge', unique_key='order_id') }}
with current_rows as (select order_id, customer_id, lower(trim(status)) as status, amount,
ordered_at, updated_at, upper(trim(currency)) as currency
from {{ source('shop', 'orders') }} where order_id is not null
qualify row_number() over (partition by order_id order by updated_at desc, amount desc nulls last) = 1)
select * from current_rows where true
{% if is_incremental() %}
and updated_at >= (select coalesce(max(updated_at), timestamp '1900-01-01') - interval '2 days' from {{ this }})
{% endif %}
The latest source row is chosen before a two-day correction window feeds keyed replacement.