Uncategorized

In real-world contracts, headers often carry subtle variations: numbering, punctuation, or word orderbut they refer to the same section. Your mission: unify these headers across multiple companies using AI-powered similarity and canonicalization, so downstream comparisons all speak the same language.

What you’ll do this week

• Write a Snowflake SQL pipeline that discovers “almost duplicates” among section headers using AI_SIMILARITY, then clusters them into stable groups.
• For each group, use AI_COMPLETE to choose one crisp canonical header (3–5 words, no numbering or punctuation).
• Map every original header in your dataset to its normalized label.
• Run diagnostics: show how many unique raw headers existed vs how many normalized header groups you produced, and present a few examples where messy variants collapse cleanly.
• All of this should run hands-off—no external tools, just Snowpark-native AI in Snowflake.

CREATE OR REPLACE SCHEMA FROSTY_DB.week_148;

CREATE OR REPLACE TABLE week_148.HEADERS_SAMPLE (
COMPANY STRING,
DOC_ID STRING,
HEADER_ORDER INTEGER,
HEADER_TEXT STRING
);

INSERT INTO week_148.HEADERS_SAMPLE (COMPANY, DOC_ID, HEADER_ORDER, HEADER_TEXT) VALUES
-- Company A
('AIG', 'A1', 1, '1. Coverage Overview'),
('AIG', 'A1', 2, '1.1 Policy Benefits'),
('AIG', 'A1', 3, '2. Exclusions & Limitations'),
('AIG', 'A1', 4, '3. Claims Process'),

-- Company B (variants)
('Zurich', 'Z1', 1, 'Coverage - Overview'),
('Zurich', 'Z1', 2, 'Policy Benefit(s)'),
('Zurich', 'Z1', 3, 'Exclusions and Limitations'),
('Zurich', 'Z1', 4, 'Claims – Process'),

-- Company C (more variants)
('Allianz', 'AL1', 1, '01) COVERAGE OVERVIEW'),
('Allianz', 'AL1', 2, 'Policy: Benefits'),
('Allianz', 'AL1', 3, 'Exclusions / Limitations'),
('Allianz', 'AL1', 4, 'Claims Processing'),

-- Company D (edge cases)
('AXA', 'AX1', 1, 'Coverage overview & scope'),
('AXA', 'AX1', 2, 'Benefit of Policy'),
('AXA', 'AX1', 3, 'Limitations and Exclusions'),
('AXA', 'AX1', 4, 'Filing a Claim');

SELECT * FROM week_148.HEADERS_SAMPLE ORDER BY COMPANY, DOC_ID, HEADER_ORDER;

Your objective :

A rather small release from Summit has rather big impacts, so says Professor Frosty. Which one? Chaining SQL! What a glorious innovation! You can query the results of queries in chained commands making life much easier than it ever has been.

For this challenge you have the following stored procedure:

CREATE OR REPLACE PROCEDURE gen_timeseries(start_date DATE, days INTEGER, category STRING, seed INTEGER)
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.10'
PACKAGES = ('snowflake-snowpark-python', 'numpy')
HANDLER = 'run'
AS
$$
import numpy as np
import datetime

def run(session, start_date, days, category, seed):
    # Make the RNG deterministic for reproducibility
    rng = np.random.default_rng(int(seed) if seed is not None else 42)

    base = datetime.date.fromisoformat(str(start_date))
    rows = []

    # Start at 100 and add small Gaussian noise each day
    value = 100.0
    for i in range(int(days)):
        day = base + datetime.timedelta(days=i)
        value = max(0.0, value + float(rng.normal(0, 3)))  # keep non-negative
        rows.append({
            "date": str(day),
            "category": category,
            "value": round(value, 2)
        })

    # Compute a 3-day trailing moving average
    for i, r in enumerate(rows):
        window = rows[max(0, i-2):i+1]
        ma = sum(x["value"] for x in window) / len(window)
        r["moving_avg"] = round(ma, 2)
        r["day_index"] = i + 1

    return {
        "meta": {
            "start_date": str(base),
            "days": int(days),
            "category": category
        },
        "rows": rows
    }
$$;

This has the following inputs:

1. start_date DATE
   • The first date in the time series.
   • Example: '2025-04-01' → first row will be 2025-04-01.
2. days INTEGER
   • How many rows (days) to generate starting from start_date.
   • Example: 7 → generates 7 rows, from Apr 1 through Apr 7.
3. category STRING
   • A label that will be attached to every row, so you can distinguish multiple runs.
   • Example: 'WIDGETS' → every row will include "category": "WIDGETS".
4. seed INTEGER
   • Seed for the random number generator.
   • Makes the random output reproducible: same seed → same sequence of random noise.
   • Example: 123 → if you call again with seed 123, you’ll get the exact same dataset.

Your task is to parse the JSON using chained commands! Call the procedure as below:

CALL gen_timeseries('2025-04-01'::DATE, 7, 'WIDGETS', 123)

And then try to get this result:

This week’s challenge puts the spotlight on secure, key-pair authentication in Snowflake using Snowpark including seamless rotation.

Snowflake continues to elevate its security offerings, and key-pair authentication is now a strong recommendation for service users. It eliminates the need for passwords, enhances automation, and aligns with modern best practices. But what if the private key needs replacement? Day-to-day operations shouldn’t be interrupted, and that’s where seamless key rotation comes in.

In this challenge, you’ll:

• Rotate your key-pair on-the-fly—without breaking your connection or workflow.

• Set up a service-style user (demo_user) with RSA key-pair authentication,

• Connect confidently via Snowpark in Python,

Your steps to follow :
1. Create a demo user

2. Generate an RSA key pair (public first , then private)

3. Link the public key to the Snowflake user

4. Grant minimal access needed for the challenge

5. Connect via Python using Snowpark and authenticate with the private key

from snowflake.snowpark import Session


# Set up the session for demo_user with RSA private key authentication
private_key_file = '<location of private key>'

connection_parameters = {
    'account': "<put your account identifier here>",
    'user': "demo_user",
    'warehouse': "compute_wh",
    'private_key_file': private_key_file
}

session = Session.builder.configs(connection_parameters).create()
# Check
result = session.sql('select current_user();').collect()
print(result)

7. Generate a secondary RSA key pair (public first , then private)

8. Link the secondary public key to the Snowflake user without removing the first one

9. Authenticate using the secondary key

from snowflake.snowpark import Session


# Set up the session for demo_user with RSA private key authentication
secondary_private_key_file = '<location of the second private key>'

connection_parameters = {
    'account': "<put your account identifier here>",
    'user': "demo_user",
    'warehouse': "compute_wh",
    'private_key_file': secondary_private_key_file
}

session = Session.builder.configs(connection_parameters).create()
# Check
result = session.sql('select current_user();').collect()
print(result)

10. Don’t forget to post your code!

Mutlimodal got a bit more multi this month with the release into public preview of AI_TRANSCRIBE(). Naturally, Prof. Frosty has been experimenting with this new AI SQL function and wants all his students to dive in!

You challenge is to download the audio file from this video and place it into a Snowflake stage:

From there, have Snowflake produce a transcript of the video, and have it summarise the contents with the following prompt:

prompt('Below is a transcript of a video. Summarise its contents and provide bulletpoints on the main points.\n {0}',transcript_text)

It ought to produce something like the below:

This week, you’ll explore Data Metric Functions (DMFs) in Snowflake—a feature designed to help monitor and enforce data quality. The idea is simple: rather than manually inspecting your data for issues, you define expectations that describe what “good” data looks like. Snowflake will then evaluate those expectations (hint) against your table and flag any violations.

create or replace schema week_146;

create table week_146.data_table (id int);

INSERT INTO week_146.data_table (id)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

This week’s setup is simple: you’ll create a table containing values from 1 to 10. Nothing fancy.

However, there’s a catch: we were expecting the data to have a maximum value of 8.

Your task is to use Data Metric Functions in Snowflake to define and apply this expectation, so that Snowflake flags the current data as non-conforming.

Once the expectation is in place, use Snowflake’s system functions and metadata tables to surface the failure.

Your final result should resemble something like this:

If you weren’t aware, this week Snowflake Intelligence went into public preview!

Obviously as an incredibly intelligent individual, Prof Frosty has already devised a challenge for his eager students.

The challenge is as follows:

• Install the SEC Filings data share
• Set up Snowflake Intelligence
• Download the following semantic models:
  • company_reference
  • fund_holdings_13f
  • sec_filings
• Upload them to a stage
• Create an agent with Snowflake Intelligence that will be able to route questions to the appropriate semantic model
• Provide the following sample questions:
  • For Walmart Inc., what are the counts of 10-K, 10-Q, and 8-K filings and the latest filing date
  • Which companies have a primary ticker in the Company Index sample, and what are those tickers?
  • Show the primary ticker and latest SEC filing date for Apple Inc.
  • List five registrants with the highest filing counts in the sample and include each latest filing date.
  • Which SIC descriptions appear in the industry sample, and how many registrants does each have?
  • Find companies with non-December fiscal year-ends and display their FYE (MM-DD).
  • What is the primary ticker for QVC Group, Inc., and what’s its latest SEC filing date?
  • Which filing managers (13F/holdings) in the sample have 40+ filings, and when was their most recent filing?
  • Which holdings attributes have the most non-null rows (e.g., MARKET_VALUE, NUMBER_OF_SHARES)? Return company name → primary ticker pairs for the tickers sample (e.g., Northern Trust Corp → NTRS).
  • Which registrants in the sample have fewer than 5 filings total, and what are their latest filing dates?
  • From the overlapping companies sample, list tickers for ESS Tech, Tango Therapeutics, and Halliburton with each latest filing date.
  • Show companies with LEIs and their LEI codes from the LEI sample.
  • From the common income measures sample, list five measures and the count of companies reporting each.
  • Identify airline/air-related candidates by name and show any available SIC description.
• Login to Snowflake Intelligence and select your FROSTY AGENT
• Ask it the following ground truth questions:
  • What is the primary ticker and latest SEC filed date for Apple Inc.?
  • Which holdings attribute has the largest non-null row count? Return the field and non_null_rows.
  • Provide the primary ticker for Northern Trust Corp.
  • List the filing manager name, filing_count, and latest_filing_date for Osborne Partners Capital Management, LLC.

Happy Intelligencing!

This week’s challenge comes with a twist:

Your task is to create an hourly snapshot table of a dynamic table—but with one major constraint:
You cannot use a basic SELECT statement anywhere in your solution.

Use the start-up code below to start your challenge

CREATE TABLE table1 (
    id INT,
    name STRING
);

INSERT INTO table1 VALUES (1, 'dummy');

CREATE OR REPLACE DYNAMIC TABLE dynamic_table
TARGET_LAG = '1 minute'
WAREHOUSE = MY_WH
AS
SELECT *
FROM table1;

Your solution , should create a regulair table , every hour, with the contents of the dynamic table.

Good luck!

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.

Hey hey hey, I was about to tell you something important. But wait! You’ll need to parse this XML to discover the hidden message.

This week’s challenge is all about XML support in Snowflake.

You are given an XML document with two main sections:

• An <art> block containing a frosty ASCII drawing
• A <message> block hiding something I want to tell you

Your task: Use Snowflake’s native XML functions to extract and display the message from the XML. You should first download the file below:

Hint: Snowflake now supports XML natively — no need for external preprocessing
Snowflake XML Docs

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.

Use Snowflake Cortex to translate and summarize international customer reviews in various languages. Analyze a single combined dataset (customer_feedback_combined) containing reviews in English, Spanish, French, German, and Japanese.

Run the following start up code:

CREATE OR REPLACE TABLE customer_feedback (
  review_id INT,
  review_text STRING
);
INSERT INTO customer_feedback (review_id, review_text) VALUES
(1, 'It was okay, but could be better. Excellent customer support throughout the process. It was okay, but could be better. Excellent customer support throughout the process. It was okay, but could be better. Excellent customer support throughout the process. It was okay, but could be better. Excellent customer support throughout the process. It was okay, but could be better. Excellent customer support throughout the process.');
INSERT INTO customer_feedback (review_id, review_text) VALUES
(1, 'Estuvo bien, pero podría mejorar. No estoy satisfecho con la calidad del artículo. Estuvo bien, pero podría mejorar. No estoy satisfecho con la calidad del artículo. Estuvo bien, pero podría mejorar. No estoy satisfecho con la calidad del artículo. Estuvo bien, pero podría mejorar. No estoy satisfecho con la calidad del artículo. Estuvo bien, pero podría mejorar. No estoy satisfecho con la calidad del artículo.');
INSERT INTO customer_feedback (review_id, review_text) VALUES
(1, 'Je recommanderais certainement ce produit. La livraison était rapide et le service excellent. Je recommanderais certainement ce produit. La livraison était rapide et le service excellent. Je recommanderais certainement ce produit. La livraison était rapide et le service excellent. Je recommanderais certainement ce produit. La livraison était rapide et le service excellent. Je recommanderais certainement ce produit. La livraison était rapide et le service excellent.');
INSERT INTO customer_feedback (review_id, review_text) VALUES
(1, 'Ich würde dieses Produkt auf jeden Fall empfehlen. Es war okay, aber es könnte besser sein. Ich würde dieses Produkt auf jeden Fall empfehlen. Es war okay, aber es könnte besser sein. Ich würde dieses Produkt auf jeden Fall empfehlen. Es war okay, aber es könnte besser sein. Ich würde dieses Produkt auf jeden Fall empfehlen. Es war okay, aber es könnte besser sein. Ich würde dieses Produkt auf jeden Fall empfehlen. Es war okay, aber es könnte besser sein.');
INSERT INTO customer_feedback (review_id, review_text) VALUES
(1, '全体的に優れたカスタマーサポートでした。 この製品は間違いなくおすすめです。 全体的に優れたカスタマーサポートでした。 この製品は間違いなくおすすめです。 全体的に優れたカスタマーサポートでした。 この製品は間違いなくおすすめです。 全体的に優れたカスタマーサポートでした。 この製品は間違いなくおすすめです。 全体的に優れたカスタマーサポートでした。 この製品は間違いなくおすすめです。');

The result ought to look something like:

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