[Bug] dbt run `--empty` tag doesn't work with `dbt_utils.get_column_values()`

[Ananz2022]

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

We use --empty to create empty tables. If there is no record in the table, dbt_utils.get_column_values() fails with a NoneType' object is not iterable error as it does not found values to loop through.

similar issue found with

• dbt_utils.pivot()
• dbt_utils.get_query_results_as_dict()

Expected Behavior

Function to create some dummy records to make it pass.

• dbt_utils.get_column_values()
• dbt_utils.pivot()
• dbt_utils.get_query_results_as_dict()

Steps To Reproduce

Use dbt_utils macros for empty tables

dbt_utils.get_query_results_as_dict(SELECT * FROM empty_table)

Relevant log output

Environment

dbt version: 1.9.2
python version: 3.11.1
adapter type: snowflake
adapter version: 1.9.1

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[dbeatty10]

Thanks for reaching out @Ananz2022 !

Did you try using the default parameter of dbt_utils.get_column_values, etc?

When I followed the reprex below, I was able to get the same error as you. ❌
But once I added , default=[] within the call to dbt_utils.get_column_values, then the error went away. ✅

Reprex

Create these files:

models/a.sql

select 1 as payment_method

models/b.sql

-- Returns a list of the payment_methods in the `a` model
{% set payment_methods = dbt_utils.get_column_values(table=ref('a'), column='payment_method', default=[]) %}

{{ log("payment_methods: " ~ payment_methods, True) }}

select 0 as id

{% for payment_method in payment_methods %}
    , 1 as {{ payment_method }}_count
{% endfor %}

Run this command:

dbt build -s +b --empty

See this error:

Compilation Error in model b (models/b.sql)
  'NoneType' object is not iterable

Then add the default parameter like this:

{% set payment_methods = dbt_utils.get_column_values(table=ref('a'), column='payment_method', default=[]) %}

Then re-run the original command and see that everything works.

[Ananz2022]

Hi @dbeatty10 , this works perfectly! Thanks so much!

I just wanted to follow up on the other dbt utils macros, such as:

• dbt_utils.pivot()
• dbt_utils.get_query_results_as_dict()
• dbt_utils.date_spine()
• and others.

Do you have any suggestions on how these interact with --Empty?

We’re using the Empty tag for CI validation purposes.
Is there a way to set default values for these so our CI can pass successfully?
Or, is there a way for --Empty to bypass the macros that require values to guarantee that the CI passes?

[dbeatty10]

You're welcome @Ananz2022 !

We’re using the Empty tag for CI validation purposes. Is there a way to set default values for these so our CI can pass successfully?
Or, is there a way for --Empty to bypass the macros that require values to guarantee that the CI passes?

I think it will depend on the macro and what you are doing with the output.

Doing some simple examples of each of those macros worked for me along with the --empty flag. See below for details.

In order to reproduce the issues you are seeing, can you give a simple examples of each of the macros that don't work the way you expect with the --empty flag?

Examples

Create these files:

seeds/c.csv

size,color
S,red
S,blue
S,red
M,red

models/a.sql

select 'visa' as payment_method

models/dbt_utils__date_spine.sql

{{ dbt_utils.date_spine(
    datepart="day",
    start_date="cast('2020-01-01' as date)",
    end_date="cast('2020-01-10' as date)"
   )
}}

models/dbt_utils__get_column_values.sql

-- Returns a list of the payment_methods in the `a` model
{% set payment_methods = dbt_utils.get_column_values(table=ref('a'), column='payment_method', default=[]) %}

select 0 as id

{% for payment_method in payment_methods %}
    , 1 as {{ payment_method }}_count
{% endfor %}

models/dbt_utils__get_query_results_as_dict.sql

{% set sql_statement %}
    select size, color from {{ ref('c') }}
{% endset %}

{%- set places = dbt_utils.get_query_results_as_dict(sql_statement) -%}

{{ log("places: " ~ places, True) }}

{% if target.type == "snowflake" %}
  {%- set sizes = places['SIZE'] -%}
  {%- set colors = places['COLOR'] -%}
{% else %}
  {%- set sizes = places['size'] -%}
  {%- set colors = places['color'] -%}
{% endif %}

select

    {% for size in sizes | unique -%}
      sum(case when size = '{{ size }}' then 1 else 0 end) as users_in_{{ dbt_utils.slugify(size) }},
    {% endfor %}

    {% for color in colors | unique -%}
      sum(case when color = '{{ color }}' then 1 else 0 end) as users_in_{{ dbt_utils.slugify(color) }},
    {% endfor %}

    count(*) as total_total

from {{ ref('c') }}

models/dbt_utils__pivot.sql

select
    size,
    {{ dbt_utils.pivot(
        'color',
        dbt_utils.get_column_values(ref('c'), 'color', default=[])
    ) }}
from {{ ref('c') }}
group by size

Run these commands:

Get this output:

$ dbt build --empty

01:19:44  Running with dbt=1.9.3
01:19:44  Registered adapter: duckdb=1.9.2
01:19:44  Found 5 models, 1 seed, 540 macros
01:19:44  
01:19:44  Concurrency: 1 threads (target='duckdb')
01:19:44  
01:19:45  1 of 6 START sql view model dbt_triage.a ....................................... [RUN]
01:19:45  1 of 6 OK created sql view model dbt_triage.a .................................. [OK in 0.03s]
01:19:45  2 of 6 START sql view model dbt_triage.dbt_utils__date_spine ................... [RUN]
01:19:45  2 of 6 OK created sql view model dbt_triage.dbt_utils__date_spine .............. [OK in 0.02s]
01:19:45  3 of 6 START seed file dbt_triage.c ............................................ [RUN]
01:19:45  3 of 6 OK loaded seed file dbt_triage.c ........................................ [INSERT 4 in 0.03s]
01:19:45  4 of 6 START sql view model dbt_triage.dbt_utils__get_column_values ............ [RUN]
01:19:45  4 of 6 OK created sql view model dbt_triage.dbt_utils__get_column_values ....... [OK in 0.01s]
01:19:45  5 of 6 START sql view model dbt_triage.dbt_utils__get_query_results_as_dict .... [RUN]
01:19:45  5 of 6 OK created sql view model dbt_triage.dbt_utils__get_query_results_as_dict  [OK in 0.02s]
01:19:45  6 of 6 START sql view model dbt_triage.dbt_utils__pivot ........................ [RUN]
01:19:45  6 of 6 OK created sql view model dbt_triage.dbt_utils__pivot ................... [OK in 0.02s]
01:19:45  
01:19:45  Finished running 1 seed, 5 view models in 0 hours 0 minutes and 0.26 seconds (0.26s).
01:19:45  
01:19:45  Completed successfully
01:19:45  
01:19:45  Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6