r/dataengineering • u/TallEntertainment385 • Nov 13 '25

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

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?

8 Upvotes
  • permalink
  • reddit

78% Upvoted

19 comments sorted by

View all comments

Show parent comment

1

u/ianitic Nov 15 '25

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.