Defer is sometimes using development schemas instead of only using production objects

[osvaldo-illescas]

The problem I’m having

dbt Cloud and dbt cloud CLI are defering just to some production schemas but not all, leading to differences in data.

The context of why I’m trying to do this

I’m trying to apply tests to some models, however, there are some exception cases that I need to consider. The idea is to trigger the error just when the number goes over those cases.
The problem is that, even though I’m in dbt Cloud with defer to production or dbt Cloud CLI the defer seems to just work with some schemas but not every one. Some ref macros still point to my user schemas.

What I’ve already tried

I’ve tried to add the defer-env-id in the dbt_cloud.yml file but didn’t work. I once changed the ref macro to always point to prod schemas but I don’t want to do that again.

Some example code or error messages

This is a piece of code when not deferring to production.

with int_model as (
    select * from ANALYTICS.user_intermediate.int_model
),

fact_model as (
    select * from ANALYTICS.user_core.fact_model
)

This is the same piece of code when deferring to production:

with int_model as (
    select * from ANALYTICS.user_intermediate.int_model
),

fact_model as (
    select * from ANALYTICS.PROD_core.fact_model
)

While I was expecting int will also change user_intermediate to PROD_intermediate. It looks that it only works with models in my core folder. Am I doing something wrong?

[joellabes]

Hey @osvaldo-illescas!

By default, defer will only choose the production version of a model based on two criteria:

• if the model is not in your selection command
• if a copy of the model does not already exist in your user schema

Based on the first criterion, you might see this behaviour if you had a DAG that looked like this, and you ran dbt run -s int_model third_model:

But that’s an unlikely selection command. The more likely cause is the second criterion: you have built user_intermediate.int_model in the past but not your fact_model.

To force dbt to always use the production model

You can pass the --favor-state flag, like this: dbt run -s third_model --favor-state and dbt will choose the production version instead, even if the model exists in your development environment. Defer | dbt Developer Hub