Frosty Friday

Snowflake Weekly Challenges

You are here: Home / Uncategorized / Week 43 – Intermediate

by on 2 Comments

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

Reader Interactions

Comments

  1. 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. 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

Leave a Reply