Pass table column values to Macro

Help
1

How can we pass column values to Macro for processing.

Example - in below model, we would like to pass column (col1,col2) values to macro (concat_macro) to process using these 2 columns .

with test as
(
select ‘abc’ as col1, ‘xyz’ as col2 union
select ‘ggg’ as col1, ‘xxx’ as col2
)select col1,col2,
{% if execute %}
‘{{ concat_macro(“col1”,“col2”) }}’ as column1 from test
{% endif %}

Regards,

Thanks @ joellabes for your reply…
Kindly find model and macro definition

macro
{% macro concat_macro(col1,col2) %}
{% set tmp = col1+col2 %}
{{ tmp }}

{% endmacro %}

Model
with test as
(
select ‘abc’ as col1, ‘xyz’ as col2 union
select ‘ggg’ as col1, ‘xxx’ as col2
)select col1,col2,
{% if execute %}
‘{{ concat_macro(“col1”,“col2”) }}’ as column1 from test
{% endif %}

I tried to remove the quotes from model but its giving error like invalid identifier ‘COL1COL2’

But Anyways if I just put quotes in model, out put model is giving like

image
image749×128 1.46 KB

COL1 COL2 COLUMN1
abc xyz col1col2
ggg xxx col1col2

Also Can you Please help me to understand when to use Macros and when to use Snowflake UDFs
Can you Please share some link/documentation for learning macros/jinja coding.

@joellabes - Appreciate your help :slight_smile:
Regards,

2

Hi @dinesh.rupchandani, the code you’ve posted looks basically correct, however you don’t need to wrap your concat_macro call in the ' at the start/end of the line - that will result in a quoted string like 'col1 || col2'. With the quotes removed, I would expect your code to render as

with test as
(
select ‘abc’ as col1, ‘xyz’ as col2 union
select ‘ggg’ as col1, ‘xxx’ as col2
)
select col1,col2,
col1 || col2 as column1 
from test

If that’s not what you’re seeing, can you share the concat_macro() that you’ve written? Perhaps there is an issue in your macro code instead of in the model you posted.

Or, are you asking whether there is a sample concatenation macro already written? You can use {{ dbt.concat() }}.

1 Like
3

Hi @dinesh.rupchandani thanks for updating the post - it’s easier to click the Reply button and add an additional post though!

Looking at the macro you provided:

{% macro concat_macro(col1,col2) %}
   {% set tmp = col1+col2 %}
   {{ tmp }}
{% endmacro %}

by creating a tmp variable you are creating a string which is just the two inputs combined, instead of creating a command for Snowflake to concatenate the two columns.

Since you’re using Snowflake, the correct syntax for concatenation is concat() or ||: CONCAT , || | Snowflake Documentation. This doesn’t necessarily need to be a macro as it’s pretty standard behaviour:

select     
    'abc' as col1, 
    'def' as col2, 
    col1 || col2 as combined

But if you wanted to do it anyway as an experiment then this would be better:

{% macro concat(val1, val2) %}
    {{ val1 }} || {{ val2 }}
{% endmacro %}

Note that this macro would only support two arguments, you wouldn’t be able to concatenate 3 columns together (which is why the native one linked above is better)

We have an on-demand course for learning Jinja here

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.