Uncategorized

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_text
1,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_name
1,Staff
2,Comfort
3,Free WiFi
4,Facilities
5,Value for money
6,Cleanliness
7,Location

• schema.yml

version: 2

sources:
  - name: challenge_002_setup
    database: mikedroog
    schema: challenge_002_setup
    tables:
      - name: hotel_reviews_with_entities
        description: "Hotel reviews with embedded target entities for entity-level sentiment analysis"
        columns:
          - name: review_id
            description: "Unique identifier for the review"
          - name: hotel_id
            description: "Hotel associated with the review"
          - name: review_text
            description: "Full review text including reference to the entity"
          - name: target_entity
            description: "Entity mentioned in the review for sentiment extraction (e.g., Staff, Location)"

      - name: hotel_review_categories
        description: "Static list of hotel review categories used for classification"
        columns:
          - name: category_id
            description: "Unique identifier for the category"
          - name: category_name
            description: "Name of the hotel review category (e.g., Cleanliness, Location)"
          - name: review_id
            description: "Unique identifier for the review"
          - name: product_id
            description: "Product associated with the review"
          - name: review_text
            description: "Customer’s free-form feedback"

✅ Tasks

1. Load both CSV files as seeds
2. Create the model classified_sentiments.sql:Use LABEL_SENTIMENT() to extract entity sentiments.
3. 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

Your company recently migrated a set of invoice records from multiple vendors into Snowflake. Each line item includes a free-text description of services provided — but there’s no consistent naming or structure across entries. To support financial reporting and vendor analysis, your team needs to classify each service description into a standard list of categories maintained by Finance.

Manually tagging hundreds of rows isn’t scalable. Luckily, you’ve just discovered Snowflake Cortex, which lets you run LLM-powered classification directly in SQL using the CLASSIFY_TEXT() function.

Your challenge: write a query that automatically assigns each service description to its most appropriate category.

Run the statements below to create the required tables and sample rows:

-- Create invoice_line_items table
CREATE OR REPLACE TABLE invoice_line_items (
    line_item_id INTEGER,
    service_description STRING
);

-- Create service_categories table
CREATE OR REPLACE TABLE service_categories (
    category_id INTEGER,
    category_name STRING
);

-- Insert sample invoice line items
INSERT INTO invoice_line_items (line_item_id, service_description) VALUES
(1, 'Deployment of Snowflake project - Phase 1'),
(2, 'Data ingestion pipeline optimization'),
(3, 'Security and access review for Snowflake'),
(4, 'Ongoing data modeling support'),
(5, 'Snowflake training session for analysts');

-- Insert sample categories
INSERT INTO service_categories (category_id, category_name) VALUES
(1, 'Snowflake Deployment'),
(2, 'Data Engineering'),
(3, 'Security Review'),
(4, 'Training'),
(5, 'Analytics Support');

Hint: Use a CTE to aggregate the category names into an array using ARRAY_AGG(), then cross join it into the invoice data so each row has access to the full list of options for classification.

P.s. does it look familiar? In that case, it was nice to meet you at #SnowflakeSummit2025 in San Francisco 🙂

AI galore!

Every week there’s something new coming from Cortex – and this week, we’ll be using the new ENTITY_SENTIMENT() function.

Professor Frosty’s training programme has been going from strength to strength thanks to these WONDERFUL reviews!

create or replace stage frosty_stage
    url = 's3://frostyfridaychallenges/'
;

create or replace table week_139 as 
select
    $1 as id,
    $2 as student,
    $3 as rating,
    $4 as review_text
from
    @frosty_stage/challenge_139 (
        file_format=>'frosty_csv'
    )
;

Now, it is your job to figure out the sentiment for the following categories:

• Overall
• Content
• Teaching

And the result ought to look something like:

Nulls Behaving Badly

Ever wondered how Snowflake decides where to place NULL values when you sort your data? 🤔 Let’s dive into the DEFAULT_NULL_ORDERING parameter and see how it influences the ordering of NULLs in your queries.

Challenge Overview

Your task is to:

1. Set the DEFAULT_NULL_ORDERING parameter to both ‘FIRST’ and ‘LAST’ in your session.
2. Create a sample table with a mix of NULL and non-NULL values.
3. Observe the ordering of NULLs in your query results under different settings.
4. Override the default behavior using explicit NULLS FIRST or NULLS LAST in your ORDER BY clause.
5. Bonus: Retrieve the current setting of DEFAULT_NULL_ORDERING using a SQL query.

Good luck, and stay frosty! ❄️

You know, I think the introduction of DATA METRIC FUNCTIONS was a bit too quiet for my liking. With them, you can proactively monitor your tables with data quality tests.

Allow me to provide you a little opportunity to play around. Run the below query:

create or replace stage week137_stage
    url='s3://frostyfridaychallenges/challenge_137/';

create or replace table week137 as
select 
    $1::int AS sale_id,
    $2 AS customer_name,
    $3 AS product,
    $4::int AS quantity,
    $5::float AS unit_price,
    date($6::string, 'DD/MM/YYYY') AS sale_date,
    $7::float AS total_amount
from 
    @week137_stage (file_format=>frosty_csv)
;

THEN

Apply the NULL_COUNT data quality test, and query it, which should give you the following result:

THEN

Create your own custom function that discovers how many rows have bad maths! Where does our total_amount not equal unit_price * quantity?

Hey hey hey!

Spring has finally arrived, and we want to celebrate it 🌸Each gardener has carefully prepared their own seed packs — arrays of seed counts (in grams) for different flower types. Your job is to help them compute the total weight of their custom mixes. 🌼

Instead of summing manually or unpacking arrays one by one, you’re going to use Snowflake’s spread operator ** to pass array values directly into a function🌿

