String Is Too Long To Be Compared Using Collation

Issue

The following error occurs when we attempt to load data that exceeds 64 MB 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, ensure that the data in each column remains under 64 MB to avoid exceeding the Snowflake collation size limit.

Cause

Snowflake VARCHAR columns support up to 128 MB of data when collation is not applied, and Fivetran can load data up to this limit into VARCHAR columns. However, Snowflake custom collation enforces a hard limit of 64 MB for string values. As a result, VARCHAR columns with collation enabled cannot store more than 64 MB of data, regardless of Fivetran configuration. This limitation exists because some collation operations can increase string length, effectively reducing the maximum supported size to half of the standard VARCHAR limit. For more information, see Snowflake documentation.