Why governance ensures data trust at scale in Snowflake - See It in Action

Why governance ensures data trust at scale

📖 Scenario: You are working in a company that uses Snowflake to store and manage data. The company wants to make sure that all data is accurate, secure, and easy to find for everyone who needs it. This is important because many teams use the data to make decisions, and they must trust it.To do this, the company needs to set up data governance. This means creating rules and controls that manage who can see and change data, how data is organized, and how to check data quality.

🎯 Goal: Build a simple Snowflake setup that shows how data governance helps keep data trustworthy. You will create a table with data, add a role to control access, and set up a policy to protect sensitive information.

📋 What You'll Learn

Create a Snowflake table with sample data

Create a role to manage access to the table

Grant the role SELECT permission on the table

Create a masking policy to protect sensitive data

Apply the masking policy to a column in the table

💡 Why This Matters

🌍 Real World

Companies use data governance in Snowflake to make sure data is accurate, secure, and only seen by the right people. This helps teams trust the data when making decisions.

💼 Career

Understanding how to create roles, grant permissions, and apply masking policies in Snowflake is important for data engineers, cloud architects, and security professionals to maintain data trust at scale.

1

Create a Snowflake table with sample data

Write a Snowflake SQL command to create a table called customer_data with columns customer_id (integer), customer_name (string), and email (string). Then insert these exact rows: (1, 'Alice', '[e-mail]'), (2, 'Bob', '[e-mail]'), (3, 'Charlie', '[e-mail]').

Snowflake

-- Your code here to create the table and insert data

Hint

Use CREATE OR REPLACE TABLE to create the table and INSERT INTO to add rows.

2

Create a role to manage access

Write a Snowflake SQL command to create a role called data_viewer that will be used to control who can see the customer_data table.

Snowflake

CREATE OR REPLACE TABLE customer_data (
    customer_id INT,
    customer_name STRING,
    email STRING
);

INSERT INTO customer_data (customer_id, customer_name, email) VALUES
    (1, 'Alice', '[e-mail]'),
    (2, 'Bob', '[e-mail]'),
    (3, 'Charlie', '[e-mail]');

-- Your code here to create the role

Hint

Use CREATE ROLE data_viewer; to create the role.

3

Grant the role SELECT permission on the table

Write a Snowflake SQL command to grant the data_viewer role the SELECT permission on the customer_data table.

Snowflake

CREATE OR REPLACE TABLE customer_data (
    customer_id INT,
    customer_name STRING,
    email STRING
);

INSERT INTO customer_data (customer_id, customer_name, email) VALUES
    (1, 'Alice', '[e-mail]'),
    (2, 'Bob', '[e-mail]'),
    (3, 'Charlie', '[e-mail]');

CREATE ROLE data_viewer;

-- Your code here to grant SELECT permission

Hint

Use GRANT SELECT ON TABLE customer_data TO ROLE data_viewer; to give read access.

4

Create and apply a masking policy to protect sensitive data

Write Snowflake SQL commands to create a masking policy called email_masking_policy that shows the full email only to users with the data_viewer role and masks it as '****@****.com' for others. Then apply this masking policy to the email column of the customer_data table.

Snowflake

CREATE OR REPLACE TABLE customer_data (
    customer_id INT,
    customer_name STRING,
    email STRING
);

INSERT INTO customer_data (customer_id, customer_name, email) VALUES
    (1, 'Alice', '[e-mail]'),
    (2, 'Bob', '[e-mail]'),
    (3, 'Charlie', '[e-mail]');

CREATE ROLE data_viewer;

GRANT SELECT ON TABLE customer_data TO ROLE data_viewer;

-- Your code here to create and apply the masking policy

Hint

Use CREATE MASKING POLICY with CURRENT_ROLE() to check roles, then ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY to apply it.

Practice

1. What is the main purpose of data governance in Snowflake?

easy

A. To set rules that keep data safe and reliable

B. To speed up data loading processes

C. To create more storage space automatically

D. To delete old data without backup

Solution

1. Step 1: Understand the role of governance

   Governance is about setting rules and policies to protect and manage data.

