Skip to content

Dynamic Data Masking

As with Group 2's version of this topic: dynamic data masking is a Snowflake feature that dbt helps orchestrate, not something dbt implements natively. This page goes further, into what masking means once two dbt projects are in a mesh together.

Worth noting up front: the new CRM domain in mediapulse_analytics (stg_crm__advertiser_accounts, stg_crm__sales_reps, stg_crm__contracts, stg_crm__touchpoints) doesn't actually contain anything masking-shaped - it's business data (industry, contract tier, contract value, touchpoint notes), not personal data. So this exercise uses the one genuine PII column in the project: stg_news__authors.email in mediapulse_base, which is access: public and therefore visible to mediapulse_analytics across the mesh boundary.

Exercise

Step 1 - Confirm the propagation behaviour

  • Step complete

A masking policy in Snowflake attaches to a column on a specific table or view - it isn't a dbt-level or lineage-level concept. If mediapulse_base applied a masking policy to stg_news__authors.email, and a hypothetical mediapulse_analytics model later did ref('stg_news__authors') and selected email into a new physical table, would the policy automatically apply to that new table too?

Hint: Read this carefully

No - re-read Using Dynamic Data Masking. A masking policy protects the specific object it's attached to. A downstream model that materializes its own physical table needs its own policy (or needs to be built in a way that avoids ever exposing the raw column in a new physical object).

Step 2 - Decide where masking should live in a mesh

  • Step complete

Given that answer, would you rather apply the masking policy once, upstream, close to where the sensitive column is first staged (in mediapulse_base) - or leave it to every downstream consumer (mediapulse_analytics, and any future third project) to reapply it themselves if they happen to select that column? Argue for one approach.

What does 'access: public' actually promise?

mediapulse_base's staging layer is access: public specifically so other projects can ref() it. Does making a model public also imply an obligation to protect any sensitive columns it exposes, before granting that access? Who should own that responsibility - the producer project or every consumer?

Step 3 - Audit the real project for exposure

  • Step complete

email is currently selected in stg_news__authors and again in dim_authors, both in mediapulse_base. Confirm no mediapulse_analytics model currently selects it (a quick search of mediapulse_analytics/models for email or author will tell you). Would your masking design change today, given that it isn't actually being consumed cross-project yet - or would you apply it proactively?

Step 4 - Build it upstream

  • Step complete

Per your Step 2 answer, the policy belongs in the producer project. Create mediapulse_base/macros/apply_email_mask.sql, a macro that wraps the masking-policy DDL in a statement() block (this is a DDL operation - statement() is correct here, not run_query(), which is for fetching query results back into Jinja) and takes the target column as an argument. Wire it up as a post-hook on both stg_news__authors and dim_authors (both are separate physical objects in mediapulse_base and each needs its own hook - masking doesn't propagate through lineage).

Hint: statement() vs run_query()

A macro that only builds SQL text without a statement() block or run_query() call never actually executes - dbt just returns it as a string. See Hooks and operations and the run_query reference for how the two compare.

Deliverable: The apply_email_mask.sql macro, its post-hook wiring on both stg_news__authors and dim_authors, and a short written policy on why it belongs upstream in mediapulse_base rather than being left to mediapulse_analytics (or a future third project) to reapply.

Extension

Step 1 - Design conditional, role-based masking

  • Step complete

Rather than a single mask-for-everyone-except-one-role policy, design a masking policy with at least three tiers of visibility (e.g. full value for a TRANSFORMER-equivalent role, a partially masked value for a general analytics role, and fully redacted for anyone else). Describe the role hierarchy your policy would need to check.

Hint: Role-aware policies

Snowflake masking policies can branch on current_role() (or role hierarchy via is_role_in_session()), not just return one fixed masked value - see the conditional examples in Using Dynamic Data Masking.

Step 2 - Parameterise the macro for it

  • Step complete

Extend apply_email_mask (built in the Exercise) to accept an exempt-role argument, so the same macro can express "mask for everyone except the role passed in" rather than a hardcoded single role. Update the post-hook calls on stg_news__authors and dim_authors to pass the argument through.

Step 3 - Connect this to deployment

  • Step complete

If you completed this workshop's Deployment & CI/CD topic, think about where "apply/verify masking policy" would sit in a CI/CD pipeline for mediapulse_base. Should a PR that touches stg_news__authors be blocked if it removes the masking post-hook? Is there a dbt-native way to detect that, or would this need a custom CI check?

Be honest about the gap

Neither grants nor dbt's built-in testing framework verify that a masking policy is actually attached in the warehouse - that's a real gap. What would a singular test that queries Snowflake's own policy-reference metadata (rather than the model's data) look like, conceptually?

Step 4 - Reason about the two-project failure mode

  • Step complete

If mediapulse_base's masking policy is later removed or misconfigured (say, during a refactor of stg_news__authors), would mediapulse_analytics - or any other project depending on mediapulse_base's public models - have any way of knowing, short of a human noticing? What does this tell you about the limits of access modifiers (public/protected/private) as a governance tool on their own?

Deliverable: The parameterised apply_email_mask macro and its updated post-hook calls, plus a one-page design covering your role tiers, where you'd hook policy verification into CI (or an honest explanation of why you can't with today's tooling), and what governance gap remains even with the mesh's access levels in place.

Done?

You've extended a real masking macro to support role-based rules and named the exact gap left over once access levels, groups, and contracts have all done their job.

Now head to Unit testing to test conditional business logic directly, without touching the warehouse.