Apply masking policy with FORCE keyword

il-dat commented 2 years ago

Describe the feature: Snowflake is now allowing us to run apply masking rules with FORCE keyword: ALTER TABLE...ALTER COLUMN...SET MASKING POLICY ...FORCE

This is opening the door for us to use this keyword into the APPLY MASKING POLICY functionalities.

With this feature, we'll remove the annoying point where we need to unset rules firstly then to re-apply rules again. While in development, it's a great benefit when performing CLONE operation, we do not need to care about 'unset' anymore.

Suggested Solution: Add variable use_force_applying_masking_policy: false. If this value turned into true then run apply masking policies with FORCE keyword

entechlog commented 2 years ago

@il-dat ,

Thank you for the PR to implement this request.
Can you also please add some test results to the PR so this can be confidently pulled for release ?
Test with flag set/unset, test both apply and un-apply to make sure there are no breaking changes)
https://github.com/entechlog/dbt-snow-mask#how-to-do-an-integration-test-
https://github.com/entechlog/dbt-snow-mask#how-to-apply-masking-policy-
https://github.com/entechlog/dbt-snow-mask#how-to-remove-masking-policy-

il-dat commented 2 years ago

Hi @entechlog, sorry I should do that at the beginning. Please help to see below: Let me know if anything else I need to perform as further. Thanks

dbt environment:

dbt version: 1.0.8
python version: 3.9.6
python path: /Users/.../snow_mask_env/bin/python3
os info: macOS-12.6-arm64-arm-64bit
Using profiles.yml file at /Users/.../.dbt/profiles.yml
Using dbt_project.yml file at /Users/.../dbt-snow-mask/integration_tests/dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Following the test steps and results:

dbt deps

07:24:10  Running with dbt=1.0.8
07:24:11  Installing ../
07:24:11    Installed from <local @ ../>
07:24:11  Installing dbt-labs/dbt_utils
07:24:11    Installed from version 0.8.4
07:24:11    Updated version available: 0.9.2
07:24:11  
07:24:11  Updates available for packages: ['dbt-labs/dbt_utils']                 
Update your versions in packages.yml, then run dbt deps

dbt seed

07:29:08  Running with dbt=1.0.8
07:29:08  Unable to do partial parsing because a project config has changed
07:29:08  Found 2 models, 0 tests, 1 snapshot, 0 analyses, 405 macros, 2 operations, 2 seed files, 1 source, 0 exposures, 0 metrics
07:29:08  
07:29:15  
07:29:15  Running 1 on-run-start hook
07:29:18  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-start.0............... [RUN]
07:29:18  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-start.0.................. [OK in 0.00s]
07:29:18  
07:29:19  Concurrency: 10 threads (target='snowflake')
07:29:19  
07:29:19  1 of 2 START seed file seeds.contact............................................ [RUN]
07:29:19  2 of 2 START seed file seeds.customer........................................... [RUN]
07:29:26  2 of 2 OK loaded seed file seeds.customer....................................... [INSERT 599 in 6.92s]
07:29:26  1 of 2 OK loaded seed file seeds.contact........................................ [INSERT 1000 in 7.21s]
07:29:26  
07:29:26  Running 1 on-run-end hook
07:29:29  14:29:29 | applying masking policy to source : DBT_SNOW_MASK.SEEDS.MP_ENCRYPT_PII on DBT_SNOW_MASK.seeds.customer.first_name [force = False]
07:29:30  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-end.0................. [RUN]
07:29:30  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-end.0.................... [OK in 0.00s]
07:29:30  
07:29:31  
07:29:31  Finished running 2 seeds, 2 hooks in 22.80s.
07:29:31  
07:29:31  Completed successfully
07:29:31  
07:29:31  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

dbt build (default)

07:36:36  Running with dbt=1.0.8
07:36:36  Found 2 models, 0 tests, 1 snapshot, 0 analyses, 405 macros, 2 operations, 2 seed files, 1 source, 0 exposures, 0 metrics
07:36:36  
07:36:48  
07:36:48  Running 1 on-run-start hook
07:36:50  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-start.0............... [RUN]
07:36:50  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-start.0.................. [OK in 0.00s]
07:36:50  
07:36:51  Concurrency: 10 threads (target='snowflake')
07:36:51  
07:36:51  1 of 5 START view model integration_tests_dat.stg_customer...................... [RUN]
07:36:51  2 of 5 START seed file seeds.contact............................................ [RUN]
07:36:51  3 of 5 START seed file seeds.customer........................................... [RUN]
07:36:51  4 of 5 START snapshot DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [RUN]
07:36:57  3 of 5 OK loaded seed file seeds.customer....................................... [INSERT 599 in 5.89s]
07:36:58  4 of 5 OK snapshotted DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [SUCCESS 1 in 6.97s]
07:36:59  2 of 5 OK loaded seed file seeds.contact........................................ [INSERT 1000 in 7.14s]
07:36:59  5 of 5 START view model integration_tests_dat.stg_contacts...................... [RUN]
07:37:00  14:37:00 | applying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email [force = False]
07:37:01  1 of 5 OK created view model integration_tests_dat.stg_customer................. [SUCCESS 1 in 10.13s]
07:37:06  14:37:06 | applying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email [force = False]
07:37:08  5 of 5 OK created view model integration_tests_dat.stg_contacts................. [SUCCESS 1 in 9.39s]
07:37:08  
07:37:08  Running 1 on-run-end hook
07:37:11  14:37:11 | applying masking policy to source : DBT_SNOW_MASK.SEEDS.MP_ENCRYPT_PII on DBT_SNOW_MASK.seeds.customer.first_name [force = False]
07:37:12  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-end.0................. [RUN]
07:37:12  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-end.0.................... [OK in 0.00s]
07:37:12  
07:37:13  
07:37:13  Finished running 2 seeds, 1 snapshot, 2 view models, 2 hooks in 37.45s.
07:37:13  
07:37:13  Completed successfully
07:37:13  
07:37:13  Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5

dbt build with use_force_applying_masking_policy = "True"

07:38:10  Running with dbt=1.0.8
07:38:10  Unable to do partial parsing because a project config has changed
07:38:11  Found 2 models, 0 tests, 1 snapshot, 0 analyses, 405 macros, 2 operations, 2 seed files, 1 source, 0 exposures, 0 metrics
07:38:11  
07:38:19  
07:38:19  Running 1 on-run-start hook
07:38:22  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-start.0............... [RUN]
07:38:22  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-start.0.................. [OK in 0.00s]
07:38:22  
07:38:23  Concurrency: 10 threads (target='snowflake')
07:38:23  
07:38:23  1 of 5 START view model integration_tests_dat.stg_customer...................... [RUN]
07:38:23  2 of 5 START seed file seeds.contact............................................ [RUN]
07:38:23  3 of 5 START seed file seeds.customer........................................... [RUN]
07:38:23  4 of 5 START snapshot DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [RUN]
07:38:30  14:38:30 | applying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email [force = True]
07:38:30  3 of 5 OK loaded seed file seeds.customer....................................... [INSERT 599 in 7.65s]
07:38:31  2 of 5 OK loaded seed file seeds.contact........................................ [INSERT 1000 in 8.20s]
07:38:31  5 of 5 START view model integration_tests_dat.stg_contacts...................... [RUN]
07:38:31  1 of 5 OK created view model integration_tests_dat.stg_customer................. [SUCCESS 1 in 8.65s]
07:38:35  14:38:35 | applying masking policy to model  : DEV_ENTECHLOG_DEMO_DB.PII.MP_ENCRYPT_PII on DEV_ENTECHLOG_DEMO_DB.pii.ods_customers.email [force = True]
07:38:37  4 of 5 OK snapshotted DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [SUCCESS 1 in 14.07s]
07:38:38  14:38:38 | applying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email [force = True]
07:38:39  5 of 5 OK created view model integration_tests_dat.stg_contacts................. [SUCCESS 1 in 8.38s]
07:38:39  
07:38:39  Running 1 on-run-end hook
07:38:43  14:38:43 | applying masking policy to source : DBT_SNOW_MASK.SEEDS.MP_ENCRYPT_PII on DBT_SNOW_MASK.seeds.customer.first_name [force = True]
07:38:43  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-end.0................. [RUN]
07:38:43  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-end.0.................... [OK in 0.00s]
07:38:43  
07:38:44  
07:38:44  Finished running 2 seeds, 2 view models, 1 snapshot, 2 hooks in 33.44s.
07:38:44  
07:38:44  Completed successfully
07:38:44  
07:38:44  Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5

