Snowflake Data Masking: Static vs Dynamic - Satori

Snowflake Data Masking: Static vs Dynamic

In today’s ever-evolving digital age, data masking is one of the most essential features of data security and privacy. It’s the act of redacting and obfuscating sensitive information that’s being shared internally or externally to maintain optimal functionality while reducing the risk of exposing sensitive data within an organization. Overall, administrators are able to seamlessly allow access to some users, and restrict it for others to maintain security control and reduce risk.

Motivations Behind Data Masking

There are several reasons why someone would want to opt for data masking. In many cases, there is more than one type of motivation behind a data masking project. Some of those underlying reasons include:

How Do You Mask Data In Snowflake?

When you have a Snowflake data warehouse, it is common for organizations to require anonymized data, as some data consumers only need partial access to the data. For instance, let’s consider a typical healthcare organization using Snowflake as its data warehouse. If we have a table with the treatments provided to patients, we may have the following data consumers:

With that being said, there are several ways to do that in Snowflake, and we’ll review each one in detail:

Snowflake Dynamic Data Masking

For starters, let’s approach this with a relatively new way to mask data in Snowflake, which is the Dynamic Data Masking feature (available for the Enterprise plan). Dynamic Data Masking allows you to set data masking policies, and apply them on certain columns.

When setting dynamic data masking in Snowflake, you are defining masking policies, which may give different results for columns generally based on the user’s role (in most cases). For example, a policy can be:

CREATE OR REPLACE MASKING POLICY phone_masking AS (val string)
RETURNS string ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN_TEAM', 'ACCOUNTING_TEAM') THEN val
        ELSE '[REDACTED]'
    END;

From here, you can apply the dynamic masking policies on any column:

ALTER TABLE customers MODIFY COLUMN home_phone SET MASKING POLICY phone_masking;
ALTER TABLE customers MODIFY COLUMN work_phone SET MASKING POLICY phone_masking;

For some more support, think of dynamic masking as an abstraction of Snowflake Secure Views, which creates a more reusable way to apply policies. This is all an effort that makes them easier to manage and scale.

Snowflake Data Masking Using Views

Now, let’s say that you don’t have an enterprise Snowflake account for some reason or another. If this is the case for you, or maybe you have other logic you want to include in the same abstraction layer, you still have options here. For instance, you can write your own custom dynamic data masking logic within views. As an example, let’s say that we have the following table:

CREATE TABLE customers (id int, name text);
INSERT INTO customers VALUES (1, 'Ben'), (2, 'Karl');

If you want to apply dynamic data masking that will give your ACCOUNTING team full read access, your ANALYST team hashed data for statistical purposes, and others redacted data, you can create a view abstract, such as:

CREATE SECURE VIEW v_customers AS
SELECT id, (
    CASE
        WHEN CURRENT_ROLE() IN ('ACCOUNTING') THEN name
        WHEN CURRENT_ROLE() IN ('ANALYST') THEN sha2(name)
        ELSE '[REDACTED]'
    END
) AS name FROM customers;

By revoking access from the underlying asset (customers) and granting access to the view (v_customers), users will now have data masking per their roles and can only retrieve data based on the commands in place.

USE ROLE ACCOUNTING;
SELECT * FROM v_customers;
// returns Ben, Karl
USE ROLE ANALYST;
SELECT * FROM v_customers;
// returns hashed values
USE ROLE OTHER;
SELECT * FROM v_customers;
// returns redacted values

Snowflake Static Masking

Another option for masking data in Snowflake is to statically mask the data per use-case. That means if we have a customers table, and we have several data access scenarios, we generate a version for each use-case. We can do this by creating new objects that contain the data anonymized per use-case, such as:

We perform these data transformations as ETLs, and the main advantage of doing so is creating a very clear separation between the different roles, and the data they may view. You can also take a hybrid approach by building a view for each role, if that aligns with your terms of maintainability.

Snowflake data masking options comparison

Satori Universal Masking

As a final note, this comprehensive overview would not be complete without mentioning the Universal Masking feature for Snowflake. This is a feature that can be used when your Snowflake is integrated with Satori. In summary, Universal Masking takes a further step into ease of management by allowing you to set complete masking profiles in one central location, and the freedom to apply these masking profiles globally:

Simple masking conditions:

More granular masking conditions: