[Bug] is a dynamic table a table?

[AartBluestoke]

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

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:

dbt-snowflake/dbt/include/snowflake/macros/materializations/table.sql

Line 19 in 87a6e80

{% if old_relation is not none and not old_relation.is_table %}

[AartBluestoke]

could this be another example of #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]

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

• SPIKE: Validate acceptable performance for switching show terse objects -> show objects to enable dynamic table detection #1038
• [SPIKE] Investigate treating dynamic tables as RelationType.Table #1040