What causes Merge Non-Deterministic Error (100088)?

The source dataset in the MERGE contains duplicate rows for the join key, meaning one target row matches more than one source row. The MERGE join condition is too broad and unintentionally matches multiple source rows to a single target row. A staging table used as the MERGE source was not deduplicated before the MERGE ran. The join key is based on a non-unique column combination

How do I fix Merge Non-Deterministic Error (100088)?

Deduplicate the source before the MERGE: `WITH deduped AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ORDER BY DESC) AS rn FROM source) SELECT * FROM deduped WHERE rn = 1`.. Add an assertion to fail fast if duplicates exist: `SELECT COUNT(*) - COUNT(DISTINCT ) FROM source` — if nonzero, raise an error before the MERGE.. Tighten the MERGE join condition to ensure it produces a 1:1 match between source and target rows.. Set `ERROR_ON_NONDETERMINISTIC_MERGE = FALSE` at the session level only as a last resort — this allows non-deterministic updates which can silently produce incorrect results.

High severitysql

Power BI Refresh Error:
Merge Non-Deterministic Error (100088)

What does this error mean?

A Snowflake MERGE statement matched a target row against multiple source rows, making the update non-deterministic. Snowflake rejects this when ERROR_ON_NONDETERMINISTIC_MERGE is set to true (the default since 2022).

Common causes

1The source dataset in the MERGE contains duplicate rows for the join key, meaning one target row matches more than one source row
2The MERGE join condition is too broad and unintentionally matches multiple source rows to a single target row
3A staging table used as the MERGE source was not deduplicated before the MERGE ran
4The join key is based on a non-unique column combination

How to fix it

1Deduplicate the source before the MERGE: `WITH deduped AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY <key> ORDER BY <updated_at> DESC) AS rn FROM source) SELECT * FROM deduped WHERE rn = 1`.
2Add an assertion to fail fast if duplicates exist: `SELECT COUNT(*) - COUNT(DISTINCT <key>) FROM source` — if nonzero, raise an error before the MERGE.
3Tighten the MERGE join condition to ensure it produces a 1:1 match between source and target rows.
4Set `ERROR_ON_NONDETERMINISTIC_MERGE = FALSE` at the session level only as a last resort — this allows non-deterministic updates which can silently produce incorrect results.

Frequently asked questions

Why did Snowflake start raising this error when it didn't before?

Snowflake changed the default of ERROR_ON_NONDETERMINISTIC_MERGE from FALSE to TRUE in 2022. Pipelines that relied on non-deterministic MERGE behavior started failing after this change.