Snowflake MERGE Failed With ‘Duplicate row detected from DML evaluation’: The Source Query Returned Two Rows for One Key

The MERGE ran for weeks. Then a CDC replay lands a second version of the same customer row, or a late-arriving dimension record slips past the dedup step, and the statement fails with a duplicate-row error from DML evaluation.

The message points at the write. The defect is upstream, in the grain of the source query. Snowflake is not complaining that the target has duplicate keys. It is refusing to guess which of two source rows should win.

What Snowflake is actually objecting to

The MERGE documentation is explicit about the condition. When a merge joins a row in the target table against multiple rows in the source, and that target row is selected for update, the result is nondeterministic: the system cannot determine which source value to use.

That is not a data-quality warning. It is a correctness boundary. Snowflake’s documented behavior is to raise an error when ERROR_ON_NONDETERMINISTIC_MERGE is TRUE, which is the default. The same section of the docs lists the second nondeterministic case: a target row selected to be both updated and deleted.

So the failure is a guard, not a bug. It fires precisely when the source side of the join is not one row per join key.

Reproduce the condition before you touch the pipeline

You do not need the failing MERGE to diagnose this. Count source rows per join key. If any key has a count above one, the MERGE is nondeterministic by construction.

-- Replace the join key and source expression with your own.
SELECT
    customer_id,
    COUNT(*) AS source_rows
FROM (
    -- paste the exact subquery from your MERGE ... USING ( ... )
    SELECT customer_id, updated_at, status
    FROM raw.customer_cdc
    WHERE ingested_at >= '2026-10-01'
) AS src
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY source_rows DESC;

Run this as a standalone statement, not inside the MERGE. The output is the list of keys that will trip the guard. If the result is empty, the duplicate is coming from somewhere else in the source expression — a join fan-out inside the subquery, for example — and you should count rows per key at each intermediate step until the count diverges.

This is the cheapest diagnostic in the article. It costs one query and tells you whether the problem is real before you spend an hour reading the MERGE text.

The two documented fixes

The MERGE documentation offers two paths to a deterministic statement.

Collapse the source to one row per key. The docs recommend GROUP BY in the source clause so that each target row joins against at most one source row. Their example uses MAX(v) grouped by the key, which makes the chosen value explicit rather than arbitrary.

Make exactly one source row satisfy the matched clause. A merge is deterministic if exactly one source row satisfies a WHEN MATCHED ... THEN UPDATE clause and no other source rows satisfy any WHEN MATCHED clauses. This is the same invariant stated from the other direction: one winner per target row.

Both fixes are the same fix. The source must be deduplicated before the MERGE evaluates it.

The deduplicating CTE pattern

A CTE is a named subquery defined in a WITH clause, usable as a temporary view within the statement that defines it. That makes it a natural place to enforce the one-row-per-key invariant without changing the MERGE’s matched and not-matched logic.

WITH deduped_source AS (
    SELECT
        customer_id,
        updated_at,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC, ingested_at DESC
        ) AS rn
    FROM raw.customer_cdc
    WHERE ingested_at >= '2026-10-01'
)
MERGE INTO analytics.dim_customer AS tgt
USING (
    SELECT customer_id, updated_at, status
    FROM deduped_source
    WHERE rn = 1
) AS src
ON tgt.customer_id = src.customer_id
WHEN MATCHED THEN UPDATE SET
    tgt.updated_at = src.updated_at,
    tgt.status = src.status
WHEN NOT MATCHED THEN INSERT (customer_id, updated_at, status)
    VALUES (src.customer_id, src.updated_at, src.status);

ROW_NUMBER() returns a unique row number for each row within a window partition, starting at 1 and continuing sequentially. Filtering to rn = 1 leaves exactly one row per customer_id, which is the invariant the MERGE needs.

The ORDER BY inside the window is where you encode your business rule. updated_at DESC picks the most recent version. ingested_at DESC breaks ties by arrival order. If neither column exists in your source, you have a different problem: the source has no defined recency, and no dedup pattern can invent one.

Snowflake’s QUALIFY clause can replace the nested subquery. It filters the results of window functions and is evaluated after window functions are computed, so the ranking and the filter can sit in one SELECT:

SELECT customer_id, updated_at, status
FROM raw.customer_cdc
WHERE ingested_at >= '2026-10-01'
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC, ingested_at DESC
) = 1;

Either form works. The CTE version is easier to test in isolation because you can run the SELECT without the MERGE and inspect the row count per key.

Why ROW_NUMBER beats GROUP BY with MAX in most CDC sources

The docs’ GROUP BY example is correct and sufficient when the source has one meaningful value column. It becomes awkward when the source has many columns that must stay consistent with each other.

