DBT macro is not returning anything in my dbt model

shafia askari 0
Viewed 25

I have created a macro that extracts the last_name from full_name and returns the last_name

{% macro parse_last_name(column_name) %}
    {% if column_name is none %}
        {{ return (column_name) }}
    {% else %}
        {{ return (column_name.split(',')[1]) }}
    {%endif%}
{% endmacro %}

this is called in my dbt model


select 
    
    {{parse_last_name('FULL_NAME')}} AS LAST_NAME

from <table_schema>

but the SQL compiled is:

select 
    
     AS LAST_NAME

from <target_schema>

so my macro is not returning anything.

I am running my model using dbt run --select model_name.sql

What am I doing wrong here?

dbt jinja2 macros sql
1 Answers

There are two problems with your macro:

  1. You do not want to use the return keyword. return passes data back to the caller in the jinja context, but you're actually trying to write SQL code here
  2. Data doesn't flow through the jinja context. When you write column_name.split(',')[1], you're actually splitting the NAME of the column that you've passed in as a string, NOT the data from that column. In other words, your macro is equivalent to "LAST_NAME".split(",")[1]

You need your macro to return SQL to do this parsing for you. Something like this would work (on Snowflake):

{% macro parse_last_name(column_name) %}
    split_part({{ column_name }}, ',', 2)
{% endmacro %}

Note that this uses the SQL function split_part, so that when your model is compiled, it becomes valid SQL:

select 
    
     split_part(LAST_NAME, ',', 2) AS LAST_NAME

from <target_schema>
tconbeer0
Related