dbt was unable to infer all dependencies for the model on-run-end

Help
, ,
1

Hello,

In my dbt project, on -run-end I call a macro to manage some authorization.
This macro calls a dbt model (called “role_schema_matrix”). When running dbt I always get the error message “dbt was unable to infer all dependencies for the model on-run-end”

I get an advice that tells me:
This typically happens when ref() is placed within a conditional block.
To fix this, add the following hint to the top of the model “events_to_datavault-on-run-end-0”:

– depends_on: {{ ref(‘role_schema_matrix’) }}

so of course I tried to add it at the top of my macro but I still get the same error.
I also tried to simply add:
– {{ ref(‘role_schema_matrix’) }}
but the result is still the same, it doesn’t work.

So my dbt_project.yml looks like that:

image

My macro looks like that:

 {% macro grant_select() %}
--{{ ref('role_schema_matrix') }}
   {% for schema in schemas %}
       {{ print ( "schema=" ~schema ) }}
     {# for each schema, we get the roles to be granted #}
     {% set sql_query %}
       select role
       from {{ ref ('role_schema_matrix') }}
        where schema = '{{ schema }}'
     {% endset %}
     {{ print ( "sql_query=" ~sql_query ) }}
 
     {% set results = run_query(sql_query) %}
 
     {% if execute %}
       -- we put the roles in a list ...
       {% set role_list = results.columns[0].values() %}
       {{ print ( "role_list_execute=" ~role_list ) }}
     {% else %}
       {% set role_list = [] %}
       {{ print ( "role_list_else=" ~role_list ) }}
     {% endif %}
   {% endfor %}
 {%- endmacro -%}

If I replace the “ref” by a “source”, it works well, but I’d prefer to use the “ref” as it’s a model built in my dbt project.

How can I fix this error?
Thank you for your help!

2

Hey @Gyom, I believe the dependency needs to be stated at the very start of the model (as it’s hidden within the macro at the moment) like:

-- depends_on: {{ ref('role_schema_matrix') }}
 {% macro grant_select() %}
   {% for schema in schemas %}
       {{ print ( "schema=" ~schema ) }}
     {# for each schema, we get the roles to be granted #}
     {% set sql_query %}
       select role
       from {{ ref ('role_schema_matrix') }}
        where schema = '{{ schema }}'
     {% endset %}
     {{ print ( "sql_query=" ~sql_query ) }}
 
     {% set results = run_query(sql_query) %}
 
     {% if execute %}
       -- we put the roles in a list ...
       {% set role_list = results.columns[0].values() %}
       {{ print ( "role_list_execute=" ~role_list ) }}
     {% else %}
       {% set role_list = [] %}
       {{ print ( "role_list_else=" ~role_list ) }}
     {% endif %}
   {% endfor %}
 {%- endmacro -%}
3

Hi,
Thanks for your advice, but unfortunately I already tried and the result remains the same :frowning: