Debugging an Incremental Model That Produces Duplicate Rows

1. Why unique_key didn't prevent duplicates

unique_key tells merge which column to match existing rows against — it does not deduplicate the incoming delta itself before the merge runs. If a single incremental run's SELECT returns two rows for the same subscription_id (e.g., the billing system emitted a pending row and then, moments later within the same batch window, an active row for the same subscription, both with updated_at timestamps inside the current run's filtered range), the MERGE statement is asked to match two source rows to the same target key in one operation. Most warehouses either error on this (some SQL engines reject a MERGE where the source has duplicate keys) or — depending on the engine and exact SQL generated — nondeterministically apply one of the two rows, which can look like "sometimes it merges correctly, sometimes it doesn't" across different runs. The unique_key config only governs matching an existing target row; it does nothing about duplicate keys within the source of a single run, which is a separate problem the query has to solve for itself.

2. Why the late correction is dropped

The incremental filter is where updated_at > (select max(updated_at) from {{ this }}) — a strict high-water-mark filter. This assumes updated_at values arrive in non-decreasing order over time, which is false here: the billing system's delayed correction has an updated_at older than the max already merged into the table. Once the table's max updated_at has advanced past that timestamp (from any later, unrelated row), the filter permanently excludes the correction — there is no run, ever, where updated_at > max(updated_at) is true for it again. The row isn't queued or retried; it's simply never selected by any future run of this query.

3. Fixed model

{{ config(
    materialized='incremental',
    unique_key='subscription_id',
    incremental_strategy='merge',
    on_schema_change='fail'
) }}

with source as (
    select *
    from {{ ref('stg_subscriptions') }}
    {% if is_incremental() %}
    where updated_at > (
        select coalesce(max(updated_at), '1900-01-01') from {{ this }}
    ) - interval '3 days'
    {% endif %}
),

deduplicated as (
    select
        *,
        row_number() over (
            partition by subscription_id order by updated_at desc
        ) as rn
    from source
)

select
    subscription_id,
    customer_id,
    plan_tier,
    status,
    updated_at
from deduplicated
where rn = 1

Two independent fixes, each addressing one of the two bugs:

• A lookback window (- interval '3 days', tuned to how delayed the billing system's corrections can realistically be) re-includes rows in a buffer behind the current watermark on every run, so a late correction within that window gets picked up on a subsequent run instead of being permanently excluded. Because the strategy is merge on unique_key, re-selecting rows that were already merged in a prior run is safe — it's just an idempotent re-upsert, not a duplicate.
• Deduplicating the delta with row_number() partitioned by subscription_id, ordered by updated_at desc, keeping rn = 1 guarantees the source side of the merge never contains two rows for the same key in a single run — collapsing exactly the situation that produced Finance's duplicate rows — and it also naturally picks the most recent status when multiple updates for the same subscription land in one incremental window, rather than leaving that to chance.

Both changes are cheap (a window function and a wider where clause, not a full-refresh) and compose safely with merge's upsert semantics, which is what makes reprocessing a small overlapping window on every run correct rather than wasteful.