[Bug] Dynamic table full refresh table creation fails

[eayursed]

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

When I tried to full-refresh dynamic more than two times, got error

 Database Error in model schema_xyz__testtable (models\schema_xyz\schema_xyz__testtable.sql)
  002002 (42710): SQL compilation error:
  Object 'TESTTABLE__DBT_BACKUP' already exists.
  compiled Code at target\run\pj_dbt\models\schema_xyz\schema_xyz__testtable.sql

model file:
file name: schema_xyz__testtable.sql

{{ config(
    materialized="dynamic_table",
    on_configuration_change="apply",
    target_lag="15 minutes",
    snowflake_warehouse="WH_LOAD",
    full_refresh=true,
    schema="SCHEMA_XYZ",
) }}

select
  col1
from {{ source('prod_db', 'TESTTABLE') }}

dbt's generated run query:

    -- get the standard backup name
    -- drop any pre-existing backup
        drop table if exists "DBT_TEST_DB"."SCHEMA_XYZ"."TESTTABLE__dbt_backup" cascade
;

    alter table "DBT_TEST_DB"."SCHEMA_XYZ"."TESTTABLE" rename to TESTTABLE__dbt_backup;
    
        create dynamic table DBT_TEST_DB.SCHEMA_XYZ.TESTTABLE
        target_lag = '15 minutes'
        warehouse = WH_PRJAPAN_LOAD
        as (
select
  a1
from prod_db.sch_xyz.TESTTABLE
        )
;
        -- get the standard backup name
        drop table if exists "DBT_TEST_DB"."SCHEMA_XYZ"."TESTTABLE__dbt_backup" cascade

Also I realized that there where TESTTABLE__dbt_backup table produced in PUBLIC schema of DBT_TEST_DB.
And I think drop query is pointing at SCHEMA_XYZ's TESTTABLE__dbt_backup table. So maybe the cause is, full refresh logic is making backup table in wrong schema, and it's causing misalignment with drop query.

Expected Behavior

Able to do full refresh of dynamic table.

Steps To Reproduce

dependency:
$ pip freeze | grep dbt

dbt-core==1.7.14
dbt-extractor==0.5.1
dbt-semantic-interfaces==0.4.4
dbt-snowflake==1.7.3
sqlfluff-templater-dbt==3.0.0

Relevant log output

No response

Environment

- OS: Windows
- Python: Python 3.10.11

Additional Context

No response

[jtcohen6]

@eayursed Thanks for opening — we're aware of the issue, this is due to a change that Snowflake made to the return types of show terse objects.

This comment has the latest updates, as well as what I believe to be a viable workaround to unblock yourself in the meantime:

• [Bug] Dynamic tables with Snowflake change bundle 2024_03 results in dynamic table to issue  #1016 (comment)

I'm going to close this issue as a duplicate.