String Is Too Long To Be Compared Using Collation

Issue

The following error appears when we attempt to load 8+ MB of data in a column with custom collation enabled:

net.snowflake.client.jdbc.SnowflakeSQLException: String is too long to be compared using collation

Environment

Destination: Snowflake

Resolution

To resolve this issue, remove any custom collation enabled for your Snowflake warehouse at the account, database, schema, table, or column level.

For example, you can use the following query to remove the collation property at the database level:

ALTER DATABASE {database_name} UNSET DEFAULT_DDL_COLLATION

We do not recommend applying custom collation because it can impact data integrity. However, if you choose to use collation, make sure that the data in the columns remains under 8 MB in size.

Cause

This error occurs when we attempt to load data exceeding 8 MB into a column with collation enabled. Snowflake's VARCHAR column has a maximum data storage limit of 16 MB, and Fivetran tries to load the maximum data size of 16 MB into all VARCHAR columns. However, custom collation reduces column storage (String) from 16 MB to 8 MB. Learn more in Snowflake's Collation is Supported only for Strings up to 8MB documentation.