Error: Duplicate Row Detected During DML Action

Issue

My sync fails with the following error:

Duplicate row detected during DML action

Environment

Destination: Snowflake

Resolution

Check collation settings

Check the account-level collation setting:

SHOW PARAMETERS IN ACCOUNT LIKE 'DEFAULT_DDL_COLLATION';

If the value is set to a case-insensitive collation (such as en-ci), you must update the collation configuration.

Remove or override collation

Option 1: Change account-level collation (recommended)

If you have account administrator access, unset the account-level collation:

ALTER ACCOUNT UNSET DEFAULT_DDL_COLLATION;

Option 2: Set database-level collation to case-sensitive

If you cannot modify the account-level setting, set the database collation to a case-sensitive value:

ALTER DATABASE <database_name> SET DEFAULT_DDL_COLLATION = 'utf8';

Align table-Level collation

If tables were created while case-insensitive collation was enabled, you must recreate them to apply the correct default collation.

Option 1: Drop and re-sync table (recommended)

Drop the affected table in Snowflake.
Perform a table re-sync in the Fivetran dashboard to ensure that the table is recreated with the correct collation.

Option 2: Recreate table manually

Create a temporary table with the default collation:

CREATE TABLE <table_name>_temp AS
SELECT * FROM <table_name>;

Drop the original table:
```
DROP TABLE <table_name>;
```

Rename the temporary table:

ALTER TABLE <table_name>_temp RENAME TO <table_name>;

Resume the Fivetran sync.

Verify and resume sync

Confirm the account collation is case-sensitive (empty or utf8):

SHOW PARAMETERS IN ACCOUNT LIKE 'DEFAULT_DDL_COLLATION';

Resume or trigger a Fivetran sync.

Cause

This error occurs during a MERGE operation when multiple incoming rows from the staging table match a single row in the target table.

The most common cause is case-insensitive collation (for example, en-ci) enabled at the account, database, schema, or table level.

How this happens

Fivetran loads incoming data into a staging table. The staging table inherits the account-level or schema-level collation.
During the MERGE operation, Fivetran matches incoming rows to existing rows using primary keys.
With case-insensitive collation enabled, Snowflake treats values such as abc and aBc as identical.
When multiple incoming rows match the same target row, Snowflake raises a duplicate row error.

Example

If the staging table contains two records with primary keys ABC and abc, and case-insensitive collation is enabled, Snowflake treats both values as the same key. When the MERGE attempts to process both rows, Snowflake detects a duplicate row operation and fails the query.

Best practices

To prevent this error in the future:

Use default collation: Avoid applying case-insensitive collation to Fivetran-managed schemas, tables, or columns.
Apply collation in queries only: If you need case-insensitive comparisons, apply collation in your SELECT queries rather than on the underlying tables:
```
SELECT * FROM table_name WHERE column_name COLLATE 'en-ci' = 'value';
```
Use views for custom behavior: Create views with case-insensitive collation rather than modifying base tables:
```
CREATE VIEW table_view AS
SELECT column_name COLLATE 'en-ci' AS column_name
FROM table_name;
```
Do not modify Fivetran-managed tables: Avoid altering collation settings on objects managed by Fivetran.