DBT Databricks automatic data masking macro, must be configured as a post-hook

automatic_dynamic_masking.sql

{% macro get_query_results_as_dict(query) %}
    {{ return(adapter.dispatch('get_query_results_as_dict', 'dbt_utils')(query)) }}
{% endmacro %}

{% macro automatic_dynamic_masking() %}

    {% set sensitive_columns = ['email', 'firstname', 'lastname', 'middlename', 'name', 'phone', 'telephone'] %}

    {% set query %}
        SELECT * FROM {{ this }} LIMIT 0
    {% endset %}
    {% set results = get_query_results_as_dict(query) %}

    {% if execute %}
        {% for column_name in results.keys() %}
            {% set normalized = column_name|lower|replace(' ', '')|replace('_', '') %}

            {% if normalized in sensitive_columns %}
                {{ print("Found sensitive column, applying auto masking: " ~ column_name) }}

                {% set query %}
                    ALTER TABLE {{ this }} ALTER COLUMN {{ column_name }}
                        SET MASK mycatalog.myschema.myemail_filter
                {% endset %}

                {% do run_query(query) %}
            {% endif %}
        {% endfor %}
    {% endif %}

{% endmacro %}