SQLMesh macros for Snowflake Dynamic Masking Policies
Project description
sqlmesh-snow-mask
SQLMesh macros used for ❄️ Dynamic Masking Policies implementation ✏️, and the Snowflake Hooker CLI (sfhook
) ⭐
List of macros:
Hooks:
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 ofnull
- 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 🎉
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.
Finally, super thanks to our Contributors:
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for sqlmeshsm-0.1.0b0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 811c3f708e0d0958fc57982bf5e402de45f64fa7abe90ce40997a238a04b0682 |
|
MD5 | a72063aa601effd419a4893063331fc0 |
|
BLAKE2b-256 | d2219d0c2c28627dbf13bb3f0e7b993404ee9f0dd103233d841c3aef43ec0ecf |