Week 11 – Basic

This week FrostyFarms are looking to create a set of chained tasks – two to be exact! The farms have plenty of cows who produce milk for us, and from there, some of that milk will be converted into skim/skimmed milk. We want our data to be edited to reflect the fact that the fat percentage of the milk will determine how the data should look.

Skim milk goes through the process of fat reduction in a centrifuge, therefore, whole milky rows won’t need columns relating to that process, but the skim milky rows will.

Create a parent and child task that will perform different actions on the different rows of data depending on the fat percentage of the milk.

-- Set the database and schema
use database <insert_db_name_here>
use schema <insert_schema_name_here>

-- Create the stage that points at the data.
create stage week_11_frosty_stage
    url = 's3://frostyfridaychallenges/challenge_11/'
    file_format = <insert_csv_file_format;

-- Create the table as a CTAS statement.
create or replace table frosty_friday.challenges.week11 as
select m.$1 as milking_datetime,
        m.$2 as cow_number,
        m.$3 as fat_percentage,
        m.$4 as farm_code,
        m.$5 as centrifuge_start_time,
        m.$6 as centrifuge_end_time,
        m.$7 as centrifuge_kwph,
        m.$8 as centrifuge_electricity_used,
        m.$9 as centrifuge_processing_time,
        m.$10 as task_used
from @week_11_frosty_stage (file_format => '<insert_csv_file_format>', pattern => '.*milk_data.*[.]csv') m;

-- TASK 1: Remove all the centrifuge dates and centrifuge kwph and replace them with NULLs WHERE fat = 3. 
-- Add note to task_used.
create or replace task whole_milk_updates
    schedule = '1400 minutes'

-- TASK 2: Calculate centrifuge processing time (difference between start and end time) WHERE fat != 3. 
-- Add note to task_used.
create or replace task skim_milk_updates
    after frosty_friday.challenges.whole_milk_updates

-- Manually execute the task.
execute task whole_milk_updates;

-- Check that the data looks as it should.
select * from week11;

-- Check that the numbers are correct.
select task_used, count(*) as row_count from week11 group by task_used;


When you run the first query, the 3% data should look like

And the non-3% rows should look like
Whilst the second query should produce something like
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.

Psst… in the near-future we might be announcing something exciting for those at the top of the challenge leaderboard. 😉

17 responses to “Week 11 – Basic”

  1. ChrisHastie avatar

    Another week, another fun task. Thanks for putting these out each week, it’s a nice way to ease into Fridays!

    Solution URL:

    • Isabella avatar

      My (delayed) solution

      • Solution URL – https://github.com/isarenz/FrostyFridayChallenges/blob/main/FrostyFriday_Challenge_Week11.sql
    • dsmdavid avatar

      My approach using dbt:

      – Create the stage
      – Load the data
      – Use post_hook to
      – create or replace the tasks
      – execute the 1st task immediately
      – suspend the task
      – Add a view on top to summarise the results

      github: https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_11.sql

      • Solution URL – http://dvd-il-bucket-public.s3-website-eu-west-1.amazonaws.com/#!/model/model.frosty.challenge_11
    • CBoyles avatar

      Nice and easy one to learn about tasks if you haven’t used them before.

      • Solution URL – https://github.com/ChrisBBiztory/FrostyFriday/blob/main/Week_11.sql
    • Pekka Kanerva avatar
      Pekka Kanerva

      Good rehearsal on tasks! The good thing with these challenges is that they make you read through the documentation to get the details right!

      • Solution URL – https://github.com/pekka-kanerva/frosty-friday/blob/main/week11/ff-week11-solution
    • Atzmonky avatar

      A slight fashionable Delay… 🙂

      • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch11_Tasks.sql
    • Cesare Simonini avatar
      Cesare Simonini

      My solution

      • Solution URL – https://github.com/CSimonini/Frosty_Friday/blob/main/W11_Solution.sql
    • Jamie Laird avatar
      Jamie Laird

      This was a great intro to tasks!

      • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_11/ff_week_11.sql
    • zlzlzl2 avatar

      Another good chance of learning. Took a while to understand the task scheduling.

      • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF11_LZ.sql
    • canonicalized avatar

      Tasks are so cool!

      • Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK11.sql
    • arjan.loogman avatar

      Great exercise! Learnt a lot. Also learnt I need to learn a lot more, especially to use the documentation… :$

      • Solution URL – https://github.com/arjansnowflake/Frosty_Friday/blob/main/Week_11/week_11.sql
    • gerardnimbus avatar

      That’s my solution!!! 😉 Thanks for your work.

      • Solution URL – https://github.com/gerardPerello/frosty_friday_11/blob/main/frosty11.sql
    • chrisv avatar

      Basic solution

      • Solution URL – https://github.com/chrisvnimbus/frosty/blob/7c5ffb25b1b2746f5ff93c30f50ff22e78b2dd36/frosty_wk11
    • lucabalduzzi avatar

      Nice challenge, tricked by the suspending!

      • Solution URL – https://github.com/LucaBalduzzi/FrostyFriday/blob/main/Week11/Week11.sql
    • Non-deterministicNorman avatar

      enjoyed this one

      • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2011%20/tasks

    Leave a Reply