sqlmeshsm [EXPERIMENTAL]

SQLMesh macros used for ❄️ Dynamic Masking Policies Implementation ✏️

Macro(s) 🚧 (currently blocked by awaiting for more supports from the sqlmesh's Macro Context)

• create_masking_policy (source)
• apply_masking_policy (source)

And, the Snowflake Hooker CLI (hook) ⭐

Hook(s) ✅

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

Data Masking Development

1. Installation

pip install sqlmeshsm --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 Full 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,
    full_name_column string
) RETURNS string ->
    CASE
        WHEN masked_column IS NOT NULL THEN LEFT(full_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
    ...
)

/* MODEL SQL CODE HERE */

/* (optional) ADD this if `mp_schema` schema is not part of any models */
CREATE SCHEMA IF NOT EXISTS mp_schema;

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

/* APPLY the masking policies */
@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, ['full_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:

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

# for example:
hook drop_masking_policy \
    -c ~\.sqlmesh\config.yml
    -mp common.mp_first_name

Here is the sample config.yml file, if you're not using sqlmesh but wanted to try the CLI:

# config.yml

gateways:
    masking_policy:
        connection:
            type: snowflake
            account: YOUR_VALUE
            user: YOUR_VALUE
            authenticator: externalbrowser
            # password: YOUR_VALUE
            warehouse: YOUR_VALUE
            database: YOUR_VALUE
            role: YOUR_VALUE
default_gateway: masking_policy

Try hook -h for more options.

Voila! Happy Masking 🎉

Contribution

If you've ever wanted to contribute to this tool, and a great cause, now is your chance!

See the contributing docs CONTRIBUTING for more information.

Our Contributors: