Snowflake — Module 15

Advanced Security and Governance

Masking policies, row access policies, object tagging, classification, access history, and auditing.

80 min September 2026

Cost Optimization Data Sharing and Marketplace

// Part 01 — Plain-English foundation

Advanced Security and Governance From Scratch

Advanced governance controls who can see sensitive values, which rows they can access, how objects are tagged, and how access is audited.

Why this matters: A warehouse often contains customer PII, finance data, employee data, and product telemetry. Basic grants are not always enough because different users may need the same table with different visibility.

Mental model

RBAC controls the door to the room. Masking policies blur sensitive fields. Row access policies decide which rows appear. Tags and audit views tell you what exists and who touched it.

// Part 02 — Core concepts

The Concepts You Must Own

✓Masking policies hide or transform sensitive column values based on role.
✓Row access policies filter rows dynamically.
✓Tags classify objects for governance, ownership, and cost.
✓Access history helps audit who used which objects.
✓Governance should protect data while still enabling legitimate work.

Concept	Meaning	Why it matters
Masking policy	Column-level dynamic protection.	Hide sensitive values by role.
Row access policy	Row-level dynamic filter.	Tenant, region, or department isolation.
Tag	Metadata label.	Classification, ownership, lineage, and cost.
Access history	Usage audit trail.	Compliance and investigation.
Classification	Identify sensitive data.	Foundation before applying controls.

// Part 03 — How the work actually flows

Step-by-Step Workflow

✓Classify sensitive data such as email, SSN, phone, payment, and health fields.
✓Apply tags to important databases, schemas, tables, and columns.
✓Use masking policies for column-level protection.
✓Use row access policies for region, tenant, or department boundaries.
✓Audit usage through ACCOUNT_USAGE views and review exceptions.

Advanced Security and Governance example

CREATE MASKING POLICY EMAIL_MASK AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_FULL_ACCESS') THEN val
    ELSE REGEXP_REPLACE(val, '(^.).*(@.*$)', '\\1***\\2')
  END;

ALTER TABLE GOLD.CUSTOMERS
  MODIFY COLUMN email SET MASKING POLICY EMAIL_MASK;

CREATE ROW ACCESS POLICY REGION_POLICY AS (region STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'GLOBAL_ANALYST'
  OR region = CURRENT_ROLE();

ALTER TABLE GOLD.SALES
  ADD ROW ACCESS POLICY REGION_POLICY ON (region);

Do not read the example as magic syntax to memorize. Read it as a production habit: name the objects clearly, make assumptions visible, preserve enough metadata to debug later, and keep the business promise attached to the SQL.

// Part 04 — Mistakes and debugging

Common Mistakes That Break Snowflake Projects

Watch these carefully

✓Assuming table SELECT grants alone satisfy privacy requirements.
✓Masking data in one table but exposing the same value in another view.
✓Applying policies without testing BI and dbt service roles.
✓Creating governance rules with no documented owner.
✓Ignoring access history until an audit starts.

How to debug this topic

Start by asking what promise failed: freshness, correctness, access, speed, or cost. Then inspect the Snowflake evidence: query history, warehouse metering, task history, copy history, grants, row counts, and sample records. Good Snowflake debugging is not guessing. It is reading the platform metadata until the failure has a shape.

// Part 05 — Production depth

Production Notes

✓Prefer governed views or marts for broad consumption.
✓Test policies with representative roles, not only ACCOUNTADMIN.
✓Version-control policy definitions where possible.
✓Combine technical controls with data contracts and steward ownership.

Production standard: A Snowflake design is not complete when the query returns rows. It is complete when the team knows who owns it, how fresh it should be, how access is controlled, what it costs, how to detect failure, and how to recover safely.

// Part 06 — Interview and project readiness

Explain It Like a Professional

Advanced Snowflake governance includes RBAC, masking policies, row access policies, tags, classification, and access history. Explain that grants decide object access, masking controls sensitive columns, row policies filter records, and audit views prove usage.

Mini project

Secure a customer mart so finance sees full emails, support sees masked emails, regional managers see only their region, and auditors can review access history.

Questions you should answer out loud

✓How would you explain Advanced Security and Governance to a non-technical manager?
✓Which Snowflake objects, roles, or SQL statements does this topic use?
✓What can fail in production and which metadata view would you inspect first?
✓What is the cost or security risk if this is implemented carelessly?
✓How would you test that the result is correct and rerunnable?

🎯 Key Takeaways

✓RBAC is necessary but not always sufficient.
✓Masking policies protect sensitive columns.
✓Row access policies filter records dynamically.
✓Tags and access history make governance auditable.
✓Governance must be tested with real roles and workflows.

Cost Optimization

Data Sharing and Marketplace

Discussion

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub