Snowflake MERGE fails with 'Duplicate row detected during DML action' (100090): find and fix it

#Symptom

100090 (42P18): Duplicate row detected during DML action
Row Values: [1001, "PAID", ...]

A MERGE that's been fine for months suddenly fails, often after an upstream change or a replay. Your task or dbt model now fails on every run.

#Why it happens

If a target row matches more than one source row, Snowflake can't tell which one should win. The result could be different each time, so by default it raises an error. This is controlled by the ERROR_ON_NONDETERMINISTIC_MERGE parameter, which is TRUE by default.

The cause is almost always in the source query, not the target table.

#Step 1: find the failing statements

SELECT start_time, user_name, warehouse_name, query_id, LEFT(query_text, 200) AS sql_text, error_message
FROM TABLE(information_schema.query_history(
       end_time_range_start => DATEADD('hour', -24, CURRENT_TIMESTAMP()),
       result_limit => 1000))
WHERE error_code = '100090'
ORDER BY start_time DESC;

#Step 2: find the duplicate keys

Use the exact same source query and join key as the MERGE:

SELECT order_id, COUNT(*) AS copies
FROM stg_orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY copies DESC
LIMIT 50;

Now see how the copies differ. This tells you what happened:

SELECT *
FROM stg_orders
WHERE order_id IN (
    SELECT order_id FROM stg_orders GROUP BY order_id HAVING COUNT(*) > 1
)
ORDER BY order_id, updated_at
LIMIT 100;

#Step 3: check the stream case

A standard stream on a table shows an update as two rows: a DELETE and an INSERT for the same key. Multiple changes can also collapse in unexpected ways. Merging directly from a stream, without care, is a classic cause of this error.

SELECT order_id,
       METADATA$ACTION,
       METADATA$ISUPDATE,
       updated_at
FROM orders_stream
QUALIFY COUNT(*) OVER (PARTITION BY order_id) > 1
ORDER BY order_id, updated_at
LIMIT 100;

Selecting from a stream doesn't consume it. Only a successful DML statement that reads from it does. So this check is safe. Use the pattern in Step 5 to handle the pairs correctly.

#Step 4: fix the batch with a deterministic dedupe

Pick the winner with a rule, usually the newest version, and put a tie-breaker in it:

MERGE INTO orders AS t
USING (
    SELECT *
    FROM stg_orders
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY order_id
        ORDER BY updated_at DESC, load_ts DESC, file_name DESC
    ) = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at >= t.updated_at THEN UPDATE SET
    t.status = s.status,
    t.amount = s.amount,
    t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount, updated_at)
    VALUES (s.order_id, s.status, s.amount, s.updated_at);

If ORDER BY columns can tie, ROW_NUMBER() may choose a different row each time. That's why the tie-breaker columns matter. The s.updated_at >= t.updated_at condition stops late-arriving old records from overwriting newer ones.

Before relying on it, confirm that the deduped source has one row per key:

SELECT COUNT(*) AS total, COUNT(DISTINCT order_id) AS keys
FROM (
    SELECT * FROM stg_orders
    QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC, load_ts DESC, file_name DESC) = 1
);

total and keys should be the same.

#Step 5: merge from a stream correctly

Drop the DELETE half of each update, and handle real deletes separately:

MERGE INTO orders AS t
USING (
    SELECT *
    FROM orders_stream
    WHERE NOT (METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE = TRUE)
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY order_id
        ORDER BY updated_at DESC
    ) = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.status = s.status, t.updated_at = s.updated_at
WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT' THEN
    INSERT (order_id, status, updated_at) VALUES (s.order_id, s.status, s.updated_at);

Test this on a copy, especially for tables with many deletes. A failed MERGE leaves the stream unconsumed, so nothing has been lost. But watch stale streams if the failure runs on past your retention period.

#Don't turn off the error

You can make the error go away:

-- Don't do this as a fix
ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = FALSE;

Caution

With this set to FALSE, Snowflake picks a matching source row without telling you which one. Your pipeline goes green, while an old status might overwrite a new one, and the result can differ between runs. You've traded a loud failure for silent data corruption. Fix the source instead.

Check that nobody's already set it somewhere:

SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_MERGE' IN ACCOUNT;
SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_MERGE' IN SESSION;

The same concern applies to UPDATE ... FROM. Its matching parameter, ERROR_ON_NONDETERMINISTIC_UPDATE, is FALSE by default. So an update with a duplicated join can quietly pick any row, with no error at all.

#Related traps

These don't give you the 100090 error, but they often turn up in the same investigation.

Check the target for duplicates too:

SELECT order_id, COUNT(*)
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

#dbt incremental models

With the merge strategy, the same error appears if a unique_key isn't unique in the model's new rows. Add a dedupe step to the model:

{{ config(materialized='incremental', unique_key='order_id') }}

select *
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
qualify row_number() over (partition by order_id order by updated_at desc, load_ts desc) = 1

Also see incremental model drift.

#Find out how it got in

The dedupe keeps the pipeline running, but you should also find out why duplicates appeared. For files, check what was loaded:

SELECT file_name, last_load_time, row_count, status
FROM TABLE(information_schema.copy_history(
       table_name => 'STG_ORDERS',
       start_time => DATEADD('day', -3, CURRENT_TIMESTAMP())))
ORDER BY last_load_time DESC;

Also see COPY INTO load metadata, since a FORCE = TRUE reload is a common source of repeats.

#Prevent it next time

• Treat one row per key as a tested rule on every staging model. See data quality checks.
• Write each MERGE with an explicit dedupe, and a tie-breaker.
• Agree on keys and ordering columns in your data contracts.
• Alert on error code 100090, so you can catch it before downstream reports go stale.