[ADAP-1096] [Bug] Misleading message when trying to update dynamic table

[awal11]

[ADAP-1096] [Bug] Misleading message when trying to update dynamic table

[ewhauser]

In document https://docs.getdbt.com/reference/resource-configs/snowflake-configs, in the example describing the dynamic table and the "Project file"/"Property file"/"Config block" - please add parameter +full_refresh: true

If you add full_refresh: true, then the table will get refreshed every time you run dbt and you lose the benefits.

[awal11]

If you add full_refresh: true, then the table will get refreshed every time you run dbt and you lose the benefits.

Clearly yes, but how do I get the model to be actually updated in the database when I change it? Without "full_refresh" nothing was happening...

[mikealfare]

@awal11 For my clarification, you're only changing the SQL for the model, but no other configurations, correct? In that case, it is expected behavior that the model does not change; only the configuration is subject to change monitoring. SQL changes need to be deployed via full refresh (more info below). However, I am surprised that the standard create log message is shown. If the configuration has not changed (regardless if whether the SQL changed), then this message should show up:

dbt-snowflake/dbt/include/snowflake/macros/materializations/dynamic_table.sql

Lines 42 to 44 in 3dc838d

	{% if configuration_changes is none %}
	{% set build_sql = '' %}
	{{ exceptions.warn("No configuration changes were identified on: `" ~ target_relation ~ "`. Continuing.") }}

Can you please confirm that you're not seeing "No configuration changes were identified on: your_model_v1. Continuing." in the logs?

I just discovered that comment: 'Dynamic table SQL cannot be updated; the dynamic table must go through a --full-refresh', but only as a result of serious head scratching.

This is the result of a Snowflake limitation. The SQL that defines a dynamic table cannot be updated through an ALTER statement. To update the SQL, the dynamic table must be dropped and recreated. In dbt parlance, this is a full refresh.

If you add full_refresh: true, then the table will get refreshed every time you run dbt and you lose the benefits.

That is correct, you wouldn't want to use full refresh every time; it would act sort of like a table in that case. It would still get updated in between runs, unlike a table. But you would lose performance and cost benefits with each run. Ideally you would only use full refresh to "deploy" your new model.

Our thought process for dynamic tables was that they behave more like views from a deployment perspective. The only reason you would need to run dbt run on a view or dynamic table is because something changed on them, whether that's configuration or the SQL itself. After that, data is either updated regularly (DTs) or is always current (views). This would differ from tables and incremental models where dbt run is also required to update the data.

[ewhauser]

@mikealfare This all makes sense but I've also been struggling with how best to handle this in our deployments as I don't want the team to have to manually do things when dynamic tables change.

I understand the limitation around not having ALTER. Wondering if the right approach here would be to run with --defer --full-refresh? In theory, that should only do models that have changed and give the right behavior?

[awal11]

@mikealfare

1. when running a regular dbt run --select your_model (without enforcing full_refresh anywhere) several times in every combination, whether I do or don't change the model: I have never seen the "No configuration changes" in dbt.log.
   On the main output it always happily shows
   1 of 1 OK created sql dynamic_table model your_model_v1 [SUCCESS 1 in 7.36s]
   while the log shows it's doing only alters on comments and lag.
   I could share the log, but it contains too much company detail to upload here.

2. Our main goal is to get rid of 'dbt run' for refreshing the data completely.
   We'd like to use only views and dynamic tables and use 'dbt run' only for deployments when the code is updated, so I think exactly as you described.

[mikealfare]

It makes sense that 1 of 1 OK appears, as well as [SUCCESS 1 in 7.36s]. I wonder how the created sql part gets inserted as that is a bit misleading. No configuration changes should appear in the detailed logs either way. We'll see if we can reproduce it on our side.