Check if table has row access policy in post_hook config

Help
1

The problem I’m having

I have an incremental table (the database is in Snowflake), and the first time I run the dbt job with an alter table command in the post_hook to add a row access policy, it runs with no issue. The next time I run it, I get an error saying the table already has a row access policy. I am trying to add a conditional to the post_hook to only add the row access policy if it doesn’t already exist.

Here’s the config block:

{{
  config(
    materialized = "incremental",
    incremental_strategy = "append",
    post_hook = "BEGIN IF (NOT EXISTS(select * from table(information_schema.policy_references(ref_entity_name =>  {{ this }} , ref_entity_domain => 'table')))) THEN alter table {{ this }} add row access policy <policy_to_add> ON (<column_to_add_to>); END IF; END;"
  )
}}

The chunk:

select * from table(information_schema.policy_references(ref_entity_name =>  {{ this }} , ref_entity_domain => 'table'))

works in Snowflake when I replace {{ this }} with the name of the table.

I keep getting the error:

001003 (42000): SQL compilation error:
syntax error line 1 at position 9 unexpected '('.
syntax error line 1 at position 59 unexpected '.'.
2

I know its an old question, but have you tried quoting the substitution?

'{{ this }}'

By default {{this}} will result in the actual string, but won’t include explicit quotation. You have to add this manually.

3

you’ve probably found a solution as this is an old post. Curious as to what it is.
here’s my thoughts anyway… I think FirefoxMetzger’s right and the quotes are needed.
but I think the problem is your script block. Does that execute successfully in snowflake alone? because it doesn’t for me (same error). Though the dbt generated select statement (with quotes) will run successfully on its own.

Another possible solution could be using is_incremental(). Once created the policy should continue to exist for all append builds.
It would only need to be created on --full-refresh recreate of the object. so could code this to call the alter table statement only if is_incremental is false. (could also do this in jinja instead of a sql block)

4

I came across this looking up a different thread and hadn’t realized there were responses! At the time I couldn’t figure out the specific error that you both mentioned (I was quite new to dbt and still learning the nuances of templating like this). Thanks both for helping to troubleshoot that. I ended up using a macro, which is basically the same as the one-liner I originally had, but allowed me to avoid the templating error:

{% macro conditional_apply_row_access_policy(table_relation) %}
{%- set source_relation = adapter.get_relation(
      database= table_relation.database,
      schema=table_relation.schema,
      identifier=table_relation.name) -%}

{% set table_exists=source_relation is not none %}

{% if table_exists %}
    alter table {{ table_relation }} add row access policy <policy_to_add> ON (<column_to_add_to>) 
{% endif %}
{% endmacro %}

And then in the config:
post_hook = conditional_apply_row_access_policy(this),

1 Like
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.