ERROR:

If HVR connects to a target Snowflake database location, there is a potential issue if the Snowflake database has been declared with a default collation.

When DDL schema of a source replicated table is modified, and HVR attempts to modify the target table e.g.

alter table test1 modify column f1 varchar(100); 

... we saw the below Snowflake Error:

SQL compilation error: cannot change column f1 from type "VARCHAR(50) COLLATE 'utf8'" to "VARCHAR(100)" because they have incompatible collations.

To reproduce this error follow the below scenario.

SCENARIO:

a). HVR setup as a target Snowflake location.
NB:  Snowflake database created with a customised/specified DDL collation specified e.g.

ALTER DATABASE SNOWFLAKE_DB1 SET DEFAULT_DDL_COLLATION = 'UTF8'

NB: DEFAULT_DDL_COLLATION can be set at ACCOUNT, DATABASE, SCHEMA and TABLE levels.

b). HVR performs a Refresh and creates a target table in Snowflake.
HVR generates a generic CREATE TABLE syntax, passed through to Snowflake

However when you describe the table definition, you will note a collation value is specified for character fields: e.g.

create table test1 (f1 varchar(50));

desc table test1; 
-- f1 type = "VARCHAR(50) COLLATE 'utf8'"

 c). Source location, e.g. SQL Server, issues an ALTER TABLE statement to expand the size of the table column, e.g.

alter table test1 modify column f1 varchar(100); 

-- ** SNOWFLAKE ERROR **
-- this fails because the modify is trying to set the collation to NULL, 
-- but the current collation is UTF8, and you can't change collation
-- i.e comparing NULL with UTF8

SUGGESTION:

Is it possible for HVR to interrogate the target definition of Snowflake table by looking at output of DESCRIBE TABLE command - and determine if there is a collation setting value used?  If a collation value is found, then use this collation setting in the required ALTER TABLE statement, e.g.

alter table test1 modify column f1 varchar(100) 
-- collation value found in Snowflake so include this...
COLLATE 'utf8'
; 

Currently we are using ADAPTDDL Action with OnEnrollBreak = WARNING.

Hope this is helpful.  Thanks