I’ve noticed this particular pattern where if I run a model by itself or as part of a DBT Cloud batch run, it drops the views that REF it.
Is that by design? If so, is there a way to disable this behavior? One of my processes references a view in my schema and I can’t have it suddenly disappear whenever the table is being updated.
Hey @chanwd – any chance you’re on Redshift here? If so, have you set the following up in your dbt_project.yml file:
models:
bind: false
If not, give that a go!
Happy to go into this in more details, but want to make sure I’m on the right track!
Hi Claire, thanks for the response. I’m using Postgres.
Ah ok!
So what happens here (on both Postgres and Redshift) is that views are “bound” to the objects they depend on. Let’s say you have a view, my_view, that selects from my_incremental_model. If you try to run:
drop table my_incremental_model
postgres is going to be unhappy with you, saying something like:
ERROR: table my_incremental_model cannot be dropped because some objects depend on it
As a result, dbt is forced to use the cascade qualifier, which drops the table, and all objects that depend on it.
drop table my_incremental_model cascade
This has the result of dropping my_view as well!
On Redshift, you can get around this by using late binding views, hence the snippet of code I was suggesting. But on Postgres, there’s no equivalent feature.
Instead, I recommend ensuring that all models that are referenced in your BI tool are materialized as tables – even though tables select from other models, they don’t have the same binding behavior, so won’t get dropped by the cascade qualifier.
Claire,
Thank you for the prompt and clear response. My question is, why does DBT try to drop the view if I’m just incrementally updating it? An incremental model shouldn’t be dropping the table prior to update.
Will
Hi Will! Some more information would be helpful here. I have an idea of what your project looks like from your description, but if you can describe the relevant models that exist in your project, their relationships to each other and materializations, and the command that you’re running, that would be super helpful in helping you out here!
I have an incremental model called fct_answer. This table is used by a view that generates a crosstab of the data in this table.
When fct_answer is being updated as part of an execution of “dbt run”, the view related to this table is no longer exists until the table is finished updating.
Ok - that doesn’t sound right to me. dbt will run a delete and then and insert to update incremental models in place. dbt will not drop...cascade incremental models, so downstream views should not be dropped in this case.
Does the view that does the crosstab reference only fct_answer, or does it reference any other models? And can you show the config() section for fct_answer?
Here’s the config section for fct_answer
{{
config(
materialized=‘incremental’,
unique_key=‘pk_id’
)
}}
Here’s the from clause in the View vw_qfriendly (which does reference 2 other views and a table):
from
{{ref(‘dim_user_survey’)}} us
inner join {{ref(‘vw_qfriendly_column_headers’)}} sqc on us.survey_id = sqc.survey_id
left outer join {{ref(‘fct_answer’)}} ad on sqc.table_friendly_col_name = ad.table_friendly_col_name and ad.user_survey_id = us.user_survey_id and us.survey_id = ad.survey_id
left outer join {{ref(‘vw_qfriendly_missing_values’)}} usqc on us.survey_id = usqc.survey_id and us.user_survey_id = usqc.user_survey_id and sqc.table_friendly_col_name = usqc.table_friendly_col_name
here’s the from clause from those two other views referenced there:
vw_qfriendly_column_headers
from {{ ref(‘dim_survey_question_choice’) }}
vw_qfriendly_missing_values
from {{ref(‘stg_incrowd_user_survey’)}} us
inner join {{ref(‘dim_survey_question_choice’)}} sqc on us.survey_id = sqc.survey_id
left outer join {{ref(‘stg_incrowd_answer’)}} a on us.id = a.user_survey_id and sqc.question_id = a.question_id
union
from {{ref(‘stg_incrowd_user_survey’)}} us
inner join {{ref(‘dim_survey_question_choice’)}} sqc on us.survey_id = sqc.survey_id
left outer join {{ref(‘fct_answer’)}} a on us.survey_id = a.survey_id and us.id = a.user_survey_id and sqc.carried_from_choice_id = a.question_choice_id
Ok, got it.
So vw_qfriendly references dim_user_survey, vw_qfriendly_column_headers , fct_answer, and vw_qfriendly_missing_values.
Just to be super clear, are you saying that when you run:
dbt run -m fct_answer
dbt is dropping the vw_qfriendly view? That doesn’t sound right to me at all, and if it’s the case, we should open up a bug report to fix it.
I would however expect that when you do a dbt run which rebuilds the dim_user_survey , vw_qfriendly_column_headers, or vw_qfriendly_missing_values models, dbt will drop the vw_qfriendly. That will happen because these are tables and views, and Postgres does not allow tables or views to be recreated in-place without dropping downstream views.
yes, when I run “dbt run -m fct_answer” and let’s say it takes a minute to run. During that minute, those views are no longer found.
dim_user_survey is also incremental. The model references by vw_qfriendly_column_headers and vw_qfriendly_missing_values are also incremental as well.
Theoretically, the way I’ve designed it, no models are being rebuilt, only incrementally updated, therefore no views should ever be dropped and recreated. So even if vw_qfriendly is dependent on those other views, those other views aren’t being dropped\recreated and neither should vw_qfriendly.
I’m really stumped here: there’s either something going on in your project which isn’t clear to me, or dbt is doing the wrong thing.
Are you able to create a minimal reproduction case that demonstrates this behavior? If so, I’d be really grateful if you could create an issue here: https://github.com/fishtown-analytics/dbt/issues/new?assignees=&labels=bug%2C+triage&template=bug_report.md&title=
This would be a high priority bug fix for us if dbt is indeed doing the wrong thing.
Thanks!
I realize what the issue is. It’s not due to the incremental models that the view gets dropped. It’s because they’re part of the ETL run. I need to exclude them as part of the dbt execution. If they’re included, they’ll be recreated.