Archives for August 2025

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.