SQLMesh macros used for ❄️ Dynamic Masking Policies implementation ✏️, and the Snowflake Hooker CLI (hook) ⭐
Project description
sqlmeshsm
SQLMesh macros used for ❄️ Dynamic Masking Policies implementation ✏️, and the Snowflake Hooker CLI (hook
) ⭐
List of macros:
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 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:
hook drop_masking_policy -c /path/to/sqlmesh/config.yml -mp you_mp_function_name
# for example: hook drop_masking_policy -c tests\macros\config.yaml -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.0b4-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 14e9d2f2ce4c1613014ed39bb384b60e3abd4ebc8b92475b95b6a92e9d0f3fcb |
|
MD5 | 9e24f0683b2a4d161e19de9bc74e825b |
|
BLAKE2b-256 | 5f98f7dbaf184cf7d3391b31283d5aa2f78cdaff477aec5e0771e062de911e71 |