Overview

Prerequisites

What You’ll Need

What You’ll Learn

What You’ll Build

Setup Data

Overview

Creating Objects, Loading Data, and Joining Data

Upload Images and Audio File to Stage

Setup Notebook

Overview

Translate

Overview

Hear what your international customers are saying

Python

# Conditionally translate reviews that are not english using Cortex Translate
reviews_df = reviews_df.withColumn('TRANSLATED_REVIEW',when(F.col('LANGUAGE') != F.lit("en"), \
                                                            cortex.translate(F.col('REVIEW'), \
                                                                            F.col('LANGUAGE'), \
                                                                            "en")) \
                                  .otherwise(F.col('REVIEW')))

reviews_df.filter(F.col('LANGUAGE') != F.lit("en")).select(["REVIEW","LANGUAGE","TRANSLATED_REVIEW"]).show(3)

SQL

 -- Add the TRANSLATED_REVIEW column with conditional translation
 WITH TRANSLATED_REVIEWS AS (
     SELECT 
         REVIEW,
         LANGUAGE,
         CASE 
             WHEN LANGUAGE != 'en' THEN SNOWFLAKE.CORTEX.TRANSLATE(REVIEW, LANGUAGE, 'en') 
             ELSE REVIEW
         END AS TRANSLATED_REVIEW
     FROM TRUCK_REVIEWS_V
 )

 -- Filter rows where the LANGUAGE is not English and select the desired columns
 SELECT 
     REVIEW, 
     LANGUAGE, 
     TRANSLATED_REVIEW
 FROM TRANSLATED_REVIEWS
 WHERE LANGUAGE != 'en'
 LIMIT 3;

Summarize reviews

Overview

We want to get a insight on what people are saying

Python

summarized_reviews_df = session.table("TRUCK_REVIEWS_V") \
    .group_by("TRUCK_BRAND_NAME") \
    .agg(ai_summarize_agg(F.col("REVIEW")).alias("SUMMARY"))

summarized_reviews_df.select(["TRUCK_BRAND_NAME", "SUMMARY"]).show(3)

SQL

WITH SUMMARIZED_REVIEWS AS (
    SELECT 
        TRUCK_BRAND_NAME,
        AI_SUMMARIZE_AGG(REVIEW) AS SUMMARY
    FROM TRUCK_REVIEWS_V
    GROUP BY TRUCK_BRAND_NAME
)
SELECT * FROM SUMMARIZED_REVIEWS;

Categorize reviews

Overview

Get intention to recommend based on review with Cortex AI_CLASSIFY

Python

# To understand whether a customer would recommend food truck based on their review 
reviews_df = reviews_df.withColumn('RECOMMEND', ai_classify(prompt("Tell me based on the following food truck customer review {0}, will they recommend the food truck to their friends and family?", F.col('REVIEW')),["Likely","Unlikely","Unsure"])["labels"][0])

reviews_df.select(["REVIEW","CLEAN_RECOMMEND"]).show(3)

SQL

WITH CLASSIFIED_REVIEWS AS (
    SELECT 
        REVIEW,
        AI_CLASSIFY(
            REVIEW, 
            ['Likely', 'Unlikely', 'Unsure'], 
            OBJECT_CONSTRUCT('task_description', 
                'Tell me based on the following food truck customer review, will they recommend the food truck to their friends and family?'
            )
        ):labels[0]::TEXT AS RECOMMEND
    FROM TRUCK_REVIEWS_V
)
SELECT * FROM CLASSIFIED_REVIEWS LIMIT 3;

Leverage an LLM

Overview

Answer specific questions you have

Python

  question = "What is the number one dish positively mentioned in the feedback?"

  summarized_reviews_df = session.table("CONCATENATED_REVIEWS").select(
      F.col("TRUCK_BRAND_NAME"),
      ai_complete(
          "openai-gpt-4.1",
          F.concat(
              F.lit("Context: "),
              F.col("ALL_REVIEWS_TEXT"),
              F.lit(f" Question: {question} Answer briefly and concisely and only name the dish:")
          )
      ).alias("NUMBER_ONE_DISH")
  )

  summarized_reviews_df.show(3)

SQL

  -- Gain Learnings from a specific question
  WITH GAIN_LEARNINGS AS (
      SELECT 
          TRUCK_BRAND_NAME,
          AI_COMPLETE(
            'openai-gpt-4.1', 
            'Context:' || ALL_REVIEWS_TEXT || ' Question: What is the number one dish positively mentioned in the feedback? Answer briefly and concisely and only name the dish:'
        ) AS NUMBER_ONE_DISH
      FROM CONCATENATED_REVIEWS
  )
  SELECT TRUCK_BRAND_NAME, NUMBER_ONE_DISH FROM GAIN_LEARNINGS LIMIT 3;

Analyze Images

Overview

  SELECT
    AI_COMPLETE (
      'claude-3-5-sonnet',
      'Please describe what you see in this image.',
      TO_FILE ('@TB_VOC.MEDIA.IMAGES', IMAGE_PATH)
    ) AS IMAGE_DESCRIPTION
  FROM
    TB_VOC.MEDIA.IMAGE_TABLE
  LIMIT
    1;

Transcription

Overview

  SELECT
    AI_TRANSCRIBE (
      TO_FILE ('@TB_VOC.MEDIA.AUDIO', AUDIO_PATH)
    ) AS TRANSCRIPTION_RESULT
  FROM
    TB_VOC.MEDIA.AUDIO_TABLE
  LIMIT
    1;

Understand sentiment

Overview

Understand sentiment with Cortex Sentiment/AI_SENTIMENT

Python

# Understand the sentiment of customer review using Cortex Sentiment
reviews_df = reviews_df.withColumn('SENTIMENT', cortex.sentiment(F.col('REVIEW')))

reviews_df.select(["REVIEW","SENTIMENT"]).show(3)

SQL

SELECT 
    REVIEW, 
    AI_SENTIMENT(REVIEW) AS SENTIMENT
FROM TRUCK_REVIEWS_V
LIMIT 3;

Conclusion And Resources

Overview

What You Learned

Resources