[Bug] Drop temp tables when doing a full refresh on Dynamic table by respecting qualified names

[srinivasreddie]

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

While trying to do full refresh or recreate on dynamic table we are getting issue.

while droping it using double quotes with DB and Schema as "DB"."SCHEMA"."model__dbt_backup"

where as while alter table rename to backup its not using DB and schema (defaulted to profiles.yml schema not considering schema from model file). Also no double quotes (so snowflake creating as upper case and model__DBT_BACKUP) which causing issue when we do full refresh

Expected Behavior

I'm expecting to take schema and DB from model file in sql we have config also add double quotes when performing alter table rename to backup so drop and alter will be in sync

Steps To Reproduce

{{
config(
materialized='dynamic_table',
target_lag='30 minutes',
schema='fake_schema'
)
}}

select

• from {{ ref('table') }}

Once you run this model in snowflake please do full refresh again to capture statements it will populate the queries

Relevant log output

No response

Environment

- OS:
- Python:
- dbt-core:
- dbt-snowflake:

Additional Context

Adding @[email protected]

[Evelyn-yx-Li]

We also met the same issue that the command alter view db.schema.xxxxx rename to model1 doesn't include snowflake db and schema before model1 when taking a look at the run SQL🙋

[sfc-gh-dfrail]

Exactly, when it should be {{this.database}}.{{this.schema}}.model.
This is causing issues, because it is using the default database/schema instead and can't find objects to remove.

[amychen1776]

Hi Folks - I'm trying to get a better understanding of this issue. @sfc-gh-dfrail , @Evelyn-yx-Li @srinivasreddie

It seems like there might be multiple concerns at hand. Could you confirm that I'm reading this correctly?

@srinivasreddie It seems like when you are trying to do a full-refresh on your dynamic table, it's not dropping your temp tables related to your dynamic table? Would you be able to provide a clean log so we can see what you see? Also what is the dbt-snowflake adapter version you're operating on.

@Evelyn-yx-Li it seems like your concern is unrelated to DTs specifically and might have been fixed by this #1031?

@sfc-gh-dfrail per your last response on the other issue, are you still experiencing this issue? If so, can you provide clear instructions on how to reproduce this?

[tomer-hai]

@amychen1776 I was about to open a bug but found this one which seems related.

I encounter a bug in dbt-snowflake around using dynamic tables with non-standard quoting policy.

I use the following configs:

dbt_project.yml:

quoting:
  schema: true

profiles.yml:

schema: TrYiNg-938

model file (from the dbt init project):

{{
    config(
        materialized='dynamic_table',
        target_lag='downstream',
        snowflake_warehouse='warehouse'
    )
}}

select *
from {{ ref('my_first_dbt_model') }}
where id = 1

Running dbt run once works and creates this dynamic table. However, the next invocations fail with error:

Database Error in model my_second_dbt_model (models/example/my_second_dbt_model.sql)
  001003 (42000): SQL compilation error:
  syntax error line 3 at position 37 unexpected '-'.

The actual SQL seen in Snowflake query history is:

show dynamic tables
            like 'MY_SECOND_DBT_MODEL'
            in schema DBT_TEST.TrYiNg-938
        ;

Which indeed shows that the schema remains unquoted. As far as I can tell, this command comes from dbt/include/snowflake/macros/relations/dynamic_table/describe.sql which indeed doesn't handle quoting.

Please let me know if more information is required.

[apd-bbaker]

Same issue when trying to replace a transient table with a dynamic table. While renaming the existing object the name is not fully qualified, so it fails at that step because there's no active USE SCHEMA context.

Executed SQL

drop table if exists "DEV__SOMEDB"."SOMESCHEMA"."MODEL_NAME__dbt_backup" cascade;

alter table "DEV__SOMEDB"."SOMESCHEMA"."MODEL_NAME" rename to MODEL_NAME__dbt_backup; --<--------

create dynamic table dev__somedb.someschema.model_name
target_lag = '30 minutes'
warehouse = SOME_WH
as (
...
)

Error returned when executing

05:31:14    Database Error in model model_name (models/somedb/someschema/model_name.sql)
  090106 (22000): Cannot perform CREATE TABLE. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.
  compiled code at target/run/someproject/models/somedb/someschema/model_name.sql

Workaround

This works because the unqualified backup table creation SQL is never generated in the first place.

1. Drop the object(s) manually.
2. Run dbt again so that it creates the objects from scratch.

Fix?

I believe the issue is somewhere around here. It doesn't appear that the backup_relation.render() function is fully qualifying the backup relation's name when generating the alter sql using the make_backup macro.
dbt/include/snowflake/macros/relations/create_backup.sql