From Zero to Agents: Building End-to-End Data Pipelines for an AI Agent

Snowflake for Developers GuidesFrom Zero to Agents: Building End-to-End Data Pipelines for an AI Agent

Quickstart

From Zero to Agents: Building End-to-End Data Pipelines for an AI Agent

Neelima Parakala

Overview

This hands-on lab guides you through transforming raw data into actionable insights using Snowflake Cortex AI for sentiment analysis and classification. You will get hands-on experience with Cortex AI and Dynamic Tables to automate AI enrichment and construct a reliable semantic layer. Learn the architecture required to create a Marketing & Sales Intelligence Agent that leverages both structured data and enriched unstructured feedback while maintaining enterprise security standards.

What You'll Learn

How to use Cortex AI functions for sentiment analysis and text classification
How to create Dynamic Tables for automated AI enrichment pipelines
How to build a Semantic View to map technical columns to business terms
How to create and configure an AI Agent with Analyst and Search tools
How to implement Role-Based Access Control (RBAC) with Dynamic Data Masking

What You'll Build

An automated AI enrichment pipeline using Cortex AI and Dynamic Tables
A Semantic View that provides business-friendly abstractions over your data
A Marketing & Sales Intelligence Agent powered by Cortex Analyst and Cortex Search
Enterprise-grade security controls with masking policies

Prerequisites