dbt build with unset/unapply

07:40:23  Running with dbt=1.0.8
07:40:23  Unable to do partial parsing because a project config has changed
07:40:23  Found 2 models, 0 tests, 1 snapshot, 0 analyses, 405 macros, 2 operations, 2 seed files, 1 source, 0 exposures, 0 metrics
07:40:23  
07:40:31  
07:40:31  Running 1 on-run-start hook
07:40:34  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-start.0............... [RUN]
07:40:34  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-start.0.................. [OK in 0.00s]
07:40:34  
07:40:35  Concurrency: 10 threads (target='snowflake')
07:40:35  
07:40:35  1 of 5 START view model integration_tests_dat.stg_customer...................... [RUN]
07:40:35  2 of 5 START seed file seeds.contact............................................ [RUN]
07:40:35  3 of 5 START seed file seeds.customer........................................... [RUN]
07:40:35  4 of 5 START snapshot DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [RUN]
07:40:41  14:40:41 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email
07:40:42  14:40:42 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email
07:40:43  3 of 5 OK loaded seed file seeds.customer....................................... [INSERT 599 in 7.17s]
07:40:43  14:40:43 | unapplying masking policy to model  : DEV_ENTECHLOG_DEMO_DB.PII.MP_ENCRYPT_PII on DEV_ENTECHLOG_DEMO_DB.pii.ods_customers.email
07:40:43  2 of 5 OK loaded seed file seeds.contact........................................ [INSERT 1000 in 8.04s]
07:40:43  5 of 5 START view model integration_tests_dat.stg_contacts...................... [RUN]
07:40:44  1 of 5 OK created view model integration_tests_dat.stg_customer................. [SUCCESS 1 in 9.12s]
07:40:45  14:40:45 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email
07:40:45  14:40:45 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email
07:40:46  14:40:46 | unapplying masking policy to model  : DEV_ENTECHLOG_DEMO_DB.PII.MP_ENCRYPT_PII on DEV_ENTECHLOG_DEMO_DB.pii.ods_customers.email
07:40:48  4 of 5 OK snapshotted DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [SUCCESS 1 in 12.32s]
07:40:50  14:40:50 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email
07:40:51  14:40:51 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email
07:40:51  14:40:51 | unapplying masking policy to model  : DEV_ENTECHLOG_DEMO_DB.PII.MP_ENCRYPT_PII on DEV_ENTECHLOG_DEMO_DB.pii.ods_customers.email
07:40:53  5 of 5 OK created view model integration_tests_dat.stg_contacts................. [SUCCESS 1 in 9.56s]
07:40:53  
07:40:53  Running 1 on-run-end hook
07:40:56  14:40:56 | unapplying masking policy to source : DBT_SNOW_MASK.SEEDS.MP_ENCRYPT_PII on DBT_SNOW_MASK.seeds.customer.first_name [force = False]
07:40:57  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-end.0................. [RUN]
07:40:57  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-end.0.................... [OK in 0.00s]
07:40:57  
07:40:58  
07:40:58  Finished running 2 seeds, 2 view models, 1 snapshot, 2 hooks in 34.30s.
07:40:58  
07:40:58  Completed successfully
07:40:58  
07:40:58  Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5

dbt build with unset/unapply and use_force_applying_masking_policy = "True"

07:41:52  Running with dbt=1.0.8
07:41:52  Unable to do partial parsing because a project config has changed
07:41:53  Found 2 models, 0 tests, 1 snapshot, 0 analyses, 405 macros, 2 operations, 2 seed files, 1 source, 0 exposures, 0 metrics
07:41:53  
07:42:01  
07:42:01  Running 1 on-run-start hook
07:42:04  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-start.0............... [RUN]
07:42:04  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-start.0.................. [OK in 0.00s]
07:42:04  
07:42:05  Concurrency: 10 threads (target='snowflake')
07:42:05  
07:42:05  1 of 5 START view model integration_tests_dat.stg_customer...................... [RUN]
07:42:05  2 of 5 START seed file seeds.contact............................................ [RUN]
07:42:05  3 of 5 START seed file seeds.customer........................................... [RUN]
07:42:05  4 of 5 START snapshot DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [RUN]
07:42:11  14:42:11 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email
07:42:12  14:42:12 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email
07:42:12  14:42:12 | unapplying masking policy to model  : DEV_ENTECHLOG_DEMO_DB.PII.MP_ENCRYPT_PII on DEV_ENTECHLOG_DEMO_DB.pii.ods_customers.email
07:42:13  2 of 5 OK loaded seed file seeds.contact........................................ [INSERT 1000 in 7.48s]
07:42:13  5 of 5 START view model integration_tests_dat.stg_contacts...................... [RUN]
07:42:13  3 of 5 OK loaded seed file seeds.customer....................................... [INSERT 599 in 7.55s]
07:42:14  1 of 5 OK created view model integration_tests_dat.stg_customer................. [SUCCESS 1 in 8.64s]
07:42:15  14:42:15 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email
07:42:15  14:42:15 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email
07:42:16  14:42:16 | unapplying masking policy to model  : DEV_ENTECHLOG_DEMO_DB.PII.MP_ENCRYPT_PII on DEV_ENTECHLOG_DEMO_DB.pii.ods_customers.email
07:42:18  4 of 5 OK snapshotted DEV_ENTECHLOG_DEMO_DB.pii.ods_customers................... [SUCCESS 1 in 12.48s]
07:42:18  14:42:18 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_ENCRYPT_PII on DBT_SNOW_MASK.integration_tests_dat.stg_customer.email
07:42:18  14:42:18 | unapplying masking policy to model  : DBT_SNOW_MASK.INTEGRATION_TESTS_DAT.MP_EMAIL on DBT_SNOW_MASK.integration_tests_dat.stg_contacts.email
07:42:19  14:42:19 | unapplying masking policy to model  : DEV_ENTECHLOG_DEMO_DB.PII.MP_ENCRYPT_PII on DEV_ENTECHLOG_DEMO_DB.pii.ods_customers.email
07:42:20  5 of 5 OK created view model integration_tests_dat.stg_contacts................. [SUCCESS 1 in 7.80s]
07:42:20  
07:42:20  Running 1 on-run-end hook
07:42:23  14:42:23 | unapplying masking policy to source : DBT_SNOW_MASK.SEEDS.MP_ENCRYPT_PII on DBT_SNOW_MASK.seeds.customer.first_name [force = True]
07:42:24  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-end.0................. [RUN]
07:42:24  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-end.0.................... [OK in 0.00s]
07:42:24  
07:42:25  
07:42:25  Finished running 2 seeds, 2 view models, 1 snapshot, 2 hooks in 32.22s.
07:42:25  
07:42:25  Completed successfully
07:42:25

entechlog commented 2 years ago

Thank You for the test details, I will go ahead and merge it to develop and create a release candidate

entechlog commented 2 years ago

Here is the RC, will wait for a day and then will do the production release

https://github.com/entechlog/dbt-snow-mask/releases/tag/0.2.3rc1

entechlog commented 2 years ago

This has been release now https://github.com/entechlog/dbt-snow-mask/releases/tag/0.2.3, Should reflect in dbt hub in a couple of hours

Thanks for the contribution and closing the issue now

entechlog / dbt-snow-mask

Apply masking policy with FORCE keyword #46