sqlmeshsm 0.1.0b3

SQLMesh macros used for ❄️ Dynamic Masking Policies implementation ✏️, and the Snowflake Hooker CLI (sfhook) ⭐

sqlmeshsm

SQLMesh macros used for ❄️ Dynamic Masking Policies implementation ✏️, and the Snowflake Hooker CLI (sfhook) ⭐

List of macros:

• create_masking_policy
• apply_masking_policy

Hooks:

• sfhook drop_masking_policy -c {config.yml} -mp {func}

Data Masking Development

1. Installation

pip install sqlmesh_snow_mask --upgrade

In your (sqlmesh-project-dir)/macros/__init__.py, let's import our lib:

from sqlmeshsm import macros

2. Create masking policy functions

For example, the customer table needs the following masking policies:

• First Name: mask with * except the first 3 characters, fixed length of 10, no masking of null
• Last Name: mask with the first character of First Name, no masking of null

There are 2 masking functions, they must be created with following requirements:

• 📂 Files located under (your-sqlmesh-project)/macros/snow-mask-ddl
• 🆎 File name format: {mp_schema}.{mp_function_name}

-- /snow-mask-ddl/mp_schema.mp_first_name.sql
CREATE MASKING POLICY IF NOT EXISTS @schema.mp_first_name AS (
    masked_column string
) RETURNS string ->
    LEFT(CASE 
        WHEN masked_column IS NOT NULL THEN LEFT(masked_column, 3)
        ELSE NULL
    END || '**********', 10);

-- /snow-mask-ddl/mp_schema.mp_last_name.sql
CREATE MASKING POLICY IF NOT EXISTS @schema.mp_last_name AS (
    masked_column string,
    first_name_column string
) RETURNS string ->
    CASE 
        WHEN masked_column IS NOT NULL THEN LEFT(first_name_column, 1)
        ELSE NULL
    END;

@schema is the keyword to indicate the schema name which matches to the first part of the file name

3. Decide to mask model's columns

/* /models/my_customer_model.sql */
MODEL(
    name my_schema.my_customer_model
    kind FULL
    ...
)

/* REGISTER the masking funcs */
@create_masking_policy(mp_schema.mp_first_name)
@create_masking_policy(mp_schema.mp_last_name)

/* MODEL SQL CODE HERE */

/* USE the masking funcs */
@apply_masking_policy(first_name, mp_schema.mp_first_name)
@apply_masking_policy(my_schema.my_customer_model, last_name, mp_schema.mp_last_name, ['first_name'])

Let's plan and apply it now: sqlmesh plan --select-model my_schema.my_customer_model

4. (Optional) Decide to clean up the masking policies

Let's run the built-in hooks:

sfhook drop_masking_policy -c /path/to/sqlmesh/config.yml -mp you_mp_function_name

Try sfhook -h for more options.

Voila! Happy Masking 🎉