Macros to return select statement?

Archive
1

I have a select statement like below that has standard columns used across many models. However, in some model there will other columns in addition to the standard ones. What’s the best way to manage the standard columns? In a macro? If so, how?

Variables don’t seem to work because I can’t use jinja in variables. Is that right?

select 
        id as page_view_id,
        anonymous_id,
        user_id,
        timestamp,
        url as page_url,
        {{ dbt_utils.get_url_host('url') }} as page_url_host,
2

Hey @kevincmclaughlin

A possible approach to this problem would be to use a macro to return the standard columns and not the entire select statement. This will make it easy to add additional columns as well when needed.

So the macro could look something like:

{% macro get_standard_columns() %}
        id as page_view_id,
        anonymous_id,
        user_id,
        timestamp,
        url as page_url,
       {{ dbt_utils.get_url_host('url') }} as page_url_host
{% endmacro %}

And when used in a model, it would look like this:

select
        {{ get_standard_columns() }},
        additional_column_x as x,
        additional_column_y as y
from
        source

I hope I understood your issue correctly and the above helps!

1 Like
3

@wayde thanks. This is exactly what I wanted.