[Bug] Dynamic tables with Snowflake change bundle 2024_03 results in dynamic table to issue
#1016
Comments
|
As of right now - there appears to be a new use role accountadmin;
select SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES();
-- [{"name":"2024_03","isDefault":true,"isEnabled":false},{"name":"2024_04","isDefault":false,"isEnabled":false}]create or replace dynamic table development_jyeo.dbt_jyeo.foo_dt target_lag = '1 minutes' warehouse = analytics as (
select 1 c from development_jyeo.dbt_jyeo.foo
);
select table_catalog, table_schema, table_name, is_dynamic, SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES() as bundle_status
from development_jyeo.information_schema.tables
where table_name = 'FOO_DT';
/*
TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,IS_DYNAMIC,BUNDLE_STATUS
DEVELOPMENT_JYEO,DBT_JYEO,FOO_DT,YES,"[{""name"":""2024_03"",""isDefault"":true,""isEnabled"":false},{""name"":""2024_04"",""isDefault"":false,""isEnabled"":false}]"
*/select SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_03');
select table_catalog, table_schema, table_name, is_dynamic, SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES() as bundle_status
from development_jyeo.information_schema.tables
where table_name = 'FOO_DT';
/*
TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,IS_DYNAMIC,BUNDLE_STATUS
DEVELOPMENT_JYEO,DBT_JYEO,FOO_DT,YES,"[{""name"":""2024_03"",""isDefault"":true,""isEnabled"":true},{""name"":""2024_04"",""isDefault"":false,""isEnabled"":false}]"
*/select SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE('2024_03');
select SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_04');
select table_catalog, table_schema, table_name, is_dynamic, SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES() as bundle_status
from development_jyeo.information_schema.tables
where table_name = 'FOO_DT';
/*
TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,IS_DYNAMIC,BUNDLE_STATUS
DEVELOPMENT_JYEO,DBT_JYEO,FOO_DT,YES,"[{""name"":""2024_03"",""isDefault"":true,""isEnabled"":false},{""name"":""2024_04"",""isDefault"":false,""isEnabled"":true}]"
*/select SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_03');
select SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_04');
select table_catalog, table_schema, table_name, is_dynamic, SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES() as bundle_status
from development_jyeo.information_schema.tables
where table_name = 'FOO_DT';
/*
TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,IS_DYNAMIC,BUNDLE_STATUS
DEVELOPMENT_JYEO,DBT_JYEO,FOO_DT,YES,"[{""name"":""2024_03"",""isDefault"":true,""isEnabled"":true},{""name"":""2024_04"",""isDefault"":false,""isEnabled"":true}]"
*/ |
|
I happened to noticed that as well yesterday seems to still be in an early build so not set to default yet would have to opt in I believe. current logs for that bundle: https://docs.snowflake.com/en/release-notes/bcr-bundles/2024_04_bundle |
|
ok after a deep dive with @mikealfare and @McKnight-42 we discovered the root cause was an undocumented change in the Prior to the change, The ostensible workaround is to use However, doing so will undoubtedly affect performance. I'm not sure how to conditionally exclude |
|
also got to callout that this same issue with the wrong info in the |
|
We're working with the Snowflake team on a long-term resolution path. Worst case, we will need to switch from using In the meantime, there are two viable workarounds:
{% materialization dynamic_table, adapter='snowflake' %}
{% set query_tag = set_query_tag() %}
{% set existing_relation = load_cached_relation(this) %}
{% set target_relation = this.incorporate(type=this.DynamicTable) %}
{{ run_hooks(pre_hooks) }}
-- >>>>>>
-- Snowflake introduced a change to 'show objects' in the 2024_03 behavior change bundle
-- such that dynamic tables now simply appear as 'table'.
-- If we think that existing_relation is a table, we need to double check by running
-- an additional 'show dynamic tables' query.
{% 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 %}
-- <<<<<<
{% set build_sql = dynamic_table_get_build_sql(existing_relation, target_relation) %}
{% if build_sql == '' %}
{{ dynamic_table_execute_no_op(target_relation) }}
{% else %}
{{ dynamic_table_execute_build_sql(build_sql, existing_relation, target_relation) }}
{% endif %}
{{ run_hooks(post_hooks) }}
{% do unset_query_tag(query_tag) %}
{{ return({'relations': [target_relation]}) }}
{% endmaterialization %} |
|
Is it possible to create a new materialization with the above macro? Something like but everything else is the same. And then materialize models with |
|
I'd like to add one more thing. I was getting errors in CI Jobs like this |
yep that should work! DM me know if it doesn't |
|
Re-opening for backports |
mikealfare
added
the
backport 1.7.latest
Is this a new bug in dbt-snowflake?
Current Behavior
Originally reported in Slack community: https://getdbt.slack.com/archives/CJN7XRF1B/p1713145308135909 but filing an official repro.
When using Snowflake change bundle
2024_03(https://docs.snowflake.com/en/release-notes/bcr-bundles/2024_03_bundle), dbt is issuing adrop table ...for dynamic tables which is resulting in someobject already existsdatabase error.When the the change bundle is disabled - there is no such
drop table ...statement and thus no database error.Expected Behavior
Keep to the same behavior even if change bundle
2024_03is enabled.Steps To Reproduce
dbt project setup
Bundle disabled behaviour
Add net new dynamic table (or just make sure it doesn't yet exist) and build twice:
Bundle enabled behaviour
Relevant log output
Environment
Additional Context
Workaround: disable the
2024_03bundle.The text was updated successfully, but these errors were encountered: