Overview

Get the Code

Prerequisites

What You'll Learn

What You'll Build

Environment Setup

Authentication Overview

Generate an RSA Key Pair

openssl genrsa 2048 | openssl pkcs8 -topk8 -nocrypt -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

grep -v "BEGIN\|END" rsa_key.p8 | tr -d '\n'

Configure the SSV2 Streaming App

cd ssv2-streaming
cp profile.json.example profile.json

{
  "user": "<YOUR_SNOWFLAKE_USERNAME>",
  "account": "<ORG-ACCT>",
  "url": "https://<ORG-ACCT>.snowflakecomputing.com:443",
  "private_key": "<BASE64_PRIVATE_KEY_NO_HEADERS>",
  "role": "DEMO_ADMIN"
}

SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();

Run the Setup Script

USE ROLE ACCOUNTADMIN;
SET MY_USER = '<YOUR_SNOWFLAKE_USERNAME>';

CREATE WAREHOUSE IF NOT EXISTS DEMO_WH
    WAREHOUSE_SIZE = 'MEDIUM'
    AUTO_SUSPEND   = 120
    AUTO_RESUME    = TRUE
    INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS ICEBERG_DEMO;
ALTER DATABASE ICEBERG_DEMO SET ICEBERG_VERSION_DEFAULT = 3;

LIST @TLC_STAGE/yellow/;
-- Expected: 26 Parquet files

Create Iceberg Tables

Repo shortcut: The complete script with inline comments is assets/01_create_tables.sql in your forked repo. The key statements are shown below.

Key Concept: Snowflake Storage for Iceberg

CATALOG = SNOWFLAKE
EXTERNAL_VOLUME = SNOWFLAKE_MANAGED

Create yellow_trips (Path A — no governance)

USE ROLE DEMO_ADMIN;
USE WAREHOUSE DEMO_WH;
USE DATABASE ICEBERG_DEMO;
USE SCHEMA PUBLIC;

CREATE OR REPLACE ICEBERG TABLE yellow_trips
    CATALOG = SNOWFLAKE
    EXTERNAL_VOLUME = SNOWFLAKE_MANAGED
    AS SELECT
        $1:VendorID::INT AS VendorID,
        TO_TIMESTAMP_NTZ($1:tpep_pickup_datetime::STRING)::TIMESTAMP_NTZ(6) AS tpep_pickup_datetime,
        TO_TIMESTAMP_NTZ($1:tpep_dropoff_datetime::STRING)::TIMESTAMP_NTZ(6) AS tpep_dropoff_datetime,
        $1:passenger_count::INT AS passenger_count,
        $1:trip_distance::FLOAT AS trip_distance,
        $1:RatecodeID::INT AS RatecodeID,
        $1:store_and_fwd_flag::STRING AS store_and_fwd_flag,
        $1:PULocationID::INT AS PULocationID,
        $1:DOLocationID::INT AS DOLocationID,
        $1:payment_type::INT AS payment_type,
        $1:fare_amount::FLOAT AS fare_amount,
        $1:extra::FLOAT AS extra,
        $1:mta_tax::FLOAT AS mta_tax,
        $1:tip_amount::FLOAT AS tip_amount,
        $1:tolls_amount::FLOAT AS tolls_amount,
        $1:improvement_surcharge::FLOAT AS improvement_surcharge,
        $1:total_amount::FLOAT AS total_amount,
        $1:congestion_surcharge::FLOAT AS congestion_surcharge,
        $1:Airport_fee::FLOAT AS Airport_fee,
        $1:cbd_congestion_fee::FLOAT AS cbd_congestion_fee
    FROM @TLC_STAGE/yellow/ (FILE_FORMAT => 'parquet_ff');

Why microseconds? This CTAS may take a few minutes. While it runs, note that TIMESTAMP_NTZ(6) (microsecond precision) is an intentional choice — Iceberg Spark runtime 1.10.x cannot read nanosecond timestamps.

Create green_trips (Path B — governance will be applied later)

CREATE OR REPLACE ICEBERG TABLE green_trips
    CATALOG = SNOWFLAKE
    EXTERNAL_VOLUME = SNOWFLAKE_MANAGED
    AS SELECT
        $1:VendorID::INT AS VendorID,
        TO_TIMESTAMP_NTZ($1:lpep_pickup_datetime::STRING)::TIMESTAMP_NTZ(6) AS lpep_pickup_datetime,
        TO_TIMESTAMP_NTZ($1:lpep_dropoff_datetime::STRING)::TIMESTAMP_NTZ(6) AS lpep_dropoff_datetime,
        $1:passenger_count::INT AS passenger_count,
        $1:trip_distance::FLOAT AS trip_distance,
        $1:RatecodeID::INT AS RatecodeID,
        $1:store_and_fwd_flag::STRING AS store_and_fwd_flag,
        $1:PULocationID::INT AS PULocationID,
        $1:DOLocationID::INT AS DOLocationID,
        $1:payment_type::INT AS payment_type,
        $1:fare_amount::FLOAT AS fare_amount,
        $1:extra::FLOAT AS extra,
        $1:mta_tax::FLOAT AS mta_tax,
        $1:tip_amount::FLOAT AS tip_amount,
        $1:tolls_amount::FLOAT AS tolls_amount,
        $1:improvement_surcharge::FLOAT AS improvement_surcharge,
        $1:total_amount::FLOAT AS total_amount,
        $1:congestion_surcharge::FLOAT AS congestion_surcharge,
        $1:trip_type::INT AS trip_type,
        $1:ehail_fee::FLOAT AS ehail_fee
    FROM @TLC_STAGE/green/ (FILE_FORMAT => 'parquet_ff');

Create green_trips_nano (timestamp compatibility demo)

CREATE OR REPLACE ICEBERG TABLE green_trips_nano
    CATALOG = SNOWFLAKE
    EXTERNAL_VOLUME = SNOWFLAKE_MANAGED
    AS SELECT
        $1:VendorID::INT AS VendorID,
        TO_TIMESTAMP_NTZ($1:lpep_pickup_datetime::STRING)::TIMESTAMP_NTZ(9) AS lpep_pickup_datetime,
        TO_TIMESTAMP_NTZ($1:lpep_dropoff_datetime::STRING)::TIMESTAMP_NTZ(9) AS lpep_dropoff_datetime,
        $1:passenger_count::INT AS passenger_count,
        $1:trip_distance::FLOAT AS trip_distance,
        $1:PULocationID::INT AS PULocationID,
        $1:DOLocationID::INT AS DOLocationID,
        $1:fare_amount::FLOAT AS fare_amount,
        $1:tip_amount::FLOAT AS tip_amount,
        $1:total_amount::FLOAT AS total_amount
    FROM @TLC_STAGE/green/ (FILE_FORMAT => 'parquet_ff')
    LIMIT 1000;

Intentional incompatibility: green_trips_nano uses TIMESTAMP_NTZ(9) (nanosecond). Snowflake reads it perfectly but Spark 1.10.x rejects it. You will observe this failure in the Databricks step.

Create zone_lookup (dimension table)

CREATE OR REPLACE TABLE zone_lookup (
    LocationID   INT,
    Borough      VARCHAR,
    Zone         VARCHAR,
    service_zone VARCHAR
);

COPY INTO zone_lookup
FROM @TLC_STAGE/lookup/
FILE_FORMAT = (FORMAT_NAME = 'csv_ff');

Verify Iceberg V3 Metadata

SELECT SYSTEM$GET_ICEBERG_TABLE_INFORMATION('yellow_trips') AS yellow_metadata;
SELECT SYSTEM$GET_ICEBERG_TABLE_INFORMATION('green_trips') AS green_metadata;

SELECT 'yellow_trips' AS table_name, COUNT(*) AS row_count FROM yellow_trips
UNION ALL
SELECT 'green_trips', COUNT(*) FROM green_trips
UNION ALL
SELECT 'zone_lookup', COUNT(*) FROM zone_lookup;

Stream Weather Data with Snowpipe Streaming V2

Get the code: The Java application and Maven project are in your forked repo (assets/SSV2WeatherIngest.java and assets/pom.xml). The complete ssv2-streaming/ directory with the correct Maven layout, source code, and a profile.json.example template is also included. Copy the ssv2-streaming/ folder to your local machine, rename profile.json.example to profile.json, fill in your credentials, then run the build commands below.

Create the Weather Table and Pipe

USE ROLE DEMO_ADMIN;
USE WAREHOUSE DEMO_WH;
USE DATABASE ICEBERG_DEMO;
USE SCHEMA PUBLIC;

CREATE OR REPLACE ICEBERG TABLE nyc_weather_ssv2 (
    location     STRING           NOT NULL,
    latitude     FLOAT            NOT NULL,
    longitude    FLOAT            NOT NULL,
    weather_data VARIANT          NOT NULL,
    ingested_at  TIMESTAMP_LTZ(6)
)
CATALOG = SNOWFLAKE
EXTERNAL_VOLUME = SNOWFLAKE_MANAGED;

CREATE OR REPLACE PIPE nyc_weather_ssv2_pipe
AS COPY INTO nyc_weather_ssv2
FROM (
    SELECT
        $1:location::STRING,
        $1:latitude::FLOAT,
        $1:longitude::FLOAT,
        $1:weather_data::VARIANT,
        $1:ingested_at::TIMESTAMP_LTZ
    FROM TABLE(DATA_SOURCE(TYPE => 'STREAMING'))
);

Run the Java SSV2 Ingest Application

cd ssv2-streaming
mvn package
mvn exec:java -Dexec.mainClass="com.snowflake.snowpipestreaming.demo.SSV2WeatherIngest"

Streamed 36 rows.
All 36 rows committed to nyc_weather_ssv2.

Query the VARIANT Data

SELECT COUNT(*) AS row_count FROM nyc_weather_ssv2;

SELECT
    location,
    ingested_at,
    weather_data:timezone::STRING         AS timezone,
    weather_data:elevation::FLOAT         AS elevation_m,
    weather_data:generationtime_ms::FLOAT AS api_gen_ms
FROM nyc_weather_ssv2
ORDER BY ingested_at DESC, location
LIMIT 9;

Sub-Column Pruning Proof

ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT * FROM nyc_weather_ssv2;

-- Now compare with sub-column access only:
SELECT
    location,
    weather_data:hourly_units:precipitation::STRING AS precip_unit
FROM nyc_weather_ssv2;

LATERAL FLATTEN — Hourly Precipitation

SELECT
    w.location,
    t.value::STRING AS weather_hour,
    p.value::FLOAT  AS precipitation_mm,
    c.value::INT    AS weather_code
FROM nyc_weather_ssv2 w,
    LATERAL FLATTEN(input => w.weather_data:hourly:time)          t,
    LATERAL FLATTEN(input => w.weather_data:hourly:precipitation) p,
    LATERAL FLATTEN(input => w.weather_data:hourly:weathercode)   c
WHERE t.index = p.index
  AND t.index = c.index
  AND w.location = 'JFK'
  AND p.value::FLOAT > 0
ORDER BY t.value::STRING
LIMIT 20;

Weather + Taxi Trips Join

SELECT
    CASE WHEN w_precip > 0 THEN 'Rainy' ELSE 'Dry' END AS weather_condition,
    COUNT(*)                                            AS trip_count,
    ROUND(AVG(y.tip_amount / NULLIF(y.fare_amount, 0)) * 100, 2) AS avg_tip_pct
FROM yellow_trips y
JOIN (
    SELECT
        TO_TIMESTAMP(t.value::STRING) AS weather_hour,
        p.value::FLOAT                AS w_precip
    FROM nyc_weather_ssv2 w,
        LATERAL FLATTEN(input => w.weather_data:hourly:time)          t,
        LATERAL FLATTEN(input => w.weather_data:hourly:precipitation) p
    WHERE t.index = p.index
      AND w.location = 'JFK'
) weather
ON DATE_TRUNC('hour', y.tpep_pickup_datetime) = weather.weather_hour
WHERE y.fare_amount > 0
GROUP BY 1
ORDER BY 1;

What you'll discover: NYC riders tip consistently (~20%) regardless of weather — but rain does reduce trip volume and average distance. The real signal is demand elasticity, not generosity. This is why joining weather + trip data at query time (zero-copy) beats pre-aggregation.

Governance — Path A vs Path B

Verify Path A is Clean

USE ROLE DEMO_ADMIN;
USE WAREHOUSE DEMO_WH;
USE DATABASE ICEBERG_DEMO;
USE SCHEMA PUBLIC;

SELECT *
FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    REF_ENTITY_NAME   => 'ICEBERG_DEMO.PUBLIC.yellow_trips',
    REF_ENTITY_DOMAIN => 'TABLE'
));
-- Expected: 0 rows (no policies attached)

Tag Sensitive Columns

CREATE TAG IF NOT EXISTS data_sensitivity ALLOWED_VALUES 'FINANCIAL', 'PII';

ALTER ICEBERG TABLE green_trips ALTER COLUMN fare_amount  SET TAG data_sensitivity = 'FINANCIAL';
ALTER ICEBERG TABLE green_trips ALTER COLUMN tip_amount   SET TAG data_sensitivity = 'FINANCIAL';
ALTER ICEBERG TABLE green_trips ALTER COLUMN total_amount SET TAG data_sensitivity = 'FINANCIAL';

Create Column Masking Policy

CREATE OR REPLACE MASKING POLICY financial_mask
    AS (val FLOAT)
    RETURNS FLOAT ->
    CASE
        WHEN CURRENT_ROLE() IN ('DEMO_ADMIN') THEN val
        ELSE -1.0
    END;

ALTER ICEBERG TABLE green_trips ALTER COLUMN fare_amount  SET MASKING POLICY financial_mask;
ALTER ICEBERG TABLE green_trips ALTER COLUMN tip_amount   SET MASKING POLICY financial_mask;
ALTER ICEBERG TABLE green_trips ALTER COLUMN total_amount SET MASKING POLICY financial_mask;

Create Row Access Policy

CREATE OR REPLACE ROW ACCESS POLICY borough_rls
    AS (pickup_location_id INT)
    RETURNS BOOLEAN ->
    CASE
        WHEN CURRENT_ROLE() = 'DEMO_ADMIN'      THEN TRUE
        WHEN CURRENT_ROLE() = 'DEMO_ANALYST'    THEN TRUE
        WHEN CURRENT_ROLE() = 'DEMO_DBX_READER' THEN
            EXISTS (SELECT 1 FROM zone_lookup z
                    WHERE z.LocationID = pickup_location_id AND z.Borough = 'Manhattan')
        ELSE FALSE
    END;

ALTER ICEBERG TABLE green_trips
    ADD ROW ACCESS POLICY borough_rls ON (PULocationID);

Grant SELECT to Databricks Reader Role

USE ROLE ACCOUNTADMIN;

GRANT SELECT ON ICEBERG TABLE yellow_trips     TO ROLE DEMO_DBX_READER;
GRANT SELECT ON ICEBERG TABLE green_trips      TO ROLE DEMO_DBX_READER;
GRANT SELECT ON ICEBERG TABLE nyc_weather_ssv2 TO ROLE DEMO_DBX_READER;
GRANT SELECT ON ICEBERG TABLE green_trips_nano TO ROLE DEMO_DBX_READER;
GRANT SELECT ON TABLE zone_lookup              TO ROLE DEMO_DBX_READER;

Generate a Programmatic Access Token (PAT)

SELECT
    CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS account_identifier,
    'https://' || CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME()
        || '.snowflakecomputing.com/polaris/api/catalog' AS horizon_irc_endpoint;

ALTER USER IDENTIFIER($MY_USER)
    ADD PROGRAMMATIC ACCESS TOKEN quickstart_dbx_pat
    DAYS_TO_EXPIRY = 1
    ROLE_RESTRICTION = 'DEMO_DBX_READER'
    COMMENT = 'Quickstart Databricks IRC token';