-- Setup Script
-- Step 1: Create the seeds table 🌸🌿
CREATE OR REPLACE TABLE seed_packs (
    gardener STRING,
    seeds ARRAY
);

-- Step 2: Insert your spring options🌼🌼
INSERT INTO seed_packs 
SELECT 'Luna', ARRAY_CONSTRUCT(5, 10, 15)
UNION ALL
SELECT 'Sol', ARRAY_CONSTRUCT(7, 8, 9)
UNION ALL
SELECT 'Anna', ARRAY_CONSTRUCT(4, 12, 18)
UNION ALL
SELECT 'Daniel', ARRAY_CONSTRUCT(6, 8, 2);

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

You’ve been asked to set up an automated alerting system for a critical data table. Whenever new rows appear, an email must be sent to notify the team immediately.

No dashboards. No polling. Just pure Snowflake magic.

Your Task:

Create an alert that sends an email when new data lands in a table named alert_input_data in your schema (week_135 or equivalent). Your alert should trigger automatically — not on a fixed schedule.

Constraints:

• Do not use the ACCOUNTADMIN role.
• The email must be sent using Snowflake’s built-in notification system.
• The alert must fire only when new rows are present. (So no timed checks)
• You must use a warehouse you have access to.

Expected Outcome:

When a new row is inserted into the table, you receive an email with a message confirming that new data has landed.

Bonus Challenges:

• Include useful metadata (like a row count or timestamp) in the email.
• Allow the alert to be reused for different tables or emails with minimal changes.
• Log when the alert was triggered and what it detected.

Hints (if you need them):

You’ll probably want to look into:

• Creating a table that supports data change detection
• Using Snowflake’s email notification integrations
• Writing alerts that are event-based, not scheduled
• Making sure your role has permission to execute the alert

If you’ve, in the past, used CORTEX.COMPLETE as part of any workflow you are well aware of the strenuous prompting necessary in order to make it produce predictable results.

Well, worry no more! Structured outputs allow you to provide a JSON schema within which the LLM must respond.

Your challenge is to have CORTEX.COMPLETE produce a list of cool Snowflake features and descriptions of those features. The output ought to look like the below, post-processing:

Every Pi Day (3.14), math students, using fake but famous mathematicians’ names, flock to The Golden Ratio Bakery to enjoy their favorite π-themed pies. Instead of picking a random dessert, they select their treat based on the legendary mathematician they admire most.

Alan Turing Apple Pie 9.99£
Ada Lovelace Cherry Pie 12.5£
Carl Gauss Pumpkin Pie 15.00£
Srinivasa Ramanujan Chocolate Pie 8.75£
Katherine Johnson Blueberry Pie 10.99£
Euclid Raspberry Pie 11.25£
Johannes Kepler Strawberry Pie 14.50£
Leonhard Euler Peach Pie 14.99£
Pythagoras Blackberry Pie 9.75£
Fibonacci Mango Pie 13.50£
Sophie Germain Mixed Berry Pie 10.00£
Pierre-Simon Laplace Banana Cream Pie 12.25£
Blaise Pascal Lemon Meringue Pie 11.99£
Hypatia Key Lime Pie 10.75£
John von Neumann Pear Pie 13.99£
Joseph Fourier Apricot Pie 14.25£
Gottfried Wilhelm Leibniz Plum Pie 15.75£
David Hilbert Fig Pie 16.00£
Bernhard Riemann Walnut Pie 14.50£
Niels Bohr Almond Pie 13.25£
Johann Bernoulli Cranberry Pie 12.75£
Henri Poincaré Plum Tart 14.00£
Emmy Noether Coconut Cream Pie 15.99£
Georg Cantor Infinite Layer Pie 17.77£

This year, demand is through the roof, and the usual sequential order processing is causing frustrating delays. Mathematicians—real or not—are notoriously impatient and expect instant, parallel execution for their pie requests.

To avoid a pie-induced catastrophe, we need to optimize the order system by leveraging Snowflake’s ASYNC execution to process multiple mathematicians’ orders simultaneously and return the updated list of satisfied customers without delays.

Your task is to speed up pie order processing by inserting two mathematicians’ pie orders asynchronously using ASYNC, ensuring both inserts complete before returning results using AWAIT, and returning the updated order list sorted by order_id so we know which legendary mind—or at least their name—got their pie first.

Happy Pi Day to all!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

“A little knowledge is a dangerous thing”—a quote attributed to many, yet still undeniably true. We’ve all witnessed situations where people jump to conclusions based on partial information rather than considering the full picture.

Today, we’re tackling this issue by enforcing restrictions that prevent users from viewing only a fragment of the data.

Join Policies to the Rescue

Join Policies in Snowflake allow us to enforce joins, ensuring that users cannot retrieve data from a table unless they join it with another table. And that’s exactly what we’re going to implement in today’s challenge.

The Challenge
Step 1: Create and Populate the Tables

CREATE OR REPLACE TABLE join_table (
col1 INT,
col2 VARCHAR,
col3 NUMBER )
JOIN POLICY my_join_policy
;

INSERT INTO join_table (col1, col2, col3) VALUES (1, 'Sample Data', 123.45);

CREATE OR REPLACE TABLE join_table_details (col1 INT,  col4 VARCHAR,  col5 DATE);

INSERT INTO join_table_details (col1, col4, col5) VALUES (1, 'Additional Info', '2025-03-07');

Step 2: Enforce Join Restriction

Your task is to configure the join policy so that only the ACCOUNTADMIN role can query join_table directly.

Any other role attempting to query join_table without a join (e.g., with SELECT * FROM join_table) should encounter an error message instead of retrieving data.

Goal

If anyone besides ACCOUNTADMIN runs a direct query on join_table, the expected result should be an error message similar to: