Using jinja sql variables in dbt_utils

Question

In my dbt project, if I declare a jinja sql variable how can I pass it to a dbt_utils function?

For example this doesn't work:

{% set exclude_columns = ["col1", "col2", "col3"] %}

SELECT {{ dbt_utils.star(from=ref('table'), except=exclude_columns) }}
FROM {{ ref('table') }}

If I manually add columns to the "except" parameter, it works, but not with the variable. I tried {{ exclude columns }} as well and same result.

Answer 1

You have to add quotation marks around the column names, otherwise Jinja will treat them as variables and fails silently, so the results will be a list of 3 None.

{% set exclude_columns = ["col1", "col2", "col3"] %}

Answer 2

I'm not sure why, but defining a variable in my dbt_project.yml and then referencing that variable works for me!

{% set exclude_columns = var('exclude_fields') %}

{{ dbt_utils.star(from=ref('my_table'), except=exclude_columns) }}

You can also check that exclude_columns is working with this log statement:

{{ log(exclude_columns, info=true) }}