Skip to content

Latest commit

 

History

History
145 lines (104 loc) · 5.24 KB

README.md

File metadata and controls

145 lines (104 loc) · 5.24 KB

Overview

This dbt package contains macros that can be (re)used across dbt projects with snowflake. dbt_snow_mask will help to apply Dynamic Data Masking using dbt meta.

Installation Instructions

  • Add the package into your project.

    Example : packages.yml

       - git: "https://github.com/entechlog/dbt-snow-mask.git"
     revision: 0.1.2
       - package: entechlog/dbt_snow_mask
     version: 0.1.2
  • Packages can be added to your project using either of these options
  • Please refer to the release version of this repo/dbt hub for the latest revision

How to apply masking policy ?

  • Masking is controlled by meta in dbt resource properties for sources and models.

  • Decide you masking policy name and add the key masking_policy in the column which has to be masked.

    Example : source.yml

    sources:
  - name: sakila
    tables:
      - name: actor
        columns:
          - name: FIRST_NAME
            meta:
                masking_policy: temp

    Example : model.yml

    models:
  - name: stg_customer
    columns:
      - name: customer_last_name
      - name: customer_email
        meta:
          masking_policy: temp

  • Create a new .sql file with the name create_masking_policy_<masking-policy-name-from-meta>.sql and the sql for masking policy definition. Its important for macro to follow this naming standard.

    Example : create_masking_policy_temp.sql

    {% macro create_masking_policy_temp(node_database,node_schema) %}

CREATE MASKING POLICY IF NOT EXISTS {{node_database}}.{{node_schema}}.temp AS (val string) 
  RETURNS string ->
      CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val 
           WHEN CURRENT_ROLE() IN ('DEVELOPER') THEN SHA2(val)
      ELSE '**********'
      END

{% endmacro %}

Its good to keep the masking policy ddl organized in a directory say \macros\snow-mask-ddl

  • Create the masking policies by running below command

    Resource Type Command
    sources dbt run-operation create_masking_policy --args '{"resource_type": "sources"}'
    models dbt run-operation create_masking_policy --args '{"resource_type": "models"}'

  • Add post-hook to dbt_project.yml

    Example : dbt_project.yml

    models:
  post-hook: 
    - "{{ dbt_snow_mask.apply_masking_policy() }}"

  • Apply the masking policy by running below commands

    Resource Type Command
    sources dbt run-operation apply_masking_policy --args '{"resource_type": "sources"}'
    models dbt run -- model <model-name>

How to remove masking policy ?

  • Remove the masking policy applied by this package by running below commands

    Resource Type Command
    sources dbt run-operation unapply_masking_policy --args '{"resource_type": "sources"}'
    models dbt run-operation unapply_masking_policy --args '{"resource_type": "models"}'

How to validate masking policy ?

-- Show masking policy
SHOW MASKING POLICIES;

-- Describe masking policy
DESCRIBE MASKING POLICY <masking-policy-name>;

-- Show masking policy references
USE DATABASE <database-name>;

USE SCHEMA INFORMATION_SCHEMA;

SELECT *
  FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(POLICY_NAME => '<database-name>.<schema-name>.<masking-policy-name>'));

Future Enhancements & Limitations

  • Optimize macros & reduce number of lines in macros
  • apply_masking_policy_list_for_sources needs changes to find the materialization, Currently this supports table sources only
  • unapply_masking_policy has to be executed before running masking policy definitions with CREATE OR REPLACE MASKING POLICY

Credits

This package was created using examples from Serge and Matt

References