---
title: Dynamic Data Masking in dbt
description: Recently, I have been experimenting with a dbt package called dbt_snow_mask and to implement Dynamic Data Masking in snowflake using dbt.
---

[DDBM Blog ](https://www.ddbm.com/en/blog)

# [Dynamic Data Masking in dbt](https://www.ddbm.com/en/blog/data-masking-in-dbt)

 Written by [Atzmon Ben Binyamin](https://www.ddbm.com/en/blog/author/atzmon-ben-binyamin) | 9 Jan, 2023

Dynamic Data Masking is a Column-level Snowflake Security feature that uses masking policies to selectively mask data in table and view columns at query time. Currently, Snowflake supports Dynamic Data Masking on tables and views.

dbt is a development framework on top of snowflake that allows me to build modular SQL data pipelines and deploy code using dev/prod environments. It allows git version control, documentation and much much more. Check dbt [documentation](https://docs.getdbt.com/) to learn more about it!

Recently, I have been experimenting with a dbt package called `dbt_snow_mask` to implement Dynamic Data Masking in snowflake using only dbt.

## Let’s not re-invent the wheel…

One of the benefits of using dbt is the robust package ecosystem. A dbt package is additional Jinja and SQL code that can be added to a project, for additional functionality. It is as simple as importing a package in python.

One such package is `dbt_snow_mask` which contains macros that can be used across our project. The package will help us to apply **Dynamic Data Masking** using [`dbt meta`](https://docs.getdbt.com/reference/resource-properties/meta). The package use [`dbt_utils`](https://hub.getdbt.com/dbt-labs/dbt_utils/latest/) as a dependency so make sure to attach it as well.

Adding the package into our project is quite simple, and we’ll do it by pasting the ‘package info’ to our `packages.yml` file:

```
packages:
  - package: dbt-labs/dbt_utils
    version: 1.0.0
  - package: entechlog/dbt_snow_mask
    version: 0.2.3
```

Execute `dbt deps` to install the packages.

## Dynamic Data Masking: **Create Masking Policy**

Under the `macro` folder, I created `snow-mask-ddl` folder with an sql file containing a macro:

```

```

The name of the masking policy created by this macro is .. . In my example we have `` as **temp**. The Jinja description at the beginning is necessary for dynamic database schema specification.

## Adding a ‘meta’ config to our models

Let’s add a `meta` config in the `models` related `.yml` file to define which columns the masking policy we created will be applied to. We will add it to `jaffle_shop.yml`, where we centralize our **jaffle_shop staging** configurations. Also, We will add it to our `core.yml`, where we centralize our **marts/core** configurations.

I Added the `meta` config under the right column for the model I want the masking to be applied. Here, under `stg_customers` we added the `meta` config to `first_name` and `last_name` columns.

An example of `jaffle_shop.yml` file I have in a project:

```
version: 2

sources:
  - name: jaffle_shop
    description: sources for jaffle_shop
    database: raw
    schema: jaffle_shop
    #loader: 
  
    #loaded_at_field: 
   
     #meta: {
    
     } #tags: [
     
      ] tables: - name: customers - name: orders loaded_at_field: _batched_at freshness: warn_after: {count: 12, period: hour} 
      models: - name: stg_customers description: Staged customer data from jaffle_shop app columns: - name: customer_id description: Primary key for customers tests: - unique - not_null - 
      name: first_name 
      meta: 
      masking_policy: temp - 
      name: last_name 
      meta: 
      masking_policy: temp - name: stg_orders description: Staged order data from jaffle_shop app columns: - name: order_id description: Primary key for orders tests: - unique - not_null - name: status description: '' tests: - accepted_values: values: ['shipped', 'completed', 'return_pending', 'returned', 'placed'] - name: customer_id tests: - relationships: to: ref('stg_customers') field: customer_id
     
    
   
  
```

An example of `core.yml` file, where we attach the `meta` config `first_name` and `last_name` columns in `dim_customers`.

```
version: 2

models:
  - name: dim_customers
    columns:
      - name: customer_id
        description: Primary key for customers
        tests:
          - unique
          - not_null
      - name: first_name
        meta:
          masking_policy: temp
      - name: last_name
        meta:
          masking_policy: temp
      - name: first_order_date
      - name: most_recent_order_date
      - name: number_of_orders
      - name: lifetime_value
  - name: fct_orders
    description: Staged order data from jaffle_shop app
    columns:
      - name: order_id
        description: Primary key for orders
        tests:
          - unique
          - not_null
      - name: customer_id
        #description:
      - name: order_date
      - name: amount
```

## Applying Dynamic Data Masking

To apply our created macro, we would like to set `pre_hook` and `post_hook` in our `dbt_project.yml` configuration and **create** and **apply** our masking policy when dbt runs. Let’s have a look at the models section in our `dbt_project.yml`:

```
models:
  jaffle_shop:
    # all models should have 'unique' and 'not_null' tests
    +required_tests: {"unique.*|not_null": 2}
    staging:
      +materialized: view
    marts:
      core:
        +materialized: table
    pre-hook: 
      - ""
    post-hook: 
      - ""
```

Alternatively, we can add a config to **each specific model**:

```

```

or using the command line:

```
dbt run-operation create_masking_policy --args '{"resource_type": "models"}'
```

Settings are now complete!

Let’s build everything with `dbt build`, and check our masking on snowflake..

## Snowflake

After creating a new role `rep1` with the right grants and permissions, we are ready to check our data masking. As a reminder, We are expecting the data to be masked for everyone except the `SYSADMIN` role:

```
CASE 
  WHEN CURRENT_ROLE() IN ('SYSADMIN') THEN val
  ELSE '***********'
END
```

let’s switch to `SYSADMIN` and check the sensitive tables:

```
use role SYSADMIN;
select * from stg_customers;
select * from dim_customers;
```

Both tables are not masked, as expected.

Checking the same tables, this time with `rep1` role:

```
use role rep1;
select * from stg_customers;
select * from dim_customers;
```

We are done! All tables behave as expected.

 

[View full post](https://www.ddbm.com/en/blog/data-masking-in-dbt)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Atzmon Ben Binyamin"
  },
  "dateModified" : "2026-02-11T08:31:25.935Z",
  "datePublished" : "2023-01-09T23:00:00Z",
  "headline" : "Dynamic Data Masking in dbt",
  "image" : {
    "@type" : "ImageObject",
    "height" : 192,
    "url" : "https://www.ddbm.com/hubfs/Imported_Blog_Media/Screenshot-2023-01-10-at-08_59_20-1-1.png",
    "width" : 904
  },
  "mainEntityOfPage" : "https://www.ddbm.com/en/blog/data-masking-in-dbt",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "DDBM Blog"
  }
}
```