A Snowflake account with a role that has the ability to create database, schema, tables, and virtual warehouse objects
Basic familiarity with SQL
Access to Snowsight (Snowflake's web interface)

Environment and Data Loading

Imagine you are on a marketing team at a fictional retail company selling sports and outdoor gear. The company has plenty of data from campaign performance from paid ads to customer feedback.

The problem the team faces:

Campaign performance data is siloed from customer feedback
Customer feedback is unstructured and unanalyzed
Analysts can’t self-serve insights from these data

What you’ll build to solve these issues:

Cortex AI turns raw transcripts into structured sentiment scores
Dynamic Tables automate so that data stays current as new feedback arrives
Semantic View bridges campaign metrics and customer feedback into one model
Snowflake Intelligence that allows analysts to ask natural language questions

Setup Instructions

Establish the secure foundation using the provided setup.sql script. This script automates the creation of roles, warehouses, and the ingestion of marketing data from public S3 buckets.

Run setup.sql in a Snowsight SQL Worksheet.

What Gets Provisioned

Infrastructure: Provisions the dash_wh_si warehouse and snowflake_intelligence database
Ingestion: Loads structured metrics (campaign spend, product sales) and unstructured transcripts (support_cases)

AI Enrichment with Cortex AI

AI Enrichment with Cortex AI refers to the process of using built-in generative AI functions directly within standard SQL queries to analyze, transform, and add value to your data.

In Snowflake, this is powered by Snowflake Cortex AI, which allows you to run high-performance Large Language Models (LLMs) without moving your data to an external service or managing complex infrastructure.

How It Works

Traditionally, if you wanted to perform sentiment analysis or extract names from text, you had to export data to a Python environment or a third-party API. With Cortex AI Functions, the LLM is treated like any other SQL function (e.g., SUM() or AVG()).

Core Cortex AI Functions

Snowflake provides several task-specific functions that make enrichment simple:

Function	Description
`SNOWFLAKE.CORTEX.AI_SENTIMENT(text)`	Returns a score from -1 (very negative) to 1 (very positive)
`SNOWFLAKE.CORTEX.AI_SUMMARIZE(text)`	Creates a concise summary of long documents or transcripts
`SNOWFLAKE.CORTEX.AI_EXTRACT_ANSWER(text, question)`	Finds a specific answer within a block of unstructured text
`SNOWFLAKE.CORTEX.AI_CLASSIFY(text, categories)`	Buckets text into predefined labels (e.g., "Refund," "Complaint," "Sales Inquiry")
`SNOWFLAKE.CORTEX.AI_TRANSLATE(text, source, target)`	Translates text between languages

Why Use AI Enrichment?

Scalability: Process millions of rows of unstructured text using Snowflake's elastic compute (Warehouses)
Security: Your data never leaves the Snowflake security perimeter, which is vital for GDPR/HIPAA compliance
Automation: By putting these functions inside Dynamic Tables, your AI enrichment happens automatically as new data arrives
Low Friction: Analysts who know SQL can now perform advanced "Data Science" tasks without learning Python or PyTorch

Integration with AI Agents

AI Enrichment is the "Preprocessing" step. By enriching your data first:

The Agent doesn't have to read every raw transcript
It can simply query the sentiment_score column to find unhappy customers
This makes the agent faster, cheaper, and more accurate

Extract Trends

Transform "dark data" (raw transcripts) into analytical trends using Cortex AI Functions. Run sentiment analysis on customer feedback to create measurable features:

USE DATABASE DASH_DB_SI;
USE SCHEMA RETAIL;
UPDATE support_cases SET product = 'Fitness Wear' WHERE product = 'ThermoJacket Pro';
SELECT 
    title,
    SNOWFLAKE.CORTEX.AI_SENTIMENT(transcript) AS sentiment_score,
    SNOWFLAKE.CORTEX.AI_CLASSIFY(transcript, ['Return', 'Quality', 'Shipping']) AS issue_category
FROM support_cases;

Updating Marketing Data

Confirm that the role is set to SNOWFLAKE_INTELLIGENCE_ADMIN by clicking on your profile in the bottom left corner.

Download the marketing_data.csv
Navigate to Catalog > Database Explorer
Open DASH_DB_SI.RETAIL.Tables.MARKETING_CAMPAIGN_METRICS. If you do not see the database, refresh the data.
Click on Load Data in the top right hand corner
Upload the marketing_data.csv and click next then load
Click View table detail to see the new data uploaded

Create Live Enrichment Pipeline

To create a "Live" enrichment pipeline, we combine Cortex AI Functions with Dynamic Tables. This allows Snowflake to automatically process new data as it arrives, keeping your sentiment scores and classifications up to date without manual intervention.

The Architecture of Live Enrichment

Instead of running a one-time enrichment, you define a Dynamic Table. This table acts as a materialized view that continuously "listens" for changes in your raw data (e.g., new customer feedback) and runs the AI functions only on the new or changed rows.

Create the Enriched Dynamic Table

Create a Dynamic Table that automatically joins campaign metrics with AI-generated sentiment scores:

USE ROLE SNOWFLAKE_INTELLIGENCE_ADMIN;
USE DATABASE DASH_DB_SI;
USE SCHEMA RETAIL;
CREATE OR REPLACE DYNAMIC TABLE enriched_marketing_intelligence
TARGET_LAG = '1 hours'
WAREHOUSE = dash_wh_si
AS
SELECT m.campaign_name, m.clicks, s.product AS product_name,
       SNOWFLAKE.CORTEX.SENTIMENT(s.transcript) AS avg_sentiment
FROM marketing_campaign_metrics m
JOIN support_cases s ON m.category = s.product;

Confirm the creation of the Dynamic Table by navigating to Catalog > Database Explorer > DASH_DB_SI.RETAIL.DYNAMIC TABLES.ENRICHED_MARKETING_INTELLIGENCE. If you do not see your table, click on the refresh button and the table should appear.

Semantic Layer and Agent Creation

Provide the agent with a "map" to understand your business logic through a Semantic View.

Create a Cortex Analyst

This tool enables the agent to query structured data in Snowflake by generating SQL. It relies on semantic views, which are mappings between business concepts (e.g., "product name," "sales") and the underlying tables and columns in your Snowflake account. This abstraction helps the LLM understand how to query your data effectively, even if your tables have complex or arbitrary naming conventions.

Navigate to AI & ML > Analyst in Snowsight then click Create with Autopilot in the top right
At the top, confirm that the role is set to SNOWFLAKE_INTELLIGENCE_ADMIN and warehouse is set to DASH_WH_SI
Click Skip on the Provide context page
Configure the following settings:
- Name: SEMANTIC_VIEW
- Location to store: DASH_DB_SI.Retail
- Select tables: Select all tables DASH_DB_SI.Retail (there should be 5 tables and 1 dynamic table)
- Select Columns: Select all columns
Click Create
Scroll to the MARKETING_CAMPAIGN_METRICS section and click Edit
Set + Primary Key to Category and click Save

Scroll down and click + on Relationships
Configure the following settings:
- From Table: ENRICHED_MARKETING_INTELLIGENCE
- To Table: MARKETING_CAMPAIGN_METRICS
- Relationship Type: Many to One
- From Column: PRODUCT_NAME
- To Column: CATEGORY
Add the relationship, then save the Analyst in the top right corner

Create a Cortex Search Service

This tool allows the agent to search and retrieve information from unstructured text data, such as customer support tickets, Slack conversations, or contracts. It leverages Cortex Search to index and query these text "chunks," enabling the agent to perform Retrieval Augmented Generation (RAG).

Navigate to AI & ML > Search in Snowsight
Confirm that role is set to SNOWFLAKE_INTELLIGENCE_ADMIN
Select Create
Configure the following settings:
- Service database & schema: DASH_DB_SI.Retail
- Service name: campaign_search
- Select data: marketing_campaign_metrics
- Select search column: campaign name
- Select attribute: Select all
- Select columns: Select all
- Warehouse for indexing: DASH_WH_SI
Click create and click the refresh icon in the top right corner. Serving will update from INITALIZING to ACTIVE

Create the Agent

An agent is an intelligent entity within Snowflake Intelligence that acts on behalf of the user. Agents are configured with specific tools and orchestration logic to answer questions and perform tasks on top of your data.

Navigate to AI & ML > Agents in Snowsight
On the top right, click on Create agent
- Database and schema: DASH_DB_SI.Retail
- Agent object name: MarketingAgent
Create the agent

Add Instructions:

Configure to the following:

Description: I am a specialized Marketing & Sales Intelligence Assistant. My primary role is to provide accurate, data-driven insights by analyzing structured marketing metrics (spend, clicks, conversions) and unstructured customer feedback (support transcripts). I bridge the gap between 'what happened' (the numbers) and 'why it happened' (customer sentiment). Always maintain a professional, analytical tone and provide clear citations for information retrieved from support transcripts.
Example question: What are the top 5 campaigns by clicks?

Add Tools

Tools are the capabilities an agent can use to accomplish a task. Think of them as the agent's skillset and note that you can add one or more of each of the following tools.

Cortex Analyst

Click on + Add > Add semantic view
Configure the following settings:

Service database & schema: DASH_DB_SI.Retail
Select semantic view: SEMANTIC_VIEW
Name: semantic_view
Description:

Retail analytics semantic view in DASH_DB_SI.RETAIL connecting 6 tables:
- ENRICHED_MARKETING_INTELLIGENCE: Campaign performance + sentiment (CAMPAIGN_NAME, PRODUCT_NAME, CLICKS, AVG_SENTIMENT)
- MARKETING_CAMPAIGN_METRICS: Campaign KPIs by category/date (CAMPAIGN_NAME, CATEGORY, CLICKS, IMPRESSIONS, DATE)
- PRODUCTS: Product catalog (PRODUCT_ID, PRODUCT_NAME, CATEGORY)
- SALES: Sales transactions by region (PRODUCT_ID, REGION, UNITS_SOLD, SALES_AMOUNT, DATE)
- SOCIAL_MEDIA: Influencer mentions by platform/category (CATEGORY, INFLUENCER, MENTIONS, PLATFORM, DATE)
- SUPPORT_CASES: Customer support interactions (ID, PRODUCT, TITLE, TRANSCRIPT, DATE)
Key joins: ENRICHED_MARKETING_INTELLIGENCE.PRODUCT_NAME → MARKETING_CAMPAIGN_METRICS.CATEGORY; PRODUCTS.PRODUCT_ID → SALES.PRODUCT_ID. Enables end-to-end analysis from marketing exposure → sales performance → post-sale support.
Warehouse: Custom > DASH_WH_SI
Query timeout: 60

Cortex Search Services

Click on +Add
Configure the following settings:

Service database & schema: DASH_DB_SI.Retail
Cortex Search Services: DASH_DB_SI.RETAIL.CAMPAIGN_SEARCH
Max Results: 4
ID Column: CAMPAIGN_NAME
Title Column: CLICKS
Name: Search

Custom tools

Click on +Add
Configure the following settings:

Resource type: procedure
Service database & schema: DASH_DB_SI.Retail
Custom tool identifier: DASH_DB_SI.RETAIL.SEND_EMAIL()
Name: Send_Email
Warehouse: Custom > DASH_WH_SI
Query timeout: 60
Paramter: body
- Description: Use HTML-Syntax for this. If the content you get is in markdown, translate it to HTML. If body is not provided, summarize the last question and use that as content for the email.
Paramter: recipient_email
- Description: If the email is not provided, send it to the current user's email address.
Paramter: subject
- Description: If the subject is not provided, use "Snowflake Intelligence".

Orchestration Instructions: Whenever you can answer visually with a chart, always choose to generate a chart even if the user didn't specify to.

Access: SNOWFLAKE_INTELLIGENCE_ADMIN

Click Save in the top right corner

Validation

Interact with the agent and verify that it respects enterprise-grade governance.

Accessing the Agent

Once your agent is created and enabled for Snowflake Intelligence, it appears in a dedicated workspace:

In Snowsight, go to AI & ML > Snowflake Intelligence
You will see a list of agents you have access to
Select your MarketingAgent

The Conversational Interface

The UI is designed to handle natural language queries. It doesn't just return data; it provides a narrative response.

Natural Language Input: Users type questions like, "Which campaigns had a low budget but high customer sentiment last month?"
Suggested Prompts: Based on your Semantic View and Agent Description, the UI often provides "starter" questions to guide the user
Multimodal Answers: The agent will combine text (summarizing feedback from Cortex Search) with tables or charts (visualizing metrics from Cortex Analyst)

Let's ask the following questions

Q1. What are the top 5 campaigns by clicks?

Q2. Show me all campaign performance metrics and it's relationship to the product

Q3. What is the relationship between campaign clicks and customer satisfaction by category?

Q4. What are the main customer complains in support cases?

Security (optional)

Trace Monitoring

For lab participants, the Trace is the most critical UI interaction. It allows you to debug the agent's logic:

The Plan: The UI displays how the agent broke down your prompt (e.g., "Step 1: Identify campaigns with budget < 5000. Step 2: Retrieve sentiment scores for those campaigns.")
Tool Selection: You can see which tool was invoked for which step—whether it chose the Analyst for the budget or Search for the "Why"
Reflection: You can see if the agent caught its own errors and re-ran a query to get a better result

Security Showcase

Apply Dynamic Data Masking to specific metrics to ensure the agent respects RBAC (Role-Based Access Control).

Create the Marketing Role and Granting Access

If you ran the setup.sql exactly as provided in the GitHub repo, it created snowflake_intelligence_admin. Let's create the marketing_intelligence_role to represent your "Restricted Team" and give it the necessary access. Be sure to have your role set to snowflake_intelligence_admin and warehouse to DASH_WH_SI.

USE DATABASE DASH_DB_SI;
USE SCHEMA RETAIL;

-- Create the Semantic View
CREATE OR REPLACE SECURE VIEW marketing_intelligence_view AS
SELECT 
    campaign_name AS "Ad Campaign",
    category AS "Product Category",
    clicks AS "Engagement Clicks",
    -- avg_sentiment will come from the Dynamic Table
    0 AS "Customer Sentiment Score" 
FROM marketing_campaign_metrics;

-- Check which roles have access to your database and schema
SHOW GRANTS ON DATABASE dash_db_si;
SHOW GRANTS ON SCHEMA dash_db_si.retail;

-- Check specifically for your Semantic View
SHOW GRANTS ON VIEW marketing_intelligence_view;

USE ROLE snowflake_intelligence_admin;
USE WAREHOUSE dash_wh_si;

-- You should see the actual budget/click numbers
SELECT * FROM marketing_intelligence_view LIMIT 5;

USE ROLE ACCOUNTADMIN;

-- Create the role
CREATE OR REPLACE ROLE marketing_intelligence_role;

-- Grant usage on the warehouse and database
GRANT USAGE ON WAREHOUSE dash_wh_si TO ROLE marketing_intelligence_role;
GRANT USAGE ON DATABASE dash_db_si TO ROLE marketing_intelligence_role;
GRANT USAGE ON SCHEMA dash_db_si.retail TO ROLE marketing_intelligence_role;

-- Grant access to the AI functions and the specific views
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE marketing_intelligence_role;
GRANT SELECT ON VIEW dash_db_si.retail.marketing_intelligence_view TO ROLE marketing_intelligence_role;

-- Assign to yourself for testing
SET current_user = CURRENT_USER();
GRANT ROLE marketing_intelligence_role TO USER IDENTIFIER($CURRENT_USER);

Apply the Masking Policy

A masking policy exists in Snowflake but isn't "active" until it is applied to a specific column. We will mask the CLICKS column so the marketing role sees 0 while the admin sees the real data.

USE ROLE snowflake_intelligence_admin;
USE SCHEMA dash_db_si.retail;

-- 1. Create the policy
CREATE OR REPLACE MASKING POLICY mask_engagement_clicks AS (val number) 
RETURNS number ->
  CASE 
    WHEN CURRENT_ROLE() IN ('SNOWFLAKE_INTELLIGENCE_ADMIN', 'ACCOUNTADMIN') THEN val 
    ELSE 0 -- Masked value for other roles
  END;

-- 2. Apply the policy to the base table column
ALTER TABLE marketing_campaign_metrics MODIFY COLUMN clicks SET MASKING POLICY mask_engagement_clicks;

-- 3. Verify the policy is active
SELECT * FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    policy_name => 'mask_engagement_clicks'
));

