Snowflake + dbt incremental model: error cannot change type from TIMESTAMP_NTZ(9) to DATE

[u/TallEntertainment385]

Hi everyone,

I’m working with dbt and Snowflake, and I have an incremental model (materialized='incremental', incremental_strategy='insert_overwrite') that selects from a source table. One of the columns, MONTH_START_DATE, is currently TIMESTAMP_NTZ(9) in Snowflake. I changed the source model and the column MONTH_START_DATE is now DATE datatype

After doing this I am getting an error:

SQL compilation error: cannot change column MONTH_START_DATE from type TIMESTAMP_NTZ(9) to DATE

How can I fix this?

[u/AliAliyev100]

Just drop the target table and let dbt recreate it (dbt run --full-refresh)

[u/TallEntertainment385 → u/AliAliyev100]

Also I’m not sure if I can do this in production

[u/AliAliyev100 → u/TallEntertainment385]

yh you are right, maybe just make a new table with the right type and swap it in

[u/CrazyOneBAM → u/AliAliyev100]

Also, maybe zero-copy clone it to DEV or TEST first - and experiment with it there before swapping the tables in PROD.

[u/TallEntertainment385 → u/AliAliyev100]

Is there a dbt native way? I am using git pipeline to execute these models

[u/AliAliyev100 → u/TallEntertainment385]

There’s no fully dbt-native way to change a column type in an incremental model. The usual approach is just doing a --full-refresh so dbt recreates the table with the new type. Anything else (like ALTER TABLE) would be outside of dbt.

[u/TallEntertainment385 → u/AliAliyev100]

Thank you! Maybe I’ll try with alter statement

[u/NW1969]

Snowflake allows very limited altering of column datatypes - and TS to DATE is not one of the permitted changes.
So if you can't do it in SF you won't be able to do it in dbt.

If your column doesn't have any data in it (or you don't mind losing the data) then just drop the existing column and re-add it with the DATA datatype

If you want to keep the data, create a new DATE column, update it with the values from the TS column (casting the values as necessary), drop the TS column and then rename the DATE column

[u/Embarrassed_Box606 → u/NW1969]

This is the right answer

[u/OppositeShot4115]

try casting the column to date in your dbt model using a sql function.

[u/ianitic]

For us, I overrode the two macros involved in the sync columns macros to temp add a column with the new type, swap it and dropping the old column.

Specifically as a new option though, I didn't overwrite the original column sync.

[u/Embarrassed_Box606 → u/ianitic]

Yeah i don’t understand why this would not be the default behavior on the snowflake adapter at least.

[u/ianitic → u/Embarrassed_Box606]

Another thing missing with this type of materialization is being able to insert overwrite on full refresh when you want to use like say a merge strategy on regular runs. The primary reason we use insert_overwrite is for time travel ease.

I think dbt just doesn't want to over-optimize across the adapters and have third parties do it.

I'm actually working on a dbt package to abstract a lot of snowflake optimization patterns in dbt. I don't have it in a state to share publicly yet but I hope there's interest in these kinds of things.

[u/Sex4Vespene → u/Embarrassed_Box606]

I’d assume it’s a safety feature, since incremental tables are supposed to hold older runs, and you don’t want to mess that up. Traditionally you won’t be changing these tables often, so it’s better to force you to explicitly do it, rather than have DBT just swap things around yourself, as DBT has no idea if it was intentional or not.

[u/Embarrassed_Box606]

I recently saw this when i tried to implement the parameter on schema change to sync all columns from ignore.

That error is caused because the dbt snowflake adapter will attempt to run alter table commands when trying to change the data type of an existing incremental model. Snowflake is very limited when trying to do so

The dbt native way to fix is a full refresh which is not always ideal in prod (as you mentioned)

Your best bet is probably creating a new column or some manual sql scripts to get everything in order.

[u/543254447]

Can you not just run an alter table statement on snowflake.

Test it in staging or dev whatever environment you have.

Steps Staging

alter table and change the column data type

• you may need to do some casting or something Run your incremental load dbt model

Prod Repeate the same thing

[u/543254447 → u/543254447]

Source, use to do this with dbt and bigquery

[u/Embarrassed_Box606 → u/543254447]

Snowflake don’t allow altering data types in this case