Overview

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

AI-Powered Governance Highlights

Key Principle: Fine-grained access controls are consistently enforced whether users query data via SQL, Python, or AI-powered natural language interfaces.

Introduction Videos

Setup

Source of Truth: The full SQL scripts for this lab are maintained in the GitHub repository. The repo is the canonical source for execution. This guide provides additional explanatory context and highlights key concepts and some of the SQL has been simplified for illusration purposes.

Run the Setup Script

What the Setup Creates

Key Setup Concepts

Data Quality Monitoring

RBAC Fundamentals

Key Concepts:

• Securable Object: Entity to which access can be granted (Database, Schema, Table, View, etc.)
• Role: Container for privileges, can be granted to users or other roles
• Privilege: Defined level of access (SELECT, INSERT, USAGE, etc.)

Data Quality Monitoring with DMFs

ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER 
  SET DATA_METRIC_SCHEDULE = 'TRIGGER_ON_CHANGES';

ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER 
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (EMAIL);

CREATE DATA METRIC FUNCTION HRZN_DB.HRZN_SCH.INVALID_EMAIL_COUNT(
    IN_TABLE TABLE(IN_COL STRING)
)
RETURNS NUMBER 
AS
'SELECT COUNT_IF(FALSE = (IN_COL regexp ''^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$'')) FROM IN_TABLE';

View DMF Results

Results appear in SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS with up to a few minutes latency. This view may not work in trial accounts.

Know and Protect Your Data

AI-Powered Classification with Tag Mapping

-- Create custom tag with propagation enabled
CREATE OR REPLACE TAG HRZN_DB.TAG_SCHEMA.DATA_CLASSIFICATION 
    ALLOWED_VALUES 'PII', 'RESTRICTED', 'SENSITIVE', 'INTERNAL', 'PUBLIC'
    PROPAGATE = ON_DEPENDENCY_AND_DATA_MOVEMENT;

PROPAGATE = ON_DEPENDENCY_AND_DATA_MOVEMENT automatically flows tags to tables created via CTAS or views.

Classification Profile with Tag Map

CREATE SNOWFLAKE.DATA_PRIVACY.CLASSIFICATION_PROFILE 
    HRZN_DB.HRZN_SCH.HRZN_STANDARD_CLASSIFICATION_PROFILE({
    'minimum_object_age_for_classification_days': 0,
      'maximum_classification_validity_days': 90,
      'auto_tag': true,
      'tag_map': {
        'column_tag_map': [
          { 'tag_name': 'HRZN_DB.TAG_SCHEMA.DATA_CLASSIFICATION', 'tag_value': 'PII',
            'semantic_categories': ['EMAIL', 'US_SOCIAL_SECURITY_NUMBER', 'CREDIT_CARD_NUMBER'] },
          { 'tag_name': 'HRZN_DB.TAG_SCHEMA.DATA_CLASSIFICATION', 'tag_value': 'RESTRICTED',
            'semantic_categories': ['PHONE_NUMBER', 'DATE_OF_BIRTH'] }
        ]
      }
    });

CALL SYSTEM$CLASSIFY('HRZN_DB.HRZN_SCH.CUSTOMER', 
    'HRZN_DB.HRZN_SCH.HRZN_STANDARD_CLASSIFICATION_PROFILE');

Tag-Based Masking Policies

-- Attach masking policy to tag (protects all tagged columns automatically)
ALTER TAG HRZN_DB.TAG_SCHEMA.DATA_CLASSIFICATION 
    SET MASKING POLICY HRZN_DB.TAG_SCHEMA.DATA_CLASSIFICATION_MASK_STRING;

Key Benefit: When new columns are tagged DATA_CLASSIFICATION='PII', masking is automatically applied—no additional policy assignment needed.

Row Access Policies

CREATE OR REPLACE ROW ACCESS POLICY HRZN_DB.TAG_SCHEMA.CUSTOMER_OPTIN_POLICY
    AS (OPTIN_STATUS STRING) RETURNS BOOLEAN ->
    CASE
        WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN', 'HRZN_DATA_GOVERNOR') THEN TRUE
        WHEN CURRENT_ROLE() = 'HRZN_DATA_USER' AND OPTIN_STATUS = 'Y' THEN TRUE
        ELSE FALSE
    END;

CREATE OR REPLACE ROW ACCESS POLICY HRZN_DB.TAG_SCHEMA.CUSTOMER_STATE_RESTRICTIONS
    AS (STATE STRING) RETURNS BOOLEAN ->
       CURRENT_ROLE() IN ('ACCOUNTADMIN','HRZN_DATA_GOVERNOR')
        OR EXISTS (SELECT 1 FROM HRZN_DB.TAG_SCHEMA.ROW_POLICY_MAP rp
                   WHERE rp.ROLE = CURRENT_ROLE() AND rp.STATE_VISIBILITY = STATE);

Defense in Depth: Row access policies (WHO sees WHICH records) combine with masking policies (HOW data appears) for layered protection.

Privacy Policies

Aggregation Policy

CREATE OR REPLACE AGGREGATION POLICY HRZN_DB.TAG_SCHEMA.aggregation_policy
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    CASE
      WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','HRZN_DATA_GOVERNOR')
      THEN NO_AGGREGATION_CONSTRAINT()  
      ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 100)
    END;

