alter-table statements during incremental run leads to potential errors on Snowflake

[githoov]

Describe the bug

For incremental models, DBT generates the following:

alter table <table> add column <col>__dbt_alter character varying(17);

update <table> set <col>__dbt_alter = <col>;

alter table <table> drop column <col> cascade;

to accommodate expanding characters in existing columns. This is necessary for Redshift, but Snowflake this is not necessary, as the string data type automatically adjusts depending on the data in the table.

In some sense, this could even be thought of as a bug, as this series of commands will fail if the dropped column is part of the cluster key in the table, leading to a failed run.

Steps To Reproduce

This is baked into an incremental run of DBT.

System information

• snowflake

The output of dbt --version:

installed version: 0.14.0
   latest version: 0.14.0

[drewbanin]

Thanks for the report @githoov - we'll check it out

[drewbanin]

fixed in #1690

Note: column expansion is still necessary on Snowflake, but we can leverage a statement like:

alter table {{ relation }} alter {{ column_name }} set data type {{ new_column_type }};

This has the benefit of being atomic, simple, and presumably a lot faster than the existing approach:

    alter table {{ relation }} add column {{ tmp_column }} {{ new_column_type }};
    update {{ relation }} set {{ tmp_column }} = {{ column_name }};
    alter table {{ relation }} drop column {{ column_name }} cascade;
    alter table {{ relation }} rename column {{ tmp_column }} to {{ column_name }}

This should also work around the problem noted in this issue: a column in a clustering key cannot be dropped, but it can have its type changed from eg. varchar(12) to varchar(36).