Overview

What is Snowflake Cortex AI?

What You Will Learn

What You Will Build

Prerequisites

Setup

Create Table and Load Data

USE ROLE ACCOUNTADMIN;

CREATE DATABASE IF NOT EXISTS DASH_DB;
CREATE SCHEMA IF NOT EXISTS DASH_SCHEMA;
CREATE WAREHOUSE IF NOT EXISTS DASH_XS_WH WAREHOUSE_SIZE=XSMALL;

USE DASH_DB.DASH_SCHEMA;
USE WAREHOUSE DASH_XS_WH;

CREATE or REPLACE file format csvformat
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  type = 'CSV';

CREATE or REPLACE stage call_transcripts_data_stage
  file_format = csvformat
  url = 's3://sfquickstarts/misc/call_transcripts/';

CREATE or REPLACE table CALL_TRANSCRIPTS ( 
  date_created date,
  language varchar(60),
  country varchar(60),
  product varchar(60),
  category varchar(60),
  damage_type varchar(90),
  transcript varchar
) COMMENT = '{"origin":"sf_sit-is", "name":"aiml_notebooks_cortex_ai", "version":{"major":1, "minor":0}, "attributes":{"is_quickstart":1, "source":"sql"}}';

COPY into CALL_TRANSCRIPTS
  from @call_transcripts_data_stage;

IMPORTANT: If you use different names for objects created in this section, be sure to update scripts and code in the following sections accordingly.

Task Specific LLM Functions

Translate

select snowflake.cortex.translate('wie geht es dir heute?','de_DE','en_XX');

Batch mode

select transcript,snowflake.cortex.translate(transcript,'de_DE','en_XX') from call_transcripts where language = 'German';

Sentiment Score

select transcript, snowflake.cortex.sentiment(transcript) from call_transcripts where language = 'English';

Summarize

select transcript,snowflake.cortex.summarize(transcript) as summary from call_transcripts where language = 'English' limit 1;

Summary with tokens count

select transcript,snowflake.cortex.summarize(transcript) as summary,snowflake.cortex.count_tokens('summarize',transcript) as number_of_tokens from call_transcripts where language = 'English' limit 1;

NOTE: Snowflake Cortex LLM functions incur compute cost based on the number of tokens processed. Refer to the documentation for more details on each function’s cost in credits per million tokens.

Prompt Engineering

ALTER ACCOUNT SET CORTEX_ENABLED_CROSS_REGION = 'ANY_REGION';

SET prompt = 
'### 
Summarize this transcript in less than 200 words. 
Put the product name, defect and summary in JSON format. 
###';

select snowflake.cortex.complete('claude-4-sonnet',concat('[INST]',$prompt,transcript,'[/INST]')) as summary
from call_transcripts where language = 'English' limit 1;

{
    "product": "XtremeX helmets",
    "defect": "Broken buckles that won't secure the helmet properly",
    "summary": "Jessica Turner from Mountain Ski Adventures contacted customer service regarding a recent order (order #68910) of XtremeX helmets. Upon inspection, they discovered that 10 helmets had broken buckles that wouldn't secure properly. The customer service agent apologized for the inconvenience and offered either a refund or replacement. Jessica requested replacements since they still needed the helmets for their customers. The agent processed a replacement order for 10 new XtremeX helmets with functioning buckles, which will be expedited and should arrive within 3-5 business days. The issue was resolved satisfactorily with the customer expressing appreciation for the assistance."
}

Streamlit Application

Setup

import streamlit as st
from snowflake.snowpark.context import get_active_session

st.set_page_config(layout='wide')
session = get_active_session()

def summarize():
    with st.container():
        st.header("JSON Summary With Snowflake Cortex AI")
        entered_text = st.text_area("Enter text",label_visibility="hidden",height=400,placeholder='Enter text. For example, a call transcript.')
        btn_summarize = st.button("Summarize",type="primary")
        if entered_text and btn_summarize:
            entered_text = entered_text.replace("'", "\\'")
            prompt = f"Summarize this transcript in less than 200 words. Put the product name, defect if any, and summary in JSON format: {entered_text}"
            cortex_prompt = "'[INST] " + prompt + " [/INST]'"
            cortex_response = session.sql(f"select snowflake.cortex.complete('claude-4-sonnet', {cortex_prompt}) as response").to_pandas().iloc[0]['RESPONSE']
            st.write(cortex_response)

def translate():
    supported_languages = {'German':'de','French':'fr','Korean':'ko','Portuguese':'pt','English':'en','Italian':'it','Russian':'ru','Swedish':'sv','Spanish':'es','Japanese':'ja','Polish':'pl'}
    with st.container():
        st.header("Translate With Snowflake Cortex AI")
        col1,col2 = st.columns(2)
        with col1:
            from_language = st.selectbox('From',dict(sorted(supported_languages.items())))
        with col2:
            to_language = st.selectbox('To',dict(sorted(supported_languages.items())))
        entered_text = st.text_area("Enter text",label_visibility="hidden",height=300,placeholder='Enter text. For example, a call transcript.')
        btn_translate = st.button("Translate",type="primary")
        if entered_text and btn_translate:
          entered_text = entered_text.replace("'", "\\'")
          cortex_response = session.sql(f"select snowflake.cortex.translate('{entered_text}','{supported_languages[from_language]}','{supported_languages[to_language]}') as response").to_pandas().iloc[0]['RESPONSE']
          st.write(cortex_response)

def sentiment_analysis():
    with st.container():
        st.header("Sentiment Analysis With Snowflake Cortex AI")
        entered_text = st.text_area("Enter text",label_visibility="hidden",height=400,placeholder='Enter text. For example, a call transcript.')
        btn_sentiment = st.button("Sentiment Score",type="primary")
        if entered_text and btn_sentiment:
          entered_text = entered_text.replace("'", "\\'")
          cortex_response = session.sql(f"select snowflake.cortex.sentiment('{entered_text}') as sentiment").to_pandas().iloc[0]['SENTIMENT']
          st.text(f"Sentiment score: {cortex_response}")
          st.caption("Note: Score is between -1 and 1; -1 = Most negative, 1 = Positive, 0 = Neutral")  

page_names_to_funcs = {
    "JSON Summary": summarize,
    "Translate": translate,
    "Sentiment Analysis": sentiment_analysis,
}

selected_page = st.sidebar.selectbox("Select", page_names_to_funcs.keys())
page_names_to_funcs[selected_page]()

Run

Sample Transcript

Customer: Hello!
Agent: Hello! I hope you're having a great day. To best assist you, can you please share your first and last name and the company you're calling from?
Customer: Sure, I'm Michael Green from SnowSolutions.
Agent: Thanks, Michael! What can I help you with today?
Customer: We recently ordered several DryProof670 jackets for our store, but when we opened the package, we noticed that half of the jackets have broken zippers. We need to replace them quickly to ensure we have sufficient stock for our customers. Our order number is 60877.
Agent: I apologize for the inconvenience, Michael. Let me look into your order. It might take me a moment.
Customer: Thank you.

Update LLM

End-to-End Application

NOTE: Before running the cells in the notebook, make sure of the prerequisites listed below.

Prerequisites

Table of Contents

Conclusion And Resources

What You Learned

Related Resources