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 '.'.