[Regression] Snowflake is now populating a value for the relation type on dynamic tables, which is not properly reflected in the metadata queries

[mikealfare]

Is this a regression in a recent version of dbt-snowflake?

• I believe this is a regression in dbt-snowflake functionality
• I have searched the existing issues, and I could not find an existing issue for this regression

Current Behavior

Querying the relation type on a dynamic table produces "BASE TABLE". This used to be empty, and was coalesced to a value unique to dynamic tables.

Expected/Previous Behavior

Querying the relation type on a dynamic table should produce "DYNAMIC TABLE".

Steps To Reproduce

• create a dynamic table
• query the relation type, or, do anything dependent on the relation type
• note that you get behavior suggesting it's a traditional table

Relevant log output

No response

Environment

- OS:
- Python:
- dbt-core (working version):
- dbt-snowflake (working version):
- dbt-core (regression version):
- dbt-snowflake (regression version):

Additional Context

We need to update the metadata query. Because it looks like the value selected matches that of a traditional table, we'll need to look for another field to differentiate the two, such as an update frequency, or the presence in a metadata table that only contains dynamic tables.

[mikealfare]

Examples of failed tests: https://github.com/dbt-labs/dbt-snowflake/actions/runs/8336750340/job/22814405444?pr=933

[mikealfare]

I don't think we're getting nulls anymore for table_type in this query (verify this).

dbt-snowflake/dbt/include/snowflake/macros/catalog.sql

Lines 39 to 70 in 7e1c1c7

	{% macro snowflake__get_catalog_tables_sql(information_schema) -%}
	select
	table_catalog as "table_database",
	table_schema as "table_schema",
	table_name as "table_name",
	coalesce(table_type, 'DYNAMIC TABLE') as "table_type",
	comment as "table_comment",
	-- note: this is the _role_ that owns the table
	table_owner as "table_owner",
	'Clustering Key' as "stats:clustering_key:label",
	clustering_key as "stats:clustering_key:value",
	'The key used to cluster this table' as "stats:clustering_key:description",
	(clustering_key is not null) as "stats:clustering_key:include",
	'Row Count' as "stats:row_count:label",
	row_count as "stats:row_count:value",
	'An approximate count of rows in this table' as "stats:row_count:description",
	(row_count is not null) as "stats:row_count:include",
	'Approximate Size' as "stats:bytes:label",
	bytes as "stats:bytes:value",
	'Approximate size of the table as reported by Snowflake' as "stats:bytes:description",
	(bytes is not null) as "stats:bytes:include",
	'Last Modified' as "stats:last_modified:label",
	to_varchar(convert_timezone('UTC', last_altered), 'yyyy-mm-dd HH24:MI'||'UTC') as "stats:last_modified:value",
	'The timestamp for last update/change' as "stats:last_modified:description",
	(last_altered is not null and table_type='BASE TABLE') as "stats:last_modified:include"
	from {{ information_schema }}.tables
	{%- endmacro %}

So we'll need to update this line:

dbt-snowflake/dbt/include/snowflake/macros/catalog.sql

Line 44 in 7e1c1c7

coalesce(table_type, 'DYNAMIC TABLE') as "table_type",