In DBT is there a clean way to tie off a for loop?

Question

Consider this macro that I wrote:

{% macro concatenate_columns() %}

    {% for column in [col_1, col_2, col_3] %}

    "{{column}}" ||

    {% endfor %}
    ''

{% endmacro %}

The point of the macro is to take an array of columns and concatenate them together. However, the after the {% endfor %} I have to include a '' in order to prevent the macro returning the final characters as ||.

This is a common problem I have faced with for loops. Here's it's not syntactically hard to deal with, but I've had a situation where I used UNIONs between loops, and the added bit at the end became very complex.

Is there a clean way to write for loops where the final loop "ties off" the output, and doesn't continue to append?

Answer 1

In Jinja you would use the {% if [not] loop.last %} bit, to let the program know that whenever you are dealing with the last iteration in the loop (or all the iterations but the last one, hence the [not] in there), you want a different behaviour.

For instance, in your example you could use the following:

{% macro concatenate_columns() %}

    {% for column in [col_1, col_2, col_3] %}

      "{{column}}" {% if not loop.last %} || ' ' || {% endif %}

    {% endfor %}

{% endmacro %}

Which should compile as: col_1 || ' ' || col_2 || ' ' || col_3

This way, as long as you are not in the last iteration of your for loop, you will add a concat (||) + space (' ') + concat (||) in your code.