How to set Dynamic Table to be Incremental refresh_mode

Help
1

The problem I’m having

The context of why I’m trying to do this

I would like the dynamic tables I build using dbt to be incremental refresh mode, but when I create the dynamic table using dbt it is always Full refresh mode. If I create the same table using the same query via snowsight, I am able to create it as incremental refresh mode

What I’ve already tried

I have tried using the tag in the dbt_project.yml

dynamic_tables:
+refresh_mode: “incremental”

I have also tried using in the header of my dbt sql

{{ config(
materialized = ‘dynamic_table’,
refresh_mode = ‘INCREMENTAL’
) }}

2

Currently, that is the limitation, it has to be fully refreshed.

Refer: Limitation section at Snowflake configurations | dbt Developer Hub

1 Like
3

I’ve been working on dynamic_tables lately and here is what I’m finding using 1.8.3.
The first time I run a dynamic table it does a “create dynamic table”.
The 2nd time I run it it does an:

alter dynamic table "DEV_EDW_DB"."MY_STAGE"."STG_MY_SRC_NM __MYTABLENAME" set
            target_lag = 'downstream'
            warehouse = dev_elt_l_wh

Note if I change the target lag or the warehouse it will alter the table to the new target lag or warehouse.
I have been working on a macro that I can put at the top of the model that I want to be a dynamic table.

{% macro dynamic_table_config(target_lag="downstream",warehouse_size="l" ) %}
    {% if target.name == 'prd' %}
        {% set warehouse_prefix = 'prd_' %}
    {% else %}
        {% set warehouse_prefix = 'dev_' %}
    {% endif %}

    {{ config(
        materialized="dynamic_table",
        target_lag=target_lag,
        on_configuration_change="apply",
        snowflake_warehouse= warehouse_prefix ~ "elt_" ~ warehouse_size ~ "_wh"
    ) }}
{% endmacro %}

Now I can drop this at the top of any model (with prereqs to be a DT)

{{ dynamic_table_config() }}

And if I want I can find tune it:

{{ dynamic_table_config(target_lag="10 min",warehouse_size="M") }}

I’d really like to be able to detect if this is the first run or a run with --full-refresh flag and switch size of the warehouse the next time I run dbt.
And as I’m typing this, I think I can put that in a post_hook.
Something like:

{{ config( 
 post_hook="alter dynamic table this  set   warehouse = dev_elt_s_wh", 
 ) }}

I am having intermittent issues with dbt, losing track of its schema.
I get a bunch of errors, and if I drop the schemas and start over, it seems to work.
I’m hoping to find a better fix than that.

Anyway, for me, it is not exactly in incremental mode, and the is_incremental() is always False.
But the behaviors is different after the first run, or a run with --full-refresh.