Dynamic Data Masking¶

Group 2 - Part 2

Dynamic data masking is a Snowflake feature, not a dbt one - dbt's job is to help you apply and manage masking policies as part of your deployment pipeline, not to mask data itself. Knowing where that line sits is the real skill here.

Exercise¶

stg_news__authors (and the dim_authors mart built on top of it) carries a genuine email column all the way through - a realistic candidate for masking.

Step 1 - Understand the feature, not the syntax¶

Step complete

Read up on Snowflake's Dynamic Data Masking. What is a masking policy attached to (a column, a table, a schema)? What decides whether a given query sees the real value or a masked one?

Hint: Where masking policies live

A masking policy is a schema-level object in Snowflake - it has to be created in a database/schema before it can be attached to a column. See Understanding Dynamic Data Masking.

Step 2 - Design a policy for `email`¶

Step complete

In prose, describe what a masking policy on stg_news__authors.email (or dim_authors.email) should do. Which role(s) should see the real address? What should everyone else see - fully redacted, partially masked (e.g. j***@newsnow.com), or something else?

Whose decision is this?

This is a data governance call, not a technical one - dbt and Snowflake will happily implement whatever rule you give them. Who in a real organisation would own this decision?

Step 3 - Work out where the policy gets applied¶

Step complete

dbt doesn't have a native "masking" feature. Two real options exist: the grants resource-config, or a post-hook. Which one is actually meant for this job?

Hint: grants vs. post-hook

dbt's grants config manages standard object privileges (select, insert, etc.) - it does not cover masking policies. The post-hook docs are explicit that hooks are for things you can't do with grants, which is exactly where masking policies fall. That's your answer, but explain why in your own words.

Step 4 - Build the macro¶

Step complete

Create a new macro file, mediapulse_base/macros/apply_email_mask.sql, that wraps the masking-policy DDL in a statement() block (this is a DDL operation with nothing to fetch, so statement() is the right tool - not run_query(), which is meant for queries you need results back from). The macro should apply your Step 2 masking rule to a column you pass in as an argument, so it isn't hardcoded to email alone.

Hint: statement() blocks

A macro that only builds a SQL string and doesn't wrap it in statement() (or pass it to run_query()) never actually executes against the warehouse - dbt just returns it as text. See Hooks and operations and the run_query reference for how the two compare and when each is appropriate.

Step 5 - Wire it up¶

Step complete

Add your macro as a post-hook on stg_news__authors - either inline via config() in stg_news__authors.sql, or via the config: block in _news__models.yml. Either is valid dbt; pick one and be able to justify it.

Hint: A real starting point

You don't have to invent this from scratch - dbt-snow-mask is an existing community package built specifically for tagging columns and applying Snowflake masking policies from within a dbt project. Skim its approach for ideas, even if you don't install it: dbt-snow-mask on GitHub.

Deliverable: The apply_email_mask.sql macro, the post-hook config wiring it to stg_news__authors, and a short written note on the masking rule and exempt role(s) it implements. (Applying it for real requires the masking policy to already exist in Snowflake - out of scope here; the deliverable is the correctly-shaped dbt artifact.)

Extension¶

Data doesn't stay in one place. email starts in stg_news__authors (a view) and flows into dim_authors (a table) - and conceptually could be exposed further downstream still.

Step 1 - Trace the masking requirement through the layers¶

Step complete

Snowflake masking policies attach to a physical column on a physical table or view. Since stg_news__authors and dim_authors are two separate physical objects (dbt rebuilds dim_authors as its own table), does applying a masking policy once "upstream" protect the column everywhere it's copied to, or does the policy need to be reapplied at every materialized layer that carries the column?

Hint: Objects, not lineage

Masking policies protect a column on a specific database object - Snowflake has no concept of "this data came from a masked source, so mask it here too." Confirm this by re-reading how policies are set on table and view columns, and think through what that means for dim_authors specifically.

Step 2 - Prove it by reapplying at the second layer¶

Step complete

Add the same apply_email_mask post-hook to dim_authors (which also carries email and materializes as its own physical table). This is the concrete proof of your Step 1 answer: if masking really doesn't propagate automatically, dim_authors needs its own hook, not a copy of stg_news__authors's.

Hint: Reuse, don't duplicate the macro

Call the same apply_email_mask macro from dim_authors's config rather than writing a second macro - that's the point of having built it as a reusable, parameterised macro rather than a one-off script.

Step 3 - Consider the failure mode¶

Step complete

What happens to your masking guarantee if someone adds a new mart that selects email from dim_authors and forgets to apply the policy to the new physical table? Is there a way to catch this in CI, or is this fundamentally a code-review/process problem rather than a tooling one? Be honest about the limits of automation here.

Deliverable: The post-hook added to dim_authors, plus a short write-up of your propagation rule (reapply-per-model vs. a shared macro/convention) and one concrete idea for catching a missed masking policy before it reaches production.

Done?

You've built a real, reusable masking macro and applied it at two physical layers, and you know exactly where Snowflake's job ends and dbt's begins.

Now head to Deployment & CI/CD to design a pipeline for a producer/consumer project pair.