Why governance ensures data trust at scale in Snowflake - Why It Works

Introduction

When many people use data in a company, it can get confusing and risky. Governance helps keep data safe, correct, and easy to find, so everyone trusts it and uses it well.

When your company has many teams accessing the same data and you want to avoid mistakes or misuse.

When you need to make sure sensitive data is only seen by the right people.

When you want to track who changed data and when to solve problems quickly.

When you want to keep data organized so it is easy to find and use.

When you want to follow rules and laws about data privacy and security.

Config File - governance_policy.sql

governance_policy.sql

CREATE ROLE data_analyst;
CREATE ROLE data_engineer;

GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE data_analyst;
GRANT SELECT ON DATABASE sales_db TO ROLE data_analyst;

GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE data_engineer;
GRANT ALL PRIVILEGES ON DATABASE sales_db TO ROLE data_engineer;

CREATE MASKING POLICY ssn_masking_policy AS
  (VAL STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('DATA_ENGINEER') THEN VAL
    ELSE 'XXX-XX-XXXX'
  END;

ALTER TABLE sales_db.customers ALTER COLUMN ssn SET MASKING POLICY ssn_masking_policy;

This file sets up roles for different users: data_analyst and data_engineer.

It grants specific permissions to each role to control who can see or change data.

The masking policy hides sensitive data like social security numbers from unauthorized users.

This helps keep data safe and trustworthy by controlling access and protecting privacy.

Commands

This command runs the governance policy SQL file to create roles, grant permissions, and set data masking in Snowflake.

Terminal

snowsql -a myaccount -u admin -f governance_policy.sql

Expected Output

Connecting to Snowflake...

SQL execution complete.

Statements executed successfully.

→

-a - Specifies the Snowflake account to connect to.

→

-u - Specifies the user to connect as.

→

-f - Runs the SQL commands from the given file.

This command lets a data analyst try to see social security numbers. The masking policy hides the real numbers for this role.

Terminal

snowsql -a myaccount -u data_analyst -q "SELECT ssn FROM sales_db.customers LIMIT 3;"

Expected Output

XXX-XX-XXXX

XXX-XX-XXXX

XXX-XX-XXXX

→

-q - Runs the given SQL query directly.

This command lets a data engineer see the real social security numbers because their role has permission to view sensitive data.

Terminal

snowsql -a myaccount -u data_engineer -q "SELECT ssn FROM sales_db.customers LIMIT 3;"

Expected Output

123-45-6789

987-65-4321

555-55-5555

→

-q - Runs the given SQL query directly.

Key Concept

If you remember nothing else from this pattern, remember: governance controls who can see and change data, making data trustworthy and safe at scale.

Common Mistakes

Giving all users full access to all data without roles or masking.

This risks exposing sensitive data and causes confusion or mistakes.

Create roles with specific permissions and use masking policies to protect sensitive information.

Not testing data access with different roles after setting policies.

You might think data is protected but users could still see or change data they shouldn't.

Run queries as different roles to verify permissions and masking work as expected.

Summary

Create roles and assign permissions to control who can access or change data.

Use masking policies to hide sensitive data from unauthorized users.

Test data access with different roles to ensure governance rules work correctly.

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