How to use a macro after another macro dbt in the same sql

Question

I'm quite a new user of dbt and I need some help whith the macros.

The point is that I'm trying to execute a macro after another macro in the same SQl.

The first macro look up to the most recent "data_modificacao" register by the "id_produto" and return it

the second macro use star to exclude some columns because the view has more than 60 columns, its a crap to call all them all the time

the code that I'm trying to apply the macros is:

on Produtos_Sem_Tratamento.sql:

with
    Produtos as (
        {{ registros_recentes(ref('stg_Produtos'), 'id_produto', 'data_modificacao') }}
    ),

    final as (
        {{filtrar_colunas_star(ref = Produtos,"row_number")}}
    )

select * from final

the first macro code is:

{% macro registros_recentes(model_name, id_column_name, time_column_name) %}

with
    added_row_number as (
        select
            *
            ,row_number() over(partition by {{id_column_name}} order by {{time_column_name}} desc) as row_number
        from {{ model_name }}
    ),

    first_register as (
        select
            *
        from added_row_number

        where row_number = 1
    )

select * from first_register

{% endmacro %}

the second macro code is:

{% macro filtrar_colunas_star(model_name, column_name) %} /* column_name é o nome da coluna da 'chave_altoqi' */

{%- set columns = dbt_utils.star(from=model_name, except=[column_name]) -%}

with
    colunas_filtradas as (
        select {{columns}}
        from {{model_name}}
    )

select * from colunas_filtradas 

{% endmacro %}

Can someone help me with that?

the error log is:

Compilation Error in model Produtos_Sem_Tratamento (models\marts\Zoho\basic\Produtos_Sem_Tratamento.sql)
Macro star expected a Relation but received the value: Produtos

Answer 1

dbt_utils.star() does not work on CTEs. It only works on a Relation. In your case, all you're doing is filtering out the row_number column you added in the first CTE to the original ref. You can just pass in the Relation from the model into the second macro just like you did in the first.

You're not filtering any columns out of the original ref, so you don't even need the except parameter. But, I left it below to keep the example consistent with the code in your question. You'll get the same result if you take it out.

{% set src_rel = ref('stg_Produtos') %}

with
    Produtos as (
        {{ registros_recentes(src_rel, 'id_produto', 'data_modificacao') }}
    ),

    final as (
        {{filtrar_colunas_star('Productos', "row_number", src_rel)}}
    )

select * from final

{% macro filtrar_colunas_star(model_name, column_name, src_ref) %} /* column_name é o nome da coluna da 'chave_altoqi' */

{%- set columns = dbt_utils.star(from=src_ref, except=[column_name]) -%}

with
    colunas_filtradas as (
        select {{columns}}
        from {{model_name}}
    )

select * from colunas_filtradas 

{% endmacro %}