[ADAP-1042] Persist docs fails when a column name matches a Snowflake no-argument function

[georgewoodhead]

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

I have a model with column name current_time and when I set persist docs to enabled for columns, my dbt run fails. The query that adds the comment to the table is failing with error message SQL execution internal error: Processing aborted due to error 300002:3929359486.
After investigating this further it seems any columns that share the name of a Snowflake no-argument function (ones that do not require brackets like CURRENT_TIME, CURRENT_DATE) will fail with this error.

This is the query dbt attempts to run but fails:

alter table db.schema.sf_test alter
    
        "CURRENT_TIME" COMMENT $$test1$$
    
        "CURRENT_DATE" COMMENT $$test2$$
    
        "CURRENT_TIMESTAMP" COMMENT $$test3$$
    
        "LOCALTIME" COMMENT $$test4$$;

Expected Behavior

I would expect no error to occur for these column names when persist docs is enabled.

Steps To Reproduce

1. Create a new dbt project
2. Add the following to the dbt_project.yml:

models:
  +persist_docs:
    relation: true
    columns: true

3. Create a new model file (models/test_table.sql) with the following content:

{{
  config(
    materialized = 'table',
    )
}}

select
  1234 as current_time,
  1234 as current_date,
  1234 as current_timestamp,
  1234 as localtime

4. Create a new yaml file models/models.yml with the following content:

version: 2

models:
  - name: test_table
    columns:
      - name: current_time
        description: "test1"
      - name: current_date
        description: "test2"
      - name: current_timestamp
        description: "test3"
      - name: localtime
        description: "test4"

5. Execute dbt run
6. See error

Relevant log output

(dbt-env) ➜  demo_dbt_snowflake git:(main) ✗ dbt run
16:57:36  Running with dbt=1.7.1
16:57:36  Registered adapter: snowflake=1.7.0
16:57:36  Unable to do partial parsing because a project config has changed
16:57:37  Found 1 model, 0 sources, 0 exposures, 0 metrics, 430 macros, 0 groups, 0 semantic models
16:57:37  
16:57:39  Concurrency: 1 threads (target='dev')
16:57:39  
16:57:39  1 of 1 START sql table model DBT_GEORGE.test_table ............................. [RUN]
16:57:41  1 of 1 ERROR creating sql table model DBT_GEORGE.test_table .................... [ERROR in 1.99s]
16:57:41  
16:57:41  Finished running 1 table model in 0 hours 0 minutes and 3.56 seconds (3.56s).
16:57:41  
16:57:41  Completed with 1 error and 0 warnings:
16:57:41  
16:57:41    Database Error in model test_table (models/test_table.sql)
  000603 (XX000): SQL execution internal error:
  Processing aborted due to error 300002:3929359486; incident 9969370.
  compiled Code at target/run/demo_dbt_snowflake/models/test_table.sql
16:57:41  
16:57:41  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
(dbt-env) ➜  demo_dbt_snowflake git:(main) ✗

Environment

- OS: MacOSX
- Python: 3.11.5
- dbt-core: 1.7.1
- dbt-snowflake: 1.7.0

Additional Context

Adding a comment using this alternative method works as expected for these column names:

COMMENT ON COLUMN db.schema.sf_test.CURRENT_TIME IS 'test';

[Fleid]

Hi @georgewoodhead, these keywords are reserved in Snowflake and should not be used as column names:

CURRENT_DATE : Cannot be used as column name (reserved by ANSI).
CURRENT_TIME : Cannot be used as column name (reserved by ANSI).
CURRENT_TIMESTAMP : Cannot be used as column name (reserved by ANSI).
CURRENT_USER : Cannot be used as column name (reserved by ANSI).

So dbt should not support them either, we will not fix this.

Thanks a lot for opening the issue, we're actually surprised the model was built successfully to be honest.