Overview

What You'll Learn

What You'll Need

Don't have a Snowflake account? Sign up for a free trial at signup.snowflake.com/summit2026. Select the AI Data Cloud for Enterprise option — this trial includes free access to Cortex Code CLI.

Important: When creating your trial account, select Amazon Web Services as the cloud provider and US West (Oregon) as the region. The lab infrastructure (S3 bucket, Glue catalog, IAM role) is deployed in AWS US West 2 — your Snowflake account must be in the same region to reach it.

What You'll Build

Lab Environment

Architecture

The quotes table

Running SQL in Snowsight

Tip: Each section in this lab uses a separate SQL block. You can run everything in a single worksheet in order, or create one worksheet per section to keep things organized.

Build with Cortex Code

Setup

# Install Snowflake CLI (macOS/Linux)
pip install snowflake-cli

# Configure a connection to your trial account
snow connection add

# Start Cortex Code
cortex code

Starter Prompt

I want to complete the Summit HOL Lakehouse Analytics lab end to end.

My Snowflake account is on AWS US West 2 (Oregon). I have ACCOUNTADMIN access
and my warehouse is COMPUTE_WH.

The lab has pre-configured AWS infrastructure:
- S3 bucket: s3://sf-lab-iceberg-407539788379/iceberg/
- IAM role: arn:aws:iam::407539788379:role/sf-lab-shared-role
- Glue database: iceberg, table: quotes (~56K insurance quote records)

Please run through all sections in order:

1. Create External Volume my_iceberg_vol, Catalog Integration my_glue_int, and
   Catalog-Linked Database my_iceberg_db. Verify the quotes table syncs.

2. Query the quotes table — row count, product breakdown by volume, and premium
   by credit score band.

3. Set up governance:
   - Create roles lab_analyst and lab_data_engineer with grants + warehouse access
   - Create view iceberg_lab_db.analytics.quotes_vw over the Iceberg table
   - Apply dynamic data masking to email, phonenumber, surname, and dateofbirth
     columns on the view using ALTER VIEW
   - Verify masking by querying as lab_analyst vs lab_data_engineer

4. Create Semantic View iceberg_lab_db.analytics.quotes_sv on top of quotes_vw
   with facts, dimensions, and metrics.

5. Create Cortex Agent iceberg_lab_db.analytics.quotes_agent backed by quotes_sv.

Run each SQL block, show me results, and explain what each step built.

Note: If you prefer to run the SQL manually, continue to the next section.

Create Snowflake Objects

External Volume

CREATE OR REPLACE EXTERNAL VOLUME my_iceberg_vol
  STORAGE_LOCATIONS = (
    (
      NAME             = 'us-west-2'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = 's3://sf-lab-iceberg-407539788379/iceberg/'
      STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::407539788379:role/sf-lab-shared-role'
    )
  )
  ALLOW_WRITES = FALSE;

Catalog Integration

CREATE OR REPLACE CATALOG INTEGRATION my_glue_int
  CATALOG_SOURCE = ICEBERG_REST
  TABLE_FORMAT   = ICEBERG
  REST_CONFIG = (
    CATALOG_URI      = 'https://glue.us-west-2.amazonaws.com/iceberg'
    CATALOG_API_TYPE = AWS_GLUE
    CATALOG_NAME     = '407539788379'
  )
  REST_AUTHENTICATION = (
    TYPE                 = SIGV4
    SIGV4_IAM_ROLE       = 'arn:aws:iam::407539788379:role/sf-lab-shared-role'
    SIGV4_SIGNING_REGION = 'us-west-2'
  )
  ENABLED = TRUE;

Catalog-Linked Database

CREATE OR REPLACE DATABASE my_iceberg_db
  LINKED_CATALOG = (
    CATALOG                  = 'my_glue_int',
    SYNC_INTERVAL_SECONDS    = 3600,
    ALLOWED_WRITE_OPERATIONS = NONE
  )
  EXTERNAL_VOLUME = 'my_iceberg_vol';

Verify Sync

SELECT SYSTEM$CATALOG_LINK_STATUS('my_iceberg_db');

Note: The quotes table should appear within 60 seconds of creating the database.

Query Data

Important: AWS Glue uses case-insensitive, lowercase identifiers. Always wrap schema and table names in double quotes when querying a Catalog-Linked Database.

Explore the table

SELECT * FROM my_iceberg_db."iceberg"."quotes" LIMIT 10;

SELECT COUNT(*) AS total_quotes FROM my_iceberg_db."iceberg"."quotes";

Quote volume by product

SELECT
    quote_product,
    COUNT(*)                           AS total_quotes,
    ROUND(AVG(totalpremiumpayable), 2) AS avg_premium
FROM my_iceberg_db."iceberg"."quotes"
GROUP BY quote_product
ORDER BY total_quotes DESC;

High-Frequency Customers

SELECT
    surname,
    postcodedistrict,
    COUNT(*)                           AS quote_count,
    MIN(quotedate)                     AS first_quote,
    MAX(quotedate)                     AS last_quote,
    ROUND(AVG(totalpremiumpayable), 2) AS avg_premium
FROM my_iceberg_db."iceberg"."quotes"
GROUP BY surname, postcodedistrict
HAVING COUNT(*) > 1
ORDER BY quote_count DESC
LIMIT 20;

Premium by Credit Score

SELECT
    CASE
        WHEN creditscore >= 8 THEN 'High (8-10)'
        WHEN creditscore >= 5 THEN 'Medium (5-7)'
        ELSE 'Low (0-4)'
    END                                AS credit_band,
    COUNT(*)                           AS quote_count,
    ROUND(AVG(newriskpremium), 2)      AS avg_risk_premium,
    ROUND(AVG(totalpremiumpayable), 2) AS avg_total_premium
FROM my_iceberg_db."iceberg"."quotes"
GROUP BY credit_band
ORDER BY avg_risk_premium DESC;

Data Governance

Create roles

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS lab_data_engineer;
CREATE ROLE IF NOT EXISTS lab_analyst;

-- Hierarchy: analyst is a subset of data engineer
GRANT ROLE lab_analyst TO ROLE lab_data_engineer;

-- Grant both roles to your user
GRANT ROLE lab_data_engineer TO USER IDENTIFIER(CURRENT_USER());
GRANT ROLE lab_analyst TO USER IDENTIFIER(CURRENT_USER());

-- Grant warehouse access so roles can run queries
-- Replace COMPUTE_WH with your warehouse name if different
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE lab_data_engineer;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE lab_analyst;

Grant Access

-- External volume and catalog integration
GRANT USAGE ON EXTERNAL VOLUME my_iceberg_vol TO ROLE lab_data_engineer;
GRANT USAGE ON INTEGRATION my_glue_int TO ROLE lab_data_engineer;

-- Catalog-linked database
GRANT USAGE ON DATABASE my_iceberg_db TO ROLE lab_data_engineer;
GRANT USAGE ON DATABASE my_iceberg_db TO ROLE lab_analyst;
GRANT USAGE ON SCHEMA my_iceberg_db."iceberg" TO ROLE lab_data_engineer;
GRANT USAGE ON SCHEMA my_iceberg_db."iceberg" TO ROLE lab_analyst;
GRANT SELECT ON ALL ICEBERG TABLES IN SCHEMA my_iceberg_db."iceberg" TO ROLE lab_data_engineer;
GRANT SELECT ON ALL ICEBERG TABLES IN SCHEMA my_iceberg_db."iceberg" TO ROLE lab_analyst;

View Wrapper

CREATE DATABASE IF NOT EXISTS iceberg_lab_db;
CREATE SCHEMA IF NOT EXISTS iceberg_lab_db.analytics;

CREATE OR REPLACE VIEW iceberg_lab_db.analytics.quotes_vw AS
SELECT * FROM my_iceberg_db."iceberg"."quotes";

Masking Policies

USE DATABASE iceberg_lab_db;
USE SCHEMA analytics;

