add for loop thru nodes to apply_masking_policy_list_for_models.sql

[melissa-ellevation]

Fix for issue 66. As stated in the issue comment, all this does is add a for loop after line 5 of apply_masking_policy_list_for_models.sql to fix the issue. I also updated the indentation to align with the new for statement, which made this PR look unfortunately heinous. Please forgive me if there was a way to avoid that :/

Ran the integration tests provided and create, apply, and unapply were successful for sources, models, and snapshots:

• [x] Create policy for Source
• [x] Apply policy to Source
• [x] Unapply policy to Source
• [x] Create policy for Model
• [x] Apply policy to Model
• [x] Unapply policy to Model
• [x] Create policy for Snapshot
• [x] Apply policy to Snapshot
• [x] Unapply policy to Snapshot

Here is the dbt-project.yml used:

name: 'dbt_snow_mask_integration_tests'
version: '1.0'
config-version: 2

profile: 'dbt-snow-utils-integration-tests'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"
clean-targets:
    - "target"
    - "dbt_packages"
    - "logs"

#vars:
#  use_force_applying_masking_policy: "True"
#  use_common_masking_policy_db: "True"
#  common_masking_policy_db: "DEMO_DB"
#  common_masking_policy_schema: "COMPLIANCE"
#  create_masking_policy_schema: "False"

#   use_common_masking_policy_schema_only: "True"
#   common_masking_policy_schema: "COMPLIANCE"

seeds:
  dbt_snow_mask_integration_tests:
    +schema: seeds

models:
  pre-hook:
    - "{{ dbt_snow_mask.create_masking_policy('models')}}"
  post-hook: 
    - "{{ dbt_snow_mask.apply_masking_policy('models') }}"
    - "{{ dbt_snow_mask.unapply_masking_policy('models') }}"

snapshots:
  pre-hook:
    - "{{ dbt_snow_mask.create_masking_policy('snapshots')}}"
  post-hook:
    - "{{ dbt_snow_mask.apply_masking_policy('snapshots') }}"
    - "{{ dbt_snow_mask.unapply_masking_policy('snapshots') }}"

  dbt_snow_mask_integration_tests:
    pii:
      database: "DBT_SNOW_MASK_TEST"
      schema: TEST

on-run-start:
  - "{{ dbt_snow_mask.create_masking_policy('sources')}}"

on-run-end:
  - "{{ dbt_snow_mask.apply_masking_policy('sources')}}"
  - "{{ dbt_snow_mask.unapply_masking_policy('sources')}}"

Here is the console output for dbt snapshot :

