Write a macro to tag columns for data masking policy in Snowflake

Help
1

The problem I’m having:

I am trying to write a macro to apply tags on columns with pii data.

The context of why I’m trying to do this:

The idea is that when these tagged columns enter Snowflake they are picked up and our data masking policy is automatically applied.

What I’ve already tried and some example code or error messages:

{% macro pii_data_masking_tags_post_hook(tag_name, tag_value, columns) %}
   -- Apply pii_data_masking_tags to specified columns
    {% if not is_incremental() %}
        {% for column in columns %}
            ALTER TABLE {{ this }} MODIFY COLUMN {{ column }} SET TAG '{{ tag_name }}' = '{{ tag_value }}' 
            {% if not loop.last %},{% endif %}
        {% endfor %}
    {% endif %}
{% endmacro %}

ERROR: (42000): SQL compilation error:
syntax error line 57 at position 12 unexpected ‘ALTER’.

Also tried:

{% macro pii_data_masking_tags_post_hook(tag_name, tag_value, columns) %}
    -- Apply pii_data_masking_tags to specified columns
    {% if not is_incremental() %}
        {% for column in columns %}
            MODIFY COLUMN {{ column }} SET TAG '{{ tag_name }}' = '{{ tag_value }}' 
            {% if not loop.last %},{% endif %}
        {% endfor %}
    {% endif %}
{% endmacro %}

ERROR: (42000): SQL compilation error:
syntax error line 57 at position 12 unexpected ‘MODIFY’.
syntax error line 60 at position 19 unexpected ‘COLUMN’.

2

Your issue might be that you put a , instead of ; within this part of the code.

{% if not loop.last %},{% endif %}

if you have multiple columns in this loop, you would want to end each alter or modify statement with a ; to separate them.

I also believe the syntax is ALTER TABLE XXXX ALTER COLUMN YYYY SET TAG MYTAG = ‘SOME VALUE’;

3

Hey. Check out our package for implementing Snowflake tag-based masking policy in dbt
https://hub.getdbt.com/infinitelambda/dbt_tags/latest/