Archives for April 2025

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.