Frosty Friday

Snowflake Weekly Challenges

You are here: Home / Uncategorized / Week 3 – Basic

by on 31 Comments

Week 3 – Basic

In Week 1 we looked at ingesting S3 data, now it’s time to take that a step further. So this week we’ve got a short list of tasks for you all to do.

The basics aren’t earth-shattering but might cause you to scratch your head a bit once you start building the solution.

Frosty Friday Inc., your benevolent employer, has an S3 bucket that was filled with .csv data dumps. These dumps aren’t very complicated and all have the same style and contents. All of these files should be placed into a single table.

However, it might occur that some important data is uploaded as well, these files have a different naming scheme and need to be tracked. We need to have the metadata stored for reference in a separate table. You can recognize these files because of a file inside of the S3 bucket. This file, keywords.csv, contains all of the keywords that mark a file as important.

Objective:

Create a table that lists all the files in our stage that contain any of the keywords in the keywords.csv file.

The S3 bucket’s URI is: s3://frostyfridaychallenges/challenge_3/

Result:

Your result should look 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.

If you have any technical questions you’d like to pose to the community, you can ask here on our dedicated thread.

31 responses to “Week 3 – Basic”

  1. mvdvelden avatar
    mvdvelden

    Nice challenge. Took the chance to create the tables using templates based on the CSV files:

    https://github.com/marioveld/frosty_friday/tree/main/ffw3

    • Solution URL – https://github.com/marioveld/frosty_friday/tree/main/ffw3
  2. dzapp avatar
    dzapp

    🙂

    • Solution URL – https://github.com/darylkit/Frosty_Friday/blob/main/Week%203%20-%20Metadata%20Queries/metadata_Queries.sql
  3. tomo avatar
    tomo

    ‘LIKE ANY’ is Basic, but Useful.
    My code is in Japanese.

    • Solution URL – https://app.snowflake.com/oghptkp/et79103/w2bUPQqmFerC/query
    1. tomo avatar
      tomo

      ‘LIKE ANY’ is Basic, but Useful.
      My code is in Japanese.

      （One previous comment had the wrong Git URL.）

      • Solution URL – https://github.com/tomoWakamatsu/FrostyFriday/blob/main/FrostyFriday-Week3.sql
  4. marcoscatassi avatar
    marcoscatassi

    Not sure it is the most straightforward approach, but using scripting you can nicely filter the staged files out! I learned a lot 🙂

    • Solution URL – https://github.com/marco-scatassi-nimbus/Frosty-Friday/blob/main/week3/load_filtering_using_metadata.sql
  5. marcopastore avatar
    marcopastore

    Really quick solution, what do you think about?

    • Solution URL – https://github.com/marco-pastore-HH/Frosty-Friday-Challenges/blob/main/ff_challenge_3.sql
  6. gergana98 avatar
    gergana98

    This is my version of the solution for this task. I hope you find it helpful! ^^

    • Solution URL – https://github.com/GerganaAK/FrostyFridays/blob/main/Week%203%20%E2%80%93%20Basic
  7. behnazhd avatar
    behnazhd

    Create stage FROSTY_FRIDAY_3 for S3 data,
    list files,
    filter and count rows in new table;)

    • Solution URL – https://github.com/BehnazHeidaridehoee/frostyfriday/blob/main/week3/frosty_friday_week3
Older Comments
1 2

Leave a Reply

Reader Interactions

Comments

  2. 🙂

    • Solution URL - https://github.com/darylkit/Frosty_Friday/blob/main/Week%203%20-%20Metadata%20Queries/metadata_Queries.sql

  3. ‘LIKE ANY’ is Basic, but Useful.
    My code is in Japanese.

    • Solution URL - https://app.snowflake.com/oghptkp/et79103/w2bUPQqmFerC/query

    • ‘LIKE ANY’ is Basic, but Useful.
      My code is in Japanese.

      （One previous comment had the wrong Git URL.）

      • Solution URL - https://github.com/tomoWakamatsu/FrostyFriday/blob/main/FrostyFriday-Week3.sql

  4. Not sure it is the most straightforward approach, but using scripting you can nicely filter the staged files out! I learned a lot 🙂

    • Solution URL - https://github.com/marco-scatassi-nimbus/Frosty-Friday/blob/main/week3/load_filtering_using_metadata.sql

  5. Really quick solution, what do you think about?

    • Solution URL - https://github.com/marco-pastore-HH/Frosty-Friday-Challenges/blob/main/ff_challenge_3.sql

  6. This is my version of the solution for this task. I hope you find it helpful! ^^

    • Solution URL - https://github.com/GerganaAK/FrostyFridays/blob/main/Week%203%20%E2%80%93%20Basic

  7. Create stage FROSTY_FRIDAY_3 for S3 data,
    list files,
    filter and count rows in new table;)

    • Solution URL - https://github.com/BehnazHeidaridehoee/frostyfriday/blob/main/week3/frosty_friday_week3

Leave a Reply