Overview

What You'll Build

What You'll Learn

What You'll Need

Get the Data

Install the Marketplace Listing

Verify the Data

SELECT COUNT(*) as total_reviews FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS;
-- Expected: ~256,000 rows

SELECT COUNT(*) as total_products FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS;
-- Expected: ~2,700 rows

Understand the Schema

Positive : This is a free dataset — no cost to install and no time limit. The data is static (not live-updating), which is perfect for learning.

Set Up Hex

Create a New Hex Notebook

Connect to Snowflake

Open Threads

Positive : Every prompt in this guide is typed into Hex Threads. Threads generates the SQL using Snowflake Cortex AI Functions, adds it as a notebook cell, and runs it. You can inspect, edit, or re-run any generated cell.

Prompt 1: Explore the Data

The Prompt

"Show me the GE Profile Opal ice maker reviews. I want to see the star distribution and a sample of reviews across all ratings. The product ASIN is B0964BF4N7."

What Threads Generates

SELECT 
  p.TITLE as product,
  p.RATING as marketplace_rating,
  p.NUMBER_OF_RATINGS as total_ratings,
  p.BREADCRUMBS as category_path,
  COUNT(r.ID) as reviews_in_dataset,
  ROUND(AVG(r.STARS), 1) as avg_stars_in_sample,
  SUM(CASE WHEN r.STARS <= 2 THEN 1 ELSE 0 END) as negative_reviews,
  SUM(CASE WHEN r.STARS = 3 THEN 1 ELSE 0 END) as neutral_reviews,
  SUM(CASE WHEN r.STARS >= 4 THEN 1 ELSE 0 END) as positive_reviews
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r ON p.ASIN = r.ASIN
WHERE p.ASIN = 'B0964BF4N7' AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 50
GROUP BY p.TITLE, p.RATING, p.NUMBER_OF_RATINGS, p.BREADCRUMBS;

SELECT 
  r.STARS,
  r.TITLE as review_title,
  LEFT(r.BODY, 300) as review_text,
  r.DATE as review_date
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 50
ORDER BY r.STARS ASC, LENGTH(r.BODY) DESC;

What You Should See

Prompt 2: Enrich with AI

The Prompt

"Analyze these Opal reviews: score sentiment (more nuanced than stars), classify each by complaint topic (quality_defect, mold_hygiene, customer_support, noise_level, setup_maintenance, value_for_money), flag reviews where the customer is returning the product or describing a health/safety concern, and prioritize them: safety > critical > high > medium > low."

What Threads Generates