-- Email: [email protected] → j***@***.com
CREATE OR REPLACE MASKING POLICY mask_email
  AS (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('LAB_DATA_ENGINEER', 'ACCOUNTADMIN') THEN val
      ELSE CONCAT(LEFT(val, 1), '***@***.', SPLIT_PART(val, '.', -1))
    END;

-- Phone: 07123456789 → 071*****789
CREATE OR REPLACE MASKING POLICY mask_phone
  AS (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('LAB_DATA_ENGINEER', 'ACCOUNTADMIN') THEN val
      ELSE CONCAT(LEFT(val, 3), REPEAT('*', LENGTH(val) - 6), RIGHT(val, 3))
    END;

-- Surname: Smith → S****
CREATE OR REPLACE MASKING POLICY mask_surname
  AS (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('LAB_DATA_ENGINEER', 'ACCOUNTADMIN') THEN val
      ELSE CONCAT(LEFT(val, 1), REPEAT('*', GREATEST(LENGTH(val) - 1, 4)))
    END;

-- Date of birth: 1985-03-15 → ****-**-15
CREATE OR REPLACE MASKING POLICY mask_dob
  AS (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('LAB_DATA_ENGINEER', 'ACCOUNTADMIN') THEN val
      ELSE CONCAT('****-**-', RIGHT(val, 2))
    END;

Apply Policies

USE ROLE ACCOUNTADMIN;

ALTER VIEW iceberg_lab_db.analytics.quotes_vw
  ALTER COLUMN email SET MASKING POLICY iceberg_lab_db.analytics.mask_email;

ALTER VIEW iceberg_lab_db.analytics.quotes_vw
  ALTER COLUMN phonenumber SET MASKING POLICY iceberg_lab_db.analytics.mask_phone;

ALTER VIEW iceberg_lab_db.analytics.quotes_vw
  ALTER COLUMN surname SET MASKING POLICY iceberg_lab_db.analytics.mask_surname;

ALTER VIEW iceberg_lab_db.analytics.quotes_vw
  ALTER COLUMN dateofbirth SET MASKING POLICY iceberg_lab_db.analytics.mask_dob;

Verify Masking

USE ROLE lab_analyst;

SELECT uuid, surname, email, phonenumber, dateofbirth, totalpremiumpayable
FROM iceberg_lab_db.analytics.quotes_vw
LIMIT 5;

USE ROLE lab_data_engineer;

SELECT uuid, surname, email, phonenumber, dateofbirth, totalpremiumpayable
FROM iceberg_lab_db.analytics.quotes_vw
LIMIT 5;

The same Iceberg data in AWS Glue, governed entirely by Snowflake — no data movement, no copies, no AWS-side policy changes needed.

Semantic View

View Wrapper

Create View

USE DATABASE iceberg_lab_db;
USE SCHEMA analytics;

CREATE OR REPLACE SEMANTIC VIEW quotes_sv
  TABLES (
    quotes as quotes_vw
      primary key (uuid)
      comment='Insurance quote requests from the Iceberg data lake in AWS Glue'
  )
  FACTS (
    quotes.newriskpremium      as newriskpremium      comment='Calculated risk premium',
    quotes.totalpremiumpayable as totalpremiumpayable comment='Total premium payable',
    quotes.iptamount           as iptamount           comment='Insurance premium tax',
    quotes.quote_record        as 1                   comment='One record per quote'
  )
  DIMENSIONS (
    quotes.quote_product    as quote_product    with synonyms=('product type', 'insurance product', 'cover type') comment='Insurance product type',
    quotes.quotedate        as quotedate        with synonyms=('quote date', 'date', 'when requested')             comment='Date the quote was requested',
    quotes.maritalstatus    as maritalstatus    with synonyms=('marital status', 'married', 'single')              comment='Customer marital status',
    quotes.homeownerind     as homeownerind     with synonyms=('homeowner', 'owns home', 'property owner')         comment='Whether customer owns home',
    quotes.sex              as sex              with synonyms=('gender', 'customer gender')                         comment='Customer gender',
    quotes.postcodedistrict as postcodedistrict with synonyms=('district', 'location', 'area', 'region')           comment='Customer postcode district',
    quotes.previnsr         as previnsr         with synonyms=('previous insurer', 'prior insurer')                 comment='Previous insurance provider'
  )
  METRICS (
    quotes.total_quotes         as COUNT(quotes.quote_record)      comment='Total number of quote requests',
    quotes.avg_total_premium    as AVG(quotes.totalpremiumpayable)  comment='Average total premium payable',
    quotes.avg_risk_premium     as AVG(quotes.newriskpremium)       comment='Average risk premium',
    quotes.total_premium_volume as SUM(quotes.totalpremiumpayable)  comment='Total premium volume'
  )
  comment='Insurance quote analytics — Iceberg data in AWS Glue via Catalog-Linked Database';

Note: The semantic view uses a quotes as quotes_vw mapping where quotes is the internal reference name and quotes_vw is the physical view. Column references in FACTS, DIMENSIONS, and METRICS use the reference name quotes.

Grant Access

GRANT USAGE ON DATABASE iceberg_lab_db TO ROLE lab_analyst;
GRANT USAGE ON SCHEMA iceberg_lab_db.analytics TO ROLE lab_analyst;
GRANT SELECT ON VIEW iceberg_lab_db.analytics.quotes_vw TO ROLE lab_analyst;
GRANT SELECT ON SEMANTIC VIEW iceberg_lab_db.analytics.quotes_sv TO ROLE lab_analyst;
GRANT USAGE ON DATABASE iceberg_lab_db TO ROLE lab_data_engineer;
GRANT USAGE ON SCHEMA iceberg_lab_db.analytics TO ROLE lab_data_engineer;
GRANT SELECT ON VIEW iceberg_lab_db.analytics.quotes_vw TO ROLE lab_data_engineer;
GRANT SELECT ON SEMANTIC VIEW iceberg_lab_db.analytics.quotes_sv TO ROLE lab_data_engineer;

SHOW SEMANTIC VIEWS IN SCHEMA iceberg_lab_db.analytics;
SHOW SEMANTIC METRICS IN iceberg_lab_db.analytics.quotes_sv;
SHOW SEMANTIC DIMENSIONS IN iceberg_lab_db.analytics.quotes_sv;

Natural Language Queries

Create the agent

USE ROLE ACCOUNTADMIN;
USE DATABASE iceberg_lab_db;
USE SCHEMA analytics;

CREATE OR REPLACE AGENT iceberg_lab_db.analytics.quotes_agent
  WITH PROFILE = '{"display_name": "Insurance Quotes Analyst"}'
  COMMENT = 'Natural language analytics on Iceberg insurance quotes data in AWS Glue'
FROM SPECIFICATION $$
{
  "instructions": {
    "response": "Answer questions about insurance quote data. Use the quotes tool to query premium amounts, product types, homeowner status, marital status, and postcode district. Summarize results clearly and suggest follow-up questions."
  },
  "tools": [
    {
      "tool_spec": {
        "type": "cortex_analyst_text_to_sql",
        "name": "Query Insurance Quotes",
        "description": "Query insurance quote data including premiums, product types, homeowner status, marital status, and postcode district."
      }
    }
  ],
  "tool_resources": {
    "Query Insurance Quotes": {
      "semantic_view": "iceberg_lab_db.analytics.quotes_sv"
    }
  }
}
$$;

Cortex CoWork

USE ROLE ACCOUNTADMIN;

GRANT USAGE ON DATABASE iceberg_lab_db TO ROLE SNOWFLAKE;
GRANT USAGE ON SCHEMA iceberg_lab_db.analytics TO ROLE SNOWFLAKE;
GRANT SELECT ON VIEW iceberg_lab_db.analytics.quotes_vw TO ROLE SNOWFLAKE;
GRANT SELECT ON SEMANTIC VIEW iceberg_lab_db.analytics.quotes_sv TO ROLE SNOWFLAKE;

Note: The SNOWFLAKE service role is available in Snowflake Enterprise and Business Critical accounts. Trial accounts do not include this role — if you receive a "Role does not exist" error, skip this step. The agent is still fully accessible via AI & ML > Agents in Snowsight.

Ask Questions

What are the top 5 insurance products by number of quotes?

What is the average premium for homeowners vs non-homeowners?

Which postcode district has the highest average risk premium?

Show me quote volume broken down by marital status and product type.

What is the total premium volume by previous insurer?

Switch between the lab_analyst and lab_data_engineer roles to see how the same natural language question returns different results — masking policies are enforced end-to-end, from Iceberg through the semantic view to the agent response.

Conclusion And Resources

What You Learned

Related Resources