week 141 - Cortex (AI / ML)
This dbt+Cortex challenge uses the LABEL_SENTIMENT function from Snowflake Cortex to classify customer reviews by category-level sentiment. You will load CSV data into seeds and build two models:
- classified_sentiments
- parsed_entity_sentiments
For this challenge , you're highly recommended to use Snowflake Workspaces and run it as a dbt project
🧪 Objective
Classify customer hotel reviews into predefined service categories and extract the per-category sentiment.
📁 Provided
- hotel_reviews.csv
review_id,hotel_id,review_text1,2004,Customer support was amazing. The Free WiFi was a big part of our experience.2,2002,Great value for the price. The Free WiFi was a big part of our experience.3,2002,"Functional, but unexciting. The Location was a big part of our experience."4,2002,"It's okay, nothing special. The Facilities was a big part of our experience."5,2004,Highly recommend to everyone. The Comfort was a big part of our experience.6,2005,I would definitely buy again. The Free WiFi was a big part of our experience.7,2004,I would definitely buy again. The Cleanliness was a big part of our experience.8,2002,Best purchase I've made this year. The Free WiFi was a big part of our experience.9,2003,"Five stars, no complaints. The Staff was a big part of our experience."10,2003,"Functional, but unexciting. The Location was a big part of our experience."11,2005,Poor quality and bad support. The Comfort was a big part of our experience.12,2002,Best purchase I've made this year. The Location was a big part of our experience.13,2001,Wouldn’t buy this again. The Staff was a big part of our experience.14,2001,No major complaints or praise. The Cleanliness was a big part of our experience.15,2002,No major complaints or praise. The Free WiFi was a big part of our experience.16,2001,I would definitely buy again. The Facilities was a big part of our experience.17,2005,Poor quality and bad support. The Free WiFi was a big part of our experience.18,2005,Fantastic service and quality. The Comfort was a big part of our experience.19,2003,Average experience. The Comfort was a big part of our experience.20,2005,Service was neither good nor bad. The Cleanliness was a big part of our experience.21,2004,Not worth the money. The Staff was a big part of our experience.22,2002,Fantastic service and quality. The Facilities was a big part of our experience.23,2001,"It's okay, nothing special. The Staff was a big part of our experience."24,2004,Poor quality and bad support. The Comfort was a big part of our experience.25,2002,Decent but not remarkable. The Location was a big part of our experience.26,2001,Average experience. The Staff was a big part of our experience.27,2005,Meets the basic requirements. The Staff was a big part of our experience.28,2004,Average experience. The Value for money was a big part of our experience.29,2003,Meets the basic requirements. The Staff was a big part of our experience.30,2003,Average experience. The Facilities was a big part of our experience.31,2005,Brilliant design and easy to use. The Comfort was a big part of our experience.32,2002,Worst product I’ve purchased. The Comfort was a big part of our experience.33,2003,Service was neither good nor bad. The Location was a big part of our experience.34,2001,Didn't meet expectations at all. The Value for money was a big part of our experience.35,2003,I would definitely buy again. The Location was a big part of our experience.36,2001,I regret buying this. The Facilities was a big part of our experience.37,2001,Decent but not remarkable. The Staff was a big part of our experience.38,2001,Highly recommend to everyone. The Cleanliness was a big part of our experience.39,2005,Great value for the price. The Value for money was a big part of our experience.40,2001,"Five stars, no complaints. The Comfort was a big part of our experience."41,2002,Meets the basic requirements. The Staff was a big part of our experience.42,2002,Highly recommend to everyone. The Cleanliness was a big part of our experience.43,2005,Customer support was amazing. The Location was a big part of our experience.44,2003,"It's okay, nothing special. The Location was a big part of our experience."45,2001,Exceeded my expectations. The Value for money was a big part of our experience.46,2002,Absolutely love this product! The Comfort was a big part of our experience.47,2001,"It's okay, nothing special. The Free WiFi was a big part of our experience."48,2003,I would definitely buy again. The Facilities was a big part of our experience.49,2005,It works as expected. The Comfort was a big part of our experience.50,2003,Very disappointed. Would not recommend. The Cleanliness was a big part of our experience.51,2002,Very disappointed. Would not recommend. The Value for money was a big part of our experience.52,2003,Exceeded my expectations. The Staff was a big part of our experience.53,2002,Decent but not remarkable. The Facilities was a big part of our experience.54,2002,"Five stars, no complaints. The Location was a big part of our experience."55,2005,Not worth the money. The Free WiFi was a big part of our experience.56,2004,Terrible customer service. The Facilities was a big part of our experience.57,2005,Service was neither good nor bad. The Value for money was a big part of our experience.58,2002,"It's okay, nothing special. The Comfort was a big part of our experience."59,2005,I would definitely buy again. The Facilities was a big part of our experience.60,2003,Customer support was amazing. The Location was a big part of our experience.61,2005,I would definitely buy again. The Staff was a big part of our experience.62,2002,Exceeded my expectations. The Free WiFi was a big part of our experience.63,2002,Decent but not remarkable. The Cleanliness was a big part of our experience.64,2005,I would definitely buy again. The Free WiFi was a big part of our experience.65,2003,Great value for the price. The Comfort was a big part of our experience.66,2002,Customer support was amazing. The Staff was a big part of our experience.67,2005,Customer support was amazing. The Value for money was a big part of our experience.68,2001,Best purchase I've made this year. The Facilities was a big part of our experience.69,2005,Didn't meet expectations at all. The Free WiFi was a big part of our experience.70,2001,Fantastic service and quality. The Location was a big part of our experience.71,2002,Exceeded my expectations. The Cleanliness was a big part of our experience.72,2002,Product broke after one use. The Location was a big part of our experience.73,2002,Best purchase I've made this year. The Facilities was a big part of our experience.74,2003,Great value for the price. The Facilities was a big part of our experience.75,2003,Not worth the money. The Free WiFi was a big part of our experience.76,2002,I would definitely buy again. The Facilities was a big part of our experience.77,2003,Best purchase I've made this year. The Staff was a big part of our experience.78,2001,Average experience. The Cleanliness was a big part of our experience.79,2005,No major complaints or praise. The Facilities was a big part of our experience.80,2002,"Five stars, no complaints. The Value for money was a big part of our experience."81,2005,"Five stars, no complaints. The Cleanliness was a big part of our experience."82,2003,Completely dissatisfied. The Free WiFi was a big part of our experience.83,2005,I regret buying this. The Location was a big part of our experience.84,2004,Worst product I’ve purchased. The Facilities was a big part of our experience.85,2002,I would definitely buy again. The Location was a big part of our experience.86,2004,Terrible customer service. The Free WiFi was a big part of our experience.87,2004,It works as expected. The Free WiFi was a big part of our experience.88,2003,Brilliant design and easy to use. The Staff was a big part of our experience.89,2002,Great value for the price. The Facilities was a big part of our experience.90,2004,Great value for the price. The Facilities was a big part of our experience.91,2005,No major complaints or praise. The Staff was a big part of our experience.92,2003,"Product is fine, does the job. The Facilities was a big part of our experience."93,2001,Wouldn’t buy this again. The Location was a big part of our experience.94,2002,Service was neither good nor bad. The Location was a big part of our experience.95,2003,Fantastic service and quality. The Cleanliness was a big part of our experience.96,2002,Brilliant design and easy to use. The Free WiFi was a big part of our experience.97,2002,Average experience. The Staff was a big part of our experience.98,2002,Decent but not remarkable. The Facilities was a big part of our experience.99,2005,Didn't meet expectations at all. The Free WiFi was a big part of our experience.100,2005,Meets the basic requirements. The Free WiFi was a big part of our experience.- review_categories.csv
category_id,category_name1,Staff2,Comfort3,Free WiFi4,Facilities5,Value for money6,Cleanliness7,Location- schema.yml
version: 2sources:- name: challenge_002_setupdatabase: mikedroogschema: challenge_002_setuptables:- name: hotel_reviews_with_entitiesdescription: "Hotel reviews with embedded target entities for entity-level sentiment analysis"columns:- name: review_iddescription: "Unique identifier for the review"- name: hotel_iddescription: "Hotel associated with the review"- name: review_textdescription: "Full review text including reference to the entity"- name: target_entitydescription: "Entity mentioned in the review for sentiment extraction (e.g., Staff, Location)"- name: hotel_review_categoriesdescription: "Static list of hotel review categories used for classification"columns:- name: category_iddescription: "Unique identifier for the category"- name: category_namedescription: "Name of the hotel review category (e.g., Cleanliness, Location)"- name: review_iddescription: "Unique identifier for the review"- name: product_iddescription: "Product associated with the review"- name: review_textdescription: "Customer’s free-form feedback"✅ Tasks
- Load both CSV files as seeds
- Create the model classified_sentiments.sql:Use LABEL_SENTIMENT() to extract entity sentiments.
- Create the model parsed_entity_sentiments.sql:
Flatten the entity_sentiments:categories array to extract each sentiment.
🧾 Deliverables
- classified_sentiments model with entity_sentiments output
- parsed_entity_sentiments model with one row per review/category sentiment
