dbt macro - using *args/**kwargs

user_10757281 0
Viewed 1345

Occasionally, our tables need to append multiple columns together to make a unique key. This can be done by doing something like:

select
    *,
    col1 || "_" || col2 as unique_key
from 
    my_table

This works but lends to a lack of uniformity amongst multiple analysts.

I would like to utilize pythons *args (i think jinja2 uses varargs) feature to make a macro that can take an arbitrary amount of arguments and create a unique key between all of them.

Ideal outcome:

select
    *,
    unique_key(col1, col1, ..., colN)
from 
    my_table
dbt snowflake-cloud-data-platform jinja2 sql
1 Answers

There is currently a macro in the dbt_utils package that does something similar, called surrogate key. It used to only use varargs and now also allows a list.

For the varargs portion, it does the following:

{%- for field in varargs %}
{%- set _ = field_list_xf.append(field) -%}
{%- endfor -%}

You can then join() or iterate through that list to do whatever you'd like. In the case of the macro, it does the following:

{%- for field in field_list_xf -%}

    {%- set _ = fields.append(
        "coalesce(cast(" ~ field ~ " as " ~ dbt_utils.type_string() ~ "), '')"
    ) -%}

    {%- if not loop.last %}
        {%- set _ = fields.append("'-'") -%}
    {%- endif -%}

{%- endfor -%}
user_133399470
Related