Building a snowflake UDF in a macro with forced lineage via hooks

[janine.rawnsley]

The problem I’m having

I’m getting a cyclical compile issue in this scenario:

MacroB builds a snowflake UDF. The UDF itself references ModelA.
ModelA has a posthook to call MacroB, since ModelA has to exist in order for the UDF within MacroB to compile.

uncaught python exception
Found a cycle: model.catalyst_model.l_application_role

I don’t believe this is actually a cycle. Simply, MacroB depends on ModelA and can only be built once it exists.

The context of why I’m trying to do this

I want dbt to compile my UDFs. In this scenario, once MacroB is run (thereby compiling into a snowflake UDF), I will then reference said UDF in other dbt models. My second challenge will be to make sure these build AFTER MacroB. I’m hoping I’m missing some crucial information on how best to make dbt work with macros, which build UDFs, that reference models.

What I’ve already tried

pre hooks, post hooks within ModelA to compile MacroB, also at runtime in the project.yml

Some example code or error messages

-- model_a
SELECT  *
FROM    {{ ref('some_otther_table') }}

{{ config(
    post_hook="{{ macro_b() }}"
)}}

-- macro_b
{% macro macro_b() %}

    CREATE OR REPLACE FUNCTION {{ target.schema }}.macro_b()

        RETURNS SMALLINT

        AS
        $$

            SELECT  result
            FROM    {{ ref('model_a') }} where ...

        $$
        ;

{% endmacro %}

[brunoszdl]

Try to change in the macro

FROM {{ ref('model_a') }} where ...

to

FROM {{ this }} where ...