[Bug] On schema change not adding new timestamp columns with precision

[jeremyyeo]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Looks like if we have newly added timestamp columns with a specific precision - we're not adding them with the precision.

Expected Behavior

Add new columns with specified precision for timestamp columns.

Steps To Reproduce

Build incremental model first go round:

-- models/foo.sql
{{ config(materialized='incremental', on_schema_change='append_new_columns') }}

select 'foo'::varchar(3) as old_varchar_three, 
        10.10::number(4, 2) as old_number_two_two,
        to_timestamp_ntz(1000000000, 3) as old_ts_three,
        to_timestamp_ntz(1000000000, 9) as old_ts_nine

$ dbt --debug build -s foo --full-refresh
...
04:51:42  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
create or replace transient table development_jyeo.dbt_jyeo.foo
         as
        (

select 'foo'::varchar(3) as old_varchar_three, 
        10.10::number(4, 2) as old_number_two_two,
        to_timestamp_ntz(1000000000, 3) as old_ts_three,
        to_timestamp_ntz(1000000000, 9) as old_ts_nine
        );
04:51:43  SQL status: SUCCESS 1 in 1.0 seconds
...

Quick describe to check data types:

Add some new columns to the model:

-- models/foo.sql
{{ config(materialized='incremental', on_schema_change='append_new_columns') }}

select 'foo'::varchar(3) as old_varchar_three, 
        10.10::number(4, 2) as old_number_two_two,
        to_timestamp_ntz(1000000000, 3) as old_ts_three,
        to_timestamp_ntz(1000000000, 9) as old_ts_nine,

        'foofoo'::varchar(10) as new_varchar_ten,
        10.10::number(4, 2) as new_number_two_two,
        to_timestamp_ntz(1000000000, 3) as new_ts_three,
        to_timestamp_ntz(1000000000, 9) as new_ts_nine 

$ dbt --debug build -s foo
04:54:27  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
create or replace  temporary view development_jyeo.dbt_jyeo.foo__dbt_tmp
  
   as (
    

select 'foo'::varchar(3) as old_varchar_three, 
        10.10::number(4, 2) as old_number_two_two,
        to_timestamp_ntz(1000000000, 3) as old_ts_three,
        to_timestamp_ntz(1000000000, 9) as old_ts_nine,

        'foofoo'::varchar(10) as new_varchar_ten,
        10.10::number(4, 2) as new_number_two_two,
        to_timestamp_ntz(1000000000, 3) as new_ts_three,
        to_timestamp_ntz(1000000000, 9) as new_ts_nine
  );
04:54:27  SQL status: SUCCESS 1 in 0.0 seconds
04:54:27  Using snowflake connection "model.my_dbt_project.foo"
04:54:27  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
describe table development_jyeo.dbt_jyeo.foo__dbt_tmp
04:54:27  SQL status: SUCCESS 8 in 0.0 seconds
04:54:27  Using snowflake connection "model.my_dbt_project.foo"
04:54:27  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
describe table development_jyeo.dbt_jyeo.foo
04:54:28  SQL status: SUCCESS 4 in 0.0 seconds
04:54:28  Using snowflake connection "model.my_dbt_project.foo"
04:54:28  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
describe table development_jyeo.dbt_jyeo.foo__dbt_tmp
04:54:28  SQL status: SUCCESS 8 in 0.0 seconds
04:54:28  Using snowflake connection "model.my_dbt_project.foo"
04:54:28  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
describe table "DEVELOPMENT_JYEO"."DBT_JYEO"."FOO"
04:54:28  SQL status: SUCCESS 4 in 0.0 seconds
04:54:28  
    In "DEVELOPMENT_JYEO"."DBT_JYEO"."FOO":
        Schema changed: True
        Source columns not in target: [SnowflakeColumn(column='NEW_VARCHAR_TEN', dtype='VARCHAR', char_size=10, numeric_precision=None, numeric_scale=None), SnowflakeColumn(column='NEW_NUMBER_TWO_TWO', dtype='NUMBER', char_size=None, numeric_precision=4, numeric_scale=2), SnowflakeColumn(column='NEW_TS_THREE', dtype='TIMESTAMP_NTZ', char_size=3, numeric_precision=None, numeric_scale=None), SnowflakeColumn(column='NEW_TS_NINE', dtype='TIMESTAMP_NTZ', char_size=9, numeric_precision=None, numeric_scale=None)]
        Target columns not in source: []
        New column types: []
  
