Error when trying to materialize a Snowflake dynamic table

Help
1

The problem I’m having

We are wanting to switch some of our longer running dbt queries to dynamic tables, when we try to run any model we are getting this error.

Compilation Error in model {model_name}
SnowflakeDynamicTableConfig.init() missing 6 required positional arguments: ‘name’, ‘schema_name’, ‘database_name’, ‘query’, ‘target_lag’, and ‘snowflake_warehouse’

in macro dynamic_table_get_build_sql (macros\materializations\dynamic_table\materialization.sql)
called by macro materialization_dynamic_table_snowflake (macros\materializations\dynamic_table\materialization.sql)

The context of why I’m trying to do this

To leverage the power of dynamic tables.

What I’ve already tried

  • Updating both dbt-core and dbt-snowflake from 1.6.1 to 1.6.3
  • Defining configuration on dbt_project.yml and on the model itself
  • Updating python from 3.10 to 3.11.5
  • Running dbt in virtual env and without virtual env

Some example code or error messages

Here is how i have my configuration set on the model itself.

{{ config(
    materialized='dynamic_table'
    , snowflake_warehouse = 'DBT_WH'
    , database='my_database'
    , target_lag = 'DOWNSTREAM'
    , schema='sandbox'
)}}

Envrionment

Windows 10
Python 3.11.5
dbt-core 1.6.3
dbt-snowflake 1.6.3

2

I am getting a similar error when a dynamic table already exists in Snowflake:

Compilation Error in model {model_name}
init() missing 6 required positional arguments: ‘name’, ‘schema_name’, ‘database_name’, ‘query’, ‘target_lag’, and ‘snowflake_warehouse’

When I am using a pre_hook:
pre_hook=[“drop dynamic table if exists {{this}}”]

The first time it runs I am getting an error, despite the dynamic table is dropped in Snowflake:
Completed with 1 error and 0 warnings:
tuple index out of range

It works the second time or if there is no dynamic table in Snowflake.

dbt-core: 1.6.5
dbt-snowflake: 1.6.4

3

Hey Matt, thanks for the advice thats a neat trick with the pre hook, but i see what you mean with it always failing when theres already a dynamic table materialized, but then it works on the next try.

The only solution we found for now that works on every run is to mark the model as a full refresh.
We added that flag directly in the dbt_project.yml
database_name:
+database: database_name
+full_refresh: true
+grants:

Runs every time like that when running with dbt.

4

Hey Kevin,

It worked and it’s much better trick than the pre_hook.
Thanks a lot.

5

If the dbt call errors out except on initial create + full refresh, what is the point of using dynamic table in this scenario? I get that dbt generates the DDL so you don’t have to, but the non-full refresh scenario should work.

I have the latest versions of both dbt-core and dbt-snowflake, and I get the same error about missing required positional arguments even though these are either (a) explicitly specified already, or (b) not required for the successful initial call.

  • name (obviously this is the name of the model itself)
  • schema_name
  • database_name (in the profiles.yml definition)
  • query (this is the definition of the model itself)
  • target_lag (specified in my model)
  • snowflake_warehouse (in the profiles.yml definition)