Search code examples
jinja2dbt

dbt / jinja: Add coalesce to every column coming from dbt_utils.get_filtered_columns_in_relation macro by loop

I am trying to make use of a loop to add the coalesce function to every column that is delivered by the dbt_utils.get_filtered_columns_in_relation macro (see picture attached). I am following the example mentioned in the docs (https://github.com/dbt-labs/dbt-utils/tree/0.9.2/#get_filtered_columns_in_relation-source), but I am still getting errors like

001003 (42000): SQL compilation error:
13:54:48    syntax error line 27 at position 2 unexpected '"ARTICLE_REFERENCE_GROUP"'.
13:54:48    syntax error line 39 at position 2 unexpected 'from'.
13:54:48    syntax error line 51 at position 0 unexpected ')'.

It seems to me that there is still something wrong regarding the quotes or similar.If I add additional single quotes to the column alias, I am getting

14:21:39    syntax error line 13 at position 36 unexpected ''COMPANY_NR''.
14:21:39    syntax error line 14 at position 49 unexpected ''ARTICLE_REFERENCE_GROUP''.

etc.

Do you have experience with this/jinja/loops and can support me fixing it? Thanks in advance!

My current code:

with source as (

  {% set coalescecols = adapter.get_columns_in_relation(from=source('stg_poc_dbt','prices'), except=['_business_dt','_country_cd','_input_filename','_row_nr','_dq_st','_dq_err_msg','_run_id','_batch_id']) %}

  select 
    {% for col in coalescecols %}
        coalesce({{ col.name }}, '') as {{ col.name }}
        {%- if not loop.last %},{% endif -%}
    {% endfor %}
    --{{ dbt_utils.star(from=source('stg_poc_dbt','prices'), except=['_business_dt','_country_cd','_input_filename','_row_nr','_dq_st','_dq_err_msg','_run_id','_batch_id'])}}
  from 
    {{ source('stg_poc_dbt','prices') }}
Solution

  • The problem is actually with the line you tried to comment out!

    Jinja is templated first, and then the SQL is passed to your database. This line:

    --{{ dbt_utils.star(from=source('stg_poc_dbt','prices'), except=['_business_dt','_country_cd','_input_filename','_row_nr','_dq_st','_dq_err_msg','_run_id','_batch_id'])}}

    will get compiled into a list of column names that are on different lines, so the sql comment -- will only remove the first of those column names.

    Jinja comments take the form {# ... #} so if you want to comment out your call to star, you need to do this:

    {# {{ dbt_utils.star(from=source('stg_poc_dbt','prices'), except=['_business_dt','_country_cd','_input_filename','_row_nr','_dq_st','_dq_err_msg','_run_id','_batch_id'])}} #}

    More generally, the best way to debug jinja is to look at the compiled code. You can find that in the target/compiled directory in your project, after you execute dbt run, dbt build, or dbt compiled. That will show you the SQL that is generated after the jinja is templated, and you would have seen the issue in that compiled code.