04:54:28  Using snowflake connection "model.my_dbt_project.foo"
04:54:28  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
alter table "DEVELOPMENT_JYEO"."DBT_JYEO"."FOO" add column
          
            NEW_VARCHAR_TEN character varying(10),
          
            NEW_NUMBER_TWO_TWO NUMBER(4,2),
          
            NEW_TS_THREE TIMESTAMP_NTZ,
          
            NEW_TS_NINE TIMESTAMP_NTZ
04:54:29  SQL status: SUCCESS 1 in 0.0 seconds
04:54:29  
    In "DEVELOPMENT_JYEO"."DBT_JYEO"."FOO":
        Schema change approach: append_new_columns
        Columns added: [SnowflakeColumn(column='NEW_VARCHAR_TEN', dtype='VARCHAR', char_size=10, numeric_precision=None, numeric_scale=None), SnowflakeColumn(column='NEW_NUMBER_TWO_TWO', dtype='NUMBER', char_size=None, numeric_precision=4, numeric_scale=2), SnowflakeColumn(column='NEW_TS_THREE', dtype='TIMESTAMP_NTZ', char_size=3, numeric_precision=None, numeric_scale=None), SnowflakeColumn(column='NEW_TS_NINE', dtype='TIMESTAMP_NTZ', char_size=9, numeric_precision=None, numeric_scale=None)]
        Columns removed: 
        Data types changed: 
  
04:54:29  Writing runtime sql for node "model.my_dbt_project.foo"
04:54:29  Using snowflake connection "model.my_dbt_project.foo"
04:54:29  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
-- back compat for old kwarg name
  
  begin;
04:54:29  SQL status: SUCCESS 1 in 0.0 seconds
04:54:29  Using snowflake connection "model.my_dbt_project.foo"
04:54:29  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
call system$wait(0, 'SECONDS');
04:54:29  SQL status: SUCCESS 1 in 0.0 seconds
04:54:29  Using snowflake connection "model.my_dbt_project.foo"
04:54:29  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
insert into development_jyeo.dbt_jyeo.foo ("OLD_VARCHAR_THREE", "OLD_NUMBER_TWO_TWO", "OLD_TS_THREE", "OLD_TS_NINE", "NEW_VARCHAR_TEN", "NEW_NUMBER_TWO_TWO", "NEW_TS_THREE", "NEW_TS_NINE")
        (
            select "OLD_VARCHAR_THREE", "OLD_NUMBER_TWO_TWO", "OLD_TS_THREE", "OLD_TS_NINE", "NEW_VARCHAR_TEN", "NEW_NUMBER_TWO_TWO", "NEW_TS_THREE", "NEW_TS_NINE"
            from development_jyeo.dbt_jyeo.foo__dbt_tmp
        );
04:54:30  SQL status: SUCCESS 1 in 1.0 seconds
04:54:30  Using snowflake connection "model.my_dbt_project.foo"
04:54:30  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.8.3", "profile_name": "all", "target_name": "sf", "node_id": "model.my_dbt_project.foo"} */
COMMIT
04:54:31  SQL status: SUCCESS 1 in 0.0 seconds

^ We see that the newly added columns were added with their specified precisions except for the timestamp_ntz columns.

Describe table to check that new columns don't have the specified precision:

Default timestamp_ntz precision appear to be 9.

Relevant log output

No response

Environment

- OS: macOS
- Python: 3.11.9
- dbt-core: 1.8.3
- dbt-snowflake: 1.8.3

Additional Context

We do appear to bring the datatype back after introspecting development_jyeo.dbt_jyeo.foo__dbt_tmp:

SnowflakeColumn(column='NEW_TS_THREE', dtype='TIMESTAMP_NTZ', char_size=3, numeric_precision=None, numeric_scale=None), SnowflakeColumn(column='NEW_TS_NINE', dtype='TIMESTAMP_NTZ', char_size=9, numeric_precision=None, numeric_scale=None)

But we just dont use it in our DDL.