Snowflake Trial

Week 43 – Intermediate

Because we’re so excited about Python worksheets, Snowpark and all that jazz and because we just love giving you those pesky JSON parsing exercises – this week, we’ve combined the two!

create or replace table week43 as select
parse_json('{
  "company_name": "Superhero Staffing Inc.",
  "company_website": "https://www.superherostaffing.com",
  "location": {
    "address": "123 Hero Lane",
    "city": "Metropolis",
    "state": "Superstate",
    "zip": "98765",
    "country": "United Superlands"
  },
  "superheroes": [
    {
      "id": "1",
      "name": "Captain Incredible",
      "real_name": "John Smith",
      "powers": [
        "Super Strength",
        "Flight",
        "Invulnerability"
      ],
      "role": "CEO",
      "years_of_experience": 10
    },
    {
      "id": "2",
      "name": "Mystic Sorceress",
      "real_name": "Jane Doe",
      "powers": [
        "Magic",
        "Teleportation",
        "Telekinesis"
      ],
      "role": "CTO",
      "years_of_experience": 8
    },
    {
      "id": "3",
      "name": "Speedster",
      "real_name": "Jim Brown",
      "powers": [
        "Super Speed",
        "Time Manipulation",
        "Phasing"
      ],
      "role": "COO",
      "years_of_experience": 6
    },
    {
      "id": "4",
      "name": "Telepathic Titan",
      "real_name": "Sarah Johnson",
      "powers": [
        "Telepathy",
        "Mind Control",
        "Telekinesis"
      ],
      "role": "CFO",
      "years_of_experience": 9
    }
  ]
}
') as json;

Your job is to create a stored procedure using Python worksheets to parse out the data and make it look like this:

A shameless plug, but if you need a little help, I do cover JSON parsing using Snowpark in the below video:

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

3 responses to “Week 43 – Intermediate”

  1. dsmdavid avatar
    dsmdavid

    Thanks for the challenge. Still getting used to this. Finding the feedback cycle different with the worksheets

    • Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_43.py.info
  2. ChrisHastie avatar
    ChrisHastie

    This is the kind of challenge I like! I had some fun with this one, challenging myself to do it without converting the Snowflake dataframe to a Pandas one.

    From my understanding, converting the Snowflake dataframe into a pandas one will execute the SQL in Snowflake to retrieve the Snowflake dataframe, then store the whole thing in memory whilst you work with it using the pandas dataframe. As I have avoided using pandas, my procedure runs as if it is executing a single SQL command.

    This doesn’t really mean much when you are running the procedure inside Snowflake as a Python worksheet, but I would expect my non-pandas solution to have much better performance when executing on a local machine, as the work will be pushed down to Snowflake instead of executed locally

    • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  3. Chris B avatar
    Chris B

    Nice way to learn more about snowpark and manipulating data with python

    • Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_43.py

Leave a Reply