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.