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'
as
    <insert_sql_here>


-- 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
as
    <insert_sql_here>


-- 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;

RESULT

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
    ChrisHastie

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

    Solution URL:
    https://github.com/ChrisHastieIW/Frosty-Friday/blob/main/Week%2011%20-%20Easy%20-%20Tasks/Week%2011%20-%20Easy%20-%20Tasks.sql

    • Isabella avatar
      Isabella

      My (delayed) solution

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

      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
      CBoyles

      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
      Atzmonky

      A slight fashionable Delay… 🙂

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

      My solution
      https://github.com/CSimonini/Frosty_Friday/blob/main/W11_Solution.sql

      • 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
      zlzlzl2

      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
      arjan.loogman

      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
      gerardnimbus

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

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

      Basic solution

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

      Nice challenge, tricked by the suspending!

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

      enjoyed this one

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

    Leave a Reply