Uncategorized

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:

It was with great delight that Prof. Frosty read Jeff Hollan‘s announcement that the most frustrating aspect of Snowflake had now been resolved:

On the same day, Doris Lee posted about the fact that Snowpark Pandas can read CSV files directly from S3. What joy! Although we shan’t post a screenshot, because that would foil this week’s challenge.

This week, we have some data in our public S3 bucket on Snowflake’s stock source credit to MarketWatch (accessed 2025-02-28)

Your challenge is to use VS Code – or some other IDE – to connect into Snowflake and use Snowpark Pandas to tell us, between the dates 2024-02-28 and 2025-02-27 which day had the greatest variance between open and close & high and low?

import modin.pandas as pd
import snowflake.snowpark.modin.plugin
from snowflake.snowpark import Session

session = Session.builder.config("connection_name", "frosty").create()

s3_loc = 's3://frostyfridaychallenges/challenge_131/snock_stock_market_watch.csv'

Happy Snowflaking!

Your goal is to establish a connection to Snowflake using an RSA PKCS8 private key. However, the behavior of snowflake.connector changes based on how the key is generated. Your challenge is to determine the correct connection parameters for both encrypted and unencrypted private keys.

Step 1: Use OpenSSL to generate two different private keys

Encrypted Private key (Passphrase Required)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key_des3.p8

Unencrypted Private key (No Passphrase)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8

Step 2: Establish Connection by using snowflake.connector library

Your task is to connect to Snowflake using two different RSA private keys. However, depending on how the key was generated, the connection parameters may need to be adjusted.

• Try connecting with the first key (rsa_key_des3.p8) and include the private_key_file_pwd parameter.
• Try connecting with the second key (rsa_key.p8) but this time, decide whether the private_key_file_pwd should be included or not.

Pay close attention to how Snowflake responds to each connection attempt. If you get an error saying a password was provided but not needed, think about whether the private_key_file_pwd parameter should be there in the first place. But, if the connection fails completely, double-check how the key was generated and whether it requires a passphrase. Try adjusting the private_key_file_pwd parameter by adding or removing it and see what changes.

Thanks to @StefanoGiostra for the topic of this challenge 🙂

This week , the Intern was given a problem that he can’t seem to fix. Professor Frosty was quite taken with his own brilliant idea though so the Intern doesn’t want to disappoint.

It’s a challenge that some of you might’ve seen before : FizzBuzz.
Before everyone starts digging through old pieces of their own leetcode to find their solution , Professor Frosty gave this challenge with a twist : FizzBuzz with SQL.

The challenge for this week :
Write a SQL query that outputs numbers from 1 to 100, but with a twist:
• For multiples of 3, output “Fizz” instead of the number.
• For multiples of 5, output “Buzz” instead of the number.
• For multiples of both 3 and 5, output “FizzBuzz”.

You are allowed to use any tables that you’d like to construct to make the query easier.

The expected output looks something like this

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.

As you may have seen, the 2025 Data Superheroes have been announced. The alter egos of your hosts – Christopher Marland, Mike Droog and Sofia Pierini are honoured to be named!

In celebration, your challenge is to get the list of data superheroes into Snowflake!

Here is your started code:

import requests
from bs4 import BeautifulSoup
import pandas as pd
import re

# URL to scrape
url = "https://medium.com/snowflake/celebrating-the-2025-data-superheroes-the-innovators-trailblazers-and-changemakers-8e219e48587a"

response = requests.get(url)
response.raise_for_status()
html_content = response.text

# Parse the HTML content using BeautifulSoup
soup = BeautifulSoup(html_content, 'html.parser')

# Find the section starting from the "Americas" header
start_section = soup.find(lambda tag: tag.name in ["h1", "h2", "h3"] and "Americas" in tag.text)
content = ""

Here is where you need to get to:

Happy Deving!