dbt Workout — Using Jinja + SQL

README.md

Question on Slack:

Hello everyone! I have a Jinja DBT question! I have set a list and I'm looping over it.

{% set my_list= ["$apple",
"$avocado",
"tomato"] %}

I want now to loop over this list, retrive the data from each column (each item is a column name), and in the column name I want to remove the dollar symbol $. I was trying to do it with this code bellow:

select
col_1
{% for item in my_list%}
   {% set fix_column_name_query %}
   select replace('{{item}}','$','')
   {% endset %}
   {% set result_table = run_query(fix_column_name_query) %}
   {% if execute %}
   {% set result = result_table.columns[0].values()%}
   {% else %}
   {% set result = "" %}
   {% endif %}
   ,"{{item}}" as "{{result}}"
{% endfor %}
from table

But I'm just getting {{item}} as the column name in the compiled code. What am I doing wrong?

Solution

Check out the Loom video

Solution 1:

Just write the SQL! Maybe use a codegen macro to generate it.

select
    col1,
    _apple as apple,
    _avocado as avocado,
    tomato as tomato

from {{ ref('data_with_bad_col_name') }}

Solution 2:

Use a hardcoded list + a Jinja replace filter:

{% set my_list = [
    "_apple",
    "_avocado",
    "tomato"
] %}

select
    col1,
    {% for item in my_list %}
    {{ item }} as {{ item | replace('_', '') }}

    {% if not loop.last %}
        ,
    {% endif %}
    {% endfor %}


from {{ ref('data_with_bad_col_names') }}

Solution 3:

Programatically generate the list of column names

{%- set my_list = adapter.get_columns_in_relation(ref('data_with_bad_col_names')) -%}


select
    {% for item in my_list %}
    {{ item.name }} as {{ item.name | replace('_', '') }}

    {% if not loop.last %}
        ,
    {% endif %}
    {% endfor %}


from {{ ref('data_with_bad_col_names') }}