for loop that iterates over a list using dbt

Question

I've just started using dbt and I wanted to ask the community if, what I will explain to you next, is possible to be done in a macro in dbt.

My idea is to iterate over a list of values using a for loop, and use each of these values in the list, in my query.

example:

list_of_variables: [x, y]

for i in list_of_variables

select case when i in column_x then 'found' else 'not found' end from table_1

do you know how this should be done? Looked at some dbt documentation, but couldn't find an answer.

Thanks!

Answer 1

Yes, It can be done. You need to use Jinja template for this.

For Example, You have a list of columns: [X,Y,Z], then you can iterate over it like this:

Select
  {% for col in columns %}
      case when {{col}} in {{ "column"~ "_" ~ col}} then "Found" else "Not Found" end as {{col}},
  {% endfor %}
from table

The above dbt is with Bigquery syntax. This will generate :

Select
    case when X in column_X then "Found else "Not Found" end as X,
    case when Y in column_Y then "Found else "Not Found" end as Y,
    case when Z in column_Z then "Found else "Not Found" end as Z,
from table

In case you don't want comma (,) after Z i.e. before from then you can write it as :

Select
  {% for col in columns %}
      case when {{col}} in {{ "column"~ "_" ~ col}} then "Found" else "Not Found" end as {{col}}
  {% if not loop.last %} , {% endif %}
  {% endfor %}
from table

This will generate a query for you without a comma after Z.

Select
    case when X in column_X then "Found else "Not Found" end as X,
    case when Y in column_Y then "Found else "Not Found" end as Y,
    case when Z in column_Z then "Found else "Not Found" end as Z
from table

You can read more about Jinja from here : dbt jinja