Important: Copy the PAT value immediately — it is shown only once. The token expires in 1 day.

Read from Databricks via Horizon IRC

Create a Databricks Cluster

Install Maven Libraries

Version warning: Use _2.13 artifacts (not _2.12). DBR 17.3 ships Scala 2.13. Do NOT install snowflake-jdbc separately — it's included transitively.

Upload and Configure the Notebook

ACCOUNT_IDENTIFIER = "<ORG-ACCT>"
PAT_TOKEN          = "<YOUR_PAT_TOKEN>"
SF_USER            = "<YOUR_SNOWFLAKE_USER>"
RSA_PRIVATE_KEY    = "<BASE64_KEY>"
REGION             = "us-east-1"

Path A — yellow_trips (Zero Snowflake Compute)

yellow_df = spark.table(f"{CAT}.{SCHEMA_NAME}.YELLOW_TRIPS")
print(f"Row count: {yellow_df.count():,}")

Zero compute cost: Check your Snowflake credit meter — it will not move. Path A uses zero warehouse compute because no policies are attached.

Path B — green_trips (Governance Enforced)

green_df = spark.table(f"{CAT}.{SCHEMA_NAME}.GREEN_TRIPS")
print(f"Row count (RLS-filtered, Manhattan only): {green_df.count():,}")

Iceberg V3 VARIANT — nyc_weather_ssv2

from pyspark.sql.functions import variant_get, col

weather_df = spark.table(f"{CAT}.{SCHEMA_NAME}.NYC_WEATHER_SSV2")

weather_df.select(
    col("location"),
    variant_get("weather_data", "$.timezone", "string").alias("timezone"),
    variant_get("weather_data", "$.elevation", "float").alias("elevation_m"),
).show(truncate=False)

Timestamp Compatibility Demo

try:
    nano_df = spark.table(f"{CAT}.{SCHEMA_NAME}.GREEN_TRIPS_NANO")
    nano_df.show(5)
except Exception as e:
    print(f"Expected failure: {type(e).__name__}")
    print("Spark 1.10.x cannot read Iceberg timestamp_ns.")

Semantic View & Cortex Agent

Repo shortcut: The complete script is assets/05_semantic_view.sql in your forked repo. You can run it directly in Snowsight. The inline code below is shown for learning — it explains each section of the DDL.

Why a Semantic View?

Create the Semantic View

USE ROLE DEMO_ADMIN;
USE WAREHOUSE DEMO_WH;
USE DATABASE ICEBERG_DEMO;
USE SCHEMA PUBLIC;

CREATE OR REPLACE SEMANTIC VIEW nyc_taxi_analytics

  TABLES (
    trips AS ICEBERG_DEMO.PUBLIC.YELLOW_TRIPS
      WITH SYNONYMS ('yellow taxi', 'yellow cab', 'taxi trips', 'rides')
      COMMENT = 'NYC Yellow Taxi trip records from Jan 2024 to Feb 2026',

    zones AS ICEBERG_DEMO.PUBLIC.ZONE_LOOKUP
      PRIMARY KEY (LOCATIONID)
      WITH SYNONYMS ('taxi zones', 'locations', 'neighborhoods')
      COMMENT = 'NYC taxi zone lookup — maps LocationID to Borough and Zone name'
  )

  RELATIONSHIPS (
    pickup_zone AS
      trips (PULOCATIONID) REFERENCES zones (LOCATIONID)
  )

  FACTS (
    trips.fare AS fare_amount
      COMMENT = 'Base fare amount in USD',
    trips.tip AS tip_amount
      COMMENT = 'Tip amount in USD',
    trips.total AS total_amount
      COMMENT = 'Total charge including fare, tips, tolls, surcharges',
    trips.distance AS trip_distance
      COMMENT = 'Trip distance in miles',
    trips.passengers AS passenger_count
      COMMENT = 'Number of passengers in the vehicle',
    zones.borough_name AS Borough
      COMMENT = 'NYC borough name from zone lookup'
  )

  DIMENSIONS (
    trips.pickup_datetime AS tpep_pickup_datetime
      WITH SYNONYMS = ('pickup time', 'start time', 'trip start')
      COMMENT = 'Timestamp when the taxi meter was engaged',

    trips.dropoff_datetime AS tpep_dropoff_datetime
      WITH SYNONYMS = ('dropoff time', 'end time', 'trip end')
      COMMENT = 'Timestamp when the taxi meter was disengaged',

    trips.pickup_hour AS HOUR(tpep_pickup_datetime)
      WITH SYNONYMS = ('hour of day', 'time of day')
      COMMENT = 'Hour of pickup (0-23)',

    trips.pickup_date AS DATE(tpep_pickup_datetime)
      WITH SYNONYMS = ('trip date', 'date')
      COMMENT = 'Date of pickup',

    trips.pickup_month AS DATE_TRUNC('month', tpep_pickup_datetime)
      WITH SYNONYMS = ('month', 'trip month')
      COMMENT = 'Month of pickup',

    trips.pickup_day_of_week AS DAYNAME(tpep_pickup_datetime)
      WITH SYNONYMS = ('day of week', 'weekday')
      COMMENT = 'Day of week (Mon, Tue, etc.)',

    trips.payment_method AS
      CASE payment_type
        WHEN 1 THEN 'Credit Card'
        WHEN 2 THEN 'Cash'
        WHEN 3 THEN 'No Charge'
        WHEN 4 THEN 'Dispute'
        ELSE 'Unknown'
      END
      WITH SYNONYMS = ('payment type', 'how they paid')
      COMMENT = 'Human-readable payment method',

    zones.pickup_borough AS BOROUGH
      WITH SYNONYMS = ('borough', 'pickup area', 'neighborhood')
      COMMENT = 'NYC borough of pickup location',

    zones.pickup_zone_name AS "ZONE"
      WITH SYNONYMS = ('zone', 'pickup zone', 'pickup neighborhood')
      COMMENT = 'NYC taxi zone name of pickup'
  )

  METRICS (
    trips.total_trips AS COUNT(*)
      WITH SYNONYMS = ('trip count', 'number of rides', 'ride count', 'how many trips')
      COMMENT = 'Total number of taxi trips',

    trips.total_revenue AS SUM(trips.total)
      WITH SYNONYMS = ('revenue', 'total sales', 'earnings', 'income')
      COMMENT = 'Total revenue in USD (fare + tips + tolls + surcharges)',

    trips.avg_fare AS AVG(trips.fare)
      WITH SYNONYMS = ('average fare', 'mean fare', 'typical fare')
      COMMENT = 'Average base fare amount in USD',

    trips.avg_tip_pct AS AVG(trips.tip / NULLIF(trips.fare, 0)) * 100
      WITH SYNONYMS = ('tip percentage', 'tip rate', 'tipping rate', 'average tip percent')
      COMMENT = 'Average tip as a percentage of fare',

    trips.avg_distance AS AVG(trips.distance)
      WITH SYNONYMS = ('average distance', 'mean distance', 'typical trip length')
      COMMENT = 'Average trip distance in miles',

    trips.avg_passengers AS AVG(trips.passengers)
      WITH SYNONYMS = ('average passengers', 'riders per trip')
      COMMENT = 'Average number of passengers per trip'
  )

  COMMENT = 'NYC Yellow Taxi analytics — trip volumes, revenue, tipping, and geography. Covers Jan 2024 through Feb 2026.'

  AI_SQL_GENERATION 'Always round currency values to 2 decimal places. Always round percentages to 1 decimal place. When computing tip percentage, exclude rows where fare_amount is 0 or NULL. Default sort order is descending by the primary metric unless the user specifies otherwise.'

  AI_QUESTION_CATEGORIZATION 'This semantic view covers NYC yellow taxi trip data only. It does NOT contain weather data, green taxi data, or airport information. If asked about weather, precipitation, temperature, or green taxis, respond that you do not have that information available.'

  AI_VERIFIED_QUERIES (
    revenue_by_borough AS (
      QUESTION 'What is total revenue by borough?'
      ONBOARDING_QUESTION TRUE
      VERIFIED_BY '(STEWARD = demo_admin)'
      SQL 'SELECT
               __zones.pickup_borough,
               ROUND(SUM(__trips.total), 2) AS total_revenue
             FROM __trips
             LEFT JOIN __zones ON __trips.pulocationid = __zones.locationid
             GROUP BY __zones.pickup_borough
             ORDER BY total_revenue DESC'
    ),

    avg_fare_by_payment AS (
      QUESTION 'What is the average fare by payment method?'
      ONBOARDING_QUESTION TRUE
      VERIFIED_BY '(STEWARD = demo_admin)'
      SQL 'SELECT
               __trips.payment_method,
               ROUND(AVG(__trips.fare), 2) AS avg_fare
             FROM __trips
             GROUP BY __trips.payment_method
             ORDER BY avg_fare DESC'
    ),

    busiest_day_of_week AS (
      QUESTION 'Which day of the week has the most trips?'
      ONBOARDING_QUESTION TRUE
      VERIFIED_BY '(STEWARD = demo_admin)'
      SQL 'SELECT
               __trips.pickup_day_of_week,
               COUNT(*) AS total_trips
             FROM __trips
             GROUP BY __trips.pickup_day_of_week
             ORDER BY total_trips DESC'
    ),

    tip_pct_by_borough AS (
      QUESTION 'What is the average tip percentage by borough?'
      VERIFIED_BY '(STEWARD = demo_admin)'
      SQL 'SELECT
               __zones.pickup_borough,
               ROUND(AVG(__trips.tip / NULLIF(__trips.fare, 0)) * 100, 1) AS avg_tip_pct
             FROM __trips
             LEFT JOIN __zones ON __trips.pulocationid = __zones.locationid
             WHERE __trips.fare > 0
             GROUP BY __zones.pickup_borough
             ORDER BY avg_tip_pct DESC'
    ),

    monthly_trip_volume AS (
      QUESTION 'Show me monthly trip volume over time'
      VERIFIED_BY '(STEWARD = demo_admin)'
      SQL 'SELECT
               __trips.pickup_month,
               COUNT(*) AS total_trips
             FROM __trips
             GROUP BY __trips.pickup_month
             ORDER BY __trips.pickup_month'
    )
  );

