Uncategorized

This week we’re asking for a combination of different skills and troubleshooting.

We’ve looked at distinct datasets on the Marketplace and we’re wondering if it can help us in the future if we were to combine the sets!

Your challenge for this week :
– Connect to the public dataset “COVID-19 Epidemiological Data” by Starschema (link)
– Connect to the public dataset “Daily Weather Data” by Knoema (link)
– For the last reported date (look for a flag) , for every country in the COVID set , return the maximum value of the Mean temperature (Fahrenheit) from the weather station in that country that has the ‘highest name’. We’re also looking for you to use RANK() to determine that specific weather station.
– Sorted by Country Name in Ascending order

The result that we’re looking for, looks something like this :

We know that we’re asking for a combination of skills in this weeks challenge but don’t be disheartened and work step-by-step!

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

This week we’ll be looking at a feature that has just hit Preview Feature – Open :
Sending Email Notifications.

We’re asking you to use this feature in conjunction with linked tasks to create a very small chain while tackling 2 different subjects.

The Challenge

The assignment for this week is two-fold :

• Create a scheduled task (task #1) that inserts the current timestamp into a table every 5 minutes
• Create a linked task (task #2) that sends an email that confirms the running of task #1 that contains the following information:
  “Task has successfully finished on <Account> which is deployed on <region> region at <timestamp>

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

To celebrate Women in Data and the upcoming Data+Women event, we’ve partnered up with Gaia Pometto , a data consultant in the Netherlands, to release a challenge for the Data+Women event. She’ll go through the challenge, live, at the event and you can find her on LinkedIn, Twitter and YouTube.

She also wanted to give a special mention to colleague Atzmon Ben Binyamin who collaborated with her on this challenge and was a great help throughout the process. You can find Atzmon on LinkedIn.

The Challenge

In order to prepare a future ingestion flow of data, your organization wants to get data from a weather API into Snowflake. At the end of the flow, it is expected to create a fact table to be used by consumers. 

Your colleague Atzmon has already pulled a sample of the weather data from the API and momentarily stored it in an s3 bucket. The s3 bucket contains a JSON file with hourly data for 7 days. 

Your Flow will consist of 3 layers (schemas) of data:
a. Landing zone (external stage, raw table – weather_raw)
b. Curated zone (raw data parsed table – weather_parsed)
c. Consumption zone (aggregated data table – weather_agg)

Objectives: 
1. Create a stage for the landing layer and copy the JSON. 
2. Parse the JSON and create a table – weather_parsed (in the curated zone). 
3. Create a table for consumption weather_agg (in the consumption zone).     
The table should have aggregates per day for: 
a. Temperature       
b. Wind speed       
c. Distinct ‘icon’ definitions for a day (e.g. [“cloudy”, “rain”])

Here is the link to the s3 bucket (hint: bucket = frostyfridaychallenges, path = challenge_25).
Good luck!

The solution should look something like this :

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

To celebrate the release of Snowpark for Python into GA and the release of the Streamlit integration into private preview, we’re going to do a doubled up Snowpark and Streamlit challenge!

Your challenge is to create an app that looks like the one below:

Requirements:

• You must allow the user to select between the following objects:
  • ‘None’
  • ‘Shares’
  • ‘Roles’
  • ‘Grants’
  • ‘Users’
  • ‘Warehouses’
  • ‘Databases’
  • ‘Schemas’
  • ‘Tables’
  • ‘Views’
• The app should display the FF logo
• It should display the version of Snowpark you are using
• It should display the results of show statements as a dataframe
• Schemas, tables, views and grants should be on the account level (i.e. not per database etc…)

Here’s the .png file for the logo, right click to download:

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

This week we’re looking into another feature that most starters haven’t touched before :
SnowSQL

“SnowSQL is the command line client for connecting to Snowflake to execute SQL queries and perform all DDL and DML operations, including loading data into and unloading data out of database tables.” (https://docs.snowflake.com/en/user-guide/snowsql.html)

This week, we’re going to ask you to use it for one of the features that it’s best known for however: the Bulk loading feature.

You’ve been asked to upload some files into a table and the Sales Manager said that “it’ll only take you about 2 minutes”.
So we’re pretty clear that it’ll take us at least 15 .

The challenge :

• Download the zip file containing the CSV files here
• Download and install SnowSQL on your machine from this page
• Connect to your Snowflake instance with SnowSQL
• Bulk load the files ending with a ‘1’ (data_batch_1-1.csv, data_batch_1-11.csv etc.)
• skip any erroneous file

The desired result should look like this :

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

The sales manager at Frosty Sales has no friends. Therefore, instead of giving each sales reps a list of cities, he decided to order them so that city-districts with an odd id belong to rep1, and even to rep2.

Your job is to create a secure view that will:

• Ensure rep1 can only see city-districts with an odd id
• Ensure rep2 can only see city-districts with an even id
• The ‘id’ fields is actually a randomly generated id (so we can fulfil secure view best practice )

-- File format to read the CSV
create or replace file format frosty_csv
    type = csv
    field_delimiter = ','
    field_optionally_enclosed_by = '"'
    skip_header = 1;
    
-- Creates stage to read the CSV
create or replace stage w22_frosty_stage
  url = 's3://frostyfridaychallenges/challenge_22/'
  file_format = frosty_csv;
  
-- Roles needed for challenge
create role rep1;
create role rep2;

-- Grant roles to self for testing
grant role rep1 to user <INSERT_YOUR_USERNAME>;
grant role rep2 to user <INSERT_YOUR_USERNAME>;

-- Enable warehouse usage. Assumes that `public` has access to the warehouse
grant role public to role rep1;
grant role public to role rep2;

-- Create the table from the CSV in S3
create table <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.week22 as
select t.$1::int id, t.$2::varchar(50) city, t.$3::int district from @w22_frosty_stage (pattern=>'.*sales_areas.*') t;

-- Code for creating the secure view
<ENTER_CODE_HERE>
    
-- Roles need DB access
grant usage on database <YOUR_DB_NAME> to role rep1;
grant usage on database <YOUR_DB_NAME> to role rep2;
-- And schema access
grant usage on schema <YOUR_DB_NAME>.challenges to role rep1;
grant usage on schema <YOUR_DB_NAME>.challenges to role rep2;
-- And usage of view
grant select on view <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities to role rep1;
grant select on view <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities to role rep2;

-- Get the result of queries
use role rep1
select * from <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities;

use role rep2
select * from <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities;

RESULTS

When rep1 queries, they should see this:

When rep2 queries, they should see this:

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

It might be our fault for having our new interns look at our data collection but not everyone at SuperHero Inc. is as data-savvy as we would like.
We’ve gotten reports that they’ve simply taken some old paper marking sheets and turned them into gathered data.
Our analysts are less than enthusiastic about the results and have asked us to clean up the data a little.

The superheroes are currently logged on a single row and a ++, + or – has been marked in the column for every superpower that they have.
The analysts are having issues counting the values and easily displaying the values and they’ve asked if we could create a new row for every hero’s power.

What the interns created looks like the following :

As you can see , every hero has 2 powers, noted as ‘++’ for the main power and + for a secondary power.

What the analysts are looking for is the following result :

The assignment for this week :

• Transform the data in such a way that every hero gets 1 row
• Put the ++ powers in the main_power column
• Put the + powers in the secondary_power column

create or replace table hero_powers (
hero_name VARCHAR(50),
flight VARCHAR(50),
laser_eyes VARCHAR(50),
invisibility VARCHAR(50),
invincibility VARCHAR(50),
psychic VARCHAR(50),
magic VARCHAR(50),
super_speed VARCHAR(50),
super_strength VARCHAR(50)
);
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Impossible Guard', '++', '-', '-', '-', '-', '-', '-', '+');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Clever Daggers', '-', '+', '-', '-', '-', '-', '-', '++');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Quick Jackal', '+', '-', '++', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Steel Spy', '-', '++', '-', '-', '+', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Agent Thundering Sage', '++', '+', '-', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Mister Unarmed Genius', '-', '-', '-', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Doctor Galactic Spectacle', '-', '-', '-', '++', '-', '-', '-', '+');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Master Rapid Illusionist', '-', '-', '-', '-', '++', '-', '+', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Galactic Gargoyle', '+', '-', '-', '-', '-', '-', '++', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Alley Cat', '-', '++', '-', '-', '-', '-', '-', '+');

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

If you know your SnowPro factoids, you’d know that when you CLONE an object, you can only replicate the grants on that object if that object is a table. But wouldn’t life be easier if that wasn’t the case? Well…make it so!

Your challenge is to create a stored procedure that not only creates a clone of a schema, but replicates all the grants on that schema. This should be able to accept a custom ‘AT’ or ‘BEFORE’ statement written by the user.

create or replace role frosty_role_one;
create or replace role frosty_role_two;
create or replace role frosty_role_three;

create or replace schema cold_lonely_schema;
create or replace table cold_lonely_schema.table_one (key int, value varchar);

grant all on schema cold_lonely_schema to frosty_role_one;
grant all on schema cold_lonely_schema to frosty_role_two;
grant all on schema cold_lonely_schema to frosty_role_three;

grant all on table cold_lonely_schema.table_one to frosty_role_one;
grant all on table cold_lonely_schema.table_one to frosty_role_two;
grant all on table cold_lonely_schema.table_one to frosty_role_three;


create or replace procedure schema_clone_with_copy_grants(database_name string, 
<code_here>


call schema_clone_with_copy_grants('frosty_friday', 
                               'cold_lonely_schema',
                               'frosty_friday',
                               'cold_lonely_clone', 
                               NULL);


select *
from table(information_schema.query_history_by_session())
order by start_time desc;

Pay attention to the parameters being passed into the function:

• database_name = this should be the name of the database of the original schema
• schema_name = this should be the name of the original schema
• target_database = this should be the database of the cloned schema
• cloned_schema_name = this should be the cloned schema’s name
• at_or_before_statement = your user should be able to provide a custom AT/BEFORE statement which will be appended to the CREATE …. CLONE statement. E.g:
  • ‘at (timestamp => to_timestamp_tz(’04/05/2013 01:02:03’, ‘mm/dd/yyyy hh24:mi:ss’));’
  • ‘before (statement => ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726′);’

With the last parameter, the code above passes a NULL. Bonus points for those who test that this part of their code is working!

RESULT

The first query the ‘call’ statement should produce the following:

The second should produce a result like this:

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