SQLMesh macros used for ❄️ Dynamic Masking Policies implementation ✏️, and the Snowflake Hooker CLI (hook) ⭐
Project description
sqlmeshsm [EXPERIMENTAL]
SQLMesh macros used for ❄️ Dynamic Masking Policies Implementation ✏️
List of macros 🚧 (currently blocked by awaiting for more supports from the sqlmesh's Macro Context)
And, the Snowflake Hooker CLI (hook
) ⭐
Hooks ✅
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 ofnull
- 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 funcs */
@create_masking_policy(mp_schema.mp_first_name)
@create_masking_policy(mp_schema.mp_last_name)
/* 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, ['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 C:\Users\DAT\.sqlmesh\config.yml -mp common.mp_first_name
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:
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.0b5-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | cd0ad281b725c1449d648e3a1a6e9e1298a024d8c50c0ed79682d0373f7a74b6 |
|
MD5 | c257268d4d7836fffd5549eee6d723ab |
|
BLAKE2b-256 | f015583cead505a9b678a107f91c559de30fd26f9498b98206f546d88301ab3b |