dbt if/else macros return nothing

Question

I'm trying to use a dbt macro to transform survey results.

I have a table similar to:

column1	column2
often	sometimes
never	always
...	...

I want to transform it into:

column 1	column 2
3	2
1	4
...	...

using the following mapping:

category	value
always	4
often	3
sometimes	2
never	1

To do so I have written the following sbt macro:

{% macro class_to_score(class) %} 
    {% if class == "always" %}
        {% set result = 1 %}
    {% elif class == "often" %}
        {% set result = 2 %}
    {% elif class == "sometimes" %}
        {% set result = 3 %}
    {% elif class == "never" %}
        {% set result = 4 %}
    {% endif -%}
    {{ return(result) }}
{% endmacro %}

and then the following sql query:

{%- set class_to_score = class_to_score -%}

select 
    {{ set_class_to_score(column1) }} as column1_score,
from
    table

However, I get the error:

Syntax error: SELECT list must not be empty at [5:1]

Anyone know why I am not getting anything back?

user_3842610 · Answer

Thanks for the time you took to communicate your question. It's not easy! It looks like you're experiencing the number one misconception when it comes to dbt and Jinja:

Jinja isn't about transforming data, it's about composing a single SQL query that will be sent to the database. After everything inside jinja's curly brackets is rendered, you will be left with a query that can be sent to the database.

This notion does get complicated with dbt macros like run_query (docs) which will go to the database and get information. But the info you fetch can only used to generate the SQL string.

Your example sounds like the way to do things if you're using Python's pandas where the transformations happens in memory. In dbt-land, only the string generation happens in memory, though sometimes we get some of the substrings from the database before making the new query. So it sounds like you'd like Jinja to look at every value in the column and make the substitution, what you really need to do be doing is make generate a query that instructs the database to make the substitution. The way we do substitution in SQL is with CASE WHEN statements (see Mode's CASE docs for more info)

This is probably closer to what you want. Note it's probably better to make the likert_map object into a dbt seed table.

{% set likert_map =
    {"1": "always", "2": "often", "3": "sometimes", "4": "never"} %}

SELECT
    CASE column_1
    {% for key, value in likert_map.items() %}
        WHEN '{{ value }}' THEN '{{ key }}'
    {% endfor %}
        ELSE 0 END AS column_1_new,
    CASE column_2
    {% for key, value in likert_map.items() %}
        WHEN '{{ value }}' THEN '{{ key }}'
    {% endfor %}
        ELSE 0 END AS column_2_new
    {% endfor %}
FROM
    table

Here's some related questions using mapping dictionary information to make a SQL query:

dbt if/else macros return nothing

1 Answers