Verify as Admin

-- Re-run the view to ensure it picks up the table-level masking
CREATE OR REPLACE SECURE VIEW marketing_intelligence_view AS
SELECT 
    campaign_name AS "Ad Campaign",
    category AS "Product Category",
    clicks AS "Engagement Clicks",
    -- avg_sentiment will come from the Dynamic Table
    0 AS "Customer Sentiment Score" 
FROM marketing_campaign_metrics;

USE ROLE snowflake_intelligence_admin;
USE SCHEMA dash_db_si.retail;
-- You should see numbers like 11103
SELECT "Ad Campaign", "Engagement Clicks" FROM marketing_intelligence_view;

Verify as Marketing Role

USE ROLE marketing_intelligence_role;
USE SCHEMA dash_db_si.retail;
-- You should see 0 for all clicks
SELECT "Ad Campaign", "Engagement Clicks" FROM marketing_intelligence_view;

By applying Dynamic Data Masking alongside RBAC, you ensured that the data serves different teams with appropriate access — admins see real click data while the marketing role sees masked values.

Conclusion And Resources

Congratulations! You've successfully built an end-to-end data pipeline for an AI agent in Snowflake. You transformed raw marketing data and unstructured customer feedback into actionable insights using Cortex AI, automated the enrichment process with Dynamic Tables, and created a governed Marketing Intelligence Agent.

What You Learned

How to use Cortex AI functions (AI_SENTIMENT, AI_CLASSIFY) for text analysis directly in SQL
How to create Dynamic Tables for automated, real-time AI enrichment
How to build Semantic Views that map technical schemas to business-friendly terms
How to configure AI Agents with Cortex Analyst and Cortex Search tools
How to implement enterprise security using Dynamic Data Masking and RBAC

Related Resources

Updated 2026-04-03

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances