[Bug] Relation within dbt_utils macro is not honoring the --defer flag

[emilee-jurkowski]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

In my model_b.sql I have something like {% set val_list = dbt_utils.get_column_values(table=ref('model_a'), column='VAL') %}. I try to run a dbt build using a defer flag. model_a does not exist in the the db & schema that I am building in, yet it still will not use the db & schema I am telling it to defer to. When I run dbt ls -s 1+model_b, dbt does recognize that model_a is upstream and being referred to.

Expected Behavior

In the above example, the dbt_utils macro should use the defer db & schema to get the column values from model_a.

Steps To Reproduce

I have model_b like such

{% set val_list = dbt_utils.get_column_values(table=ref('model_a'), column='VAL') %}

{% for val in val_list %}
select
      col_1
    , null as col_2
    , date_trunc('month', col_3) as col_3  
    , '{{ val }}' as val
from {{ ref('model_ab') }}
{%- endfor -%}

Then run dbt run -s model_b+1 in the dev environment with a defer to the prod environment.

Relevant log output

Compilation Error in model model_b (models/model_b.sql)
    In get_column_values(): relation DEV.SCHEMA.MODEL_A does not exist and no default value was provided.

Environment

- dbt-core: 1.8.7
- dbt-snowflake: 1.8.4
- python: 3.11.x

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[dbeatty10]

Thanks for reporting this @emilee-jurkowski !

Did you happen to try something like this?

{% set model_reference = ref('model_a') %}
{% set val_list = dbt_utils.get_column_values(table=model_reference, column='VAL') %}

I didn't try it myself, but curious if it behaves different or the same as what you are seeing with this:

{% set val_list = dbt_utils.get_column_values(table=ref('model_a'), column='VAL') %}

[emilee-jurkowski]

@dbeatty10 I did try something like that and it appears to have the same issue

[dbeatty10]

@emilee-jurkowski

See below for something that worked for me. Could you let me know if it doesn't work for you?

It looks like the key bit is making sure to fully build the environment that is being deferred to. I got the same error as you when I didn't fully build the production environment first. But once I built the production environment, then the commands worked without error.

Alternatively, you can use the default parameter to specify the list of values to return if the relation is not found:

{% set val_list = dbt_utils.get_column_values(table=ref('model_a'), column='id', default=[]) %}

Example

Create these files:

models/model_a.sql

{{ config(materialized='table') }}

select 1 as id

models/model_b.sql

{{ config(materialized='table') }}

{% set val_list = dbt_utils.get_column_values(table=ref('model_a'), column='id') %}

{% for val in val_list %}
-- , '{{ val }}' as val
select *
from {{ ref('model_a') }}
{%- endfor -%}

Run these commands to create a production ("prod") environment and defer to it when compiling the development ("dev") environment:

dbt build --target-path artifacts --target prod
dbt compile -s model_b --defer --state artifacts --target dev

Get this output:

18:25:12  Compiled node 'model_b' is:

-- , '1' as val
select *
from "db"."dbt_dbeatty_prod"."model_a"

Run this command:

dbt run -s model_b+1 --defer --state artifacts --target dev

Get this output:

18:43:38  Running with dbt=1.8.8
18:43:39  Registered adapter: duckdb=1.8.4
18:43:39  Found 2 models, 526 macros
18:43:39  
18:43:40  Concurrency: 1 threads (target='dev')
18:43:40  
18:43:40  1 of 1 START sql table model dbt_dbeatty_dev.model_b ........................... [RUN]
18:43:40  1 of 1 OK created sql table model dbt_dbeatty_dev.model_b ...................... [OK in 0.31s]
18:43:40  
18:43:40  Finished running 1 table model in 0 hours 0 minutes and 0.66 seconds (0.66s).
18:43:40  
18:43:40  Completed successfully
18:43:40  
18:43:40  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

[emilee-jurkowski]

@dbeatty10 I did some testing and realized that I was wrong about what the issue is. The dbt build is honoring the defer flag with the macro like you showed above. We are actually running into an issue when trying to execute dbt docs generate --target dev (the log output I listed above). I tried utilizing the defer flag with the docs generate command, like so dbt docs generate --defer --state /Users/ejurkowski/prod-manifest --target dev, and it completed without error. I just want to make sure that this is the correct action to take as I could not find any documentation on the site saying you can/should use the defer flag with docs generate.

[dbeatty10]

@emilee-jurkowski I'm not quite following your example. Could you share the following?

1. example files
2. example commands
3. actual output (either from logs/dbt.log, target directory, or screenshots of our documentation website)
4. expected output that differs from actual output (please explain)

If you can provide those pieces, then that will help me assess if dbt is operating differently than it is designed.

[emilee-jurkowski]

1. We have model_b that references model_a in a macro and model_ab in the query like so.

{% set val_list = dbt_utils.get_column_values(table=ref('model_a'), column='VAL') %}
{% for val in val_list %}
select
      col_1
    , null as col_2
    , date_trunc('month', col_3) as col_3  
    , '{{ val }}' as val
from {{ ref('model_ab') }}
{%- endfor -%}

2. We have a github workflow in which we are utilizing pre-commit. For some of our pre-commit checks we need to produce a dbt manifest and catalog for it to use, so we are running dbt docs generate --target dev.
3. When we have the above model and run the docs generate command for dev, we get the below error. For context, we are running with --target dev because we do not have everything enabled for PROD. In this example, all 3 models are enabled for DEV and PROD, and have been built in PROD.

Compilation Error in model model_b (models/model_b.sql)
    In get_column_values(): relation DEV.SCHEMA.MODEL_A does not exist and no default value was provided.

4. We are expecting the docs generate command to pass so that we can run our pre-commit checks.

If I run dbt docs generate --defer --state /Users/ejurkowski/prod-manifest --target dev instead of dbt docs generate --target dev, it does pass and we are able to run the pre-commit checks. I would like to make sure that running docs generate with a defer flag makes sense, and is correct usage as there is no documentation on running the docs generate command with the defer flag.

[dbeatty10]

I would like to make sure that running docs generate with a defer flag makes sense, and is correct usage as there is no documentation on running the docs generate command with the defer flag.

@emilee-jurkowski Yeah, running docs generate with a --defer flag is totally supported, and it looks like the perfect candidate for your use-case here! See below for a brief history of when it was first added.

I'm going to close this as resolved since we got to the bottom of things and dbt is behaving as we expect. If you'd like to see any changes/additions to the dbt product documentation, you can reach out to our Docs team by opening an issue in the docs.getdbt.com repo.

Abbreviated history of --defer for docs generate

dbt-core 1.3.0 - October 12, 2022

• Add --defer flag to dbt compile & dbt docs generate (#4110 4514)

dbt-core 1.4.0 - January 25, 2023

• fix docs generate --defer by adding defer_to_manifest to before_run (#6488)