(dbt-warehouse) <user> integration_tests % dbt snapshot
20:10:00  Running with dbt=1.5.2
20:10:00  Registered adapter: snowflake=1.5.2
20:10:00  Unable to do partial parsing because a project config has changed
20:10:02  Found 2 models, 0 tests, 1 snapshot, 0 analyses, 552 macros, 3 operations, 2 seed files, 1 source, 0 exposures, 0 metrics, 0 groups
20:10:02  
20:10:05  
20:10:05  Running 1 on-run-start hook
20:10:06  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-start.0 .............. [RUN]
20:10:06  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-start.0 ................. [OK in 0.00s]
20:10:06  
20:10:06  Concurrency: 1 threads (target='dev')
20:10:06  
20:10:06  1 of 1 START snapshot pii.ods_customers ........................................ [RUN]
20:10:12  16:10:12 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.TEST.stg_customer.email [force = False]
20:10:12  16:10:12 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_CONDITIONAL_CONTACTS_PII on DBT_SNOW_MASK_TEST.TEST.stg_contacts.first_name [force = False]
20:10:13  16:10:13 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_EMAIL on DBT_SNOW_MASK_TEST.TEST.stg_contacts.email [force = False]
20:10:13  16:10:13 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_IP on DBT_SNOW_MASK_TEST.TEST.stg_contacts.ip_address [force = False]
20:10:14  16:10:14 | applying masking policy to model  : DBT_SNOW_MASK_TEST.PII.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.pii.ods_customers.email [force = False]
20:10:14  16:10:14 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.TEST.stg_customer.email
20:10:14  16:10:14 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_CONDITIONAL_CONTACTS_PII on DBT_SNOW_MASK_TEST.TEST.stg_contacts.first_name
20:10:14  16:10:14 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_EMAIL on DBT_SNOW_MASK_TEST.TEST.stg_contacts.email
20:10:14  16:10:14 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_IP on DBT_SNOW_MASK_TEST.TEST.stg_contacts.ip_address
20:10:14  16:10:14 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.PII.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.pii.ods_customers.email
20:10:15  1 of 1 OK snapshotted pii.ods_customers ........................................ [success in 9.04s]
20:10:15  
20:10:15  Running 2 on-run-end hooks
20:10:16  16:10:16 | applying masking policy to source : DBT_SNOW_MASK_TEST.SEEDS.MP_CONDITIONAL_CUSTOMER_PII on DBT_SNOW_MASK_TEST.seeds.customer.first_name [force = False]
20:10:16  1 of 2 START hook: dbt_snow_mask_integration_tests.on-run-end.0 ................ [RUN]
20:10:16  1 of 2 OK hook: dbt_snow_mask_integration_tests.on-run-end.0 ................... [OK in 0.00s]
20:10:17  16:10:17 | unapplying masking policy to source : DBT_SNOW_MASK_TEST.SEEDS.MP_CONDITIONAL_CUSTOMER_PII on DBT_SNOW_MASK_TEST.seeds.customer.first_name [force = False]
20:10:17  2 of 2 START hook: dbt_snow_mask_integration_tests.on-run-end.1 ................ [RUN]
20:10:17  2 of 2 OK hook: dbt_snow_mask_integration_tests.on-run-end.1 ................... [OK in 0.00s]
20:10:17  
20:10:17  
20:10:17  Finished running 1 snapshot, 3 hooks in 0 hours 0 minutes and 15.35 seconds (15.35s).
20:10:17  
20:10:17  Completed successfully
20:10:17  
20:10:17  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

here is the console output for dbt run

