String Is Too Long To Be Compared Using Collation

Issue

The following error occurs when Fivetran attempts to load data that exceeds the size limit for Snowflake collation operations:

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

Environment

Destination: Snowflake

Resolution

To resolve this issue, remove the custom collation configured for your Snowflake destination and recreate the affected tables without collation.

We do not recommend using custom collations because they can affect data integrity.

Remove custom collation

Remove the custom collation configured at the account, database, schema, or table level to prevent the recreated tables from inheriting it.

For example, to remove the default collation from a database, run the following statement:

ALTER DATABASE <database_name> UNSET DEFAULT_DDL_COLLATION;

If you choose to continue using collation elsewhere in your destination, ensure that string values used in collation operations remain within the 64 MB limit for Snowflake collation.

Recreate affected tables

Snowflake does not support changing the collation of an existing column. Recreate each affected table so that its columns no longer use collation.

Use the Snowflake role Fivetran uses when you recreate the tables, so Fivetran retains ownership of the recreated tables.

If an affected table uses masking policies, ensure the Snowflake role you use to copy the data can read the unmasked values. Otherwise, the copy operation may write masked values to the recreated table.

If the role cannot access the unmasked data, do not proceed with the copy operation. Work with your Snowflake administrator to provide the required access before continuing.

Pause the connection in the Fivetran dashboard.
For each affected table, do the following:
1. Run the following query to retrieve the table DDL, and save the output:
```
SELECT GET_DDL('TABLE', '<schema_name>.<table_name>', TRUE);
```
  Do not execute the retrieved DDL yet because it may contain CREATE OR REPLACE TABLE, which would replace the existing table.
2. Rename the existing table to preserve its data:
```
ALTER TABLE <schema_name>.<table_name>
RENAME TO <schema_name>.<table_name>_unsupported_collation;
```
3. Modify the DDL you saved:
  - Replace CREATE OR REPLACE TABLE with CREATE TABLE.
  - Remove all COLLATE clauses.
  - Keep the remaining table definition, including the primary key, unchanged.
  For example, change:
```
CREATE OR REPLACE TABLE MY_SCHEMA.MY_TABLE (
  ID NUMBER(38,0) NOT NULL,
  NAME VARCHAR(134217728) COLLATE 'en-ci',
  DESCRIPTION VARCHAR(134217728) COLLATE 'en-ci',
  _FIVETRAN_SYNCED TIMESTAMP_TZ(9),
  PRIMARY KEY (ID) RELY
);
```
  To:
```
CREATE TABLE MY_SCHEMA.MY_TABLE (
  ID NUMBER(38,0) NOT NULL,
  NAME VARCHAR(134217728),
  DESCRIPTION VARCHAR(134217728),
  _FIVETRAN_SYNCED TIMESTAMP_TZ(9),
  PRIMARY KEY (ID) RELY
);
```
  If the table has a composite primary key, preserve it in the recreated table.
4. Execute the modified DDL to recreate the table without custom collation.
5. Copy the data from the renamed table into the recreated table:
```
INSERT INTO <schema_name>.<table_name>
SELECT *
FROM <schema_name>.<table_name>_unsupported_collation;
```
6. Compare the recreated table with the renamed table to confirm that the recreated table contains the expected data.
7. Restore any grants and applicable table-level configurations that are missing from the recreated table. These may include:
  - Search Optimization Service
  - Automatic Clustering
  - DATA_RETENTION_TIME_IN_DAYS
  - Other table-specific settings
After you recreate and verify all affected tables, resume the connection in the Fivetran dashboard.
Confirm that the next sync succeeds and that the recreated tables contain the expected data.
After you verify that the recreated tables are functioning correctly, drop the corresponding backup tables:
```
<table_name>_unsupported_collation
```

Cause

Snowflake VARCHAR columns support strings up to 128 MB. However, Snowflake supports collation operations only when the resulting string is 64 MB or less.

When a column uses collation and an operation requires Snowflake to process a string that exceeds this limit, the operation fails with the String is too long to be compared using collation error.

For more information, see Snowflake documentation.