Overview

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

Key Features in This Lab

Setup

Source of Truth: The full SQL scripts for this lab are in the GitHub repository. This guide provides narrative context; the SQL files are the canonical source for execution.

Run the Setup Script

What the Setup Creates

Key Setup Concepts

Access Control

RBAC and DAC Fundamentals

Access is denied by default in Snowflake. Every privilege must be explicitly granted. Without USAGE on the database, querying any object inside it returns: "Database 'HRZN_DB' does not exist or not authorized."

The Complete Privilege Grant Pattern

-- 1. Create the role (USERADMIN)
CREATE OR REPLACE ROLE HRZN_DATA_ANALYST COMMENT = 'Analyst role';

-- 2. Grant warehouse access (SECURITYADMIN)
GRANT USAGE ON WAREHOUSE HRZN_WH TO ROLE HRZN_DATA_ANALYST;

-- 3. Grant database and schema access
GRANT USAGE ON DATABASE HRZN_DB TO ROLE HRZN_DATA_ANALYST;
GRANT USAGE ON ALL SCHEMAS IN DATABASE HRZN_DB TO ROLE HRZN_DATA_ANALYST;

-- 4. Grant object-level privileges
GRANT SELECT ON ALL TABLES IN SCHEMA HRZN_DB.HRZN_SCH TO ROLE HRZN_DATA_ANALYST;

Explore the Raw Data

Know and Protect Your Data

AI-Powered Classification with the BYOT Pattern

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 this tag to tables created via CTAS, INSERT...SELECT, and views — downstream tables inherit governance automatically.

CREATE SNOWFLAKE.DATA_PRIVACY.CLASSIFICATION_PROFILE
    HRZN_DB.HRZN_SCH.HRZN_STANDARD_CLASSIFICATION_PROFILE({
    '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 (Multi-Type)

-- Attach all policies to the DATA_CLASSIFICATION tag
ALTER TAG HRZN_DB.TAG_SCHEMA.DATA_CLASSIFICATION
    SET MASKING POLICY HRZN_DB.TAG_SCHEMA.DATA_CLASSIFICATION_MASK_STRING;
-- Repeat for NUMBER, DATE, TIMESTAMP

Key Benefit: Tag a column → masking is applied immediately, for the correct data type, automatically.

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 ('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 control WHO sees WHICH records. Masking policies control HOW data appears when visible. Together they provide layered protection.

Aggregation and Projection Policies

-- Aggregation: force non-admin users to group by ≥100 rows
CREATE OR REPLACE AGGREGATION POLICY HRZN_DB.TAG_SCHEMA.AGGREGATION_POLICY
    AS () RETURNS AGGREGATION_CONSTRAINT ->
    CASE
        WHEN CURRENT_ROLE() IN ('HRZN_DATA_GOVERNOR') THEN NO_AGGREGATION_CONSTRAINT()
        ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 100)
    END;

-- Projection: block ZIP from appearing in SELECT output (but allow it in WHERE)
CREATE OR REPLACE PROJECTION POLICY HRZN_DB.TAG_SCHEMA.PROJECTION_POLICY
    AS () RETURNS PROJECTION_CONSTRAINT ->
    CASE
        WHEN CURRENT_ROLE() IN ('HRZN_DATA_GOVERNOR') THEN PROJECTION_CONSTRAINT(ALLOW => true)
        ELSE PROJECTION_CONSTRAINT(ALLOW => false)
    END;

Tag Propagation to Derived Tables

CREATE TABLE HRZN_DB.HRZN_SCH.CUSTOMER_COPY AS
SELECT * FROM HRZN_DB.HRZN_SCH.CUSTOMER;
-- DATA_CLASSIFICATION tags propagate automatically
-- Masking policies apply to CUSTOMER_COPY without any manual configuration

Trust Center Verification

Navigate the Dashboard

Verify Classification Results via SQL

-- Mirror the dashboard breakdown tiles
SELECT TAG_VALUE AS CLASSIFICATION_LEVEL,
    COUNT(DISTINCT COLUMN_NAME) AS COLUMN_COUNT,
    COUNT(DISTINCT TABLE_NAME) AS TABLE_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_CLASSIFICATION_LATEST
WHERE TABLE_DATABASE = 'HRZN_DB'
GROUP BY TAG_VALUE ORDER BY COLUMN_COUNT DESC;

Note: ACCOUNT_USAGE has up to 3-hour latency. Use INFORMATION_SCHEMA.TAG_REFERENCES_ALL_COLUMNS for immediate verification (demonstrated in Step 2).

Objects That Need Review (UI Walkthrough)

Sensitive Data Entitlement Report

SELECT DISTINCT USER_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE
FROM SNOWFLAKE.DATA_SECURITY.ENTITLEMENT_REPORT
ORDER BY USER_NAME, TABLE_NAME, PRIVILEGE;

Governance Gap Analysis

-- Sensitive columns WITHOUT an active masking policy
SELECT dc.TABLE_NAME, dc.COLUMN_NAME, dc.TAG_VALUE AS SENSITIVITY_LEVEL,
    CASE WHEN pr.REF_COLUMN_NAME IS NOT NULL THEN 'Protected' ELSE 'UNPROTECTED' END AS STATUS
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_CLASSIFICATION_LATEST dc
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES pr
    ON dc.TABLE_NAME = SPLIT_PART(pr.REF_ENTITY_NAME, '.', 3)
    AND dc.COLUMN_NAME = pr.REF_COLUMN_NAME
    AND pr.POLICY_KIND = 'MASKING_POLICY'
WHERE dc.TABLE_DATABASE = 'HRZN_DB';

Access and Audit Trail

Important: Access History has up to 3-hour latency. Some queries may return empty results immediately after lab steps. This is expected; plan for this latency in production compliance reporting.

Query Count and Read/Write Patterns

-- Access count by object
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_access
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY,
LATERAL FLATTEN(input => direct_objects_accessed)
WHERE object_name ILIKE 'HRZN%'
GROUP BY object_name, query_type;

Sensitive Data Column-Level Lineage

AI_REDACT for Unstructured PII

The Problem: PII in Free-Form Text

"Customer John Smith called from 555-123-4567. Email: [email protected]"
"Customer Sarah Williams mentioned her SSN 123-45-6789 was visible on invoice."

AI_REDACT: Automatic PII Removal

SELECT
    original_feedback,
    SNOWFLAKE.CORTEX.AI_REDACT(original_feedback) AS redacted_feedback
FROM HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS LIMIT 5;

ORIGINAL: "Customer John Smith called from 555-123-4567. Email: [email protected]"
REDACTED: "Customer [NAME] called from [PHONE_NUMBER]. Email: [EMAIL]"

Selective Redaction

-- Redact only names and emails; preserve phone numbers
SNOWFLAKE.CORTEX.AI_REDACT(text, ['NAME', 'EMAIL'])

Pre-Compute + Secure View Pattern

-- 1. Create a redacted table once (batch job)
CREATE TABLE HRZN_DB.HRZN_SCH.CUSTOMER_FEEDBACK_REDACTED AS
SELECT ORDER_ID,
    CUSTOMER_FEEDBACK AS original_feedback,
    SNOWFLAKE.CORTEX.AI_REDACT(CUSTOMER_FEEDBACK) AS redacted_feedback
FROM HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS WHERE CUSTOMER_FEEDBACK IS NOT NULL LIMIT 100;

-- 2. Secure view switches columns based on role
CREATE SECURE VIEW HRZN_DB.HRZN_SCH.CUSTOMER_FEEDBACK_SECURE AS
SELECT ORDER_ID,
    CASE WHEN CURRENT_ROLE() IN ('HRZN_DATA_GOVERNOR') THEN original_feedback
         ELSE redacted_feedback
    END AS CUSTOMER_FEEDBACK
FROM HRZN_DB.HRZN_SCH.CUSTOMER_FEEDBACK_REDACTED;

Privacy-Safe Sentiment Analysis

SELECT redacted_feedback,
    SNOWFLAKE.CORTEX.SENTIMENT(redacted_feedback) AS sentiment_score
FROM HRZN_DB.HRZN_SCH.CUSTOMER_FEEDBACK_REDACTED;

Tag Propagation: CUSTOMER_FEEDBACK_REDACTED automatically inherits DATA_CLASSIFICATION tags from CUSTOMER_ORDERS because of the propagation setting from Step 2.

Cortex Code Skills

Skill Domains Used in This Lab

6.1 — Governance Maturity Assessment

"What is the governance coverage for HRZN_DB?
 Which tables have PII but no masking policy applied?"

"Generate a data governance health report for HRZN_DB.
 Include: tables with sensitive data, policy coverage percentage, and top risks."

6.2 — AI Classification via Skills

"Scan HRZN_DB.HRZN_SCH.CUSTOMER_ORDERS for PII and sensitive data."

"Which tables in HRZN_DB need re-classification because their results are older than 30 days?"

"Help me create a custom classifier for employee IDs matching the pattern EMP-XXXXX."

6.3 — Policy Creation and Audit

"Audit all masking policies in HRZN_DB.TAG_SCHEMA. Are there anti-patterns?"

"Create a GDPR-compliant masking policy for the SSN column in HRZN_DB.HRZN_SCH.CUSTOMER."

"What is the recommended ABAC pattern for masking in Snowflake?"

Anti-Pattern Teaching Moment: The masking policies created in Step 2 use CURRENT_ROLE(). Cortex Code will flag this: CURRENT_ROLE() only evaluates the primary active role, while IS_ROLE_IN_SESSION() evaluates all active roles including secondary roles. Best practice is always IS_ROLE_IN_SESSION().

Cortex Code generates complete, runnable CREATE MASKING POLICY SQL using Snowflake best practices. Review the generated SQL before applying it to production tables.

6.4 — Compliance Audit

"Who has accessed HRZN_DB.HRZN_SCH.CUSTOMER in the last 30 days?"

"Were there any queries on HRZN_DB sensitive tables outside of business hours?"

"Which roles have the APPLY MASKING POLICY privilege on my account?"

"Show the role hierarchy for HRZN_DATA_GOVERNOR."

Conclusion and Resources

What You Learned

Clean Up

Related Resources