(dbt-warehouse) <user> integration_tests % dbt run
20:11:55  Running with dbt=1.5.2
20:11:55  Registered adapter: snowflake=1.5.2
20:11:55  Found 2 models, 0 tests, 1 snapshot, 0 analyses, 552 macros, 3 operations, 2 seed files, 1 source, 0 exposures, 0 metrics, 0 groups
20:11:55  
20:11:59  
20:11:59  Running 1 on-run-start hook
20:11:59  1 of 1 START hook: dbt_snow_mask_integration_tests.on-run-start.0 .............. [RUN]
20:11:59  1 of 1 OK hook: dbt_snow_mask_integration_tests.on-run-start.0 ................. [OK in 0.00s]
20:11:59  
20:11:59  Concurrency: 1 threads (target='dev')
20:11:59  
20:11:59  1 of 2 START sql view model TEST.stg_contacts .................................. [RUN]
20:12:02  16:12:02 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.TEST.stg_customer.email [force = False]
20:12:03  16:12:03 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_CONDITIONAL_CONTACTS_PII on DBT_SNOW_MASK_TEST.TEST.stg_contacts.first_name [force = False]
20:12:03  16:12:03 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_EMAIL on DBT_SNOW_MASK_TEST.TEST.stg_contacts.email [force = False]
20:12:03  16:12:03 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_IP on DBT_SNOW_MASK_TEST.TEST.stg_contacts.ip_address [force = False]
20:12:04  16:12:04 | applying masking policy to model  : DBT_SNOW_MASK_TEST.PII.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.pii.ods_customers.email [force = False]
20:12:04  16:12:04 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.TEST.stg_customer.email
20:12:05  16:12:05 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_CONDITIONAL_CONTACTS_PII on DBT_SNOW_MASK_TEST.TEST.stg_contacts.first_name
20:12:05  16:12:05 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_EMAIL on DBT_SNOW_MASK_TEST.TEST.stg_contacts.email
20:12:05  16:12:05 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_IP on DBT_SNOW_MASK_TEST.TEST.stg_contacts.ip_address
20:12:05  16:12:05 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.PII.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.pii.ods_customers.email
20:12:06  16:12:06 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.TEST.stg_customer.email [force = False]
20:12:06  16:12:06 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_CONDITIONAL_CONTACTS_PII on DBT_SNOW_MASK_TEST.TEST.stg_contacts.first_name [force = False]
20:12:06  16:12:06 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_EMAIL on DBT_SNOW_MASK_TEST.TEST.stg_contacts.email [force = False]
20:12:06  16:12:06 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_IP on DBT_SNOW_MASK_TEST.TEST.stg_contacts.ip_address [force = False]
20:12:07  16:12:07 | applying masking policy to model  : DBT_SNOW_MASK_TEST.PII.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.pii.ods_customers.email [force = False]
20:12:08  1 of 2 OK created sql view model TEST.stg_contacts ............................. [SUCCESS 1 in 8.11s]
20:12:08  2 of 2 START sql view model TEST.stg_customer .................................. [RUN]
20:12:10  16:12:10 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.TEST.stg_customer.email [force = False]
20:12:11  16:12:11 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_CONDITIONAL_CONTACTS_PII on DBT_SNOW_MASK_TEST.TEST.stg_contacts.first_name [force = False]
20:12:11  16:12:11 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_EMAIL on DBT_SNOW_MASK_TEST.TEST.stg_contacts.email [force = False]
20:12:12  16:12:12 | applying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_IP on DBT_SNOW_MASK_TEST.TEST.stg_contacts.ip_address [force = False]
20:12:12  16:12:12 | applying masking policy to model  : DBT_SNOW_MASK_TEST.PII.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.pii.ods_customers.email [force = False]
20:12:12  16:12:12 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.TEST.stg_customer.email
20:12:13  16:12:13 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_CONDITIONAL_CONTACTS_PII on DBT_SNOW_MASK_TEST.TEST.stg_contacts.first_name
20:12:13  16:12:13 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_EMAIL on DBT_SNOW_MASK_TEST.TEST.stg_contacts.email
20:12:13  16:12:13 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.TEST.MP_IP on DBT_SNOW_MASK_TEST.TEST.stg_contacts.ip_address
20:12:13  16:12:13 | unapplying masking policy to model  : DBT_SNOW_MASK_TEST.PII.MP_ENCRYPT_PII on DBT_SNOW_MASK_TEST.pii.ods_customers.email
20:12:13  2 of 2 OK created sql view model TEST.stg_customer ............................. [SUCCESS 1 in 5.54s]
20:12:13  
20:12:13  Running 2 on-run-end hooks
20:12:14  16:12:14 | applying masking policy to source : DBT_SNOW_MASK_TEST.SEEDS.MP_CONDITIONAL_CUSTOMER_PII on DBT_SNOW_MASK_TEST.seeds.customer.first_name [force = False]
20:12:14  1 of 2 START hook: dbt_snow_mask_integration_tests.on-run-end.0 ................ [RUN]
20:12:14  1 of 2 OK hook: dbt_snow_mask_integration_tests.on-run-end.0 ................... [OK in 0.00s]
20:12:15  16:12:15 | unapplying masking policy to source : DBT_SNOW_MASK_TEST.SEEDS.MP_CONDITIONAL_CUSTOMER_PII on DBT_SNOW_MASK_TEST.seeds.customer.first_name [force = False]
20:12:15  2 of 2 START hook: dbt_snow_mask_integration_tests.on-run-end.1 ................ [RUN]
20:12:15  2 of 2 OK hook: dbt_snow_mask_integration_tests.on-run-end.1 ................... [OK in 0.00s]
20:12:15  
20:12:15  
20:12:15  Finished running 2 view models, 3 hooks in 0 hours 0 minutes and 19.95 seconds (19.95s).
20:12:15  
20:12:15  Completed successfully
20:12:15  
20:12:15  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

Notes:

• In the dbt run console output, the masking policies are applied, unapplied, and then applied again after stg_contacts is built because it has a custom config posthook.
• running apply_masking_policy('models') or apply_masking_policy('snapshots') yield the same result (same for create), but this seems to be how the code was already written, so no functionality has changed there.

Attached is a screenshot of the test db structure created using your integration material:

Please let me know if further testing is needed or if I can answer any questions. Thanks!

[melissa-ellevation]

show masking policies

2023-06-27 4_17pm.csv