How do we pass column values into dbt macros

Help
1

Need to strip/format an address field in a customer account. Trying to pass the value of the customer account street column into a dbt/jinja macro to parse

Example query:

with 
test as (
    SELECT 
        first_name, last_name, 
       {{ format_street(customer_account.street) }} as account_street, 
       city, region, postal_code, country
    FROM  {{ source(db, table }} customer_account
    LIMIT 5
)

Macro example just to log what the street even looks like:

{% macro format_street(row_value) %}
    {{ log(row_value, info=true)  }}    
{% endmacro %}

What I’ve already tried

Trying below line:

 {{ format_street('street') }} as account_street

logs the column name into the macro and workable with sql, but I’d need the actual string column value to use with jinja

Trying below line:

 {{ format_street('customer_account.street') }} as account_street

logs the literal string “customer_account.street” into the macro and not the column value

Trying without quotations:

 {{ format_street(customer_account.street) }} as account_street

throws error “‘customer_account’ is undefined. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with “dbt deps”.”

Is there a specific format that I’m missing? I’d just need the column value passed into a macro. Have been super hard to find further information on this. Any help is much appreciated in advance

2

That would be the correct way to pass the column name value to the macro. The problem is to debug you are using another macro.
If you want to truly see the effect of the macro, I would suggest adding a simple trim statement to it like:-

{% macro format_street(column_name) %}
    trim({{ column_name }})
{% endmacro %}

And then check the compiled SQL.

I am guessing from the way you named the argument to the macro (row_value ) you were confusing it with working on the actual data instead of just creating the SQL to work on it.

3

Hi,

Thanks for replying

I don’t quite understand the last part of your statement. I do need to work on the actual data ( will transform it in the macro with jinja )

If I used below line inside the sql select

 {{ format_street('customer_account.street') }} as account_street

The log value of the macro is “customer_account.street” vs the actual value of the row column. Though I think the data is actually passed in correctly, I’m just not able to use it in jinja? The end use case for this macro is I’d need to check the length of the value passed, and if it begins with a certain character, to strip it out

4

For a 10,000 feet overview, the way dbt works, the Jinja-SQL we write is converted into SQL for your target database in the compile step, and the run step basically runs that SQL.

Your macros are replaced with SQL code at the compile time itself, they don’t work on the data in your database. For the example the modified macro I mentioned in my previous post (which you completely ignored) when used in a dbt model would have resulted in the following :-

{% macro format_street(column_name) %}
    trim({{ column_name }})
{% endmacro %}

The dbt model code:-

with 
test as (
    SELECT 
        first_name, last_name, 
       {{ format_street("customer_account.street") }} as account_street, 
       city, region, postal_code, country
    FROM  {{ source(db, table }} customer_account
    LIMIT 5
)
select * from test;

The above SQL when doing (dbt compile or dbt run) would get transformed to:-

with 
test as (
    SELECT 
        first_name, last_name, 
       trim(customer_account.street) as account_street, 
       city, region, postal_code, country
    FROM  <actual_table_path_in_your_db> customer_account
    LIMIT 5
)
select * from test;

When dbt runs the query in your target warehouse / db, the above SQL would be the one sent, and as you can see, there is no jinja code or macros here.

So to summarise, in the jinja, you don’t work with the actual values in the database, you use the jinja to generate the SQL code which will work on the values in the database.

5

Hi stephen,

Thanks for replying. Your macro suggestion wasn’t ignored, that’s how I knew the value was getting passed into the macro by changing the value like:

{{ format_street('customer_account.first_name') }}

Would pass the name as value inside db with the trim function

Can I confirm that the column value passed into dbt macro is not workable in a jinja template?