Investigate DbtDatabaseSQLCompilationError exceptions in Snowflake

[grishick]

Several connections started throwing DbtDatabaseSQLCompilationError in Snowflake exceptions and this looks like a pattern. The common thread between these errors is cannot change column X from type A to B. Here are examples:

002108 (22000): SQL compilation error: cannot change column _AIRBYTE_START_AT from type VARCHAR(16777216) to NUMBER(38,0) 

002108 (22000): SQL compilation error: cannot change column DATE from type TIMESTAMP_TZ(9) to VARCHAR(16777216) 

002108 (22000): SQL compilation error: cannot change column CREATED_AT from type VARCHAR(16777216) to TIMESTAMP_NTZ(9) 

002108 (22000): SQL compilation error: cannot change column ID from type NUMBER(38,0) to FLOAT 

Related support tickets: 587, 581, 580, 574

[edgao]

Apparently snowflake doesn't support ALTER TABLE statements that change column data types to different types - https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html

The workaround is apparently to do an add column, update <table> set <new_column> = <old_column>, drop column, rename column <new_column> <old_column>. But that would probably be best-suited to happen in dbt-snowflake rather than our base-normalization (and maybe runs into weird issues with permissioning, etc)

[grishick]

Why would DBT need to alter table given that we don't support changing field types in schema evolution yet?

[edgao]

if you refresh schema but don't reset destination tables, then normalization will attempt to propagate column type changes to the destination (e.g. if the column was previously timestamp and becomes string, then dbt will detect that and automatically run an alter table).

[grishick]

For now, we will prioritize dropping SCD tables that will allow customers to have a workaround. Once we are working on schema evolution, we will address the ALTER TABLE case

[grishick]

Now that normalization deletes _scd tables, these errors should be gone after a reset.