2. Step 2: Identify the main goal

   The main goal is to keep data safe and reliable for users.

3. Final Answer:

   To set rules that keep data safe and reliable -> Option A

4. Quick Check:

   Governance = Data safety and reliability [OK]

Hint: Governance means setting rules for safe, trusted data [OK]

Common Mistakes:

• Confusing governance with performance tuning
• Thinking governance deletes data automatically
• Assuming governance creates storage space

2. Which Snowflake feature helps control who can see sensitive data?

easy

A. Data unloading

B. Warehouse scaling

C. Automatic clustering

D. Data masking policies

Solution

1. Step 1: Identify features related to data access

   Data masking policies hide sensitive data from unauthorized users.

2. Step 2: Exclude unrelated features

   Warehouse scaling, clustering, and unloading do not control data visibility.

3. Final Answer:

   Data masking policies -> Option D

4. Quick Check:

   Masking = Control data visibility [OK]

Hint: Masking policies hide sensitive data from unauthorized users [OK]

Common Mistakes:

• Confusing warehouse scaling with data access control
• Thinking clustering affects data visibility
• Assuming unloading controls data masking

3. Given this Snowflake SQL snippet:

CREATE MASKING POLICY ssn_mask AS (val STRING) RETURNS STRING ->
CASE WHEN CURRENT_ROLE() IN ('HR_ROLE') THEN val ELSE 'XXX-XX-XXXX' END;

What will a user with role 'SALES_ROLE' see when querying the SSN column?

medium

A. 'XXX-XX-XXXX' as masked value

B. The actual SSN value

C. An error message

D. NULL value

Solution

1. Step 1: Understand the masking policy logic

   The policy shows the real value only if the role is 'HR_ROLE'. Otherwise, it shows 'XXX-XX-XXXX'.

2. Step 2: Apply the policy for 'SALES_ROLE'

   Since 'SALES_ROLE' is not 'HR_ROLE', the user sees the masked value 'XXX-XX-XXXX'.

3. Final Answer:

   'XXX-XX-XXXX' as masked value -> Option A

4. Quick Check:

   Role not HR_ROLE = masked value [OK]

Hint: Masking shows real data only for allowed roles [OK]

Common Mistakes:

• Assuming all roles see real data
• Expecting an error instead of masked value
• Thinking masked value is NULL

4. You created a masking policy but users still see sensitive data. What is the likely cause?

medium

A. The warehouse size is too small

B. The masking policy syntax is incorrect

C. Users have roles that bypass the masking policy

D. Data is stored in an external stage

Solution

1. Step 1: Check role permissions

   Users with roles allowed by the masking policy see real data, bypassing masking.

2. Step 2: Exclude unrelated causes

   Incorrect syntax causes errors, not visible data; warehouse size and external stage do not affect masking.

3. Final Answer:

   Users have roles that bypass the masking policy -> Option C

4. Quick Check:

   Allowed roles see real data [OK]

Hint: Check user roles that bypass masking policies [OK]

Common Mistakes:

• Blaming warehouse size for masking issues
• Ignoring role permissions in masking
• Assuming external stage affects masking

5. How does combining role-based access control (RBAC) with masking policies help maintain data trust at scale in Snowflake?

hard

A. It speeds up query performance by caching data

B. It ensures only authorized users see sensitive data, while others see masked data

C. It automatically deletes data after a set time

D. It allows all users to see all data for transparency

Solution

1. Step 1: Understand RBAC and masking roles

   RBAC assigns roles to users controlling data access; masking policies hide sensitive data based on roles.

2. Step 2: Combine effects for trust

   Only authorized roles see real data; others see masked data, ensuring trust and security at scale.

3. Final Answer:

   It ensures only authorized users see sensitive data, while others see masked data -> Option B

4. Quick Check:

   RBAC + masking = trusted data access [OK]

Hint: RBAC plus masking controls who sees real vs masked data [OK]

Common Mistakes:

• Thinking governance deletes data automatically
• Confusing masking with performance features
• Assuming all users see all data