Verify the Semantic View

DESCRIBE SEMANTIC VIEW nyc_taxi_analytics;
SHOW SEMANTIC DIMENSIONS IN nyc_taxi_analytics;
SHOW SEMANTIC METRICS IN nyc_taxi_analytics;

Test with a Direct Semantic Query

SELECT * FROM SEMANTIC_VIEW(
  nyc_taxi_analytics
  METRICS trips.total_trips, trips.total_revenue
  DIMENSIONS zones.pickup_borough
) ORDER BY total_revenue DESC;

Create the Cortex Agent

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE AGENT nyc_taxi_agent
  COMMENT = 'NYC Yellow Taxi trip analyst — answers questions about trip volumes, revenue, tips, and geography'
  FROM SPECIFICATION $$
tools:
  - tool_spec:
      type: cortex_analyst_text_to_sql
      name: taxi_analytics
      description: "Answers questions about NYC yellow taxi trips including revenue, trip counts, tip percentages, payment methods, boroughs, and time-based trends. Covers January 2024 through February 2026."
tool_resources:
  taxi_analytics:
    semantic_view: "ICEBERG_DEMO.PUBLIC.NYC_TAXI_ANALYTICS"
    execution_environment:
      type: warehouse
      warehouse: "DEMO_WH"
$$;

GRANT USAGE ON AGENT ICEBERG_DEMO.PUBLIC.nyc_taxi_agent TO ROLE DEMO_ADMIN;
GRANT USAGE ON AGENT ICEBERG_DEMO.PUBLIC.nyc_taxi_agent TO ROLE DEMO_ANALYST;

Test the Agent

USE ROLE DEMO_ADMIN;
USE WAREHOUSE DEMO_WH;