MAX(v) GROUP BY k aggregates each column independently. If updated_at comes from one row and status comes from another, the merged row is a composite that never existed in the source. That is not a Snowflake defect; it is what independent aggregation does. ROW_NUMBER filtered to rank 1 keeps the full payload of a single source row, so the target receives a row that actually existed upstream.

This is a design preference, not a documented requirement. If your source has one value column and no recency ordering, GROUP BY is simpler and the docs already bless it.

The INSERT-side asymmetry that partial fixes miss

Here is the trap. The duplicate-row error fires on the matched branch. Teams fix that branch, ship, and later discover doubled rows in the target for brand-new keys.

The documentation explains why. If the MERGE contains a WHEN NOT MATCHED ... THEN INSERT clause, there are no matching rows in the target, and the source contains duplicate values, then the target gets one copy of the row for each copy in the source. No error. No warning. Just duplicates.

So a dedup applied only to the WHEN MATCHED path is not a fix. It silences the loud failure and leaves the silent one. The dedup must cover the entire source subquery, which is why the CTE pattern above wraps the whole USING clause rather than a single branch.

If you have already shipped a partial fix, check the insert side directly:

SELECT customer_id, COUNT(*) AS target_rows
FROM analytics.dim_customer
GROUP BY customer_id
HAVING COUNT(*) > 1;

Any row here is evidence that the source duplicates reached the target through the insert path.

The tempting one-line fix, and what it costs

You can set ERROR_ON_NONDETERMINISTIC_MERGE = FALSE. The docs say what happens next: one row from among the duplicates is selected to perform the update or delete, and the row selected is not defined.

That is the entire trade. The statement succeeds. The winning row is undefined. The same MERGE can produce different target values on different runs, and nothing in the query text tells you which run you got.

For a pipeline that feeds a dimension table, this converts a loud failure into silent dimension drift. The drift is not reproducible, so it cannot be debugged from the error log. It surfaces later as a report that disagrees with the source system, and the investigation starts from the report rather than from the MERGE.

The parameter is documented and legitimate for cases where any source row is acceptable. If your business rule is genuinely “any of these is fine,” setting it to FALSE is honest. If your business rule is “the latest one wins,” setting it to FALSE is a correctness debt that the next on-call engineer inherits without a ticket.

Price the fix in hours, not sprints

The deduplicating CTE is a few lines inside the source subquery. It does not change the target schema, the matched clauses, or the orchestration. It is reviewable in one sitting by someone who knows the source table’s grain.

The pre-flight count is one query. Run it as a separate statement in the pipeline, or as a dbt test on the source model, and the regression is caught before the MERGE executes rather than after it fails.

The alternative — flipping the session parameter — is a one-line change that costs nothing today and shifts the cost to whoever debugs the drift. That is the maintenance tax this architecture charges: the fix that makes the error go away is cheaper than the fix that makes the data correct, and the difference is paid later by someone who was not in the room.

What to assert before every MERGE

Treat “source rows per join key = 1” as an invariant, not an error to suppress. The diagnostic is the grouped count. The fix is the dedup in the source. The parameter is not a fix.

When the error fires again — and on a CDC-fed source it will — start at the source subquery’s row count per key. The message names DML evaluation, but the defect is in the grain of the query that feeds it.

FAQ

Does this error mean my target table has duplicate keys?

No. The documented condition is multiple source rows matching one target row under a matched update or delete. The target’s key uniqueness is a separate question, and the pre-flight count above checks the source, not the target.

Why did the MERGE work for weeks and then fail?

The source grain changed. A CDC replay, a backfill, or a late-arriving record added a second row for a key that previously had one. The MERGE text did not change; the data feeding it did.

Can I just add DISTINCT to the source?

DISTINCT collapses identical rows. If the two source rows differ in any selected column — a different updated_at, a different status — DISTINCT leaves both, and the MERGE still fails. You need a rule for which row wins, which is what ROW_NUMBER with an ORDER BY provides.

Is setting ERROR_ON_NONDETERMINISTIC_MERGE = FALSE ever correct?

Only when any matching source row is genuinely acceptable for the update. The docs state the selected row is not defined, so if your downstream consumers depend on which value lands, the parameter is not a fix.

Does the dedup need to cover the INSERT branch too?

Yes. Source duplicates with no target match are inserted once per source copy rather than raising an error. A dedup applied only to the matched branch leaves the insert path open to silent duplicates.

Where can I read the exact rules?

The Snowflake MERGE documentation covers the nondeterministic cases, the ERROR_ON_NONDETERMINISTIC_MERGE behavior, the GROUP BY remedy, the deterministic-merge conditions, and the insert-side duplicate behavior. The ROW_NUMBER and QUALIFY pages cover the ranking and filtering mechanics used above.