Post-hook to DBT_CLOUD_PR Schemas (DBT Cloud CI)

Question

I was wondering if it is possible to attach a post hook to DBT_CLOUD_PR Schemas (Generated by dbt Cloud CI) so that only the developers can see the PR tables generated on the database.

I would like to do something looking like the following:

dbt_cloud_pr:
  +post-hook:
    - "revoke select on {{ this }} from role reporter"

Right now, our dbt_cloud_pr schemas can be seen by multiple roles on Snowflake, and it clusters the database with some non-essential schemas that we would rather hide.

Thanks for your help !

Answer 1

This is a cool idea!

You can configure your dbt Cloud CI job to use a custom "target" name (it's the Job > Edit Settings page). Let's say you set that to ci

Then I think in your code you should be able to add a post-hook like

models:
  +post-hook: {% if target.name == 'ci' %} revoke select on {{ this }} from role reporter {% else %} select 1 {% endif %}

If that if..else syntax isn't allowed in the post-hook itself, you can wrap it in a macro, and call that macro from the post-hook:

models:
  +post-hook: {{ revoke_select_on_ci(this) }}

And in your macros directory:

{%- macro revoke_select_on_ci(model, ci_target='ci') -%}
    {%- if target.name == ci_target -%}
        revoke select on {{ model }} from role reporter
    {%- else -%}
        select 1
    {%- endif -%}
{%- endmacro -%}