SELECT TRY_PARSE_JSON(
  SNOWFLAKE.CORTEX.DATA_AGENT_RUN(
    'ICEBERG_DEMO.PUBLIC.NYC_TAXI_AGENT',
    $${ 
      "messages": [
        {
          "role": "user",
          "content": [{"type": "text", "text": "What is total revenue by borough?"}]
        }
      ]
    }$$
  )
) AS resp;

Try it in Snowflake Intelligence

How guardrails work: The agent's guardrails are not magic — they come from AI_QUESTION_CATEGORIZATION in the semantic view DDL. You define what the AI should and should not answer.

RBAC Enforcement Demo

Create a Denied Role

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS DEMO_SV_DENIED
  COMMENT = 'Role with no semantic view or table access — for RBAC demo';
GRANT ROLE DEMO_SV_DENIED TO ROLE ACCOUNTADMIN;
GRANT USAGE ON WAREHOUSE DEMO_WH TO ROLE DEMO_SV_DENIED;
GRANT USAGE ON DATABASE ICEBERG_DEMO TO ROLE DEMO_SV_DENIED;
GRANT USAGE ON SCHEMA ICEBERG_DEMO.PUBLIC TO ROLE DEMO_SV_DENIED;

Verify Access is Denied

USE ROLE DEMO_SV_DENIED;
USE SECONDARY ROLES NONE;
USE WAREHOUSE DEMO_WH;

SELECT * FROM SEMANTIC_VIEW(
  nyc_taxi_analytics
  METRICS trips.total_trips
  DIMENSIONS zones.pickup_borough
);
-- Expected: Insufficient privileges error

Restore Admin Context

USE ROLE DEMO_ADMIN;
USE SECONDARY ROLES ALL;
USE WAREHOUSE DEMO_WH;

Try it yourself: Switch to the DEMO_SV_DENIED role in Snowflake Intelligence and ask the agent a question. It will fail. RBAC applies to every access path — SQL, API, and AI.

Explore with Cortex Code (Optional)

How to Use

Data Engineering Prompts

"What tables exist in ICEBERG_DEMO.PUBLIC? Describe each one including
 row counts, column types, and whether they are Iceberg tables."

"Explain the difference between CATALOG = SNOWFLAKE and using an external
 volume with a customer-managed bucket. What are the tradeoffs?"

"Create a new Iceberg table called trip_summary that aggregates yellow_trips
 by pickup date and borough with total trips, revenue, and average fare."

Streaming & VARIANT Prompts

"Explore the nyc_weather_ssv2 table. Show me how to extract hourly
 temperature and precipitation from the weather_data VARIANT column for JFK."

"Write a query that finds the rainiest days at each airport station using
 the VARIANT data in nyc_weather_ssv2. Flatten the hourly array."

"Write a query that joins nyc_weather_ssv2 weather data with yellow_trips
 to see if rainy days have lower tip percentages."

Governance Prompts

"Explain what masking and row-level security policies are applied in
 ICEBERG_DEMO.PUBLIC. Which tables and columns are protected?"

"Show me how to verify that the borough_rls policy is working. Write
 queries that demonstrate filtered vs unfiltered results by role."

"What is the difference between Path A and Path B when Databricks reads
 through Horizon? Explain the governance enforcement mechanism."

Semantic View & Agent Prompts

"Show me the semantic view nyc_taxi_analytics. What dimensions, metrics,
 and verified queries does it define?"

"Add a new metric to the nyc_taxi_analytics semantic view called
 avg_trip_duration that calculates average minutes between pickup and dropoff."

"Write a new verified query (VQR) for the semantic view that answers:
 What is the average trip distance by hour of day?"

Open-Ended Exploration

"Build a Streamlit dashboard that shows a bar chart of total trips by
 borough and a line chart of daily revenue trends from yellow_trips."

"Compare yellow_trips and green_trips — what are the differences in schema,
 row counts, and trip patterns? Which boroughs does each serve?"

"Write a data quality check that validates yellow_trips has no future
 pickup dates, no negative fares, and no null location IDs."

Conclusion and Resources

Cleanup

What You Learned

Related Resources