1

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.

Nema Sobhani
  • 41
  • 5
  • it looks right to me! can you try using `{{ log }}` ([docs](https://docs.getdbt.com/reference/dbt-jinja-functions/log/)) before the select statement? I'd be interested to see if the variables are stored correctly. something like this: `{{ log(‘nema’s exclude_columns’ ~ exclude_columns, info=true) }}` – Anders Swanson Feb 02 '22 at 23:32

2 Answers2

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) }}
Josiah McDonald
  • 21
  • 3
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"] %}
Dauros
  • 9,294
  • 2
  • 20
  • 28