search

dbt-labs / dbt-snowflake

dbt-snowflake contains all of the code enabling dbt to work with Snowflake
https://getdbt.com
Apache License 2.0
297 stars 177 forks source link

[Bug] is a dynamic table a table? #1055

Closed AartBluestoke closed 6 months ago

AartBluestoke commented 6 months ago

Is this a new bug in dbt-snowflake?

Current Behavior

dynamic tables are tables causing the following check to fail:

{%- set existing_relation = adapter.get_relation(database=database, schema=schema, identifier=identifier) -%}
   {% if existing_relation is not none and not existing_relation.is_table %}
     {{ log("Dropping relation " ~ existing_relation ~ " because it is of type " ~ existing_relation.type, True) }}
     {{ drop_relation_if_exists(existing_relation) }}
   {% else %}
     {{ log("Keeping relation " ~ existing_relation ~ " because it is of type " ~ existing_relation.type, True) }}
   {% endif %}

doesn't drop then issues the standard create or replace table as ... This prints the message Keeping relation "DEV_mydatabase"."raw"."mytable" because it is of type table then fails with SQL compilation error: Object 'mytable' already exists as DYNAMIC_TABLE

Expected Behavior

because snowflake considers table and dynamic table to be different object types (as per the error message, grant allocations, and assorted other different capabilities) that "is table" would be 'false' for a dynamic table.

Steps To Reproduce

  1. create a dynamic table create dynamic test_table as select 1
  2. setup a dbt model for 'test_table' to use the snoflake table materialiser for this table, instead of a dynamic table/
  3. watch it fail.

Relevant log output

No response

Environment

- OS:
- Python:
- dbt-core: 1.7.11
- dbt-snowflake: 1.7.3

Additional Context

although the above fragment is my code, dbt-snowflake has the same check: https://github.com/dbt-labs/dbt-snowflake/blob/87a6e808dfb025df1eeef3741ad3822635249889/dbt/include/snowflake/macros/materializations/table.sql#L19

AartBluestoke commented 6 months ago

could this be another example of https://github.com/dbt-labs/dbt-snowflake/issues/1016 ? - which required an updated 'is_dynamic' table check - the current table code only has the outer check?

 {% if existing_relation.is_table %}
    {% set is_dynamic_table = (snowflake__describe_dynamic_table(existing_relation).dynamic_table.rows | length > 0) %}
    {% if is_dynamic_table %}
        {% set existing_relation = existing_relation.incorporate(type=this.DynamicTable) %}
    {% endif %}
   {% endif %}
jtcohen6 commented 6 months ago

@AartBluestoke Thanks! I'm going to close this as a duplicate of these issues, addressing the same concern: