Search code examples
snowflake-cloud-data-platformdbt

How do I run dbt macro multiple times based on a variable from a select statement?


I have a problem with automatically granting rights to multiple roles per different dbt models.

I have a snowflake table with Model_Name and a role_name(s) column. One to many. I need to post_hook macro

{{ grant_select_on_schemas( "ROLE_NAME" ) }}

; as many times as a role_name appears in the table for a selected Model_Name, with "ROLE_NAME" being dynamically assigned based on the values on the snowflake table.

Any pointers would be greatly appreciated as I'm very fresh to jinja.

BIG thanks


Solution

  • The recommended way is to set the permissions in the configuration for your project and/or models.

    In dbt_project.yml:

    models:
      my_project:
        +grants:
          select:
            - role1
            - role2
    

    This will set the grant on all models in your project. You can go down into directory level, if needed.

    If you want to set grants at the model level:

    {{
        config({
            "materialized": 'table',
            "grants": {
                '+select': ['role1', 'role2],
            },
        })
    }}
    

    If you specify grants in both dbt_project.yml and in the model, the grants will stack.

    Older method (not recommended): Create a macro:

    {% macro grant_select_on_schemas(database_schemas) %}
      {% for (database, schema) in database_schemas %}
        grant usage on database {{ database }} to role role1;
        grant usage on database {{ database }} to role role2;
    
        grant usage on schema {{ database }}.{{ schema }} to role role1;
        grant usage on schema {{ database }}.{{ schema }} to role role2;
    
        grant select on all tables in database {{ database }} to role role1;
        grant select on all tables in database {{ database }} to role role2;
      {% endfor %}
    {% endmacro %}
    

    And call as a post-hook in dbt_project.yml

    on-run-end:
      - "{{ grant_select_on_schemas(database_schemas)}}"
    

    More info: