Week 37 – Intermediate

Recently, I’ve been training people for SnowPro Core exams and a lot of the feedback has been that unstructured data and various URLs has come up a lot. If you want to pass that exam, this challenge is probably the best challenge you can do!

We want you to create an external stage with a directory table, and query it. Each row should be accompanied by a:
– Scoped File URL: to give scoped access for internal uses
– Stage File URL: to give permanent access to a stage
– Pre-signed URL: to give external users access

create or replace storage integration week37_si
    type = external_stage
    storage_provider = 's3'
    storage_aws_role_arn = 'arn:aws:iam::184545621756:role/week37'
    enabled = true
    storage_allowed_locations = ('s3://frostyfridaychallenges/challenge_37/');

Use the url from storage_allowed_locations to create your external stage.

The final product should look like:

P.S. I personally think you should click on some of the URLs…maybe all of them.

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

5 responses to “Week 37 – Intermediate”

  1. ChrisHastie avatar

    A great idea to share the study guides as part of the challenge!

    • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  2. mat avatar

    A useful library and nice pictures too πŸ™‚

    • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w37.sql
  3. lukas.bogacz avatar

    Nice one!

    • Solution URL – https://github.com/lbinfolab/frosty-friday/blob/666f65d867f3a2a50829a83f2a017d351ab94fbf/week37.sql
  4. Jamie Laird avatar
    Jamie Laird

    Solution added!

    • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_37/ff_week_37.sql
  5. dsmdavid avatar

    This was fun. Neat way to provide resource files πŸ™‚

    • Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_37.sql

Leave a Reply