Projection Policy

CREATE OR REPLACE PROJECTION POLICY HRZN_DB.TAG_SCHEMA.projection_policy
  AS () RETURNS PROJECTION_CONSTRAINT -> 
  CASE
    WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','HRZN_DATA_GOVERNOR')
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
    ELSE PROJECTION_CONSTRAINT(ALLOW => false)
  END;

Key Takeaways

Access and Audit

Important: Access History has latency of up to 3 hours. Some queries may not return results immediately after running lab exercises.

Key Access History Queries

-- Query access patterns
SELECT 
    value:"objectName"::STRING AS object_name,
    COUNT(DISTINCT query_id) AS number_of_queries
FROM snowflake.account_usage.access_history,
LATERAL FLATTEN (input => direct_objects_accessed)
WHERE object_name ILIKE 'HRZN%'
GROUP BY object_name
ORDER BY number_of_queries DESC;

-- Read vs Write breakdown
SELECT 
    value:"objectName"::STRING AS object_name,
    CASE WHEN object_modified_by_ddl IS NOT NULL THEN 'write' ELSE 'read' END AS query_type,
    COUNT(DISTINCT query_id) AS number_of_queries,
    MAX(query_start_time) AS last_query_start_time
FROM snowflake.account_usage.access_history,
LATERAL FLATTEN (input => direct_objects_accessed)
WHERE object_name ILIKE 'HRZN%'
GROUP BY object_name, query_type
ORDER BY object_name, number_of_queries DESC;

Governance Dashboard Queries

Semantic Views for AI Analytics

Create a Semantic View

CREATE OR REPLACE SEMANTIC VIEW CUSTOMER_ORDER_ANALYTICS 

Query with Cortex Analyst

Governance Inheritance

Key Benefit: Masking and row access policies automatically apply to semantic view queries. HRZN_DATA_USER sees:

• Only MA customers (row access policy)
• Masked PII columns (masking policy)
• Same governance as direct table queries!

AI_REDACT for Unstructured PII

Add Customer Feedback Column

ALTER TABLE HRZN_DB.HRZN_SCH.CUSTOMER ADD COLUMN CUSTOMER_FEEDBACK VARCHAR;

-- Sample feedback with embedded PII
UPDATE HRZN_DB.HRZN_SCH.CUSTOMER 
SET CUSTOMER_FEEDBACK = 'My name is ' || FIRST_NAME || ' ' || LAST_NAME || 
    '. I had an issue with order delivery to ' || STREET_ADDRESS || 
    '. Please call me at ' || PHONE_NUMBER || ' or email ' || EMAIL
WHERE RANDOM() > 0.7;

AI_REDACT Function

WITH feedback_sample AS (
    SELECT 
        'Contact John Smith at [email protected] or call 555-123-4567 for updates.' as text
)
SELECT 
    text as original,
    SNOWFLAKE.CORTEX.AI_REDACT(text) as full_redaction,
    SNOWFLAKE.CORTEX.AI_REDACT(text, ['NAME', 'EMAIL']) as partial_redaction
FROM feedback_sample;

Pre-Computed Redacted Table with Tag Propagation

-- Create redacted table - tags propagate automatically
CREATE OR REPLACE TABLE HRZN_DB.HRZN_SCH.CUSTOMER_FEEDBACK_REDACTED AS
SELECT 
    ORDER_ID,
    CUSTOMER_ID,
    ORDER_TS,
    CUSTOMER_FEEDBACK as original_feedback,
    SNOWFLAKE.CORTEX.AI_REDACT(CUSTOMER_FEEDBACK) as redacted_feedback,
    CURRENT_TIMESTAMP() as redacted_at,
    CURRENT_USER() as redacted_by
FROM HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS 
WHERE CUSTOMER_FEEDBACK IS NOT NULL
LIMIT 100;

-- Verify classification tag propagated
SELECT 
    ORDER_ID,
    original_feedback,
    redacted_feedback
FROM HRZN_DB.HRZN_SCH.CUSTOMER_FEEDBACK_REDACTED
WHERE original_feedback NOT LIKE 'Standard order%'
LIMIT 10;

Sentiment Analysis on Redacted Data

SELECT 
    ORDER_ID,
    redacted_feedback,
    SNOWFLAKE.CORTEX.SENTIMENT(redacted_feedback) as sentiment_score,
    CASE 
        WHEN SNOWFLAKE.CORTEX.SENTIMENT(redacted_feedback) > 0.5 THEN 'Positive'
        WHEN SNOWFLAKE.CORTEX.SENTIMENT(redacted_feedback) < -0.5 THEN 'Negative'
        ELSE 'Neutral'
    END as sentiment_category
FROM HRZN_DB.HRZN_SCH.CUSTOMER_FEEDBACK_REDACTED
WHERE redacted_feedback NOT LIKE 'Standard order%'
ORDER BY sentiment_score DESC
LIMIT 100;

Natural Language Governance Queries

Example Governance Questions

Conclusion And Resources

What You Learned

Clean Up (Optional)

Resources