[Feature] Optionally refresh dynamic tables when not rebuilding

[chrisluedtke]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

I'd like to define a graph of dynamic tables and run them exclusively as a dbt command, rather than establishing a target lag and relying on Snowflake to schedule updates.

When I dbt run --select my_dynamic_table, I'd like this to happen if the table already exists and we're not in a full refresh scenario:

ALTER DYNAMIC TABLE my_dynamic_table REFRESH

Describe alternatives you've considered

No response

Who will this benefit?

Users with a mixture of tables, views, and dynamic tables who want to control model updates through an orchestrator like Airflow.

Are you interested in contributing this feature?

Yes

Anything else?

No response

[amychen1776]

@chrisluedtke I'm curious to ask you what the use case here for your use of dynamic tables rather than say incremental. Is this to avoid writing incremental logic? Based on what I'm aware of, fundamentally you don't create a dynamic table without a target lag which means Snowflake is always refreshing it on a cadence anyways.

[chrisluedtke]

@amychen1776 dynamic tables can be created with 'downstream' target lag and refreshed explicitly.

[amychen1776]

@chrisluedtke that's true but I'm curious why you want to use a dynamic table in this use case that is manually refreshed so I can better understand.

[chrisluedtke]

@amychen1776 as I said in the original post, this would enable users to leverage a tool like airflow to schedule and monitor refreshes in a DAG that includes both dynamic tables and other types of model materialization. That makes it extremely simple to implement incremental updates alongside existing models and tech stacks. Additionally, dynamic tables have full support for deletions. The vanilla dbt incremental materialization cannot update a record that was excluded due to a data update (like a deletion or exclusion by a where clause or join condition).

[amychen1776]

Thank you for clarifying! Unfortunately, due to other priorities, we will not be able to support this on our roadmap at this time. That said, I would love to revisit this in the future.

[kingalban]

I just want to clarify the current design and behaviour.
There is currently a macro snowflake__refresh_dynamic_table in dynamic_table/refresh.sql which would do the job, but it seems that it's never called?
Something similar is included in the test suite 🤔

It seems to me that this macro was meant to be used here dynamic_table.sql:43.

I fully understand your prioritisation, but I'm wondering if there was some obstacle to this originally?