SELECT 
  r.STARS,
  r.TITLE as review_title,
  LEFT(r.BODY, 300) as review_text,

  -- AI_SENTIMENT: nuanced sentiment beyond star ratings
  AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR as sentiment,

  -- AI_CLASSIFY: categorize into your custom labels
  AI_CLASSIFY(
    r.BODY, 
    ['quality_defect', 'mold_hygiene', 'customer_support', 'noise_level', 
     'setup_maintenance', 'value_for_money']
  ):labels[0]::VARCHAR as complaint_topic,

  -- AI_FILTER: natural-language yes/no question per row
  AI_FILTER(
    PROMPT('The reviewer is returning the product, filing a complaint, 
            warning others not to buy, or describing a health/safety concern: {0}', r.BODY)
  ) as escalation_risk,

  -- Priority scoring: business logic layered on AI outputs
  CASE 
    WHEN AI_FILTER(PROMPT('The reviewer describes a health or safety concern 
        including mold, contamination, or injury: {0}', r.BODY))
    THEN 'safety'
    WHEN AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR = 'negative' 
      AND AI_FILTER(PROMPT('The reviewer is returning the product, filing a complaint, 
          or warning others not to buy: {0}', r.BODY))
    THEN 'critical'
    WHEN AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR = 'negative' THEN 'high'
    WHEN AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR IN ('mixed', 'neutral') THEN 'medium'
    ELSE 'low'
  END as priority

FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 50;

What Each AI Function Does

The Key Insight

Negative : Cortex AI Functions are billed per row processed. The 98-row GE Opal dataset costs fractions of a cent. If you modify the query to process all 256K reviews, expect higher compute costs — use LIMIT when exploring.

Prompt 2b: More AI Functions

Extract Specific Defects (AI_EXTRACT)

"Extract the specific product defect from each negative review so engineering can triage issues."

SELECT 
  r.STARS,
  r.TITLE as review_title,
  AI_EXTRACT(
    r.BODY, 
    [{'name': 'defect', 'description': 'The specific mechanical or quality defect experienced by the reviewer'}]
  ):response:description::VARCHAR as extracted_defect,
  LEFT(r.BODY, 200) as review_preview
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' 
  AND r.STARS <= 2 
  AND r.BODY IS NOT NULL 
  AND LENGTH(r.BODY) > 100
ORDER BY r.STARS ASC, LENGTH(r.BODY) DESC;

Draft Customer Service Responses (AI_COMPLETE)

"Draft empathetic customer service responses for the most critical reviews so my team can respond faster."

SELECT 
  r.STARS,
  r.TITLE as review_title,
  LEFT(r.BODY, 200) as review_preview,
  AI_COMPLETE(
    'mistral-large2',
    PROMPT('You are a customer support agent. Write a brief, empathetic 2-sentence 
            response to this negative review. Acknowledge the specific issue and offer 
            a concrete next step (replacement, refund, or engineering escalation).

Review: {0}', r.BODY)
  ) as suggested_response
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' 
  AND r.STARS = 1 
  AND r.BODY IS NOT NULL 
  AND LENGTH(r.BODY) > 100
  AND AI_FILTER(
    PROMPT('The reviewer is returning the product, filing a complaint, 
            or warning others not to buy: {0}', r.BODY)
  )
LIMIT 10;

Strip PII Before Sharing (AI_REDACT)

"Before we share this review data with our manufacturing partner, strip any personal information."

SELECT 
  LEFT(r.BODY, 300) as original_review,
  LEFT(AI_REDACT(r.BODY), 300) as safe_for_vendor,
  CASE 
    WHEN r.BODY != AI_REDACT(r.BODY) THEN 'PII detected & removed'
    ELSE 'No PII found'
  END as pii_status
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 100
LIMIT 20;

Translate Multilingual Reviews (AI_TRANSLATE)

"We also have international reviews in Spanish. Translate them and run the same analysis."

SELECT 
  LEFT(r.BODY, 150) as original_review,
  AI_TRANSLATE(r.BODY, '', 'en') as english_translation,
  AI_SENTIMENT(AI_TRANSLATE(r.BODY, '', 'en')):categories[0]:sentiment::VARCHAR as sentiment,
  AI_CLASSIFY(
    AI_TRANSLATE(r.BODY, '', 'en'), 
    ['product_quality', 'shipping_delivery', 'value_for_money', 'durability', 'customer_service']
  ):labels[0]::VARCHAR as topic,
  r.STARS
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
WHERE r.BODY IS NOT NULL 
  AND LENGTH(r.BODY) > 50
  AND (r.BODY LIKE '%estrellas%' OR r.BODY LIKE '%producto%' 
       OR r.BODY LIKE '%muy bueno%' OR r.BODY LIKE '%excelente%')
LIMIT 10;

Positive : All 8 Cortex AI Functions (AI_SENTIMENT, AI_CLASSIFY, AI_FILTER, AI_EXTRACT, AI_COMPLETE, AI_REDACT, AI_AGG, AI_TRANSLATE) are SQL-native. Hex Threads knows how to use them — just describe what you want in plain English.

Prompt 3: Executive Summary

The Prompt

"What are customers complaining about? Show me the topic breakdown as a bar chart. Then summarize the top 3 recurring quality issues across all negative reviews in one executive paragraph. Be specific about what's failing, how quickly, and recommend engineering prioritization."

What Threads Generates

SELECT 
  AI_CLASSIFY(
    r.BODY, 
    ['quality_defect', 'mold_hygiene', 'customer_support', 'noise_level', 
     'setup_maintenance', 'value_for_money']
  ):labels[0]::VARCHAR as complaint_topic,
  COUNT(*) as review_count
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' 
  AND r.STARS <= 3 
  AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 50
GROUP BY complaint_topic
ORDER BY review_count DESC;

SELECT AI_AGG(
  r.BODY, 
  'You are a product analyst writing for the VP of Product. Summarize the top 3 
   recurring quality issues with this ice maker based on these negative reviews. 
   Be specific: name the component that fails, how quickly it fails, and how many 
   reviewers mention it. End with a one-sentence prioritization recommendation 
   for the engineering team. Write one concise paragraph.'
) as executive_summary
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' AND r.STARS <= 2 AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 100;

What You Should See

The Opal 2.0's recurring failures center on three components: (1) the ice auger drivetrain — over half of negative reviews describe progressive squealing or grinding starting at ~6 months, with replacement units failing identically; (2) the water-delivery subsystem — sensor faults, pump failures, and leaks; (3) mold contamination in internal tubing, flagged as a health concern in reviews rated 3+ stars where star-based monitoring would miss them. Engineering priority: P0 auger drivetrain redesign, P0 mold mitigation, P1 water pump reliability.

Bonus Prompt: Find the Mismatches

"Find reviews where the star rating doesn't match the actual sentiment — those are the ones we're misreading."

SELECT 
  r.STARS,
  r.TITLE as review_title,
  AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR as ai_sentiment,
  CASE 
    WHEN r.STARS >= 4 AND AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR IN ('negative', 'mixed') 
      THEN 'High stars but negative tone — hidden dissatisfaction'
    WHEN r.STARS <= 2 AND AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR = 'positive' 
      THEN 'Low stars but positive tone — frustrated fan'
  END as mismatch_insight,
  LEFT(r.BODY, 250) as review_text
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' 
  AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 50
  AND (
    (r.STARS >= 4 AND AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR IN ('negative', 'mixed'))
    OR (r.STARS <= 2 AND AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR = 'positive')
  );

Prompt 4: Build & Publish

The Prompt

"Build me an interactive app with: summary cards (total reviews, safety alerts, critical count, top defect), filters for priority and complaint topic, a bar chart of topics, and a sortable review table with priority coloring. Title it 'What 3.7 Stars Won't Tell You'. Then publish it."

What Threads Generates

Publish

Positive : See it live: Open the published app →

Dynamic Tables Pipeline

"Turn this enrichment into a dynamic table that auto-refreshes every hour."

Expected SQL

CREATE OR REPLACE DYNAMIC TABLE AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.OPAL_REVIEW_INTELLIGENCE
  TARGET_LAG = '1 hour'
  WAREHOUSE = COMPUTE_WH
AS
SELECT 
  r.ID as review_id,
  r.ASIN,
  r.STARS,
  r.TITLE as review_title,
  r.BODY as review_text,
  r.DATE as review_date,
  r.VERIFIED_PURCHASE,

  AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR as sentiment,
  
  AI_CLASSIFY(
    r.BODY, 
    ['quality_defect', 'mold_hygiene', 'customer_support', 'noise_level', 
     'setup_maintenance', 'value_for_money']
  ):labels[0]::VARCHAR as complaint_topic,
  
  AI_FILTER(
    PROMPT('The reviewer is returning the product, filing a complaint, 
            warning others not to buy, or describing a health/safety concern: {0}', r.BODY)
  ) as escalation_risk,

  AI_EXTRACT(
    r.BODY, 
    [{'name': 'defect', 'description': 'The specific defect, quality issue, or complaint experienced'}]
  ):response:description::VARCHAR as extracted_defect,

  CASE 
    WHEN AI_FILTER(PROMPT('The reviewer describes a health or safety concern 
        including mold, contamination, or injury: {0}', r.BODY))
    THEN 'safety'
    WHEN AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR = 'negative' 
      AND AI_FILTER(PROMPT('The reviewer is returning the product, filing a complaint, 
          or warning others not to buy: {0}', r.BODY))
    THEN 'critical'
    WHEN AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR = 'negative' THEN 'high'
    WHEN AI_SENTIMENT(r.BODY):categories[0]:sentiment::VARCHAR IN ('mixed', 'neutral') THEN 'medium'
    ELSE 'low'
  END as priority

FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.REVIEWS r
JOIN AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.PRODUCTS p ON r.ASIN = p.ASIN
WHERE p.ASIN = 'B0964BF4N7' AND r.BODY IS NOT NULL AND LENGTH(r.BODY) > 50;

Positive : Change WAREHOUSE = COMPUTE_WH to match your warehouse name. Cortex AI Functions support incremental refresh in Dynamic Tables — you only pay for new rows, not re-processing the entire table.

Verify the Pipeline

SELECT priority, complaint_topic, COUNT(*) as cnt
FROM AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.OPAL_REVIEW_INTELLIGENCE
GROUP BY priority, complaint_topic
ORDER BY 
  CASE priority WHEN 'safety' THEN 0 WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END,
  cnt DESC;

Conclusion and Resources

What You Built

What You Learned

Go Further

Cleanup

DROP DYNAMIC TABLE IF EXISTS AMAZON_BEST_SELLERS_RATINGS_AND_REVIEWS.PUBLIC.OPAL_REVIEW_INTELLIGENCE;

Resources