[SOLVED] for loop that iterates over a list using dbt

for loop that iterates over a list using dbt

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!

Solution

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