Skip to content
Advertisement

dbt macro – using *args/**kwargs

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

Advertisement

Answer

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 contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement